Skip to main content
Delphix

vDB Provision fails when using contained database in SQL Server

 

Applicable Delphix Versions

 

Major Release

All Sub Releases

5.2 5.2.2.0, 5.2.2.1, 5.2.3.0

Issue

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.

 

Troubleshooting

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"

Additional Information

BUG DLPX-58002 Support for contained database in MSSQL Server has been raised so the above is not required going forward.