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/