How to: Configure SQL Server 2012 AlwaysOn – Part 2 of 7

How to: Configure SQL Server 2012 AlwaysOn

Building the Environment

When building our environment there is an assumption of basic familiarity with Windows Failover Clustering. The following will begin with a Windows Failover Cluster configured with 2 nodes, a shared disk added to the cluster, and 1 SQL Server Failover Cluster Instance already installed in data center 1. Also, we have 2 nodes added to the WFC for data center 2. If you need to get up to speed on how to set these up please see the references below.

What we will be doing in this part of our series is adding a new shared storage from our secondary data center (cross sub-net), setting cluster timeout settings, and then installing a second SQL Server Failover Cluster Instance in our secondary data center. We will get into detail on everything except the SQL Server installation, which we will breeze over, as it is generic and no special steps are needed for this architecture.

References:

• MSDN – Requirements and Recommendations for a Multi-Site Failover Cluster
• TechNet – Create a New Failover Cluster in 12 Steps
• MSDN – Creating a Failover Cluster or Adding a Node
• TechNet – Add Storage to a Failover Cluster
• MSDN – Create a New SQL Server Failover Cluster Instance (Setup)
• sqlHammer – Automated Unattended (Silent) SQL Server Installs with Powershell
• sqlHammer – Set Windows Failover Cluster Timeout Thresholds

Adding a Shared Disk to the WFC from Data Center 2:

When adding a shared disk to a WFC (normally) a validation process will occur which checks to see if the disk can be owned by all nodes currently in the cluster before allowing it to be added. This is a problem for our target architecture because when using multiple sub-nets in a WFC your disk located at one site cannot be owned by the other sites in the configuration. For that reason, we won’t be able to use the Windows Failover Cluster Manager GUI as seen below.

Instead we have to go to a lower-level and hit up the command line but we are getting just slightly ahead of ourselves here. First lets add a new shared disk to the nodes in data center 2. The steps to accomplish this will be more specific to your architecture because there are more than one set of steps to accomplish this depending upon whether you are using iSCSI, Fiber, etc. and what SAN software you are using.

Once the disk is added to Windows;

1. Open up the Windows Disk Manager to initialize the disk.
   1. Right click on the disk that you want and select Initialize Disk.
   2. In the Initialize Disk dialog box, select the disk(s) to initialize. You can select whether to use the master boot record (MBR) or GUID partition table (GPT) partition style.
2. Format the disk.
   1. NOTE: For disks containing SQL Server files it is recommended to format the disk as 64k. This provides a performance enhancement due to the fact that SQL Server processes everything in 64k extents.
   2. Using Windows Disk Manager; Right click on the drive you just initialized and select Format…
   3. Click Yes if a warning pops up.
   4. Set your format settings and then click OK.
3. Assign a drive letter, also in Windows Disk Manager.
   1. Right click on your drive and select Change Drive Letter and Paths.
   2. Click Add…
   3. Select the drive letter you want to use. IMPORTANT NOTE: File paths must be identical for SQL Server AlwaysOn Availability Groups to Failover correctly. This means that your drive letter must be the same as the shared disk already setup in data center 1.
   4. Click OK.

Now that we have our disk ready to go, we need to add it into the WFC as available storage. This is where the command line comes into play because we need to skip the Failover Cluster Manager’s disk validation checks.

1. Run command prompt as administrator. (To use Poweshell instead, see John Toner’s great blog post)
2. Execute the following to create the clustered disk:
   1. In the command below; note that the “Disk Z:” is not the actual drive letter you want to use. It is going to be the name of the disk in the Failover Cluster Manager. This can be whatever you like. In the screenshot below you will see Cluster Disk 1 which is the GUI default name and Disk E: which is the one I added with these commands. Drive letter will be referenced in the next command.
   2. [shell]cluster res “Disk E:” /create /type:”Physical Disk” /group:”Available Storage”[/shell]
3. Execute the following to set theDiskPath property:
   1. Alter the DiskPath property to the letter previously selected when adding the disk to Windows.
   2. Alter the clustered disk name (in this case “Disk E:”) to whatever you named it in step 2.B.
   3. [shell]cluster res “Disk E:” /priv DiskPath=”E:”[/shell]
