Extending a Single SQL Server Node to a Cluster

This guide explains how to install a single SQL Server node and extend it to a clustered node. Please read this document carefully prior to installation.

Planning Steps:

• Start with a Microsoft SQL Server standalone node, where data resides on the C drive.

Note: It is recommend that similar hardware be used for the standalone node (for the purpose of this guide, we will call this node the backup node).

• On the new backup node setup Windows failover cluster and setup a one node cluster (setup alongside a file share quorum).

• Setup Datakeeper Cluster Edition on the new node.

• Create empty DataKeeper Volume resource(s) using the Failover Clustering UI. Provide a name(s) that best describes its intended use (Example: “DataKeeper Volume F (NonMirrored)”).

The following steps explain how to manually create this resource:

a. In the Failover Cluster Manager, create an empty role, right-click on Role and select Create Empty Role.

b. Right-click the empty role and select Add a Resource, More Resources, then select Add DataKeeper Volume.

c. Right-click the new DataKeeper Volume resource and select Properties.

d. Enter the Resource Name you chose earlier (Example: “DataKeeper Volume F (NonMirrored)”) then select Done. No other properties changes are needed at this time. Follow the steps below for setting the Properties needed for the non-mirrored resource.

e. Repeat the steps a – d for the E drive.

Assign the following properties using Powershell:

VolumeLetter = ”F” (if the drive letter is F, otherwise whatever the drive letter is)

NonMirrored = 1 (there is no space between Non and Mirrored)

Assign the following properties using Powershell:

Get-ClusterResource “DataKeeper Volume F (Non-Mirrored)” | Set-ClusterParameter –Name VolumeLetter –Value “F”

Get-ClusterResource “DataKeeper Volume F (Non-Mirrored)” | Set-ClusterParameter –Name NonMirrored –Value 1

After the storage is created, right-click and select Remove from empty role. This will move the storage to Available Storage which can now be used by SQL Server installation in the following steps.

• Run SQL Server setup, choose the SQL Server Cluster edition to install. Select the same features that are installed on the primary node. (Note: Use the domain user ID and password to start SQL Server services instead of local account.)

• During installation setup will prompt you for failover cluster storage, select the storage you created earlier.

• Copy the database from the primary server to the new backup node the use the BACKUP DATABASE command to make a backup of the database.

• Connect to the Cluster database on the backup node and use the RESTORE DATABASE command to restore the database.

It is highly recommend using the MOVE option of the RESTORE option to move the files to separate drives (separate DATA and LOG file). The following example shows how to move a sample Sales database to different drives. (Note: The data and the log files are moved to different volumes.)

RESTORE DATABASE sales

FROM DISK = ‘C:\Backup\Sales.bak’

WITH RECOVERY,

MOVE ‘Sales_Dat’ TO

‘E:\MSSQL11.MSSQLSERVER\MSSQL\Data\Sales.mdf’,

MOVE ‘Sales_Log’ TO

‘F:\MSSQL11.MSSQLSERVER\MSSQL\Data\Sales.ldf’;

• Users can now be moved to the new database and the new backup server. Also, the original primary server can now be reconfigured. It is recommend that the entire Windows operating system be re-installed. Once you have re-installed the operating system, setup failover clustering and join the cluster created earlier. (Note: When joining the cluster,select “No, I do not require support from Microsoft”. The cluster can be verified later in the process.)

• Setup Datakeeper Cluster Edition on this server.

• Remove both non-mirrored DataKeeper volume resources from the MS SQL Server cluster group.

• Delete both non-mirrored DataKeeper Volume resources from Storage.
  (Note: It is important to remove the resource from both the cluster group and available storage. The resource should be deleted from failover clustering before proceeding.)

• Use the DataKeeper GUI to create jobs that contain the E and F volume mirrors for all nodes in the cluster. Choose the node that is currently running Microsoft SQL Server as the source. After creation choose Yes to add the volumes to Failover clustering

• Add the DataKeeper Volume E and DataKeeper Volume F storage to the SQL cluster group.
  Right-click on the resource and select Add Storage.

• Setup SQL Server using the cluster edition Add Node to a SQL Server Cluster option.

• Finish by adding dependencies to the Microsoft SQL Server resource for both the DataKeeper volume resources.

The single node database has now been converted to a clustered highly available database and is ready for failover testing.