Setup up a SQL Server 2008 R2 cluster using Virtual Machines

Sometimes, SQL Server Professionals need to practice setting up a SQL Server 2008 R2 Cluster on their local machines to gain some experience before setting it up for real in Staging or Production environments. In the past, this used to be virtually impossible without specific hardware such as a Storage Area Network (SAN) which is typically used for hosting the Shared Disks. However, with the release of Windows Storage Server 2008 and Windows Storage Server 2008 R2, it’s become much easier to do so using Virtual Machines. Please note that the following configuration is NOT recommended for a real-world Production environment. This configuration should only be used for a Development environment.





The following are the steps on how to go about it:

1. Setup 3 Virtual Machines running Windows Server 2008 R2 with 2 NICs on each of them. You can use either VMWare Player or Virtual Box. Configure the 1st NIC with a dynamic IP Address that will be used to connect to the host machine. Configure the 2nd NIC with a static IP Address that will be used by the 3 Servers to talk to each other.



2. Configure the 1st server as your Active Directory Domain Controller. You can call it MainServer.contoso.net.



3. Add the 2nd and 3rd servers to the Active Directory created on the 1st server. You can call them SQLServer1.contoso.net and SQLServer2.contoso.net respectively.



4. Configure MainServer.contoso.net as the Storage Server that will host the Shared Disks. You will need to install the iSCSI Target 3.3 software that can be found in the ISO image of Windows Storage Server 2008 R2. For more information on how to install it, please refer to: http://blogs.technet.com/b/josebda/archive/2010/09/27/windows-storage-server-2008-r2-and-the-microsoft-iscsi-software-target-3-3-are-available-on-msdn-technet-here-s-how-to-install-them.aspx. NOTE: You ONLY need the iSCSI Target software. You do NOT need to install all the other bits for Windows Storage Server 2008 R2.



5. Configure the iSCSI Target and 2 Shared Disks on MainServer.contoso.net i.e. 1 for the quorum and the other for SQL Server data. This blog gives excellent step-by-step instructions on how to do so: http://mohsartawi.wordpress.com/2010/05/25/implementing-a-virtual-machine-failover-cluster-with-windows-storage-server-2008/. Even though it talks about Windows Storage Server 2008 (not R2), the steps are very similar.



6. Create a Service Account in Active Directory such as SQLService which will be used to run the SQL Server instances on both the servers.



7. Run the SQL Server setup on SQLServer1.contoso.net and choose the option “New SQL Server failover cluster installation”. Use a generic name such as SQLServer.contoso.net for the SQL Cluster.



8. Run the SQL Server setup on SQLServer2.contoso.net and choose the option “Add node to a SQL Server failover cluster”.



9. Create a sample database on SQLServer1 but connect to it from your host machine using SQLServer.contoso.net. Shut down SQLServer1. You should still be able to access the database because SQLServer2 will take over.