Dev Blog

This is part 3 of a set of posts around creating a Virtual cluster on SQL Server, which I’m creating to raise my awareness of clustering.

Part 1 of this series created the Shared Storage used by the cluster, using FreeNAS.

Part 2 of this series created a Windows Cluster using this storage.

This section will cover creating a SQL Server cluster using this environment.

The architecture diagram to the right shows the cluster we are creating. This post will cover the clustering of SQL1 and SQL2.

Step 1 – Starting the Process

The first step is to get the SQL Server media available to the server. I’ve taken a copy of the DVD and placed it on the C Drive on the first node of the cluster.

Run Setup.exe, which’ll install some pre-requisites (you should do this on both nodes, to make for an easier life). When the server’s have restarted, run setup.exe on the active node (SQL1 in my case).

Within SQL Server Installation Center, click the Installation option on the Left, and choose New SQL Server Failover cluster Installation.

The Wizard will start, and as normal, will take you through installing the setup support files. Once this has completed, the Setup support rules will run, and any issues will be flagged. This includes a set of checks around Clustering.

The one that has a warning here, checking to see if MSDTC is clustered, effectively means that there may be issues with linked server queries, and SSIS. It’s not required for the Cluster to build however, so lets press on. Clicking Next gives us the Feature Selection screen.

I’m only going to install the Database engine, Integration Services and Management tools. Click Next.

In the Instance Configuration screen, enter the name for the SQL Cluster.(SQLCluster in my case).

Click next to confirm the disk usage requirements, Confirm the name of the Cluster Resource group (selecting the default).

Click next, and we are prompted to select the disks for the SQL Cluster. I’m selecting all the unused disks.

Click Next, and confirm the Network settings for the Cluster.

Click next and confirm the Cluster Security Policy (the default being to use the Service SIDs).

Click Next to continue,  for the Services, provide a Domain account, and click next.

Next you are prompted for Database engine configuration as normal, click Next, Click Next on the Error Reporting screen. The next screen is the Cluster Installation rules. These should all be passed, so click Next.

Click next and next to start the Installation. The installation will take a little while to run.

Upon completion, you’ll get this:

If you now look in the Failover Cluster Manager, you’ll see the SQL Cluster in Services and Applications.

Next, you can fire up SQL Server Management Studio and connect to the Cluster.

If you go to Server Properties, you can see that the Server is Clustered.

The Default System databases (Master, MSDB, Model and Tempdb) are all now stored in one of the cluster drives, and the default drive for databases will also be that drive.

Step 2 – Adding the Second Node

Next we need to add the second node to the Cluster, so switch to your other Member server, and run setup.exe, from the Installation section choose ‘Add node to a SQL Server failover cluster’.

Run through the installation screens, and note that it should pickup the SQL Server Cluster name when you get to the Cluster Node configuration screen.

Click Next, enter the passwords for the Domain accounts on the on the Service accounts page, and click next on the Error Reporting page.

The Add Node Rules page should complete successfully, so click Next.

Then Click Install to continue.

When this has been successful, you’ll see:

Step 3 – Test it out

I’ve taken a backup of the AdventureWorks DW 2008 R2 database from my laptop and copied the backup to SQL1. Note that to restore a database, it must be on one of the Cluster drives, so having copied the Backup file to the shared drives, I can now restore it.

Having restored this database, I can now run queries against it, and see what activity is taking place, through the performance graphs on FreeNAS. You can see from the graphs below that are spikes when the Cluster nodes were connecting, and the then later when the database was copied over and restored.

The next post will be around optimising and getting some performance metrics off this cluster.