SQL Server Clustering

How to Cluster SQL Server 2005:

In this topic I am going to show you how to create SQL Server 2005 Failover Cluster on two virtual nodes step by step. There is no big difference between SQL Server 2005 and 2008, but I will use SQL Server 2005 in this demo.

Lab Setup:

Create a virtual machine to work as Active Directory, DNS server and SAN (DC).

Create 2 virtual nodes with 2 network card installed on each (N1 and N2).

For the DC:

Install “Windows Server 2008” then add the role “Active Directory Domain Services”.

Assign ip addr. (e.g., 192.168.1.111).

Install StarWind software and add at least 3 iSCSI disks and assign letters (S, Q, Z).

you can download a trail version from here: CLICK HERE.

and in this topic you will find a complete explanation to how to create you SAN: CLICK HERE.

For N1 and N2:

Install Windows Server 2008, join the two nodes to the domain and then initiate the 3 disks on both servers.

Also you will need to install Framework 3.5 and IIS Features.

Add “Application Server” Role and mark Incoming/Outgoing Remote Transactions.

From Server Manager click “Features” then choose Failover Clustering.

Assign ip addr. for the internal NIC on both servers (e.g., 192.168.1.112 and 192.168.1.113).

Assign ip addr. for clustering NIC on both servers (e.g., 10.0.0.112 and 10.0.0.113).

Now we are ready to begin configuring the clustering services.

First open the failover clustering console from Start Menu > Administrative Tools > Failover Cluster Management.

You will need to validate N1 and N2 configuration, if everything is green so you are ready to create your cluster (1).

Click on “Create a cluster” (2). 

Add N1 and N2 to the cluster

You will asked to assign a name and ip addr for the cluster (e.g., SQLCluster1 – 192.168.1.114), and add the nodes N1 and N2 to the cluster.

Now you have created a cluster and you will need to configure it.

First you will need to change the quorum configuration.

Under your cluster > More Action > Configure Cluster Quorum Settings.

Node and Disk Majority will set your quorum on one of your shared disks. Node and File Share Majoity will set your qurum on a shared file.

Choose Node and Disk Majority and assign one of your Shared Disks (e.g., Drive Q:\).

Second you will need to add Distributed Transaction Coordinator (DTC) from Services and Applications.

Expand your cluster > Right click on Services and Applicatons > Select DTC then click Next.

Type a name and assign ip addr for this services (e.g., SQLClusterDTC/192.168.1.114).

Select one of your Shared Disks (e.g., Drive Z:\).

Now you are ready to start installing SQL Server Fail Over Cluster.

First windows will be the prerequisites so click Install to start installing the prerequisites. 

Then click Next

Now SQL Server Installation process will perform a System Configuration Check, if everything is ok and there is no errors click Next. 

After entering license key and, Select SQL Server 2005 components to install.

Mark SQL Server Database Services and Create SQL Server Failover Cluster.

Keep the Default Instance Name and click Next. 

Enter a Virtual Server Name (e.g., sqlcluster).

Note: This is the name that clients will use to connect to this instance. 

 

Enter the virtual IP address for this instance of SQL Server 2005 (e.g., 192.168.1.115). 

Select the Cluster Group as the group where you want to create the SQL Server resources, and be sure that the Data files will be created on the correct shared drive (e.g., S:\)

On the next box SQL Server will detect the others nodes in the cluster group you choosed, if you have more than two nodes and you want to exclude any node from this cluster you just select it and click remove. 

Now you must provide an account (domain account) with an administrative rights on all the nodes you choosen.

 

In the next step you must provide Service Account which could be “Built-in System Account” or a dadicated domain account, and I prefer a deticated account for sql services. 

In the next box, you must select pre-existing global domain groups, you cab choose the same group for all services or create a separate one for each services.

The next boxes are the same with the normal SQL installation, and now everything is completed and SQL Server is ready to be clustered.

Ready for Production?

Before go into Production you must TEST, TEST and TEST.

Test everything,

Manually failover nodes by turning them off,

Manually failover nodes by unplug or disable the public netwok connection,