Skip to main content
Delphix

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
Major Release All Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.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

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Affected database properties

Available database properties differ depending on the SQL Server version, but may include:

  • COMPATIBILITY_LEVEL
  • 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, the hook script should be attached to the following VDB hooks:

  • Configure Clone
  • Post Rollback

 


 

 


Related Articles

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