First, let me apologize for not getting these articles out quicker than expected, my work schedule has been very hectic.
Today, we are moving on to Part 2 of my series on SQL 2012 Always On Availability Groups where we will discuss prerequisites, the SQL Installation, and the creation of our test db to validate replication and Availability Group configuration.
In Part 1, I wrote about what an SQL Availability Group was, and the architecture behind it, the VMware support towards this technology, and the use case in our Solution Center to mitigate a SPoF (Single Point of Failure), which is in our current db Infrastructure.
Since we are not interested in Instance Redundancy and more db redundancy and 100% uptime, we are putting our focus towards Availability Groups only and not SQL Failover Clustering, however, WSFC is still a requirement for Availability Groups to function. SQL 2012 Availability Groups leverages the Cluster NAME & Cluster IP’s that are part of Failover Clustering technology and adds it as a roll within WSFC. One very important point that needs to be made here, WSFC does not manage the Availability Group Config or settings. This should be done in SSMS (SQL Server Management Studio). There isn’t a requirement for shared storage, however. Each SQL Server node can have it’s own storage.
Windows Server Failover Clustering
– Minimum nodes needs to be identified for the Availability Group configuration. Do you want a Primary and several Secondaries, one Primary and one Secondary, etc? In our case we will have two nodes, one Primary and one Secondary. They will be setup for synchronous replication and will allow for immediate failover. Keep in mind that you can only choose one Secondary for this purpose if you have more than one Secondary node for replicas. All other can only be setup for Asynchronous Replication.
-SQL Server Nodes will need to be joined to the same Domain and have valid DNS records
-Availability groups can be used within the Datacenter and across Datacenters. Depending on Node count and how you will be setup (in our case, two nodes, one in each Datacenter) you will determine the best choice for Quorum. Microsoft outlines the quorum mode based on Node count and where those nodes live. See table below:
Since I will have one SQL Server in each DC in a “multisite” cluster, and will use Node and File Share Majority.
-Choose the same drive configuration on all servers that belong to the Availability Group, like C:\OS, A:\Application, S:\SQLdb, L:\Logs, to each is own, however make sure that each node is setup the same
-VERY IMPORTANT: Make sure that your account that is creating the cluster is a Domain Account that is a Local Admin on each node. If not a Domain Admin account, then make sure that the account has Create Computer Objects and Read All Properties permissions in the domain. Now, another thing that could be troublesome is when you actually create the Availability Group and the Cluster needs to create the Availability Group Listener which will be listed as a resource within WSFC and will be a another computer object on the Domain, the Cluster Name Object in the Domain itself must have the same permissions on the Computers OU.
Here, you can see my setup with the Cluster Name Object as the Cluster name, and the permissions required for that Object in the Domain. Remember, in ANY WSFC, this is required, because this Object actually creates the resource within the Domain, for any Cluster Resource. This applies if it’s a Hyper-V failover cluster, SQL Failover cluster, etc.
Microsoft SQL Server 2012 (This article assumes that you know how to install and configure SQL Server and is in no particular order)
-Create a Domain Account for the SQL service that can be used on all nodes within the Cluster.
-Install an Instance of SQL Server 2012 on Each Node. This will be a single instance. Do not install a Cluster Instance, treat each node as a Separate SQL Server. Also make sure you are using the latest ISO from MS.
-Again, create your directories on each node for the SQL Server DB, Logs, etc. Make sure they are the same configuration on each node.
-Update latest patches on OS AND SQL
-Install SQL Server and Create a test db.
-Enable Always On HA within SQL Server Configuration Manager under the SQL Service Properties.
Now we are ready to get into the Always On Availability Group configuration. I do realize that this post was not very detailed, however, again, I find that if the information is already out there, not reason to repeat it here. I followed a great guide from http://www.brentozar.com . You can get the Always On Availability Group information here. I always go to Brent’s site for anything related to SQL. They have a great site with superb info that’s updated regularly. The next post will be more detailed as getting to this point is not that difficult but setting up the Always On Availability Group(s) correctly, can be a bit challenging. If you missed part one you can get there by following the Index below.