Data Management

SQL Server 2012 AlwaysOn: High Availability database for cloud data centers

John Joyner explains how to deploy SQL Server AlwaysOn in a failover cluster without shared storage to achieve high availability SQL.

It has traditionally been almost impossible to architect infrastructure-class, highly available (HA) SQL Server solutions using shared storage in the public cloud. Recently Microsoft announced support for some System Center 2012 SP1 applications to work with SQL AlwaysOn, a new way to achieve HA SQL. AlwaysOn uses an availability group concept, much like Exchange 2010 database availability groups (DAGs) to achieve clustered HA services without shared storage.

This is good news for architects looking to move management workloads into the public cloud when appropriate. In theory, a pair of powerful VMs in Azure running SQL 2012 AlwaysOn can approach and exceed the HA SLAs expected of many mission critical applications. Figure A shows the new dashboard view of AlwaysOn HA availability groups with some System Center 2012 SP1 databases made highly available.

Figure A

SQL Server 2012 AlwaysOn Availability Groups enable high availability without a SAN. (Click to enlarge.)

Why HA SQL is Important

Highly available (HA) SQL Server services are the cornerstone of many enterprise database applications. Few enterprise solutions today are deployed on non-HA SQL. Without an HA mechanism for database services, enterprise applications and e-commerce websites cannot offer maximum available uptime. Even perfectly managed servers require periodic restarts for updates and maintenance; and you always need to be prepared for equipment failure such as extended outages of particular servers or disk drives.

The traditional way to offer HA SQL is by creating a SQL Server failover cluster based on shared storage. That is, a storage area network (SAN) presents shelves of disk drives to two or more servers at the same time ("shared storage"). The SAN and shared storage is often the most expensive component in the datacenter. Public cloud solutions abstract you from the storage, and usually don't offer the kind of infrastructure you would need to run a conventional HA SQL failover cluster with shared storage in the cloud.

HA SQL for the public cloud

A drag on projects looking for ways to move workloads to the public cloud is the lack of HA SQL support on infrastructure-class SQL services. In the Microsoft public cloud, Windows Azure, you can rent a slice of SQL Azure, which is highly available; however, many commercial applications won't install correctly against a SQL Azure backend. Applications running with a SQL Azure backend are generally conceived as Azure applications and written to support SQL Azure specifically. There is a demand to run HA SQL as infrastructure in public and hybrid cloud solutions.

To achieve infrastructure-class SQL services, you can install SQL Server inside a Windows Azure Infrastructure-as-a-Service (IaaS) class virtual machine (VM), and this works as expected. Your SQL server will be subject to the SLA of a single Azure IaaS VM instance. Windows Azure VMs by default have their virtual hard drives (VHDs) geo-replicated to a second Azure datacenter, providing a degree of protection against failure of some Azure hardware. Take note, Windows Azure geo-replicated VHD storage is not an HA solution.

SQL Server 2012 AlwaysOn, no need for SAN

With SQL Server 2012 AlwaysOn, two or more complete copies of each HA database can exist, synchronized by AlwaysOn technology. The independent database copies are presumed to exist locally in direct attached storage (DAS) on each SQL node, or over on the network using economical Windows Server 2012 SMB 3.0 file shares on dedicated Windows Server 2012 file servers.

  • You can use Windows Server 2012 Standard for the SQL server nodes-unlike in previous Windows releases, you can enable the failover cluster feature in the Standard edition of Windows Server 2012 as well as Windows Server 2012 Datacenter.
  • SQL Server 2012 does require the Enterprise edition of SQL Server 2012 to use the AlwaysOn feature.

Steps to deploy a SQL Server 2012 AlwaysOn Availability Group

Here are the high-level steps to follow to deploy SQL 2012 AlwaysOn in a SQL 2012 failover cluster (without shared storage).

  1. Install Windows Server 2012 in two computers or virtual machines (VMs), each with a single network interface card (NIC).
  2. Create a two-node failover cluster without shared storage. You will need a cluster name and an IP address for the cluster network name.
  3. Install SQL Server 2012 Enterprise on both computers as if they were going to be stand-alone SQL servers.
    • When you install, use a domain account for the SQL server services.
    • Open the Windows Firewall on ports TCP 1433 and TCP 5022.
  4. Create a temporary "seed" database on the first SQL server using SQL Server 2012 Management Studio. This database will be used to establish the AlwaysOn cluster, and then can be deleted after the first production database is deployed.
  5. Make sure the database is of the "Full" type model, and perform a SQL Backup job.
  6. In the Management Studio, create an AlwaysOn Availability Group and an Availability Group Listener. (The Availability Group Listener is essentially the virtual (or clustered) SQL Server instance. There is a one-to-one relationship between availability groups and listeners.)
    • Assign a DNS name and TCP IP address for the AlwaysOn Availability Group and an Availability Group Listener.
    • Assign a shared network folder that is accessible to all SQL servers that will have AlwaysOn database replicas.
  7. At the AlwaysOn High Availability node, right-click and select Add A Database To An Availability Group. If your database is of the Full type and has been backed up, the status will be "Meets Requirements". Click Next.
  8. Select that you will perform a full synchronization, using the shared network folder you specified in step 6(b). Click Next.
  9. Enter security information to access the primary database replica. Click Next, observe the validation and click Next, and then Finish.
  10. Observe after a moment that new database replica on the secondary node in the SQL AlwaysOn availability group has been created, as seen in Figure B.

Figure B

Successful creation of database replica in an AlwaysOn Availability Group.

Get the latest release of SQL Server 2012

You should install the latest release of SQL Server 2012. In November 2012, Microsoft released Service Pack 1 to SQL Server 2012. Download slipstreamed install files at this link:

For a discussion about SQL Server in Windows Azure Virtual Machine vs. SQL Azure, see this link:

About

John Joyner, MCSE, CMSP, MVP Cloud and Datacenter Management, is senior architect at ClearPointe, a cloud provider of systems management services. He is co-author of the "System Center Operations Manager: Unleashed" book series from Sams Publishing, ...

3 comments
Shashijk
Shashijk

Hi John,

We currently have a 5 to 6 discreet databases in SQL 2005 and SQL 2008 which need to talk to each other on the database level. Hence we have Linked servers created which seem to working fine. We now are moving to SQL 2012 and probably in next 3-6 months would like to move to Cloud. So wanted to check with you if Linked servers will continue to exist in SQL 2012 or not? If not what will be an alternative? Also will Linked server be supported/will work on cloud?

Hope to hear from you soon.

kroels
kroels

If I had virtualized my storage to make it uber-HA, like two EMC or IBM arrays behind a VPLEX (local) or SVC appliance so that even if one of the arrays quit serving storage my clients would not notice... Would I prefer to use SQL Server 2012 they way I had in the past, or use the AlwaysOn availability groups and connect directly to each of the storage arrays (not use the VPLEX or SVC)? Kind of a question between using the native functionality in SQL 2012 versus licensing storage space on a virtualization appliance.