locked
SQL database migration on new storage RRS feed

  • Question

  • Hi,

    Is it possible to migrate SQL database on new storage? we have Always on enabled on SQL 2014  and its 4 nodes Windows 2012 R2 cluster. Now we want to retire our existing SAN storage and want to move existing databases on new SAN storage. Please let us know how to proceed for this.


    Kuldeep

    Tuesday, April 10, 2018 8:26 AM

Answers

  • I am using HPE 3par SAN storage. I have SQL FCI on WSFC and using AOL as well.

    Kuldeep

    How are 4 nodes configured how many nodes are part of SQL FCI. Plus there is nothing like alwayson it is wrong term the correct term is availability group.

    You would have to follow normal process of SAN migration. I had old doc which had these steps copied from somewhere I dont know the source

    Steps to Migrate Windows Server Cluster Storage

    Cluster DB server Details:


    Before Migration:  

    1. Stop the application.
    2. Note the SQL server configuration and windows cluster details.
    3. Take SQL Server complete backup this should include backup of all user and system databases.
    4. Also backup logins and user permissions just in case.
    5. Disable SQL Server agent and replication(if any) make sure no pending transactions are left.
    6. Stop SQL Server service.

    Present the new LUNs

    Please prepare the new LUNs from new storage with same size as old storage, 

    Present the new LUNs:

    Now Add RDM LUN’s to both the nodes of cluster. SAN admin or storage guy to take care of this.

    Preparing the new RDM’s in Windows:

    Note: these steps need to preform only one node 1. The steps are mentioned in This Link

    1. Open Disk Management and Rescan the server for new disks
    2. Right click on the first new drive and select “Online”
    3. Right click again on the first new disk and select “Initialize”
    4. Now right click in the right area of the first new disk and pick “Create Volume”
    5. Complete the new volume wizard and assign a temporary drive letter

    Repeat Step 2 – 5 for each new drive

    Add the new drives to the cluster:

    1. Open “Failover Cluster Manager”
    2.  Expand out the cluster you are working on and select the Storage item in the left tree.
    3.  On the right click Add a Disk
    4.  Make sure there are check marks beside all of the new drives you wish to add as a cluster disk
    5.  Click OK
    6. Verify that the new disks now appear under Available Storage in the middle column.

    Now you have added new storage to our cluster, we have both Old and New storage to our cluster.

    Copy Data from Old storage to New storage

    1. We need to move our data from Old storage to new storage.
    2. Move all database, transaction log, tempdb and other such files to new storage.
    3. This may or may not transfer permissions but that can be set later after migrating

    Move the Cluster Quorum Disk:

    1. Open the Cluster Manager MMC, expand the cluster.
    2. In the left hand pane, click the cluster name at the top of the list.  In the actions pane go to More Options and select Configure Cluster Quorum Settings
    3. Follow through the wizard and the only thing you need to change is on the Configure Storage Witness page.  On this page, simply select the check next to the new storage disk.
    4. You have now successfully moved your cluster quorum to another drive, but let’s verify it.  Open Windows explorer and view the new drive.  You should now see a folder called Cluster, which verifies the move
    5. If you want to reuse the old drive letter (Typically Q:), then right click the old drive under the Available Storage group and select Change Drive Letter.  Now you can right click the old drive and select delete, which will remove it from the Available Storage group in Cluster Manager
    6. In order to assign your new drive to the previously used drive letter, simply right click it in Cluster Manager and select Change Drive Letter.
    7. After changing the drive letter you now need to restart the cluster service

    Move the SQL Data Disk/Log Disk/Temp DB Disk:

    1. Open “Failover Cluster Manager”
    2. Expand out the cluster you’re working on and select “SQL Server” under Services and applications
    3. Select “Add storage” from the menu on the right
    4. Select the new drive from the list, and click OK
    5. Take SQL offline
    6. Verify that SQL Server and SQL Server Agent are offline
    7. Open Windows Explorer and copy the SQL data from the old drive to the new drive
      1. Open Windows Explorer and copy the SQL Log from the old drive to the new drive.
    8. Back in Failover Cluster Manger right click on the old disk and select “Change drive letter”

    10. Make the old drive a temporary drive letter other than what it currently is, Click OK

    11. Next right click the new drive and select change drive letter, set the new drive’s letter to what the old drive was for both Data /Log and Temp

    12. Right click on SQL Server and select “Bring this resource online”, do the same for SQL Server Agent

    13. Do the failover to Second node and test the failover

    14. Verify the all your databases and other components in SQL server

    15. Now bring the SQL offline

    16. Remove the Old storage drives from SQL dependencies and Windows cluster

    17. remove the old storage from server level

    18. Bring the SQL online and check.

    19. Check Failover and failback.

    Reference:

    https://mssqltrek.com/2012/06/28/how-to-add-a-new-diskdrive-to-sql-server-failover-cluster/


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Tuesday, April 10, 2018 1:18 PM

