Build a two-node SQL Server 2008 R2 Cluster with iSCSI

Build a two-node SQL Server 2008 R2 Cluster with iSCSI

Lab VMs:
1.iSCSITarget (Windows Storage Server 2008 R2 SP1) (DC and iSCSI Target Server)
2.SQL1 (SQL Server 2008 R2 on Windows 2008 R2 SP1)
3.SQL2 (SQL Server 2008 R2 on Windows 2008 R2 SP1)

[Action Plan]

1.on SQL1 and SQL2

configure ip address for public network
configure ip address for heartbeat network
configure ip address for iSCSI network

2.on SQL1 and SQL2

(1)Add Role and role service
add Application Role and enable Role Service:Distributed Transactions
[V]Distributed Transactions
[V]Incoming Remote Transactions
[V]Outgoing Remote Transactions

(2)Enable Feature
Enable [Failover Clustering] feature

3.on SQL1 and SQL2
Join AD Domain and reboot

4.on iSCSITarget
Create Targets and Virtual Disks for Quorum ,SQL Server and MSDTC(if you need a MSDTC cluster resource)

(1)configure Windows firewall for iSCSI Target Service

3260 / TCP

135 / TCP

138 / UDP

program: %SystemRoot%\System32\WinTarget.exe

program: %SystemRoot%\System32\WTStatusProxy.exe

(2)Create a Target for all Virtual Disks or create a target for each Virtual Disk

OR

(3)add SQL1,SQL2 IP Address(for iSCSI) to [iSCSI Initiator] tab on each Targets(Quorum,DTC,SQL) properties

5.On SQL1

(1)configure shared disk using iSCSI Initiator.

On Targets Tab of the iSCSI Initiator properties, enter the IP Address of the iSCSI target Server and click Quick Connect.

select Target ,then click Connect

On the Volumes And Devices tab, click Auto Configure and the click OK

(2)verify that the volumes are available to be brought onlin and formated in the Disk Management node of the Server Manager console.

(3)Bring disk onlin and formatted, assign a drive letter. (只要在SQL1做一次format與assign drive letter即可)

PS.如果兩個share disk都是512MB(ex.Quorum disk, DTC disk)，可以在iSCSI Target先加一個Quorum Disk，然後就到SQL1的iSCSI做Auto Configure一次，再到Disk Management去format與assign Drive Letter，然後再回到iSCSI Target再加入一個DTC Disk，然後同樣再回到SQL1的iSCSI做Auto Configure -> Disk Management->format->assign Drive Letter，這樣就可以確認哪一個iSCSI Disk是Quorum哪一個是DTC。

PS.沒有DTC Cluster並不會影響SQL Server Cluster的安裝，所以並不一定需要DTC share disk。

6.on SQL2

(1)configure shared disk using iSCSI Initiator.

On Targets Tab of the iSCSI Initiator properties, enter the IP Address of the iSCSI target Server and click Quick Connect.

On the Volumes And Devices tab, click Auto Configure and the click OK

(2) verify that the volumes are visiable with off-line status in the Disk Management node of the Server Manager console.(you don't have to configure or verify drive letter)

7.on SQL1, Create a Windows Cluster

步驟請參考這篇
How to create a failover cluster

https://blogs.msdn.com/b/bradchen/archive/2014/06/18/how-to-create-a-failover-cluster.aspx

8.configure quorum

確認一下Quorum Disk有設定正確，如果沒有設定正確則重新設定一下Quorum。

Failover Cluster Step-by-Step Guide: Configuring the Quorum in a Failover Cluster
https://technet.microsoft.com/en-us/library/cc770620(v=ws.10).aspx

To change the quorum configuration in a failover cluster by using the failover cluster snap-in

Review the configuration of the quorum by typing:

C:\cluster /quorum

9.create msdtc cluster application(optional)

How to Cluster MSDTC
https://msdn.microsoft.com/en-us/library/dd897479(v=bts.10).aspx

10.on SQL1
run SQL Server 2008 R2 setup.exe

(1)Insert the SQL Server installation media, and from the root folder, double-click Setup.exe
(2)When prerequisites are installed, the Installation Wizard will launch the SQL Server Installation Center. click New SQL Server failover cluster installation on the installation page
Detail procedure you can reference follow URL:

How to: Create a New SQL Server Failover Cluster (Setup)

https://msdn.microsoft.com/en-us/library/ms179530(v=sql.105).aspx

11.on SQL2

run SQL Server 2008 R2 setup.exe join SQL2 to exist SQL Cluster
(1)Insert the SQL Server installation media, and from the root folder, double-click Setup.exe
(2)When prerequisites are installed, the Installation Wizard will launch the SQL Server Installation Center. To add a node to an existing failover cluster instance, click Installation in the left-hand pane. Then, select Add node to a SQL Server failover cluster
Detail procedure you can reference follow URL:
To add a node to an existing SQL Server failover cluster
https://msdn.microsoft.com/en-us/library/ms191545(v=sql.105).aspx#Add
Add a Node to an Existing Failover Cluster in SQL Server 2008 (SQL Server Video)
https://msdn.microsoft.com/en-us/library/ee915053(v=sql.100).aspx

12.on SQL1 and SQL2

Configuring the Windows firewall to allow SQL Server Access
allow follow program
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Binn\sqlservr.exe

Configuring the Windows Firewall to Allow SQL Server Access
https://msdn.microsoft.com/en-us/library/cc646023(v=sql.105).aspx

如果需要step by step可以參考這篇外部的blog

Walkthrough: Cluster Setup SQL/Win2008
https://dbperf.wordpress.com/2010/07/10/walkthrough-cluster-setup-sql-win-2008/