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 L |
| Disk X: MSDTC Disk | Disk M |
| Disk R: SQL Data disk | Disk N |
| Disk S: SQL Log disk | Disk O |
| Disk T: SQL TempDB disk | Disk P |
SOLUTION/WORKAROUND
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]
For Ex:- 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.
SQL SERVER – Event 17058 – initerrlog: Could not Open Error Log File
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)
14 Comments. Leave new
Thanks, what will be the case if drives are mounted? is there any extra steps we need to take care.
thats the most compicated way i could think of…
the way we use to do that:
disks from old storage get virtualized through new storage.
change zoning and reboot server and update storage driver if necessary
assign correct drive letters to “new” disks
copy disks on storage side from old to new storage
done with nearly no downtime…
I work a lot with SAN storage, I recommend that after you add the new drives to the Windows cluster, you add them to a test group and test moving the group from server to server. This can be done without impacting a production system.
Hi Pinal,
Yes, this is most preferred method. I have used this method number of time and it never fail me.
For copying the data I would use robocopy /MIR + I would run robocopy /SEC /SECFIX just to make sure that the security is set correctly. security should not be an issue if you place data in dedicated folders (ie. D:Datadbfile.mdf instead of D:dbfile.mdf) since the ACLs are with the folder which should be moved as a whole. To move the system databases this can be achieved with the alter database modify file (name, filename) command except master which can be moved by changing the proper startup parameters (or the registry keys if you wish) and moving the files (mdf, ldf) while the role is offline.
Dear Pinal!
This is a great tutorial but I have a question:
How can I remove the drive letters pointing to the old storage from the SQL Server dependencies using the Failover Cluster Manager? Can you please give me a detailed explanation?
Thank you!
Kind regards,
Attila
Is great , but to have the least time offline to the database I made a backup full / restore on new disks (database with alternative name). In the “work window” a differential backup/restore o. Then I rename the databases.
Other ways is mirroring database.
saludos!
What is this SQL Server dependencies mean exactly?
Also before making sqlserver offline, shouldnt we give command ” alter database modify fle” to the new location?
what do you mean by step 5 Take SQL Server Resource to an OFFLINE State.
please more detail,
do I need just stop sql server role service in cluster manager, no need stop sql server instance service in active node.
please advice