One of my clients consulted me for the best and easy method for migrating the Cluster Storage from old to newly attached disks. They wanted to move/reconfigure disks mapped to SQL Server, MSDTC, Quorum.
At present, they were using SQL Server 2014 as a mission-critical database server in a cluster environment. Storage being used was IBM DS 8300. They want to migrate the database from current storage i.e. IBM DS 8300 to new storage IBM DS 8870.
|Current Disk Assignment
|Newly Added Storage
|Disk Q: Quorum Disk
|Disk X: MSDTC Disk
|Disk R: SQL Data disk
|Disk S: SQL Log disk
|Disk T: SQL TempDB disk
My search on the internet combined with my experience, I have come up with below plan for each component.
Change SQL Server Disks in Cluster Group:
SQL Server databases can be migrated on to the new storage by many ways:
- Backup and restore using “WITH MOVE” option to the new storage.
- Detach, Move and Attach the SQL database from the new storage.
The above process is tedious, and your problem shoots up when you want to migrate the storage hosting the System DBs as they need special care while moving to new storage/location. The overall steps needed to accomplish the above task also becomes more. Below are the steps outlined to ease out the above challenges and can also be done in a very simple way.
- Take backups of all the databases including System DBs. [ONLY for disaster recovery]
- Present all the drives from the new storage to the Windows Cluster. These disks must be visible under “Available Storage” Failover Cluster Manager.
- Now add these disks to the SQL Cluster Role/Group.
- From the SQL Server dependencies, remove the drive letters pointing to the old storage.
- Take SQL Server Resource to an OFFLINE State.
- Be sure that the disks should be online. Now start copying all the folders as is from old storage to the new storage. <Bold>DO NOT change any files or folder structures<Bold>.
Disk R: ==> Disk N
Disk S: ==> Disk O
Disk T: ==> Disk P
- Once all the files are copied, you need to swap the drive letters. Such that the new drives will get the old drive letters and vice-versa [This can be directly from Failover Cluster Manager]
- Change Disk R to Disk E
- Change Disk N to Disk R
- Add the disks to the SQL Server dependencies.
- Bring the SQL Resource ONLINE.
- Test Application.
- Once Application testing is completed, disks from old storage can be removed from the Failover Cluster Manager.
While starting the SQL Server resource in Failover cluster manager, you may face this error,
initerrlog: Could not open error log file ‘Drive:\pathname\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG’. Operating system error = 5(Access is denied.). Refer below to solve this issue.
Change Windows Cluster Quorum Disk
- From the Start menu (the Start screen on Windows 2012 operating systems), click Administrative Tools.
- To launch the Failover Cluster Manager snap-in, click Failover Cluster Manager.
- Right-click the cluster node and in the context menu, select More Actions> Configure Cluster Quorum Settings.
- The Configure Cluster QuorumWizard appears.
- Click Next to continue.
- On the Select Quorum Configuration Option panel, select Select the quorum witness.
- Click Next to continue.
- On the Select Quorum Witness panel, select Configure a disk witness and then click Next.
- On the Configure Storage Witness panel, select the disk group that is added for cluster quorum, and then click Next.
- Review the report that the wizard generates and click Finish to complete the operation.
Change MSDTC Disk:
The easiest way I have known is to just re-configure MSDTC using the new disk.
- Remove the existing MSDTC Role
- Right-click on Roles and pick the “Configure Role” option.
- A new window will open. Click “Next”.
- Choose the option “Distributed Transaction Coordinator (DTC)” from the list. Click “Next”.
- Give a name for your MSDTC Role. Click “Next”
- Select the new disk which you want to use. Click “Next”
- Click “Next” to check the summary and then click “Finish”
These steps helped my client migrate with very less downtime. The only downtime for SQL was to copy/paste the file from old to new storage. Please comment and let me know if it helped you.
Reference: Pinal Dave (https://blog.sqlauthority.com)