SQL Server – Always On

An availability group supports a replicated environment for a discrete set of user databases, known as availability databases. You can create an availability group for high availability (HA) or for read-scale. An HA availability group is a group of databases that fail over together. A read-scale availability group is a group of databases that are copied to other instances of SQL Server for read-only workload.

An availability group supports one set of primary databases and one to eight sets of corresponding secondary databases. Secondary databases are not backups. Continue to back up your databases and their transaction logs on a regular basis.

Availability modes

Always On availability groups supports two availability modes-asynchronous-commit mode and synchronous-commit mode.

Asynchronous-commit mode

Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgment that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.

Synchronous-commit mode

Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.

Types of failover

A session between the primary replica and a secondary replica, the primary and secondary roles are potentially interchangeable in a process known as failover. During a failover the target secondary replica transitions to the primary role, becoming the new primary replica.

Three forms of failover exist-automatic, manual, and forced (with possible data loss). The form or forms of failover supported by a given secondary replica depends on its availability mode, and, for synchronous-commit mode, on the failover mode on the primary replica and target secondary replica, as follows.

  1. Synchronous-commit mode supports two forms of failover-planned manual failover and automatic failover, if the target secondary replica is currently synchronized with the primary replica. 
  • Planned manual failover (without data loss) – A manual failover occurs after a database administrator issues a failover command and causes a synchronized secondary replica to transition to the primary role (with guaranteed data protection) and the primary replica to transition to the secondary role. A manual failover requires that both the primary replica and the target secondary replica are running under synchronous-commit mode, and the secondary replica must already be synchronized.
  • Automatic failover (without data loss) – An automatic failover occurs in response to a failure that causes a synchronized secondary replica to transition to the primary role (with guaranteed data protection). When the former primary replica becomes available, it transitions to the secondary role. Automatic failover requires that both the primary replica and the target secondary replica are running under synchronous-commit mode with the failover mode set to “Automatic”. In addition, the secondary replica must already be synchronized, have WSFC quorum, and meet the conditions specified by the flexible failover policy of the availability group.
  1. Under asynchronous-commit mode, the only form of failover is forced manual failover (with possible data loss), typically called forced failover. Forced failover is considered a form of manual failover because it can only be initiated manually. Forced failover is a disaster recovery option. It is the only form of failover that is possible when the target secondary replica is not synchronized with the primary replica.

Legacy Architecture: Database Mirroring for High Availability and Log Shipping for Disaster Recovery

Prior to SQL Server 2012, one popular customer SQL Server deployment architecture involved using database mirroring for high availability within the primary data center, and using log shipping for cross-data center disaster recovery. For this solution, database mirroring is configured within the primary data center. To achieve automatic failover, synchronous database mirroring with a witness (a third SQL Server instance) is configured. When zero data loss is required, the database mirroring high-safety mode (synchronous) setting is enabled to help ensure zero data loss between the two servers located in the primary data center. To improve database availability within the primary data center, a third SQL Server instance is configured to act as a witness to enable automatic failover between the database mirroring partners.

If a primary data center outage renders both database mirroring partner instances unavailable, log shipping is used for disaster recovery. Log shipping involves ongoing transaction log backups of the principal database. These transaction log backups are then copied to a SQL Server instance in the disaster recovery data center. Incoming transaction log backups are restored in sequence on an ongoing basis. You could also choose to configure log shipping for read-only workloads, but with the drawback that read-only connections must be disconnected before incoming transaction log backups are applied. Figure 1 shows a representation of this solution architecture.

Always On
Database mirroring for high availability and log shipping for disaster recovery

AlwaysOn Availability Groups for High Availability and Disaster Recovery

AlwaysOn Availability Groups can be used to replace the previously described database mirroring and log shipping solution. Using availability groups for HA and DR provides the following advantages: Figure 2 shows the HA and DR solution using availability groups.

Alwayson
Using availability groups for high availability and disaster recovery
  • You can group more than one user database into a single unit of failover. In contrast, database mirroring permits only one user database as a unit of failover.
  • Availability groups multiple secondaries enable the user to unify the HA and DR solution into one technology, instead of using multiple technologies used in the earlier solution.
  • Secondary replicas can also be configured to permit read-only workloads to obtain close to real-time data from them. Unlike with log shipping, ongoing read-only connections to the secondary replicas do not need to be disconnected in order to see ongoing data modifications against the primary replica. Secondary replicas can also be used to offload full database and transaction log backup operations.
  • Availability groups and the associated availability group listener support automatic client redirection to the primary replica or redirection to available readable secondaries. Availability group listeners remove the need to designate a failover partner in the client connection string.

As Figure 2 indicates, the three nodes, each running an instance of SQL Server, participate in a single Windows Server Failover Cluster (WSFC) that spans two data centers. Also, it is important to note that this is a non-shared solution, and the nodes don’t share any storage with another node. Each node is running an instance of SQL Server and has its own copy of the data.

Note: Figure 2 illustrates a simple scenario with two data centers: the primary data center hosts two replicas, and the DR data center hosts one replica. The architecture allows for variations to this topology using multiple data centers as well as multiple replicas (up to five). The discussion in this white paper focuses on the topology shown in Figure 2; however, the general concepts apply to the other variations as well.

Conclusion

SQL Server 2012 AlwaysOn provides multiple options for building high availability (HA) and disaster recovery (DR) solution for your application. This white paper describes a solution that uses availability groups for HA and DR. This solution is purely a non-shared-storage solution, because each instance of SQL Server in the topology has its own copy of data, and does not need to share storage. You can use this solution to replace legacy topologies that use database mirroring and log shipping.

Successful deployment of such an HA/DR solution involves not just the DBA team, but close collaboration between the DBA team, Windows Server administration team, and the networking team in the IT organization. Cross-education of skills is very valuable when you deploy the HA/DR solution.

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply