Post reply

Move databases to a new server

  • June 15, 2012 at 12:10 pm

    #257608

    Hello All,

    I am planning on moving all my databases to a new database server with better hardware (memory and cache). My plan is to detach the databases from the existing server...move the luns on which the mdf and ldf files reside from the exisiting server to the new server and attach the databases on the new server. I will take full backups and once all the apps are shut down I will take transaction log backups. (do I still need to take a tail log backup?? I know I am taking TX log backups after all the apps are down)

    Also I dont want any of the applications to use a new server name in their connection strings hence when the luns are moved from the old db server I am going to take this server offline and rename the new server with the old server name and give the same IP address. I have already moved all the windows logins and Jobs that are required.

    I need your valuable inputs and any steps that I need to take care of if I am missing something.

    Thanks for your inputs friends.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • June 15, 2012 at 12:54 pm

    #1501851

    Ignore post - I just mentioned IPs and Ports configurations. (But I re-read and you are using same IP for new server machine)

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • June 15, 2012 at 12:56 pm

    #1501852

    Also, the old server is a SQL Server 2008 R2 and the new one has 2008 r2 Sp1

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • June 15, 2012 at 6:48 pm

    #1501925

    If you're detaching the databases first, then re-attaching them on the new server should be fine (the upgrade to SP1 will be done automatically as the databases come online). Since they were detached, there will have been no transactions performed against them, so they should be perfectly consistent. The backups are a good idea as a failsafe, but you shouldn't need to restore them.

    Before detaching databases:

    1. Script out any linked servers

    2. Script out all logins exactly from the old server using sp_help_revlogin (http://support.microsoft.com/kb/246133) and save the resulting output.

    3. Script out all jobs that may be defined on the old server.

    After shutting down the old server and renaming the new one:

    1. Reset @@servername on the new server to match old server.

    2. Run the output script from sp_help_revlogin against the new server

    3. Re-attach all database files

    4. Re-create any linked servers on the new server using the scripts created above

    5. Re-create all jobs on the new server using the script created above.

    I've probably forgotten something along the way, but that's not a bad list to get you going.

  • June 16, 2012 at 12:01 am

    #1501938

    run select * from sys.databases and save off a copy. Some settings (like being flagged as trustworthy) won't carry over.

    check any non-default server configuration options (maxdop, cost threshold for parallelism, etc)

    any server audit specifications?

    any encryption?

    any certificates?

  • June 16, 2012 at 7:46 pm

    #1501992

    Thanks for the replies guys...it was really helpful...the move was smooth.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply