SQL SERVER 2012 HA/DR

Mission-critical systems require high availability as a core system attribute. The overwhelming majority of mission-critical systems in production today leverage some sort of Relational Database System (RDBMS), and often assuring the availability of these systems has been seen as a costly proposition. Fortunately, SQL Server 2012 offers organizations high availability capabilities that are built into the platform and provide a cost effective solution for those looking to operate with reliability in the five nines.

AlwaysOn Failover Cluster Instances

AlwaysOn Failover Cluster Instances employs Windows Server Failover Clustering (WSFC) infrastructure in order to provide an enterprise high availability solution. Redundancy is achieved at the server-instance level—a failover cluster instance (FCI), which is a SQL Server instance installed across Windows Server Failover Clustering (WSFC) nodes. An FCI appears to be an instance of SQL Server running on a single network node, but the FCI will automatically failover from one WSFC node to another in the event that the current node becomes unavailable. Applications failovers are automated, and clients do not have to be reconfigured during failovers in order to access the new node. Disaster recovery capabilities can be enhanced by locating each WSFC node in a separate datacenter. For high availability when employing a third-party shared disk solution (a SAN or Fibre Channel), Microsoft recommends AlwaysOn Failover Cluster Instances. However, there are several requirements to keep in mind when employing AlwaysOn Failover Cluster Instances. Some of these include:

• AlwaysOn Failover Cluster Instances requires a third-party shared disk solution, and storage level replication middleware is also required when an installation of AlwaysOn Failover Cluster Instances is configured for disaster recovery across production and disaster recovery sites.
• AlwaysOn Failover Cluster Instances requires Windows Server Failover Clustering, which is available only the Enterprise or above additions of Windows Server 2008 and Windows Server 2008 R2, but is available in all editions of Windows Server 2012 and Windows Server 2012 R2.
• Each server participating in a failover cluster must not be a domain controller.

An overview of the various features of AlwaysOn Failover Cluster Instances can be found here. Prerequisites as for configuring AlwaysOn Failover Cluster Instances can be found here, while step-by step instructions for setting up AlwaysOn Failover Cluster Instances can be found here. AlwaysOn Failover Cluster Instances natively provides high availability, but further configuration is required to support disaster recovery, especially when the production and disaster recovery sites are on different network subnets. An installation of AlwaysOn Failover Cluster Instances that provides high availability as well as disaster recovery capabilities across subnets consists of two or more Windows Server Failover Clustering (WSFC) nodes operating in a multi-subnet failover cluster. In a multi-subnet failover cluster, failover cluster nodes are connected to a different subnet or different set of subnets. For disaster recovery purposes, these subnets would represent two geographically dispersed sites. Because there is no shared storage that all the nodes can access, data must be replicated between the data storage located on the multiple subnets. Data replication ensures there is more than one copy of the data available. Therefore, a multi-subnet failover cluster provides a disaster recovery solution in addition to high availability. A complete overview of multi-subnet failover clustering can be found here. .

AlwaysOn Availability Groups

AlwaysOn Availability Groups is an enterprise high availability solution and is a new feature within SQL Server 2012. An availability group provides automated and manual failover support for a configured set of availability databases that fail over together. An availability group consists of one set of read-write primary databases and one to five sets of secondary databases which can be made available for read-only access in reporting applications and/or some backup operations. Fast application failover is automated as applications are configured to connect to a virtual endpoint, instead of being manually connected with secondary databases in the event of failover. Additionally, automatic page repair provides data protection by attempting to repair certain types of I/O errors. Disaster recovery capabilities can be enhanced when primary and secondary databases are located across separate datacenters. Microsoft recommends AlwaysOn Availability Groups as the high availability solution for organizations that do not employ a SAN for shared storage. AlwaysOn Availability Groups can be viewed as the next evolution of database mirroring and offers advantages over database mirroring, including automatic application failover capabilities, the ability to failover to multiple secondary databases and the ability use secondary databases for read-only backup and reporting application purposes. There are several requirements to keep in mind when employing AlwaysOn Availability Groups. Some of these include:

