VDB Provision Fails When Using Contained Database in SQL Server (KBA1758)
KBA
KBA#1758Applicable Delphix Versions
This article applies to the following versions of the Delphix Engine:
|
Major Release |
Sub Releases |
| 5.2 | 5.2.2.0, 5.2.2.1, 5.2.3.0 |
Troubleshooting Container Database
If a Customer is using contained databases in SQL server and they provision a vDB, this will faill with the Error:
The containment state of database 'Vdb0_4KC' does not match the value in master. Contained database functionality will not work as expected. Detach and re-attach the database to correct the database state in master.
Confirm the customer is using contained databases.
Resolution
Workaround: A detach and reattach at the SQL Server database level will resolve the Issue and mark the database as partially contained (expected outcome).
Alternative Workarounds: The following Configure Clone hook Script can be used to remove the manual process of the detach and attach at SQL Server level
# Create variables and assign the environment variables
$INSTANCE_HOST = $env:VDB_INSTANCE_HOST
$INSTANCE_PORT = $env:VDB_INSTANCE_PORT
$INSTANCE_NAME = $env:VDB_INSTANCE_NAME
$DATABASE_NAME = $env:VDB_DATABASE_NAME
function die {
"Error: $($args[0])"
exit 1
}
function verifySuccess {
if (!$?) {
die "$($args[0])"
}
}
sqlcmd -b -E -d master -S "tcp:$INSTANCE_HOST\$INSTANCE_NAME,$INSTANCE_PORT" -q "DECLARE @physical_name nvarchar(300);
SELECT @physical_name = physical_name FROM sys.master_files WHERE database_id = DB_ID('$DATABASE_NAME') and file_id = 1;
ALTER DATABASE [$DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db [$DATABASE_NAME];
EXEC ('CREATE DATABASE [$DATABASE_NAME] ON (FILENAME = ''' + @physical_name + ''') For ATTACH;');
ALTER DATABASE [$DATABASE_NAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE;"
VerifySuccess "sqlcmd failed"