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.

Alternatively, you may see difference in Error and Error Code:

Error: Failed to set virtual database "VDB_2" in read-write mode

Error Code: exception.db.mssqlvdb.vdb.set.database.read_write.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.
...

It is caused by the same issue - the FILESTREAM DIRECTORY_NAME is not unique.

 

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, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.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_NAMEoption during provisioning. As a result, provisioning multiple VDBs using the FileTable feature will cause an error unless additional steps are taken.

In order to find out which databases may already be utilizing the same directory, run the following query:

SELECT DB_NAME(database_id) as DatabaseName, non_transacted_access, non_transacted_access_desc, directory_name 
FROM sys.database_filestream_options
WHERE directory_name IS NOT NULL

In the results, look at the "directory_name" column for a database that is already utilizing the directory. Using the above error as an example, any database with "SourceDatabase_FileShare" and "non_transacted_access" equal to zero would be problematic. In the example shown, the parent VDB, VDB_1, does have "non_transacted_access" = 2, and has the directory_name of "SourceDatabase_FileShare". The child VDB, VDB_2 has the same directory_name - this presents a conflict on the SQL Server instance. Also, notice the child VDB has the  "non_transacted_access" equal to zero because of the Provision failure. The database is not activated for multi-user access since the filestream ability is not working.

DatabaseName non_transacted_access non_transacted_access_desc directory_name
VDB_1 2 FULL SourceDatabase_FileShare
VDB_2 0 OFF SourceDatabase_FileShare

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 on the same SQL Server instance, 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.  In the event the child VDB was provisioned and failed, delete it and proceed with the following steps.

To prevent a single VDB Provision or Refresh from failing, temporarily change the file sharing directory used by your existing (parent) VDB using the following command, with the parent VDB name of "VDB_1". The DIRECTORY_NAME depends on the source database configuration:

ALTER DATABASE [VDB_1] 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 VDBs, but run the command on the new VDB first or you will receive an error about a conflict with the original DIRECTORY_NAME used by the parent. Even though DIRECTORY_NAME is changed in the step above on the parent VDB, VDB_1, the snapshot you will use to Provision from will still contain the DIRECTORY_NAME of 'ORIGINAL_FILETABLE_DIR', and results of the filestream_database_options query show this. During runtime, the parent VDB, VDB_1 has DIRECTORY_NAME of 'TEMP_FILETABLE_DIR':

DatabaseName non_transacted_access non_transacted_access_desc directory_name
VDB_1 2 FULL TEMP_FILETABLE_DIR
VDB_2 2 FULL ORIGINAL_FILETABLE_DIR

In order to revert VDB_1 to use ORIGINAL_FILETABLE_DIR run the following two ALTER DATABASE commands in the order shown as you need to change the child VDB, VDB_2, first in order to prevent any conflict messages from being returned:

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

ALTER DATABASE [VDB_1] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'ORIGINAL_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: