Monday, 12 August 2013

Microsoft SQL Server 2008 R2 Failover Cluster in VMware Workstation

Setup Microsoft SQL Server 2008 R2 Failover Cluster in VMware Workstation

Hardware Information:

HP ElieBook Intel ® Core ™ i-5-3320M CPU @ 2.60GHz
Installed memory (RAM):          8.00 GB
Hard disk:                                           500GB
System type:                                     64-bit Operating System
Operating System:                          Windows 7 Enterprise

Required Software:

1) VMware Workstation 9.0.2
2) Windows Server 2008 R2 (with trial license free for 180 days)
7600.16385.090713-1255_x64fre_server_eval_en-us-GRMSXEVAL_EN_DVD
3) Starwind Software (with free trial for 30 days)
4) SQL Server 2008 R2 (Download from Microsoft website)
5) Three virtual machines: one setup as Domain Controller and DNS Server(DC), the other two as  clustered nodes (WIN1 and WIN2).

I. Installation of VMware and Create Virtual Machines

1) Install VMware
2) Create first VM machine named DC (Domain Controller)
Choose Typical (recommended) and Click Next


Click on  I will install the operating system later and click Next 



 Choose Microsoft Windows for Guest Operating system and choose Windows Server 2008 R2x64 for version and click Next




Give Virtual machine name: DC and type in the path of the location of VM going to be created. 


 Default maximum disk size is 40GB, you may adjust allocation size  according to your own needs.


 Review the summary page and click Finish. You may also customize your Hardware settings by clicking Customize Hardware…



 3) click Edit Virtual Machine Settings tab to point Virtual CD Rom to use ISO image file:





 Choose Windows Server 2008 R2 Enterprise Edition


















 5) Create 2 more VM Machine WIN1 & WIN2 with the same steps as given for DC VM Machine. The configuration For all three machine should be same.

  7)  Configure network card information 
Select bridged network for all three Machine for first network card.
Add 1 more network card for both 3 machine (DC,WIN1 & WIN2 ) for configuring Heartebeat.




 Select Bridged Network option.




 a) assign static I.P to three machines as For First Network card:
                                                           
                                                                                                                                                                                                                DC                                      WIN1                                   WIN2                   
I.P Address                         192.168.102.128             192.168.102.129             192.168.102.130
Subnet  Mask                     255.255.255.0                  255.255.255.0                  255.255.255.0
Default Gateway             192.168.102.2                  192.168.102.2                  192.168.102.2
DNS Server                        192.168.102.128             192.168.102.128             192.168.102.128

b) make sure three machines can ping each other.
c) Add a second bridged network card from VMware and configure Heartbeat.

              DC                                          WIN1                                    WIN2
IP:                                          10.0.0.1                                10.0.0.2                                10.0.0.3
Default gateway:                 -                                              -                                              -
DNS:                                      10.0.0.1                                10.0.0.1                                10.0.0.1

3) Install Active Directory Service on DC and thus creating DNS Server as well.

1)     Install Active Directory Service


















 2) start dcpromo.exe command and the next screen will show up:
















 Choose Assign Static I.P. option

























 4, Add Win1 and Win2 to Domain Controller

a)      Add WIN2 to Domain Controller
Click Change 




Enter Domain name as testcluster.com 







 a)      Add WIN1 to the domain.


III. Install Starwind Software to create SCSI disks:

(For installing Starwind first start Microsoft iSCSI Service.)
1)     Install Starwind software on DC virtual machine

























 1)     Start Starwind software
Add Host.
Enter IP of DC and click OK.
testcluster.com will appear in left pane.
In the left pane, right click testcluster.com and click Connect
At the login prompt, the username field will have root already entered. Enter starwind for the password and click OK.
In the left pane, right click Targets and select Add Target.
Add a target alias as of Quorum and click Next.




 Leave Storage type as Hard Disk and click Next




 Leave Device Type as Basic Virtual and click Next




Leave Device type as Image File Device and click Next




 Click Create New Virtual Disk and click Next




 Type in name of c:\q.img, enter 5000 for the size in MB field and click Next




 Leave Asnychronous mode selected, select Allow Multiple concurrent iSCSI connections (clustering) and click Next




 Bypassing this screen and click Next




 Click Next to continue




 Click Finish to complete creating an iSCSI disk




 Do the same for the following disks:

Name                    File                                        Size
Data                      c:\s.img                               5000Mb
Logs                      c:\l.img                                5000Mb
MSDTC                 c:\m.img                             5000Mb

Close StarWindManagement Console
Configuring the iSCSI initiator
Click WIN1 tab




 If Microsoft iSCSI service is not already started, you will see next popup and just click Yes to continue.



 Enter Target name or I.P in the Target window: Click Connect for each disk.




Do the same for the WIN2.
Open the computer management window:




 Right click disk management will bring up the next window:




 Bring the disks online and initialize the disks and label them













 Do the same for DISK2, DISK3 and DISK4, assigning them drive letters Q,L and M and labeling them, Logs, Quorum and MSDTC, respectively.




 IV. Adding Failover Cluster Feature on each node and Verifying Cluster:
       
      1)     Adding Failover cluster feature on each node(WIN1 and WIN2)






 1)     Validating a Windows failover Cluster







Enter the server name of the clustered node:
Win1.testcluster.com
Win2.testcluster.com










 Testing Completed Success fully , we can go ahead install SQL Server 2008 R2





 V. Installation of SQL Server 2008 R2:

1) Runing Failover Cluster Wizard




 Enter win1.testcluster.com, win2.testcluster.com




 Enter vmcluster for Cluster Name and 192.168.102.131 for I.P Address.







 Installing MSDTC on a failover cluster










 Enter vmclusterDTC for  Name and 192.168.102.132 for I.P Address.




 Check mark Cluster Disk4 and click Next










 vmclusterDtc service is created and online




 1)     Installing SQL Server 2008 R2 on WIN1




 Choose New SQL Server Failover cluster installation




























 Choose Disk2 and Disk3




 Uncheck DHCP and type in I.P address 192.168.102.133







 Choose use the same account for all SQL Server services



















 VI. Add WIN2 to the SQL Cluster
Choose Add node to a SQL Server failover cluster



















 Enter password for SQL Server Engine and SQL Server Agent account.















 VII. Test Failover
 Start Microsoft SQL Server Management Studio from WIN1 machine and connect to SQL Server on the SQL Cluster do a query (see below)




 Current owner is WIN2




 I will initiate Failover to WIN1 from WIN2
Click Move SQL Server (MSSQLSERVER) to WIN1 and wait for about 20 seconds.




 Now current owner is WIN1




 In the management Studio, click the Execute button again, the same query will run and return the following result




Congratulations. You have successfully setup SQL Server 2008 R2 Failover Cluster on your personal computer. 


If you get stuck with the setup somewhere, please leave a comment here and I will be glad to help. 

Have fun!