How to Configure a SQL Server 2008 R2 Failover Cluster Instance on Windows Server 2008 R2 in Azure: Part 2

DZone 's Guide to

How to Configure a SQL Server 2008 R2 Failover Cluster Instance on Windows Server 2008 R2 in Azure: Part 2

In part 2, see how to configure a SQL Server 2008 R2 failover cluster instance on Windows Server 2008 R2 in Azure.

· Database Zone ·
Free Resource

This is part two of an article on how to create a two-node SQL Server 2008 R2 Failover Cluster Instance (FCI) in Azure, running on Windows Server 2008 R2. You can find part 1 here.

If you create the cluster using the GUI, you are not given the opportunity to choose an IP address for the cluster. Because your instances are configured to use DHCP (required in Azure), the GUI wants to automatically assign you an IP address using DHCP. Unfortunately, Azure's implementation of DHCP does not work as expected, and the cluster will be assigned the same address that is already being used by one of the nodes. Although the cluster will create properly, you will have a hard time connecting to the cluster until you fix this problem.

To fix this problem, from one of the nodes run the following command to ensure the Cluster service is started on that node.

Net start clussvc /fq

On that same node, you should now be able to connect to the WSFC UI, where you will see the IP Address has failed to come online.

Open the properties of the Cluster IP address and change it from DHCP to Static and assign it an unused IP address.

Bring the Name resource online.

Next, we need to add the File Share Witness. On the 3rd server that we provisioned as the FSW, create a folder and share it as shown below. You will need to grant the Cluster Name Object (CNO) read/write permissions at both the Share and Security levels as shown below.

Once the share is created, run the Configure Cluster Quorum wizard on one of the cluster nodes and follow the steps illustrated below.

We are almost ready to install DataKeeper. However, before we do that, you need to create a Domain account and add it to the Local Administrators group on each of the SQL Server cluster instances. We will specify this account when we install DataKeeper.

Install DataKeeper on each of the two SQL Server cluster nodes as shown below.

This is where we will specify the Domain account we added to each of the local Domain Administrators group.

Once DataKeeper is installed on each of the two cluster nodes you are ready to configure DataKeeper.

NOTE — The most common error encountered in the following steps is security related, most often by pre-existing Azure Security groups blocking required ports. Please refer to the SIOS documentation to ensure the servers can communicate over the required ports.

First, you must connect to each of the two nodes.

If everything is configured properly, you should see the following in the Server Overview report.

Next, create a New Job and follow the steps illustrated below.

Choose Yes here to register the DataKeeper Volume resource in Available Storage.

NOTE — At this point, the replicated volume is only accessible on the node that is currently hosting Available Storage. That is expected, so don't worry!

Complete the above steps for each of the volumes. Once you are finished, you should see the following in the WSFC UI.

You are now ready to install SQL Server into the cluster.

On the first node, run the SQL Server setup.

Choose New SQL Server Failover Cluster Installation and follow the steps as illustrated.

Choose only the options you need.

Please note that this document assumes you are using the Default instance of SQL Server. If you use a Named Instance, you need to make sure you lock down the port that it listens on and use that port later on when you configure the load balancer. You also will need to create a load balancer rule for the SQL Server Browser Service (UDP 1434) in order to connect to a Named Instance. Neither of those two requirements are covered in this guide, but if you require a Named Instance, it will work if you do those two additional steps.

Here, you will need to specify an unused IP address.

Go to the Data Directories tab and relocate data and log files. At the end of this guide, we talk about relocating tempdb to a non-mirrored DataKeeper Volume for optimal performance. For now, just keep it on one of the clustered disks.

Run the SQL Server setup again on the second node and choose Add node to a SQL Server Failover Cluster.

Congratulations, you are almost done! However, due to Azure's lack of support for gratuitous ARP, we will need to configure an Internal Load Balancer (ILB) to assist with client redirection as shown in the following steps.

NOTE — I don't know if it is a fluke, but on occasion, I have run this command and it looks like it runs, but it doesn't complete the job and I have to run it again. The way I can tell if it worked is by looking at the Subnet Mask of the SQL Server IP Resource, if it is not then you know it didn't run successfully. It may simply be a GUI refresh issue, so you can also try restarting the cluster GUI to verify the subnet mask was updated.

In order for the ILB to function properly, you must run the following command from one of the cluster nodes. It SQL Cluster IP enables the SQL Cluster IP address to respond to the ILB health probe while also setting the subnet mask to in order to avoid IP address conflicts with the health probe.

cluster res <IPResourceName> /priv enabledhcp=0 address=<ILBIP> probeport=59999 subnetmask=

After it runs successfully, take the resource offline and bring it back online for the changes to take effect.

The final step is to create the load balancer. In this case, we are assuming you are running the Default Instance of SQL Server, listening on port 1433.

The Private IP Address you define when you Create the load balancer will be the exact same address your SQL Server FCI uses.

Add just the two SQL Server instances to the backend pool. Do NOT add the FSW to the backend pool.

In this load balancing rule, you must enable Floating IP.

The most simple test is to open SQL Server Management Studio on the passive node and connect to the cluster. If you are able to connect, congratulations, you did everything correctly! If you can't connect, don't fear, you wouldn't be the first person to make a mistake. I wrote a blog article to help troubleshoot the issue. Managing the cluster is exactly the same as managing a traditional shared storage cluster. Everything is controlled through the Failover Cluster Manager.

For optimal performance, it would be advisable to move tempdb to the local, non replicated, SSD. However, SQL Server 2008 R2 requires tempdb to be on a clustered disk. SIOS has a solution called a Non-Mirrored Volume Resource, which addresses this issue. It would be advisable to create a non-mirrored volume resource of the local SSD drive and move tempdb there. However, the local SSD drive is non-persistent, so you must take care to ensure the folder holding tempdb and the permissions on that folder are recreated each time the server reboots.

After you create the Non-Mirrored Volume Resource of the local SSD, follow the steps in this article to relocate tempdb. The startup script described in that article must be added to each cluster node.

As always, if you have questions or comments you can leave them in the comment section below or reach me on Twitter.

database ,failover cluster instance ,sql server ,tutorial ,windows server 2008

Published at DZone with permission of David Bermingham . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}