All replies

  • Yes it is possible but before that please let me know that are you using shared storage or local storage attached to each node. Do you also have SQL Server FCI on WSFC or just plain Availability groups on WSFC ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, April 10, 2018 10:15 AM
  • I am using HPE 3par SAN storage. I have SQL FCI on WSFC and using AOL as well.

    Kuldeep

    Tuesday, April 10, 2018 12:25 PM
  • I am using HPE 3par SAN storage. I have SQL FCI on WSFC and using AOL as well.

    Kuldeep

    How are 4 nodes configured how many nodes are part of SQL FCI. Plus there is nothing like alwayson it is wrong term the correct term is availability group.

    You would have to follow normal process of SAN migration. I had old doc which had these steps copied from somewhere I dont know the source

    Steps to Migrate Windows Server Cluster Storage

    Cluster DB server Details:


    Before Migration:  

    1. Stop the application.
    2. Note the SQL server configuration and windows cluster details.
    3. Take SQL Server complete backup this should include backup of all user and system databases.
    4. Also backup logins and user permissions just in case.
    5. Disable SQL Server agent and replication(if any) make sure no pending transactions are left.
    6. Stop SQL Server service.

    Present the new LUNs

    Please prepare the new LUNs from new storage with same size as old storage, 

    Present the new LUNs:

    Now Add RDM LUN’s to both the nodes of cluster. SAN admin or storage guy to take care of this.

    Preparing the new RDM’s in Windows:

    Note: these steps need to preform only one node 1. The steps are mentioned in This Link

    1. Open Disk Management and Rescan the server for new disks
    2. Right click on the first new drive and select “Online”
    3. Right click again on the first new disk and select “Initialize”
    4. Now right click in the right area of the first new disk and pick “Create Volume”
    5. Complete the new volume wizard and assign a temporary drive letter

    Repeat Step 2 – 5 for each new drive

    Add the new drives to the cluster:

    1. Open “Failover Cluster Manager”
    2.  Expand out the cluster you are working on and select the Storage item in the left tree.
    3.  On the right click Add a Disk
    4.  Make sure there are check marks beside all of the new drives you wish to add as a cluster disk
    5.  Click OK
    6. Verify that the new disks now appear under Available Storage in the middle column.

    Now you have added new storage to our cluster, we have both Old and New storage to our cluster.

    Copy Data from Old storage to New storage

    1. We need to move our data from Old storage to new storage.
    2. Move all database, transaction log, tempdb and other such files to new storage.
    3. This may or may not transfer permissions but that can be set later after migrating

    Move the Cluster Quorum Disk:

    1. Open the Cluster Manager MMC, expand the cluster.
    2. In the left hand pane, click the cluster name at the top of the list.  In the actions pane go to More Options and select Configure Cluster Quorum Settings
    3. Follow through the wizard and the only thing you need to change is on the Configure Storage Witness page.  On this page, simply select the check next to the new storage disk.
    4. You have now successfully moved your cluster quorum to another drive, but let’s verify it.  Open Windows explorer and view the new drive.  You should now see a folder called Cluster, which verifies the move
    5. If you want to reuse the old drive letter (Typically Q:), then right click the old drive under the Available Storage group and select Change Drive Letter.  Now you can right click the old drive and select delete, which will remove it from the Available Storage group in Cluster Manager
    6. In order to assign your new drive to the previously used drive letter, simply right click it in Cluster Manager and select Change Drive Letter.
    7. After changing the drive letter you now need to restart the cluster service

    Move the SQL Data Disk/Log Disk/Temp DB Disk:

    1. Open “Failover Cluster Manager”
    2. Expand out the cluster you’re working on and select “SQL Server” under Services and applications
    3. Select “Add storage” from the menu on the right
    4. Select the new drive from the list, and click OK
    5. Take SQL offline
    6. Verify that SQL Server and SQL Server Agent are offline
    7. Open Windows Explorer and copy the SQL data from the old drive to the new drive
      1. Open Windows Explorer and copy the SQL Log from the old drive to the new drive.
    8. Back in Failover Cluster Manger right click on the old disk and select “Change drive letter”

    10. Make the old drive a temporary drive letter other than what it currently is, Click OK

    11. Next right click the new drive and select change drive letter, set the new drive’s letter to what the old drive was for both Data /Log and Temp

    12. Right click on SQL Server and select “Bring this resource online”, do the same for SQL Server Agent

    13. Do the failover to Second node and test the failover

    14. Verify the all your databases and other components in SQL server

    15. Now bring the SQL offline

    16. Remove the Old storage drives from SQL dependencies and Windows cluster

    17. remove the old storage from server level

    18. Bring the SQL online and check.

    19. Check Failover and failback.

    Reference:

    https://mssqltrek.com/2012/06/28/how-to-add-a-new-diskdrive-to-sql-server-failover-cluster/


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Tuesday, April 10, 2018 1:18 PM