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 at least one secondary replica of the SQL Server AG is present with Synchronous-Commit Availability mode enabled when the backupBased parameter is set to false.
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).
Make sure that no AG replica is hosted on the SQL Server failover cluster instance.

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 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 sources present in MDS will depend on the number of AG replicas accessible to Delphix.

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. Currently, Delphix provides a way (subject to certain conditions) to bypass this requirement while performing the Enable operation on the AG virtual source. We call this “Fast Enable”. Delphix does not perform “Fast Enable” by default.

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 by providing an external shared path for storing database backups (required for joining AG). If this value is not provided, Delphix 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.

In case the backupBased parameter is set to false for a single replica AG then Disable/Enable is not supported because we need at least one secondary replica of the SQL Server AG with Synchronous-Commit Availability mode enabled.

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. Note – 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.

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.

SQL Server AG virtual database runtime attributes

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

All runtime attributes can be set via CLI because for the SQL Server AG virtual database there is no UI support.

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.
Make sure that at least one secondary replica of the AG virtual database is in the SYNCHRONIZED state in the SQL server and the same is reflected in the engine metadata when backupBased is set to false.
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')
```