Inheritance of database properties during SQL Server VDB operations (KBA6278)
KBA
KBA# 6278
Issue
Following a VDB Provision, Refresh, or Rewind operation, several database properties are inherited from the model database of the Target SQL Server instance.
If these properties have been customized on your Source database, some applications or queries may behave differently when run on the VDB compared to the Source database.
Background
To facilitate VDB operations, the Delphix Engine temporarily creates an empty database on the Target SQL Server instance.
As a result, several database properties are inherited from the model database. These inherited properties become part of the database configuration, and persist even after the database is re-connected with the VDB's data files.
Applicable Delphix Versions
- Click here to view the versions of the Delphix engine to which this article applies
-
Date Release Nov 2023 17.0.0.0 Oct 2023 16.0.0.0 Sep 2023 15.0.0.0 Aug 2023 14.0.0.0 July 2023 13.0.0.0 June 2023 12.0.0.0 May 2023 11.0.0.0 Apr 13, 2023 10.0.0.0 Mar 13, 2023 | Mar 20, 2023 9.0.0.0 | 9.0.0.1 Feb 13, 2023 8.0.0.0 Jan 12, 2023 7.0.0.0 Releases Prior to 2023 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, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0, 6.0.17.0, 6.0.17.1, 6.0.17.2
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
Affected database properties
Available database properties differ depending on the SQL Server version, but may include:
- COMPATIBILITY_LEVEL (see note below)
- ANSI_NULL_DEFAULT
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- ARITHABORT
- AUTO_CLOSE
- AUTO_SHRINK
- AUTO_UPDATE_STATISTICS
- CURSOR_CLOSE_ON_COMMIT
- CURSOR_DEFAULT
- CONCAT_NULL_YIELDS_NULL
- NUMERIC_ROUNDABORT
- QUOTED_IDENTIFIER
- RECURSIVE_TRIGGERS
- AUTO_UPDATE_STATISTICS_ASYNC
- DATE_CORRELATION_OPTIMIZATION
- ALLOW_SNAPSHOT_ISOLATION
- PARAMETERIZATION
- READ_COMMITTED_SNAPSHOT
- PAGE_VERIFY
- TARGET_RECOVERY_TIME
- DELAYED_DURABILITY
- MAXDOP
- LEGACY_CARDINALITY_ESTIMATION
- PARAMETER_SNIFFING
- QUERY_OPTIMIZER_HOTFIXES
Some VDB Parameters, such as DB_CHAINING and TRUSTWORTHY, are always disabled when databases are created or provisioned. For more information, see the Microsoft document Manage Metadata When Making a Database Available on Another Server.
Resolution
To set a parameter for all VDBs on an instance
To automatically apply a specific database setting to all newly provisioned VDBs on a target SQL Server instance, you can apply it to the model database on that instance.
The following SQL will automatically apply the ALLOW_SNAPSHOT_ISOLATION setting to all VDB Provision and Refresh operations on that instance, as well as any manually created databases.
ALTER DATABASE [model] SET ALLOW_SNAPSHOT_ISOLATION ON GO
To set a parameter for a single VDB
To automatically apply a specific database setting to a single VDB, you can use Hook Scripts. Example scripts that set the VDB Parameter READ_COMMITTED_SNAPSHOT are available in our KB Article Executing SQL in a PowerShell Hook Script (KBA1370).
These hooks scripts can be further extended to suit your needs, such as maintaining the correct VDB settings in a repository, or using Hook Templates for different databases with different settings.
To ensure that settings are consistently applied each time a VDB is Refreshed or Rewound, the hook script should be attached to the following VDB hooks:
- Configure Clone
- Post Rollback
An example hook script that will change the AUTO_UPDATE_STATISTICS parameter is shown below:
$ErrorActionPreference = "Stop"; Invoke-Sqlcmd -Database master -Query "ALTER DATABASE [$env:VDB_DATABASE_NAME] SET AUTO_UPDATE_STATISTICS OFF WITH ROLLBACK IMMEDIATE;" -ServerInstance "$env:VDB_INSTANCE_HOST,$env:VDB_INSTANCE_PORT"
Examples of other hook scripts, including a more detailed example script that sets the VDB Parameter READ_COMMITTED_SNAPSHOT, are available in the KB Article Executing SQL in a PowerShell Hook Script (KBA1370).
These hooks scripts can be further extended to suit your needs, such as maintaining the correct VDB settings in a repository or using Hook Templates for different databases with different settings.
Related Articles
The following articles may provide more information or related information to this article:
- Manage Metadata When Making a Database Available on Another Server from Microsoft's SQL Server documentation
- Executing SQL in a PowerShell Hook Script (KBA1370)