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 1 shows the new dashboard view of AlwaysOn HA availability groups with some System Center 2012 SP1 databases made highly available.

Figure 1

HA SQL is Important. What is the Reason?

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.

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 2.

Figure 2