DBA Sensation

January 31, 2013

SQL 2008 Active-active cluster on windows 2008 R2 Share storage migration

Filed under: 3. MS SQL Server — Tags: , , , , , — zhefeng @ 11:16 am

This is my POC doc before we migrate our production sql 2008 cluster to new storage. it has two parts: first part simply install the cluster on 2 ESX VM images by using vmfs shared disks. Second part is the storage migration part.

====Part 1: build sql 2008 cluster

IP Address mapping: vmsql2008cls01 vmsql2008cls02 vmsql2008cls vmsql2k8clsvip1 vmsql2k8clsvip2 vmsql2008clsdtc

Initializing the LUN and labelling them:
G: OLTP (OLTP data volume)
H: OLAP (OLAP data volume)
I: Quorum (Quorum Disk)
R: msdts

1. enable “application server” roles in OS “Server Manager”
check “.NET Framework 3.5.1”, “Distributed Transactions – Incoming Remote Transactions”, “Distributed Transactions – Outgoing Remote Transactions”

2. Add “failover clustering” feature

3. setup cluster in cluster manager, cluster IP/name: vmsql2008cls

4. setup clustered MSDTC service
Open Cluster manaager, In “services and applications” add “DTC” service, use name “vmsql2008clsdtc”, ip “”

5. Install first instance. Run under command prompt: Setup.exe /ACTION=PrepareFailoverCluster (note: you have to use this setup option and run it on BOTH nodes)

1). pickup instance features and shared features (except reporting services)

2). Default instance id: MSSQLSERVER

3). Cluster Security Policy – “Use service SIDs”

4). Server Configuration – use the same account for all sql server services “pgdev\sevice.pg.prod”, keep all the default startup type (especially for dtabase engine “manual”)

6. Run “setup” to finish cluster on ONLY active node (owns the storage)
Run under command prompt: setup.exe /ACTION=CompleteFailoverCluster
1). SQL Server Network Name for first instance: vmsql2k8clsvip1
2). pickup “OLTP” disk
3). ip address for virtual:
4). done with installation. Varify it in cluster admin

7. Install 2nd instance.
1). run Setup.exe /ACTION=PrepareFailoverCluster on BOTH nodes
2). run setup.exe /ACTION=CompleteFailoverCluster on ONLY active node
3). virtual IP and network name: vmsql2k8clsvip2
4). pick up “OLAP” disk
5). done with installation

====Part 2: migrating from old LUNs to new LUNs

8. Add new disks on Node1 (V, W, X, Y) and plan their mapping drives:
V -> G
W -> H
X -> I
Y -> R

9. in cluster manager, “add disk” to add all new disks to storage catalog

10. move the new disks to corresponding service or applications except Quorum disk (right click on disk, “more actions” -> “move this resource to another service or application”)

11. Stop your clustered application(s) (the virtual SQL instance in this case) and copy data from old drives to new drives (include msdtc drive)
1). In Failover Cluster Management, take all SQL instances and MSDTC service offline. This will stop SQL services and release any open handles on the SQL data files so we can copy the data to the new drive as below order:
G -> V
H -> W
R -> Y

2). When the data copy is complete, change the drive letter of an existing data drive to a temporary letter. Set the drive letter of the new drive to the original drive letter of your original storage. (If the original data drive was G:, the new drive should be G: or SQL will not be able to find its files and thus will not start).
So set old ones to temp drive letter first:
G -> O
H -> P
R -> Q

12. Then Map new drives to old drive letters:
V -> G
W -> H
Y -> R

13. change the dependency for each application and service. For example, for first instance ‘sql server’, right click “properties” -> dependencies -> change to “cluster disk 5” (the new disk)

14. delete MSDTC service and recreate
1). delete the resource “MSDTC” in dtc service catalog
2). add resource “MSDTC” back to catalog
right click on dtc sevice icon on the left -> add a resource > more resource -> “2- Add Distribute Transaction Coordinator”
3). add dependency for the new dtc service
right click on “microsoft Distributed Transaction Coordinator” -> ” properties” -> “Dependencies” -> add virtual server name And new cluster disk as depenndencies for this service.
4). bring it online.

15. Move quorum
1). right click on the cluster name under “failover cluster manager” -> “more actions” -> “configure cluster quorum settings”
2). choose default Quorum configuration ” Node and disk Majority”
3). check new cluster disk for “storage witness”,
4). confirm and done with reconfigure
5). no need to change the drive letter since it’s quorum, so the new quorum is “X”

16. bring all old cluster disk offline
that’s: O, P, Q, I

17. bring all clustered application(s) online

18. Verify services and applications are online and functional, and failover works

19. delete old disks in cluster manager, delete partitions as well.

1. How to Move a Windows 2008 Cluster to New SAN Storage

Create a free website or blog at WordPress.com.