How to Configure SQL Server 2012 2008 R2 Cluster for SharePoint 2013 2010

Posted in: Blog, SharePoint 2007, SharePoint 2010, SharePoint 2013 | Apr 12, 2013 at 9:38 AM

Comments are closed

INTRODUCTION

SQL Clustering is awesome for HA (high availability). In this instance I was tasked to install and configure Production DMZ environment for my client’s Internet sites. We want to use clustering so that it improves availability (ie. when the main SQL server goes down, the second one can be up and running straight away).

Setting up SQL Clustering can be both tricky and easy at the same time. As long as we know what we need and the steps required, we should be fine.

The servers are within Virtual Machine realm and managed using VMWare. In this article I’m using SQL Server 2012 and Windows Server 2012 but the same process can be used with Windows Server 2008 R2 and SQL Server 2008 R2.

The stuff you need:
– A dedicated VM for the shared drives (let’s call the machine name VMDRIVES)
– 3 drives to be attached to VMDRIVES (let’s name them E:\SQLDATA 100GB, F:\SQLLOGS 100GB , G:\DTC 10GB)  –> NOTE: Your drive letters may be different and that’s OK. Do not forget to format and make the disks available in Disk Management.
– A dedicated VM for Main Cluster node (let’s call the machine name VMSQL1)
– A dedicated VM for secondary node (let’s call the machine name VMSQL2)
– A dedicated VM for SharePoint Application Server (let’s call the machine name VMSPCA)
– A dedicated VM for SharePoint WFE (let’s call the machine name VMSPWFE)
– At least 5 IP Addresses (1 for VMDRIVES, 1 for VMSQL1, 1 for VMSQL2, 1 for CLUSTER, 1 for DTC)

NOTE: You can have as many cluster nodes and SharePoint servers as necessary but the above is what I have.

NOTE: You should NOT attached any shared drives directly to your SQL server. The drives will NOT be able to be added to the cluster. Somehow when you turn on Clustering Service on a server, it can’t add any shared drives attached to the clustered server. This is why you will need a dedicated VM for your shared drives.

So, the steps to configure the clustering is as follows:

CONFIGURE SHARED DRIVES

The shared drives need to be configured first. Go to VMDRIVES -> Server Manager and add the following role:

This is sort of saying that this server is a File and Storage Services server where the shared drives will be.

After it is installed, go to Server Manager -> File and Storage Services -> iSCSI. You will not see any drive. So, what we want to do next is to promote the 3 drives you want to use as the cluster. To do this repeat the following step:

– Right click and select “New iSCSI Virtual Disk”

– On iSCSI Virtual Disk Location: Select a particular disk you want to use. For the first one I want to promote the E:\ drive which will be the SQL Data drive.

– On iSCSI Virtual Disk Name: Give it a name and description eg. SQLData

– On iSCSI Virtual Disk Size: Assign a space. I have a 100GB disk but I will only use 90GB just in case it needs to assign some system data on top of it.

– On iSCSI Target: Click on “New iSCSI target”

– On Target Name and Access: Assign a name and description eg. SQLData

– On Access Servers: Access Servers are the servers that will be accessing the shared drive. In this case are the cluster servers: VMSQL1 and VMSQL2. Add the IP addresses of VMSQL1 and VMSQL2.

– On Enable authentication: Leave blank.

Click Next and it should configure the drives successfully.

Repeat the steps for the other 2 drives (SQL Logs and DTC).

CONFIGURE SHARED DRIVE ACCESS

Now that the shared drives have been configured, you now need to configure the access to them from VMSQL1 and VMSQL2.

So, the steps are:

– Go to VMSQL1 -> Control Panel -> iSCSI Initiator. It will ask you to start the service if it has not been started. Just click Yes/OK.

– Then on the following screen specify the target IP address (ie. the IP adress of VMDRIVES). You should then be able to see all shared drives. You need to connect to ALL of them:

Once you’ve done this the drives should be available in Disk Management of VMSQL1. So, on Disk Management make sure you bring all the shared drives online and format them using NTFS and assign each one of them a drive letter.

Repeat the steps above for VMSQL2.

CONFIGURE CLUSTER

Now, we’re ready to go into the next step: the cluster configuration.

Go to VMSQL1 -> Server Manager and install the following Feature:
– .NET Framework 3.5 Features
– Failover Clustering

Go to VMSQL2 and do the same.

Then go to VMSQL1 -> Administrative Tools and you should be able to see a menu called Failover Cluster Manager. Click it and let’s configure the cluster. When you click that menu you will see a screen something like below:

The next outstanding steps to complete the cluster are:
– Create cluster
– Setting up cluster disks
– Setting up Distributed Transaction Coordinator (DTC)

To create cluster click on Failover Cluster Manager -> Create Cluster.

On Select Servers, select the two servers for cluster which is VMSQL1 and VMSQL2. Then on Access Point for Administering the Cluster specify the IP address and the network name for the cluster. For SQL connection you can use the SQL cluster name instead. I’ll explain that later. Then simply finish off the wizard.

Next is the cluster disk configuration. To setup the cluster disk, right click on Storage -> Disks and click Add Disk. Select all the 3 disks you’ve created and shared previously. You should get ONLINE status for all the 3 disks.

Once you’ve setup the disks let’s now setup the DTC. Click on Roles and select Configure Role. Then select Distributed Transaction Coordinator (DTC). Then specify a name eg. CLUSTERDTC and a dedicated IP address. Then select the quorum/DTC dedicated storage. Once you’ve done that simply confirm the installation and you’re done.

After this step you are done with the cluster. You do not need to configure the steps above for the other SQL server.

INSTALLATION OF SQL SERVER

Now we are ready to install SQL Server.

Go to VMSQL01. This is your master cluster. Run the wizard and select Installation -> New SQL Server failover cluster installation.

Simply follow the wizard. When it comes to the Features selection, select the features you want to install. NOTE: You can NOT use the cluster disk as the Shared Feature Directory. You have to select a local disk to the server (eg. C:\).

In the Instance Configuration section the wizard will ask you for SQL Server Network Name. This will be the server name you will use in your SharePoint/application connection string! Please remember that this is a cluster therefore you will not use a particular server name but rather this network name. In your connection string it will be something like Data Source=CLUSTER\INSTANCENAME.

On Cluster Disk Selection nominate the 2 disks you’ve shared previously.

On Cluster Network Configuration, specify an IP Address for the SQL cluster node. The IP address is a new IP address and NOT VMSQL01’s nor VMSQL02’s.

Then you can finish of the rest of the installation. Set DATA and LOG directory as per your added cluster drives.

Once the main cluster node SQL install is finished, go to the second node VMSQL02.

Run the wizard but this time select Installation -> Add node to a SQL Server failover cluster.

Simply follow the wizard. When you arrive at Cluster Node Configuration,  select the instance you created earlier. It should automatically detect the master cluster.

Then specify the service accounts and finish off the installation.

USING SQL SERVER CLUSTER

So finally we are at the very last step of using the SQL Cluster! YIPPEE!! As mentioned earlier, simply using CLUSTERNAME\INSTANCENAME rather than a specific server name.

Hope this helps,

Tommy