Problem
SQL Server has produced some excellent High Availability options, but I was looking for an option that would allow me to access my secondary database without it being read-only or in restoring mode. I need the ability to see transactions occur and query the secondary database.
Solution
Enter SQL Server 2012 AlwaysON High Availability Groups.
The new AlwaysOn feature combines the powers of clustering and mirroring into one High Availability option, but also allows you to interact with the secondary databases something that clustering and mirroring do not allow. In addition, AlwaysOn Availability Groups allows you to configure failover for one database, a set of databases or the entire instance again something you could not do with database mirroring. Another feature is that you can create multiple failover targets where in the past database mirroring only allowed one failover partner.
In this tip I’ll show you a basic look at how to setup SQL Server 2012 AlwaysON Availability Groups. Note that this tip is separated into 2 parts: Part 1 will consist of installing and configuring the prerequisites for AlwaysOn and Part 2 will consist of setting up the Availability Groups and showing how they work.
Let’s get started…
Environment: I have setup two separate VM’s running in VM Workstation with Windows 2008 R2 Enterprise Edition and SQL Server 2008 R2 Enterprise named Denali and Denali2.
First thing we need to do is make sure both servers have .NET Framework 3.0 Features and Failover Clustering installed. To do this, go to Server Manager, Features, Add Feature. Check .NET Framework 3.0 and Failover Clustering and Install as shown below.
Once .NET and Failover Clustering are installed we can configure the cluster. Go to Control Panel, Administrative Tools, Failover Cluster Manager and click Validate a Configuration.
On the “Validate A Configuration Wizard” page click Next, then enter the names of the SQL Servers you want to configure. In this example we are using Denali and Denali2.
Click Next to run through some validation tests. If you receive any errors click View Report to view the errors.
As you can see from above I received errors on my validation, because I am only using one network card. This means I have a single point of failure. In a production environment you would want to fix this, but this is only a warning and does not stop us from configuring clustering. From the main screen click “Create a Cluster”
After clicking “Create a Cluster” and clicking Next on the main page you should see the “Access Point for Administering the Cluster”. Here is where you will type in the name of your cluster. This doesn’t need to be your server name. Use a name to distinguish this cluster from other clusters. In this example, I’ll use DenaliCluster.
Click Next to install the cluster.
After the creation, you can see the install report as shown below.
From the Failover Cluster Management screen you can see I have an error regarding my storage solution (no quorum drive) by the yellow exclamation point over my Cluster Name. No worries in a test environment, but you would want to address this in production.
After creating the cluster, we’ll need to install SQL Server 2012 on both servers (in this example, I’m using Denali CTP3). On the installation screen we could click “New SQL Server failover cluster installation”, but since we’re only using Availability Groups we can just use the standard installation, “New SQL Server stand-alone installation…”
Accept all defaults, choose SQL Server Feature Installation and select Database Engine Services and Management Tools – Complete.
Accept defaults, specify a domain user on the Server Configuration screen and click Next. On the Database Engine Configuration screen specify Mixed Mode Authentication and enter a password for the sa account.
Click Next, Next, Next, Install. In a few minutes you should have a working copy of SQL 2012. Once 2012 is installed on both servers we’ll need to do a few things to configure it.
Enable TCP/IP: Click Start, Programs, Microsoft SQL Server 2012 CTP3, Configuration Tools, SQL Server Configuration Manager. Click SQL Server Network Configuration, Protocols for MSSQLSERVER, and right click TCP/IP and enable.
Enable AlwaysON: In SQL Server Configuration Manager, click SQL Server Services, right click SQL Server (MSSQLSERVER) and choose Properties. Click the AlwaysOn High Availability tab and Enable AlwaysOn Availability Groups.
Add Startup Parameter: In SQL Server Configuration Manager, click SQL Server Services, right click SQL Server (MSSQLSERVER) and choose Properties. Click the Startup Parameters tab, type -T9532 in the startup parameter box and click Add and Restart Services.
Setup Logins: Open SSMS and connect to one of your instances. Click Security, right click Logins and click New Login. Create logins for the accounts you used for your SQL Services. For example, I used SQLBrady/SQLUser for my service accounts so I’m going to make a login for this user and give this login sysadmin rights from the Server Roles page. Do this for both instances.
Create file share for backups and replicas: If you’ve ever setup log shipping you know you have to have a file share on a server and this is the same for this new feature. Create a file share on one of the servers and give read/write access to all your service accounts. Once clustering is setup, 2012 is installed and configured, we can create our first Availability Group for AlwaysOn.
Next Steps
- Stay tuned for part 2 for creating Availability Groups
