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
Notes -
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 -
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. |
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
Caution: 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.
In order to check the Availability Group Replica role and the replica synchronization state, use the following SQL query.
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')