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.

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:

backupBased	It is set to “true” by default. When set to “true”, Delphix performs Backup and Restore operation during all VDB operations. When set to “false”, Delphix performs all operations except “Enable” same as before (i.e. using backup and restore) but “Enable” operation or rather “Fast Enable” is performed without performing backup and restore
backupLocation	It enables the user to provide an external shared path for taking database backups (required for joining AG).

Subject to certain conditions, user can bypass taking backups of the primary database of AG VDB while performing the Enable operation, this is referred to as Fast Enable. By default, Delphix Engine does not perform Fast Enable.

To activate “Fast Enable”, 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, 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')
```