Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: migrating servers

    Hi,

    we currently have a SQL 2008 R2 database on a Win 2003 32bit box with 4MB RAM and 300GB hardrive.

    We just installed SQL Server 2008 R2 on Win 2008 Server with 32MB RAM and 2TB SAN Storage

    I have about 10-15 databases that i need to migrate over to the new server. What is the best way to migrate the datbases, logins, SQL Server jobs, etc..? Is there a good side that has step by step instructions or best practices to follow?

    thanks
    Scott

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without a better definition of your criteria for "best", there is no way to answer your question.

    You can backup and restore the databases, but especially with the master and msdb databases that is a bit tricky. This is the best documented and therefore the best supported way to move databases from one server to another. If yoiu choose to do this, migrate master first then msdb then other databases as needed. Those two are the most challenging to move because they are the "personality" of your SQL Server and almost always need to be in place first before moving other databases.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2009
    Posts
    62
    what are the downsides if I do not move over the msdb and master databases? do i need to move those over and can i just move over the user databases?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you do not move the msdb database, you will need to recreate all of your SQL Agent jobs, alerts, operators, etc.. What you need to move over depends on what features of SQL server you are using.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    just a thought

    Does SQL Server even install on 32 MB RAM ?
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Enigma View Post
    Does SQL Server even install on 32 MB RAM ?
    Holy Hanna

    Where the heck have YOU been?

    32MB indeed

    do you mean 32GB?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2009
    Posts
    62
    sorry, i meant 32GB...

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The master database contains the "server wide" data like the logins, linked servers, etc. If you don't move it, you'll have to recreate the Windows Authenticated logins, the Linked Servers, and all other server wide objects. If you have SQL Authenticated logins, you'll need to rebuild them and also repair their access to every database that you restore. If you have good documentation, this usually takes a few hours to set up and a few months to work out all of the resulting kinks. I prefer to restore master, since that takes minutes and gets everything correct on the first try.

    The msdb database stores many things, especially the SQL Agent Jobs, SQL Main configuration, etc. If you document everything in excruciating detail you can rebuild msdb in less than a day. If you don't get it right, your server may work anyway but you'll usually find out about it within a week or two.

    Neither of these are show-stoppers for a well documented installation. I've only worked with one organization that consistantly did good enough documentation for simple, consistant rebuildsl I've worked with two other organizations that weren't bad and could have everything stable again within a week. I've worked with a few hundred other organizatos with varying degrees of documentaiton, but none good enough that I'd suggest a rSQL Serveer rebuild without taking master and msdb before the user databases.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Sep 2009
    Posts
    62
    I've also read that one of the best ways to tackle this situation is to stop SQL Server on both instances, copy all of the MDF and LDF files over to the new server and then restart SQL again on both boxes.

    This sounds really simple, so I'm wondering if there is anything else i should be worried about?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As long as you:

    a) have the same driver leters and folder layouts on both servers
    b) keep a backup copy of any files you replace
    c) give careful thought to the Resource database (dbid 32767)

    ...you should be fine.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •