From the MVPs: Get ready for AlwaysOn in SQL Server 2012

  • Article

Here’s the fourth post in our series of guest posts by Microsoft Most Valued Professionals (MVPs). Since the early 1990s, Microsoft has recognized the inspiring activities of MVPs around the world with the MVP Award . MVPs freely share their deep knowledge, real-world experience, and impartial and objective feedback to help people enhance the way they use technology . Of more than 100 million users who participate in technology communities, around 4,000 are recognized as Microsoft MVPs.

This post is by Denny Cherry, SQL Server MVP . Thank you, Denny!

This post and our next few “From the MVPs” posts cover aspects of the about-to-be-released Microsoft SQL Server 2012.

 

Hello; Denny here. AlwaysOn and SQL Server Availability Groups are probably the biggest, shiniest new feature in SQL Server 2012. There’s a lot of pretty complex stuff that goes into building a successful AlwaysOn implementation. Throughout this post I’ll demystify the various parts that make up AlwaysOn and describe how all these pieces fit together.

AlwaysOn

The basic concept of AlwaysOn is to take Database Mirroring–like data-moving capabilities combined with the Windows Clustering failover capabilities to provide a single seamless environment that allows for the failover of databases between physical servers without any single points of failure. This is done by using the Windows Clustering components of Microsoft Windows Server 2008 R2 to control which SQL Server will be hosting the active copy of the database and using the data-moving processes I just mentioned to replicate the data between the servers in either a synchronous or asynchronous configuration.

While SQL Server 2012 was in its beta cycle, many people (including me) assumed that Database Mirroring had been upgraded and that Database Mirroring was actually doing the data movement between the servers hosting the Availability Groups. But this isn’t the case at all. The data movement component of AlwaysOn was actually rewritten completely (as Database Mirroring was being deprecated).

Availability Groups

You’ve probably heard the term Availability Groups being kicked around already. Availability Groups, or AGs, are the overarching object name used to set up this thing we call AlwaysOn. When we configure AlwaysOn, we create and then configure one or more Availability Groups within AlwaysOn. Three components are configured within an Availability Group: Availability Replicas, the Availability Databases, and the Availability Group Listeners. Configuration of the Availability Replicas and the Availability Databases is mandatory, and configuration of the Availability Group Listener is optional.

Availability Replicas

The Availability Replicas are the servers that will be hosting the databases contained within the Availability Group. The Availability Replicas include a primary replica and multiple secondary replicas. The primary replica is the SQL Server that has the databases available in read/write mode. The secondary replicas receive the transactions from the primary replica in a continuous feed of data. There must be multiple Availability Replicas within a single Availability Group, and a server can be an Availability Replica for multiple Availability Groups.

Availability Databases

The Availability Databases are the databases on the primary replica that are to be replicated to the secondary replicas. There can be one or more databases within a single Availability Group, but databases can exist only within a single Availability Group. For a database to be placed within an AlwaysOn Availability Group, the database must be in full recovery mode and in any compatibility mode supported by SQL Server 2012.

The secondary databases can be configured in three different modes:

  • No access to the secondary databases.
  • All connections are allowed to the secondary databases but only for read access.
  • “Read-intent only” mode, meaning only direct read-only connections are allowed to the secondary databases and the secondary databases are all available for read access

When the database is in read-intent only mode, only applications using the new ApplicationIntent=ReadOnly connection string parameter can connect. (Different drivers have different connection string parameters, with this example being for the “.NET Framework Data Provider for SQL Server.”) Databases in the more traditional read-only mode allow any application to connect to the database, allowing the user to query for data from the read-only database. In either of these modes, users are able to connect to the read-only secondary and run queries against the database, getting back real-time data from the database.

Availability Group Listeners

Without an Availability Group Listener, users would need to know which SQL Server instance was hosting the primary replica of the Availability Group. The Availability Group Listener gives end users a single network name to connect to, no matter which Availability Replica is hosting the primary replica. This is because the network name and IP address (or addresses configured on a multisubnet cluster), which are configured within the Availability Group Listener, are configured within the Windows Cluster, so they are brought online automatically when the Availability Group is failed over from one Availability Replica to another.

Prerequisites for Using AlwaysOn

A few prerequisites must first be met before you configure SQL Server 2012’s AlwaysOn feature. All of the servers that will become Availability Group Replicas must be within the same Windows 2008 R2 Cluster. This means that they must be running the Enterprise or Data Center edition of Windows 2008 R2 Server. The instances don’t need to be installed as clustered instances; they can be, but it isn’t required.

Additionally, each instance must have the AlwaysOn feature enabled within the SQL Server Configuration Manager. You do this by selecting the properties of the SQL Server Service within the SQL Server Configuration Manager and, on the AlwaysOn High Availability tab, checking the Enable AlwaysOn Availability Groups checkbox, as shown here:

image

After you click OK, the SQL Service must be restarted for the AlwaysOn feature to be enabled. The final prerequisite is that all of the SQL Server services must be running under the same Windows Domain Account. Local service and local Windows accounts are not supported for running the SQL Server.