How to create and setup a SQL clustering on your Desktop or Laptop

I thought to write this post after I discussed in a forum. SQL clustering is a bit complicated one. I mean most of the DBAs are not configured or tested it, because we don’t get a chance to do or to learn SQL clustering. Also it needs bit knowledge from out of SQL (Windows AD, Network and Storage (SAN)).

In this post, I will give you an idea how to configure SQL failover clustering to yourself. We are doing everything in virtual environment (Single machine) the same you can do physically but you need all in physically in production environment (Machine, Domain, Storage & network). It’s not possible to show all the steps and screen shot here because it has more than 40 screenshots. I have prepared a document and will share with you if needed.

What is SQL clustering?

In general cluster means more than one.

SQL clustering is a collection of two or more servers access the data from same single shared storage (SAN), if one server failed another one server will take the data from the storage and the application will be running without any problem.

I suggest readers to read Kendra Little’s article. It’s good article and she explains with diagram.

http://www.brentozar.com/archive/2012/02/introduction-sql-server-clusters/

The followings are the software for setup clustering.

Planning the IP address

Installing VMware

Installing a VMware workstation is an easy one. Just click the typical and next.

Creating VMware

Creating three virtual machines using the windows trail edition and assigning the right amount of RAM, CPU & disk Space for each guest host. Configure the network adapter for public and private network for internet connection.

1. Domain controller (DC)

2. Machine1 (Node1)

3. Machine2 (Node2)

Creating Domain DC

The domain controller is also known as active directory.

http://en.wikipedia.org/wiki/Active_Directory

• Install and create a domain.

Creating Shared disk

We know SQL clustering needs a shared disk typically SAN. The use of StarWind’s ISCSI we can make our local disk as shared disk. Because we don’t have a SAN storage server physically so i am creating the shared storage in the DC machine itself using StarWind’s iSCSI.

Format the disk on each guest and use it.

Creating and installing windows Failover cluster

• Installing the windows fail over cluster.

• Validate and add the nodes.

• Assign the IP and Cluster the Nodes

• Add the MSDTC

Installing SQL Failover cluster

Install the SQL server failover cluster on both the nodes. Its easy one just you can give the network IP address and choose the shared disk.

Second node will automatically display the SQL cluster name and we can just add it.

To view the Cluster admin go to run –> type CLUADMIN.MSC

Node1 (Muthu1) is the primary host. Which will be taking care of the SQL server and the database applications.

Use this command to check which node the cluster SQL server is currently running.

SELECT SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’)

How do I know, Is the cluster automatically fail over the server, if anything goes wrong on the running node?

Just test it. Power off the Node1 (Muthu1) and you can see the SQL server automatically fail over to the node2 (muthu2).

When the SQL cluster will be down? Or Is clustering is disaster recovery?

SQL fail over clustering is not a disaster recovery. In case if the storage (SAN) is not available then clustering won’t start.

Testing: Power off the DC. In our case DC machine has storage shared disk. Hence the shared disk (SAN) goes off, then SQL clustering will not work.

My special thanks go to my VM ware team.