SQL SERVER – Steps to Migrate Cluster Storage from Old Disks to Newly Attached Disks

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 AssignmentNewly Added Storage
Disk Q: Quorum DiskDisk L
Disk X: MSDTC DiskDisk M
Disk R: SQL Data diskDisk N
Disk S: SQL Log diskDisk O
Disk T: SQL TempDB diskDisk P

SQL SERVER - Steps to Migrate Cluster Storage from Old Disks to Newly Attached Disks clusterreplacedisk-1

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:

Solarwinds

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.

  1. Take backups of all the databases including System DBs. [ONLY for disaster recovery]
  2. Present all the drives from the new storage to the Windows Cluster. These disks must be visible under “Available Storage” Failover Cluster Manager.
  3. Now add these disks to the SQL Cluster Role/Group.
  4. From the SQL Server dependencies, remove the drive letters pointing to the old storage.
  5. Take SQL Server Resource to an OFFLINE State.
  6. 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
  7. 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] SQL SERVER - Steps to Migrate Cluster Storage from Old Disks to Newly Attached Disks migrate-clus-01
    For Ex:

    • Change Disk R to Disk E
    • Change Disk N to Disk R
  8. Add the disks to the SQL Server dependencies.
  9. Bring the SQL Resource ONLINE.
  10. Test Application.
  11. 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

  1. From the Start menu (the Start screen on Windows 2012 operating systems), click Administrative Tools.
  2. To launch the Failover Cluster Manager snap-in, click Failover Cluster Manager.
  3. Right-click the cluster node and in the context menu, select More ActionsConfigure Cluster Quorum Settings.
  4. The Configure Cluster QuorumWizard appears.
  5. Click Next to continue.
  6. On the Select Quorum Configuration Option panel, select Select the quorum witness.
  7. Click Next to continue.
  8. On the Select Quorum Witness panel, select Configure a disk witness and then click Next.
  9. On the Configure Storage Witness panel, select the disk group that is added for cluster quorum, and then click Next.
  10. 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.

  1. Remove the existing MSDTC Role
  2. Right-click on Roles and pick the “Configure Role” option.
  3. A new window will open. Click “Next”.
  4. Choose the option “Distributed Transaction Coordinator (DTC)” from the list. Click “Next”.
  5. Give a name for your MSDTC Role. Click “Next
  6. Select the new disk which you want to use. Click “Next
  7. 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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Identifying Deprecated SQL Server Features with Extended Events
Next Post
SQL SERVER – Script – Transaction Log Backups Based on Growth / Size

Related Posts

11 Comments. Leave new

  • Thanks, what will be the case if drives are mounted? is there any extra steps we need to take care.

    Reply
  • 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…

    Reply
  • 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.

    Reply
  • Hemantgiri Goswami
    April 12, 2018 7:53 am

    Hi Pinal,

    Yes, this is most preferred method. I have used this method number of time and it never fail me.

    Reply
  • Michal Robert Piatek
    April 18, 2018 7:21 pm

    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.

    Reply
  • Attila Losoncz
    October 1, 2018 3:34 pm

    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

    Reply
  • 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!

    Reply

Leave a Reply

Menu