1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Tools to migrate SQL instances to another server

Discussion in 'Servers and Enterprise Solutions' started by steinooo, Jul 5, 2019.

  1. steinooo

    Mobster

    Joined: Dec 31, 2003

    Posts: 4,441

    Hi, does anyone have any experience of using a tool for migrating entire SQL instances to another physical machine please?
     
  2. Tee Hee Johnson

    Mobster

    Joined: May 30, 2007

    Posts: 3,874

    Location: Glasgow, Scotland

    Redgate do good, yet expensive, tools. Not sure if they have what you are looking for, but have a look.
     
  3. steinooo

    Mobster

    Joined: Dec 31, 2003

    Posts: 4,441

    Thank you, will check out redgate I'd not heard of them and this topic seems difficult to Google
     
  4. Vince

    Man of Honour

    Joined: Oct 30, 2003

    Posts: 7,451

    Location: Essex

    As above redgate might be of use but more to the point why wouldn't you just back up the databases, restore them to the new server and then reconnect clients? Hardly seems worth finding a "tool" when no tool will be able to give an end-to-end solution for sql migrations.
     
  5. steinooo

    Mobster

    Joined: Dec 31, 2003

    Posts: 4,441

    i'm trying to assess the options with no experience of having done this before. Thoughts going through my head include

    1) The storage is all on a SAN, is it possible to simply detach the storage then re-attach the storage to a brand new server with a new SQL version?
    2) Your approach of backing them up then restoring them will cause a large downtime due to the sizes of the databases involved, but is the "cleanest" way
    3) Clients reconnecting is an issue, because the clients are looking for NETWORKNAME\INSTANCENAME, and you can't have 2 of the same network name online at the same time I believe
     
  6. oneilldo

    Wise Guy

    Joined: Jan 16, 2003

    Posts: 1,676

    Few options.
    Not sure on one, I really doubt it though and it’s high risk.

    Can reduce downtime by doing a full backup, restoring in recovery mode and then and then a differential during downtime.

    You will want to check logins are copied over as well as settings a broadly similar (hardware dependant). You can run sp_configure to get most of the info. Logins MS do a helpful Stored procedure called sp_help_revlogin and think you can now get a few more helpful PowerShell scripts to do this.
    Clients reconnecting. Options are to either change your connection strings on the clients (depends on number and feasibility, changing destination server name/IP during downtime or configuring new connections with CNAMES etc which might be a better idea going forward. Essentially will be like using DNS so can make future changes more easily.

    And test test test
     
  7. steinooo

    Mobster

    Joined: Dec 31, 2003

    Posts: 4,441

    I didn't know about recovery mode, so thanks for that tip.

    I saw some scripts which can copy the DB permissions. I was rather hoping I could chuck some cash at a tool which will restore the differentials right up until the restore point, meaning next to no downtime.
     
  8. Tee Hee Johnson

    Mobster

    Joined: May 30, 2007

    Posts: 3,874

    Location: Glasgow, Scotland

    These might be the tools you have seen, but dbatools and Ola Hallengrens scripts can be a fantastic check, backup and restore companion.

    The instance backup is good as it scripts out everything in the db: sp's, roles, etc.
     
  9. Little_Crow

    Hitman

    Joined: Oct 3, 2007

    Posts: 742

    A little late to the thread, but you haven't mentioned what SQL versions, but just in case it's reeeeeealy old keep in mind there is no direct path from SQL 2000 to SQL 2012 and up - you have to go via SQL 2008

    I've used broadly this approach for an SQL server hardware migration where time was a factor. How you describe could work, but won't give you a quick and easy back out plan, other than restore from backup.

    The plan with an EMC VNX5600 (YMMV):
    > Setup Clone of storage beforehand
    > Shutdown SQL on Live Server
    > Create Marker Text files on Database drives (If these files are there when you mount the clone to the new box then you can be sure that your clone was in sync and you have consistent DB's)
    > Shutdown Live server
    > Fracture Clone so that the clone is now independant
    > Attach new server to fractured clone
    > Power on new server
    > Attach DB's

    You can carry out dry runs of this process (without the SQL shutdown) until you have a solid plan you can follow when doing it for real.

    One final tip, regardless of your approach, document every step of the process beforehand in a notepad file - what commands to type or exactly where in a gui to go and what options are getting set.

    Having a printed copy you can tick off as you complete each step is an excellent aid, and a digital version to copy and paste from will save you from silly mistakes and typos - if you've tested the process thoroughly you'll have given yourself the best chance of a successful migration.

    ###Edit: I found my own cribsheet for the process and updated the broad outline of steps above ###
     
    Last edited: Jul 16, 2019 at 2:27 PM
  10. Nikumba

    Mobster

    Joined: Dec 4, 2002

    Posts: 3,551

    Location: Bourne, Lincs

    If you are getting rid of your old server, you could CNAME the new one, with servers like this where we have clients connecting always CNAME the server so can change it without having to reconfigure your clients