Give Your App High Availability, Disaster Recovery Using AlwaysOn SQL Server 2012
Have you wanted to offer your customers disaster recovery/high availability? But providing that capability seems like a big programming challenge. Instead, SQL Server 2012 introduces AlwaysOn where you can offer added value with a straight forward code change in your connection string.
AlwaysOn by design looks similar to database mirroring, but is actually a combination of both databases mirroring and clustering. The goal is to maximize the database availability and also give benefits of disaster recovery.
Using AlwaysOn, you can increase application availability and get a better return on your hardware investments through a simplified high availability (HA) deployment and management experience.
About AlwaysOn
Mission-critical applications require nonstop access to data. You can meet your customers’ continuity goals by supporting Microsoft SQL Server 2012 high-availability technologies.
The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in Microsoft SQL Server 2012 Release Candidate 0 (RC 0), AlwaysOn
Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to four sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.
An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.
Setting Up Always On
You can set up to four computers for Always On: a primary, a secondary replica, a read only database, and a
(Quick Tip: You could use the read only database for reporting.)
I’ll summarize the steps here. There’s a more detailed version of how to set up Always On. There’s also a demo that you can walk through in the SQL Server 2012 Developer Toolkit, called Demo: Configuring SQL Server 2012 Availability Groups.
But to give you a quick overview:
- Start by building out AlwaysON in “Standalone” SQL Server 2012 instance on both the node. Your data is NOT a clustered SQL Server Instance. As such a standalone SQL Server setup is required on both the clustered nodes.
- Enable AlwaysON feature on both the instances
- Put your database onto both instances and insert some data.
- Create an Availability Group. The wizard will walk you through the steps.
- Synchronize your data. This step will allow you to synchronize your user PRIMARY and SECONDARY server by taking backup of user database and restoring this on secondary server.
Once synchronization completes, expand the tree and you will be able to see the Availability Replicas (along with current role) and Availability Databases.
Now you have high availability.
You can have disaster recovery by adding a database in another location. See SQL Server Multi-Subnet Clustering.
What to Change in Your Application
But how does your application know where to get the data?
You create an Availability Group Listener. An availability group listener is a server name to which for you to connect to the primary or secondary replica database. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica.
To connect to the primary replica for read-write access, the connection string specifies the availability group listener DNS name. If an availability group primary replica changes to a new replica, existing connections that use an availability group listener's network name are disconnected. New connections to the availability group listener are then directed to the new primary replica.
An example of a connection string to port 1433:
Server=tcp: AVG,1433;Database=AdventureWorks;IntegratedSecurity=SSPI
Now instead of pointing to a database, you are pointing to the availability group.
You can walk through a demo of how it all works in the Developer Training Kit. See Demo Configuring a SQL Server 2012 AlwaysOn Listener.
How to Test that Always On Works
You can test the feature by inserting a row into the primary database.
And then connect to the secondary and select the row you just inserted. It should just be there.
To test failover, force a fail over in Management Studio.
You will see the server roles change.
Run the query using the connection string with the Availability Group and you will see the results from the secondary server.
You specified the availability group listener of a (high-availability, disaster-recovery) availability group in the connection string. So when your application is connected to an AlwaysOn database that fails over, the original connection is broken and the application opens a new connection to continue work after the failover.
Voila! High Availability. And your code used a connection string that pointed to the Availability Group.
For More Information
See SQL Server Denali - AlwaysON (HADR): Step-by-Setup setup guide.
Introducing Always On on MSDN Developer Center.
Books Online: Overview of AlwaysOn Availability Groups (SQL Server) and SQL Server Native Client Support for High Availability, Disaster Recovery.
Download: SQL Server 2012 Developer Training Kit Web Installer.
Bruce D. KyleISV Architect Evangelist | Microsoft Corporation
