Sunday, March 09, 2008

Microsoft SQL 2005 Failover Cluster

Today, I'd like to share creating SQL 2005 Failover Cluster with Microsoft Virtual Server 2005. This involves creating 3x virtual Windows 2003 Enterprise Edition on Virtual Server, creating Quorum disks, configuring cluster and installing SQL 2005

To start, download the Microsoft Virtual Server 2005 here, Virtual Machine Remote Control Client Plus here. Install the Virtual Server 2005, but don't install the Web Server Administration part - we are going to manage it with the VMRCplus. Install the VMRCplus once the Virtual Server 2005 installation finished.

Create a Windows 2003 Enterprise Edition Template

Create a new virtual machine template with the following specs:
1x Dynamic HDD (IDE) - size: 10GB
1x NIC - connects to the Internal Network
512MB RAM

sql-2005-cluster-1

Boot the VM, install Windows 2003 Ent edition, install the VM additional tools and sysprep the machine, shut down

Create a Domain Controller

Create a new virtual machine with the following specs:
Parent: Template
1x Differencing HDD (IDE) - size: 10 GB

sql-2005-cluster-2

1x NIC - connects to the Internal Network

start the virtual machine and do the following:
  • rename the machine to: DC
  • give an IP address: 192.168.0.1
  • install DNS
  • dcpromo
  • reboot
  • create 2x service accounts: _cluster and _sql
  • create domain group for SQL cluster: SQL Cluster
Create a Virtual Heartbeat Network

Create a new virtual network to be used by the cluster for heartbeat. From VM manager, click on Virtual Networks and Create a new network, with physical adapter: None (Guests Only)

sql-2005-cluster-3

Create The First Node (NODE1)

Create a new virtual machine with the following specs:
Parent: Template
1x Differencing HDD (IDE) - size: 10 GB
3x SCSI Adapters - all with Share SCSI bus and using SCSI adapter ID: 7
2x NIC - connects to the Internal Network and Heartbeat Network
3x Fixed HDD (SCSI) - size: 1GB (cluster quorum), 10GB (SQL data shared drive) and 1GB (MSDTC shared drive)

sql-2005-cluster-4

sql-2005-cluster-5

start the virtual machine and do the following:
  • rename the machine to: NODE1
  • change the IP address: NIC1: 192.168.0.10/24, DNS: 192.168.0.1; NIC2: 10.0.0.1/8
  • join the computer to the domain
  • reboot
  • activate 3x SCSI drives, primary partition: cluster quorum -> Q:, SQL data -> S:, MSDTC -> M:
  • install .net framework 2
  • install 'Enable network DTC access'
  • shutdown
Create The Second Node (NODE2)

Create a new virtual machine with the following specs:
Parent: Template
1x Differencing HDD (IDE) - size: 10 GB
2x NIC - connects to the Internal Network and Heartbeat Network
3x SCSI Adapters - all with Share SCSI bus and using SCSI adapter ID: 7. Assign the HDD (.vhd) files to the NODE1 vhd files according to its purposes (e.g. SCSI 0:0 -> cluster quorum, SCSI 1:0 -> SQL data shared drive, SCSI 3:0 -> MSDTC drive)

sql-2005-cluster-6

start the virtual machine and do the following:
  • rename the machine to: NODE2
  • change the IP address: NIC1: 192.168.0.2024, DNS: 192.168.0.1; NIC2: 10.0.0.2/8
  • join the computer to the domain
  • reboot
  • assign drive letters to 3x SCSI drives: cluster quorum -> Q:, SQL data -> S:, MSDTC -> M:
  • install .net framework 2
  • install 'Enable network DTC access'
  • shutdown

Create a Cluster

Start NODE1 virtual machine (make sure NODE2 is off)
start the cluster administratorr
create a new cluster, named: SQL
cluster IP address: 192.168.0.30
cluster account: _cluster
choose Q: as the quorum drive

Start NODE2

On NODE1 do the followings:
add NODE2 to the cluster
change the preference of the private network to use the second NIC card

Create Resource Group For MSDTC

on NODE1, start the cluster administrator and do the followings:
  • rename the Group1 groups to MSDTC
  • create a new resource, IP address; no dependencies, both NODE1 and NODE2 as possible owners and IP address: 192.168.0.40/24
  • create a new resource, Network Name; depends on IP address, both NODE1 and NODE2 as possible owners and Network Name: MSDTC
  • create a new resource, Distributed Transaction Coordinator; depends on Disk M: and Network Name

To enable MSDTC, do the followings:
  • In Control Panel, open "Component services".
  • Expand Component Services, expand Computers, right-click My Computer, and then click Properties.
  • On the MSDTC tab, click Secuity Configuration under Transaction Configuration, click to select the Network DTC Access check box under Secuity Settings, and then click to select the following check boxes under Transaction Manager Communication:• Allow Inbound• Allow Outbound
  • You cannot select Mutual Authentication Required. Therefore, click to select one of the following check boxes:• Incoming Caller Authentication Required• No Authentication RequiredNote For more information about these options, click the following article number to view the article in the Microsoft Knowledge Base:899191 (http://support.microsoft.com/kb/899191/) New functionality in the Distributed Transaction Coordinator service in Windows Server 2003 Service Pack 1 and in Windows XP Service Pack 2
  • Make sure that the Logon Account is set to NTAUTHORITY\NetworkService.
  • Click OK. A message box explains that the MS DTC Service will be stopped and restarted, and that all dependent services will also be stopped and restarted. Click Yes.
    Note If this is a Majority Node Set (MNS) cluster, do not use the MNS resource as the storage device for MS DTC. MS DTC requires a storage resource such as a physical disk.
  • Restart NODE1.
once NODE1 restarted, restart NODE2

Create Resource Group For SQL

on NODE1, start the cluster administrator and do the followings:
  • rename Group0 to SQL
  • make sure NODE1 is the active node for S: drive, cluster and MSDTC

SQL 2005 Installation

run the SQL 2005 setup
on 'Components to Install' setup page, make sure the 'failover cluster' option is selected

sql-2005-cluster-7

virtual servername: sql-cluster
network use: publicIP address: 192.168.0.50

on 'Cluster Group Selection' setup page, select the SQL cluster group for the virtual server resources
make sure NODE2 is selected on the nodes to be included in the virtual server
use the _sql user account for all the services
specify 'NET\SQL Cluster ' as the domain group

Testing

once the SQL 2005 installation finished, you can try to failover the cluster from one node to the other by 'Move Group' from the SQL resource group option

you can also try install the SQL Management Studio on the other computer (e.g. DC) and try to connect to the clustered name of the SQL - sql-cluster.domain.com

sql-2005-cluster-8

References:
http://msdn2.microsoft.com/en-us/library/ms179530.aspx
http://msdn2.microsoft.com/en-us/library/ms189910.aspx
http://support.microsoft.com/kb/301600
http://support.microsoft.com/kb/817064
http://support.microsoft.com/kb/258750