Provisioning a SQL Server AG Virtual Database

Prerequisites

The following prerequisites must be completed before provisioning the SQL Server data source with Always-On AG configuration: 

  • Make sure the Primary replica of the AG is reachable from the Delphix Continuous Data Engine. 

  • Make sure users have access to the command line interface (CLI) on the Delphix Continuous Data Engine.

  • Make sure users have the sysadmin privileges on the SQL instance hosting AG replicas, from the cluster nodes added to the Delphix Continuous Data Engine. 

  • Make sure that the Delphix engine has ample storage (zfs) to accommodate AG database(s). 

Procedure

For provisioning steps, refer to the CLI cookbook: provisioning the SQL Server AG VDB page

  • Support for CDC, TDE and other VDB config parameters is not available for AG virtual source. 

  • Hooks except the ConfigureClone hook are not supported for an AG virtual source. 

  • Extended properties are not available for the AG VDBs as compared to standalone VDBs. 

  • Currently, the SQL Server availability group on target cluster environments or more specifically provisioning of VDBs into SQL Server availability groups is only available via the Command Line Interface (CLI).

The AG virtual database creation will be successful as long as Delphix Engine succeeds in creating the primary replica and joining it to AG, regardless of any failures encountered in the creation of secondary replicas. However, the replica database containers created by Delphix will depend on the number of SQL Server AG replicas which are healthy and accessible to Delphix. This behaviour is also true for other AG VDB operations like refresh and enable which re-create the database.

Customise Provisioning of AG VDB

For a database to join the SQL Server availability group, it requires taking a Full & Transaction log backup before it can join AG.

Delphix Engine provides the following parameters:

Parameter

Description

backupBased

When set to true, Delphix performs all VDB operations — Provision, Refresh, and Enable — using the Backup and Restore mechanism. By default, this property is set to true.

When set to false, Delphix performs all operations in fast mode, i.e. without performing Backup and Restore.

Prerequisites for Fast Provision and Fast Refresh operations:

  • These operations are supported from snapshots taken from databases running on SQL Server 2016 or later.

  • The snapshot must be from either a Source-based dSource or a Staging Push (Restoring mode) dSource.

  • At the time the snapshot was taken, the corresponding database must have been in the FULL recovery model.

Fast Enable, on the other hand, is not subject to these prerequisites. For more details on the enable operation behaviour, please refer to the Enabling and Disabling the SQL Server AG virtual database section.

Although, the backupBased attribute is set to true by default (as this follows SQL Server’s standard backup-and-restore workflow and has no restrictions like Fast Provision or Fast Refresh), users are encouraged to set it as false whenever applicable.

When the required conditions for Fast Operations are met, setting backupBased=false enables significantly faster Provision and Refresh operations by eliminating the backup and restore steps.
backupLocation It enables the user to provide an external shared path for taking database backups (required for joining AG).

Subject to certain conditions, users can bypass taking backups of the primary database of an AG VDB during Delphix operations. This behaviour is referred to as Fast Operations. By default, the Delphix Engine does not perform Fast Operations.

To activate Fast Operations, set the following attribute during VDB provisioning from CLI -

Copy 
delphix database provision *> set agProvisionConfig.backupBased=false

If Fast Enable is deactivated, then AG VDB can be customized with an external shared path for storing database backups via the backupLocation parameter.

Copy 
delphix database provision *> set agProvisionConfig.backupLocation=<external_shared_path>

If this value is not provided, Delphix Engine uses a temporary directory located inside AG VDB mount paths on replica hosts. If this value is provided, hosts need full access to this location.

Additionally, the storage requirement for this external shared location dictates that the free space available should be equal to at least the size of the source database. For example, If the source database is 10 TB, then at least 10TB space should be available in the external shared location to accommodate the backup taken.
In case the backupBased parameter is set to false for a single replica AG then Disable/Enable is not supported because we need Primary replica and at least one secondary replica of the SQL Server AG with Synchronous-Commit Availability mode

How to choose the correct value of the backupBased option for your AG virtual database?

Choose BackupBased = true if

Choose BackupBased = false if

Time is not a constraint during Delphix operations like Upgrade, Replication, Disable/Enable.

Delphix operations like Upgrade, Replication, Provision, Refresh and Disable/Enable should finish quickly.

Data consistency in an AG virtual database is of utmost importance.

Data consistency in an AG virtual database is not the top priority.

In most cases, Delphix will provide data consistency. For more details check the Best Practices for AG VDB Operations section on this page.

All AG replicas need to be in asynchronous commit availability mode.

It can be made sure that at least one secondary AG replica is present in synchronous commit availability mode.

SQL Server AG virtual database runtime attributes

The following runtime attributes are available for AG virtual source databases via the CLI:

Attribute name

Description

runtime.status

Indicates the runtime status of the AG virtual database.

runtime.healthyPrimaryHost

Indicates the host address on which a healthy primary replica database is present.

runtime.healthySecondaryHost

Indicates the host address on which a healthy secondary replica database is present.

runtime.lastFetchedDurationInMinutes

Indicates the time elapsed since the healthy primary replica host and healthy secondary replica host attributes were updated.

Whenever the AG virtual database is not in RUNNING status, a critical fault “AG_VDB_NOT_OPERATIONAL” is raised on the VDB. This fault needs urgent attention, and it should be resolved as soon as possible because some AG virtual database operations depend on the VDB status
In case a critical fault is present on the AG virtual database, do not rely on these runtime attributes for diagnostic or any other purpose. These attributes are provided for informational purposes only and are not always up to date. 

Best practices for SQL Server AG VDB operations  

  • Make sure that all critical faults on the AG virtual database are resolved 

  • In case of a Failover event on AG, make sure the correct primary replica host is visible in the runtime attribute.  

  • In case backupBased is set to false, then before performing disable/enable, upgrade or replication (this is not required for other operations of AG VDB), it is best to ensure the following:

    • At least one secondary replica of the SQL Server Avalability group, which hosts a replica database container on the Delphix engine, is in SYNCHRONIZED state.

    • The replica database container on the Delphix engine corresponding to this secondary replica, also reflects the same state i.e. SYNCHRONIZED. This can be verified by checking the timeflow properties of the replica database container from CLI. 

    To check the Availability Group Replica role and the replica synchronization state, use the following SQL query.
    Copy
    USE master; 
    SET NOCOUNT ON; 
    SELECT rs.is_primary_replica, rs.synchronization_state_desc 
    FROM sys.dm_hadr_database_replica_states AS rs 
    WHERE rs.database_id = DB_ID('database_name')