4. Once that is complete you can close command prompt and navigate to your Failover Cluster Manager.
5. Drill down into your WFC and navigate to the Storage folder.
6. Right click on your new cluster disk (should be under Available Storage).
7. Select Properties.
8. Navigate to the Advanced Policies tab.
9. Ensure that only data center 2’s nodes are selected checked in the Possible Owners section.
10. Click OK.
    1. NOTE: If your disk is currently offline or in a failed state that might be because when you added the disk it tried to be owned by a node which it cannot be owned by (such as in data center 1). Now that you’ve set the possible owners correctly you will have to reboot which ever node is currently owning the failed disk so the disk can come up on a node which it can be owned by. From this point forward you will not have this issue.

*** Our target architecture includes 2 nodes in data center 2 but in the above screenshot you will notice that only 3 nodes are present. This screenshot is only for demonstration of the possible owners properties and is not an exact indication of what your environment should look like. What is important is that you see only 1 node is checked off as a possible owner. This is because in the screenshot that is the only node in data center 2. The others cannot own the node because they are on a different sub-net. ***

Setting Cluster Heart Beat Timeout Settings:

With multi-site clustering there is always a major concern of latency. Naturally, geographic distance affects data transfer times and in some cases our companies might not want to spend the VERY LARGE amount of money to maintain a large pipe to your secondary data centers. In any case, we probably don’t want our cluster timeout settings to be default because those defaults were designed for connections local to a single data center.

By default your WFC connection will fail when 5 pings are lost (1 ping per second for a total of 5 seconds). The below cluster.exe commands are run on only one of the clustered nodes and will apply to all of them. In my example, the thresholds will be changed to a threshold of 10 pings lost with pings being sent every 2 seconds for same site and every 4 seconds for cross site (totals of 20 seconds and 40 seconds) but you can configure them to whatever you feel is appropriate to the latency and stability of your cross data-center connection.

1. Run command prompt as administrator.
2. Execute the following to change the same-site heart beat to 2 seconds (replace clustername with theactualWFC name).
   1. [shell]cluster /cluster:clustername /prop SameSubnetDelay=2000:DWORD[/shell]
3. Execute the following to change the cross-site heart beat to 4 seconds (replace clustername with theactualWFC name).
   1. [shell]cluster /cluster:clustername /prop CrossSubnetDelay=4000:DWORD[/shell]
4. Execute the following to change the same-site ping loss threshold to 10 lost pings (replace clustername with theactualWFC name).
   1. [shell]cluster /cluster:clustername /prop SameSubnetThreshold=10:DWORD[/shell]
5. Execute the following to change the cross-site ping loss threshold to 10 lost pings (replace clustername with theactualWFC name).
   1. [shell]cluster /cluster:clustername /prop CrossSubnetThreshold=10:DWORD[/shell]

Install an Instance of SQL Server 2012 in Data Center 2:

As mentioned above, we are only going to touch on this section because there is no special steps necessary to setup this SQL Server FCI on the secondary site.

We are going to be using database level failover (AlwaysOn Availability Groups) to manually fail between data center 1 and data center 2. Since this is a database level failover as opposed to an instance level failover we need to install a second SQL Server FCI in data center 2 because the existing SQL Server FCI in data center 1 will not move during a failover.

IMPORTANT NOTE: AlwaysOn requires that your databases exist with the exact same file path on all instances. So when you setup your next instance make sure to use the disk we just configured (the one who’s drive letter is the same as the one used in data center 1) and use the same user database paths as you did for data center 1. If you don’t install with the correct path, don’t worry too much about it, you’ll just have to create an identical path on that drive to match data center 1. The identical path does not have to be the instance’s default data/log paths; it just needs to be available with property security settings when initializing the replica.

See these references for help with installing a SQL Server FCI:

• sqlHammer – Automated Unattended (Silent) SQL Server Installs with Powershell
  
• MSDB – Create a New SQL Server Failover Cluster Instance (Setup)

Wrap-up:

We’ve now covered the abnormal steps necessary to add a shared disk when joining a multi-site Windows Failover Cluster and the configuration of heart beat thresholds to protect against latency induced by geographic distance. Please feel free to comment or ask questions and I would be more than happy to clarify any steps or provide additional detail were necessary.

Follow the links below to move on through this tutorial.

• Scope and Architecture – Part 1 of 7
• Building the Environment – Part 2 of 7
• Configuring AlwaysOn – Part 3 of 7
• Building Availability Groups – Part 4 of 7
• Explanation of Implicitly Created Components – Part 5 of 7
• How to: Fail-Over – Part 6 of 7
• The Availability Group Listener – Part 7 of 7