• AlwaysOn Availability Groups is a feature of the Enterprise edition of SQL Server 2012, and is not available with the Standard edition.
• AlwaysOn Availability Groups requires Windows Server Failover Clustering, which is available only the Enterprise or above additions of Windows Server 2008 and Windows Server 2008 R2, but is available in all editions of Windows Server 2012 and Windows Server 2012 R2.
• Databases should also be running the same collation on all of the SQL Server instances acting as replicas, and all databases must be running in Full recovery mode before joining them to an availability group.
• Each server participating in an availability group must not be a domain controller.

A complete overview of configuring AlwaysOn Availability Groups is available here. In addition, step-by-step instructions for setting up AlwaysOn Availability Groups can be found here. In addition to high availability, AlwaysOn Availability Groups can be extended to provide a disaster recovery platform supporting business continuity. An installation of AlwaysOn Availability Groups that provides high availability as well as disaster recovery capabilities would consist of deploying a Windows Server Failover Cluster (WSFC) node with an AlwaysOn Availability Groups primary replica server and secondary replica server, and a File Share Witness employed by the WSFC infrastructure, all hosted in the same datacenter. An additional secondary replica server would then hosted in another datacenter for disaster recovery purposes. Because all availability replicas must be in the same WSFC cluster, the WSFC cluster must span both networks (a multi-subnet WSFC cluster). Supporting Availability Group multi-subnet failovers through the MultiSubnetFailover connection string attribute is discussed here. .

Database Mirroring

Database mirroring maintains two database copies for each user database residing on different servers. A database mirroring session between these server instances consists of a principal server that serves the database to clients, and a mirror server that acts as either a hot or warm standby server, depending on configuration. Every insert, update, and delete operation that occurs on the principal database is redone onto the mirror database by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Mirroring provides high availability in the event of a disaster, as high safety mode with automatic failover can quickly bring the standby copy of the database online. Additionally, automatic page repair provides data protection by attempting to repair certain types of I/O errors. However, not all applications are failover aware and may require manual configuration in the event of a failover, which affects downtime. Disaster recovery capabilities can be enhanced when primary and standby databases are located across separate datacenters. An overview of database mirroring on SQL Server 2012 can be found here, and recommendations for configuring database mirroring can be found here. Database Mirroring is a cost effective high availability and disaster recovery solution. A typical scenario involves a principal server running in the production datacenter and a mirror server running in the disaster recovery site. However, Microsoft does not recommend database mirroring on SQL Server 2012 as is deprecated in the next version of the product, which would mean that SQL Server 2014 is the last version supporting this feature. Microsoft recommends AlwaysOn Availability Groups instead, which is the next evolution of database mirroring and offers all the benefits of database mirroring along with additional advantages. .

Log Shipping

SQL Server Log shipping automatically sends transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances, and then applies these transaction logs to each of the secondary databases individually. A third server monitors the history and status of backup and restore operations and raises alerts if these operations fail. Logs can be shipped to multiple secondary server instances, which can be used for read-only reporting applications. However, failover from the primary server to the secondary server is not automated and must be achieved manually, which affects downtime. Application failover is typically achieved by manually configuring the application to work with secondary databases when an outage occurs, which means that the application will be unavailable from the time of the database failover until configuration changes are made. Disaster recovery capabilities can be enhanced when primary and standby databases are located across separate datacenters. An introduction to the various features of log shipping can be found here, and instructions for configuring log shipping on SQL Server 2012 can be found here. Log shipping is a cost effective high availability and disaster recovery solution, with an added benefit in that secondary databases can be utilized for read-only reporting applications. A typical scenario involves a primary server running in the production datacenter and a secondary server running in the disaster recovery site. However, failover from the primary server to the secondary server is not automated and must be achieved manually, which affects downtime. In addition, application failover with log shipping is typically achieved by manually configuring the application to work with secondary databases when an outage occurs, which means that the application will be unavailable from the time of the database failover until configuration changes are made. .