Skip to main content
Delphix

Resolving "FILESTREAM DIRECTORY_NAME" Errors During VDB Provision and Refresh Operations (KBA7189)

 

KBA

KBA# 7189

 

Issue

When using the FileTable feature of SQL Server, you may encounter the following error when provisioning two VDBs from the same dSource to the same SQL Server instance:

Error: Recovery failed for virtual database "VDB_2"

Error Code: exception.db.mssqlvdb.vdb.restore.failed

Command Output:

...
Msg 33415, Level 16, State 4, Server VDBHOST\SQL2016, Line 1
FILESTREAM DIRECTORY_NAME 'SourceDatabase_FileShare' attempting to be set on database 'VDB_2' is not unique in this SQL Server instance. Provide a unique value for the database option FILESTREAM DIRECTORY_NAME to enable non-transacted access.
Msg 5069, Level 16, State 1, Server VDBHOST\SQL2016, Line 1
ALTER DATABASE statement failed.
...

This error may also occur when provisioning a VDB from a snapshot of another VDB using the same instance.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

Background

When configured with "non-transactional access", SQL Server allows access to files from FileTable-enabled databases via an SMB (Windows File Sharing) share.

The name of the share is configured at a per-database level, and must be unique to the SQL Server instance.

The Delphix Engine does not currently track or modify the FILESTREAM DIRECTORY_NAME option during provisioning. As a result, provisioning multiple VDBs using the FileTable feature will cause an error unless additional steps are taken.

Resolution

Two resolutions are available for this issue. Using a hook operation will generally be preferable, but if provisioning a VDB from another VDB snapshot the manual resolution must be used.

Manual resolution

This issue can be manually resolved when:

  • Provisioning two FileTable-enabled VDBs to the same SQL Server instance
  • Provisioning a VDB from a snapshot of another VDB using the same SQL Server instance
Note

Note:

The manual resolution steps are not persistent and must be used before and after each time a FileTable VDB is Provisioned or Refreshed.

 

The first step must be done before the second VDB is provisioned.

To prevent a single VDB Provision or Refresh from failing, temporarily change the file sharing directory used by your existing VDB using the following command:

ALTER DATABASE [MyOldVDB] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'TEMP_FILETABLE_DIR')

After provisioning is successful, you can adjust the FileTable directories as required for the old and new databases:

ALTER DATABASE [MyOldVDB] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'ORIGINAL_FILETABLE_DIR');
GO
ALTER DATABASE [MyNewVDB] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'NEW_FILETABLE_DIR');
GO

Resolving with Hook Operations

Hook operations can be used to resolve this issue when provisioning two FileTable-enabled VDBs to the same SQL Server instance

The following example Configure Clone hook script will automatically change the FileTable directory name for the provisioned VDB to match the name of the VDB. If this is still not unique within the SQL Server instance, you may wish to extend this to include a prefix or random characters.

For more information on Hook Scripts, see our document Hook Scripts for Automation and Customization.

 

Warning

Disclaimer:

This sample code is offered for illustration purposes only, and does not carry any warranty or guarantee. Employing copies of this code, in whole or in part, indicates acceptance of all risks.

 

function die {
    "Error: $($args[0])"
    exit 1
}

function verifySuccess {
    if (!$?) {
        die "$($args[0])"
    }
}

$vDatabaseName = $env:VDB_DATABASE_NAME
$vInstance = $env:VDB_INSTANCE_NAME
$vInstancePort = $env:VDB_INSTANCE_PORT
$vInstanceHost = $env:VDB_INSTANCE_HOST

$vConnectionString = "`"tcp:" + $vInstanceHost + "\" + $vInstance + "," + $vInstancePort + "`""

$vQueryString = "ALTER DATABASE [" + $vDatabaseName + "] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = '" + $vDatabaseName + "')"

echo $vQueryString

sqlcmd -b -E -S $vConnectionString -d $vDatabaseName -Q $vQueryString;

VerifySuccess "sqlcmd failed to change filetable directory name"
exit 0;

As long as each VDB is provisioned to the instance using this Hook Operation, and the FileTable directory name of the dSource is not already in use, this should allow Provision and Refresh operations to complete without conflicts.

 

 


Related Articles

The following articles may provide more information or related information to this article: