none
SQL cluster 2008 R2 migration to new Hardware in VM

    Question

  • Hi,

    We have a task to migrate existing Physical SQL Cluster DB to new Hardware but in VM. (cannot argue with the client)

    hence our plan is to install new Cluster in VM with same Windows and SQL version i.e Win 2K8 R2 ENT w/SP and SQL 2K8 R2 STD w/SP and use SQL mirroring option for the DB to migrate on the new Server (keeping the downtime to as low as possible and advantage of bringing the old server online in case of any issues..)

    Below points are for a single instance, couple of DB to migrate:

    1. Keep the  physical production server running
    2. In-Parallel build New SQL Cluster in VM (keep same version windows/sql)
    3. Keep Same drive letter for Quorum, MSDTC, DATA, LOG & Backup
    4. Keep the same Instance Name, SQL Network Name can be different (will be changed to old network name once the PROD is shutdown)
    5. Check all logins windows and SQL to be same on both the clusters
    6. Copy all the jobs, SSIS packages etc.. .create the location folder as per current server on the VM SQL Cluster
    7. Perform full backup of old cluster company-db databases or use the existing latest backup (full, differential, transaction)
    8. Restore databases in the new cluster with no recovery so additional log file or differential backup can be restored
    9. Create SQL Mirroring for the Database and select the principal server (old cluster) and the mirror server (new VM cluster)
    10. Choose mirroring without witness ( no auto failover as we don’t need the old server later)
    11. Start the mirroring – (hopefully everything synchronized and working)
    12. During the downtime –
    13. Stop the application/ client connection – if possible
    14. Failover the DB through Mirroring option
    15. Once the mirrored server becomes Principal and data is accessible – stop the mirroring
    16. Now the new VM SQL Cluster DB is online
    17. Shutdown the old cluster company-db (Node1 & Node2)
    18. Come back to VM SQL Cluster and change the Network name to the old server network name (so that client connections are not affected)
    19. Check if the DNS entries are updated with correct name and IP address
    20. Delete DNS entries for old company-db on DNS Servers if not deleted, delete the old server SQL network name in computer object
    21. Stop SQL Resources on the new VM SQL Cluster (take it offline all the resources)
    22. Bring online the SQL Resource and make sure the SQL resources are online and new network name is reachable
    23. Wait till you see machine name company-db in active directly (AD) and DNS server will have entry for company-db
    24. Verify connectivity for company-db from outside.
    25. If things are not going as per the plan then shut down the new VM cluster
    26. Revert back the DNS settings
    27. Power up the Old SQL Server
    28. The DB will show “restoring mode” – use command to make it online “RESTORE DATABASE (dbname) WITH RECOVERY”
    29.  Check the DB
    30. Start using the OLD server again
    31. Troubleshoot the failed scenario ;)

    Need to know if the above steps are okay, and any consideration once the new VM SQL Cluster is online.

    Kindly let me know if there anything else to transfer apart from the logins, jobs, folder location etc.. !! 

    Many Thanks

    Saturday, August 09, 2014 11:25 AM

Answers

All replies

  • Hello,

    I doubt you can install both instances with the same instance name (computername\instance name or computername), deal with it with the SQL Network Name, and then be able to create a database mirror between both instances.

    The procedure should not work.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Saturday, August 09, 2014 9:20 PM
    Moderator
  • Hi Alberto,

    Thanks for the reply !!

    Well i have also mentioned the same information, if you see piont#4

    (Keep the same Instance Name, SQL Network Name can be different -will be changed to old network name once the PROD is shutdown)

    Here its a named instance for eg: ABC, so the SQL network name (virtual name) will look like this:

    PROD Cluster: SQL01\ABC

    VM Cluster: SQL02\ABC (later will be changed to SQL01\ABC - so client/app don't need to change anything).

    If anyone can share some easy way to move the logins/ jobs/ etc then will be of great help !!

    Thanks

    Sunday, August 10, 2014 1:54 PM
  • Hello,

    Well if you don’t change the instance name (name = ABC) then its OK.

    Try the following resources about transferring SQL Server logins:

    http://support.microsoft.com/kb/918992/en-us

    http://www.mssqltips.com/sqlservertip/2081/transfer-sql-server-jobs-task-and-transfer-sql-server-logins-task-in-ssis/

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, August 11, 2014 4:02 AM
    Moderator
  • Have you thought about the possibility of restoring the master/msdb databases to the new VM cluster as well, so that you dont have to worry about logins/jobs/other configs etc when you do the switch over?

    More over Once the new server name is changed  still if you do Select @@servername it will point to SQL02\ABC .

    You need to change the entries in sys.servers http://msdn.microsoft.com/en-gb/library/ms178530.aspx. However if you are moving the master db of the SQL01\ABC instance as well then it is not required.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Monday, August 11, 2014 9:59 AM
  • @ Alberto: Thanks for those links - it was indeed  helpful !!

    @ Ashwin: I have noted down all your suggestions, will try to move the master and msdb db in my lab-setup and will update further. I am aware that we don't need to move tempdb but how about model db ? will it make any difference ?

    Well on the second point - i have a clustered instance, lets say sql01\abc, i have changed the network name to sql2 (under cluster), did move-group to both the nodes, checked the sql configuration protocols and then used select @@servername - and i could see the updated network name i.e. sql2\abc  :)  -- so i didn't change anything under "sys.servers"... but have noted your points as it will be helpful in troubleshooting :)

    (ur ms link is not working)

    Many Thanks !!


    • Edited by Tariq_Shaikh Monday, August 11, 2014 6:03 PM update
    Monday, August 11, 2014 6:02 PM
  • Hi,

    For Model database, it depends if you have changed lot of things in the model database for all the new databases to have that effect. Its upto you. What I normally do is to move the Master database before hand and make sure there are no new logins created after wards. Then during the cutover , I just move the msdb database so I have all information of jobs till the last point.

    The link works if you copy and paste in browser, and not when clicking it .. Wierd..

    http://msdn.microsoft.com/en-gb/library/ms178530.aspx Posted again.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, August 12, 2014 8:41 AM
  • Are your databases being upgraded? ie - 2005 to 2008 R2? 2008 to 2012? If so you ensure you run DBCC CHECKDB and update your stats.

    Rgds,

    Scott

    Tuesday, August 12, 2014 9:14 AM
  • Hi Scott,

    No, databases are not being upgraded !

    Monday, August 18, 2014 9:00 AM
  • Dear All,

    Thanks for the suggestion, will follow the process for backup and restore.

    Then move the logins through the scripts (Microsoft script).

    Use SQL Mgmt DEV Studio and create a package for moving the jobs..

    Many Thanks

    Monday, August 18, 2014 9:07 AM