Inheritance of Database Properties During SQL Server VDB Operations (KBA6278)

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.

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, disabled/enabled, or provisioned. For more information, see the Microsoft document Manage Metadata When Making a Database Available on Another Server.

 

Note:

The COMPATIBILITY_LEVEL setting of a VDB is inherited from the model database of the target SQL Server instance during initial provisioning, but will revert to the setting of the parent container the first time the VDB is disabled and enabled. To ensure that a specific value is used, it will be necessary to set it manually or using hook scripts as described below.

 

Resolution

 

Tip:

Continuous Data Engine releases 9.0 and later allow the setting of the READ_COMMITTED_SNAPSHOT property to be specified during VDB provisioning, as part of a VDB Configuration Template. For more information on this behavior, please see our documents New Features and Configuration Settings for SQL Server Virtual Databases.

 

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.

 

---

 

 

---