Here we are again, delving further into Windows Failover Clusters. This series has been following the construction of a Windows cluster and in this installment we’re going to layer SQL Server onto the Failover Cluster that we built in Part 3.
The first two parts of the series covered Using iSCSI to Connect to Shared Storage (Part 1) and Configuring Shared Disk in the OS (Part 2). So without further ado, let’s create a basic SQL Server Cluster.
First, the initial SQL Server node must be created. Start the SQL installation and follow the steps outlined below. Remember, these are just basic steps. You might want to change the way some of the steps are performed, like using different credentials for each of the SQL services.
Creating a SQL Server cluster starts with cluster validation. The SQL cluster installation will fail if the validation is not performed.
Once the cluster validation is finished you can then install the SQL Server software in a clustered configuration. The following steps will walk us through that process.
Now that the first SQL node is installed, we will run the SQL installation from the other node in the cluster and add that server as a node in the SQL cluster following the steps as outlined below.
Now that both nodes of the SQL Server cluster have been installed over the Windows Failover Cluster you should perform some failover tests to confirm that SQL is working. We’ll go through a simple test here.
First, install the SQL Server Management Studio on a machine that is not a part of the cluster. I used a workstation in the lab I’ve been building throughout this series. Connect to the cluster using the SQL cluster alias. A rudimentary failover test could be to run a query against the master database before and after a simulated failure. I ran the following query:
SELECT * FROM [master].[dbo].[spt_monitor]
It returned one row. Open the Failover Cluster Manager on the node that is not the owner of the SQL Server role. Next, disconnect the network cards of the node that owns the SQL Server role as we did when testing the underlying Windows Failover Cluster in the preceding part of this series. Again, we want to simulate a failure. We don’t want the cluster sensing a shutdown or a service stoppage because then it will perform a planned failover, and in the real world it isn’t the planned failovers that we are trying to protect ourselves from.
After disconnecting the machine from our IT universe we should then see the cluster failover to the second node. The disk resources should fail over, and the SQL Server role will do so as well. You can see all of this in the Failover Cluster Manager as the resources go offline and come back online, and the machine name in the Owner Node column is updated. Finally, run the query against the master database again and you will see that it returns the same row. Then you know that your cluster has successfully failed over.
Now, I know that this is a rudimentary test. You can set up an application to use this clustered database and verify that the application continues to work as you fail the nodes back and forth. I would encourage that this be done in the real world, and in this series we will do that in a future installment. Right now we are looking into the things we can do to set up different clusters and at this point are keeping it simple.
I am performing all of the tasks being outlined in each blog installment as I am writing them, hoping that your experience is the same as you follow along. This information can all be found elsewhere, but it is scattered around and this is an attempt to bring it together under one roof.
There are advanced clustering options in the SQL Server setup, and there are numerous options for getting your database clustered, and this is one of them. Next, we will look at creating a generic service on a cluster to create an application cluster.