Understanding SQL Server AG virtual database

Managing Microsoft SQL Always-On configurations is complex, particularly for large databases over 1 TB. To solve this challenge of creating non-production copies of Availability Group (AG) databases regularly, Delphix now provides the Availability Group (AG) as a Target feature to enable users to efficiently create virtual databases within Always-On environments. This feature automates the database creation process for SQL Availability databases which not only eliminates the tedious effort and errors associated with manual work but also minimizes the storage requirements.

Before we understand how Delphix works with SQL Server Availability Groups, let’s first understand how availability groups work in SQL Server.

SQL Server AG Overview

SQL Server Always On Availability Groups (AG) is a high-availability solution designed for critical databases, built on top of Windows Server Failover Clustering (WSFC). WSFC provides the underlying infrastructure that connects multiple Windows Server nodes into a cluster.

WSFC or Windows Server Failover Clustering

WSFC is the underlying clustering technology used by Windows Server to manage resources (such as SQL Server AGs) across multiple physical or virtual servers (nodes).

It provides:

  • Heartbeat monitoring: Detects node failures.

  • Cluster-aware failover: Ensures resources like SQL AGs are moved to a healthy node.

  • Quorum: Maintains cluster health and ensures only one active cluster exists at a time.

Without WSFC, SQL Server AGs cannot function, as AGs depend on WSFC for health monitoring and failover coordination. For more information, refer to the Windows Server Failover Clustering with SQL Server documentation.

SQL Server Availability Group (AG) or Always On availability group

SQL Server Always On Availability Group is a high-availability and disaster-recovery solution introduced by Microsoft to ensure data availability and minimize downtime for critical databases. It works by replicating one or more databases across a group of SQL Server instances (replicas), which are hosted on different nodes of a Windows Failover Cluster. It requires a listener, configured during AG setup, which provides a consistent network name and IP for clients to connect to, regardless of which replica is active. For more information, refer to the Overview of SQL Server Availability Group documentation.wfsc architecture

 

Key Features:

  • Supports automatic failover, manual failover, or forced failover.

  • Each AG can contain multiple databases (called availability databases).

  • There are two types of replicas:

    • Primary replica: Accepts read-write connections.

    • Secondary replicas: Can be used for read-only queries, backups, etc.

 

Related Links: