SQL Server 2005 Migration – Complete Procedure

migrationsql-server-2005

I'm tasked with 'rebuilding' a database server, because the old one is dying. To reduce potential for the new server f**king up, we had decided to rebuild the server as similar as possible to the old one, hence, SQL Server 2005 Enterprise on top of Windows Server 2003.

Now, the original dev/maintainer of the old server has gone the way of the dodo (as in, he's left the company quite some time ago, and nowhere to be found now). So, we had to do the migration in-whole.

To make matters worse, the old server is a hodge-podge of: Databases, a mix of user credentials (SQL Server authentication and Windows authentication), a crowd of Linked Servers, and a gaggle of SQL Server Agent Jobs.

So, how do I approach this monumental task? In my mind, I had the following half-baked plan:

  1. Copy the databases using the Copy Database Wizard
  2. Copy the users using Script > CREATE To
  3. Re-create all Linked Servers
  4. Copy the Jobs using Script > CREATE To

For every step above, I might have to do it one-by-fricking-one 🙁

Is there an easier (as in, less tedious) way to do the migration?

Best Answer

Do not use copy database wizard. Instead use backup/restore. Also, if you want to minimize the downtime then below steps would be sufficient :

Prepwork on the New server :

  1. Create blank databases with the same name as the old server. This is because if any logins or jobs has that database as default, there would be errors.

  2. Script out all the logins using the sp_help_revlogin from the old server to the new server and create them on the new server.

  3. Script out all the jobs. You dont have to Script > CREATE To for each jobs. Instead right click in Object Explorer and script all the jobs in one shot :-) Remember to disable the jobs until you failover to the new server.

    enter image description here

  4. Scrip out all Linked servers and recreate them on the destination server. Remember that passwords will not be scripted out.

  5. Once 1-4 is set up then set up either Logshipping or database mirroring (Async mode) from source server to destination server.

  6. On the day of failover

    • if using logshipping then manually failover to new server.

    • if using Mirroring then change the mode to synchronous and then initiate a failover to new server.

This way you will do all the prep work before hand and then during the day of failover, the downtime will be minimal.

You can refer to my answer HERE for more steps (they are for migration from old version to higher version, but still will help you).

Related Question