Transfer SQL Server 2008 to new server

Discussion in 'Networking & Security' started by AMD_Gamer, Jun 7, 2011.

  1. AMD_Gamer

    AMD_Gamer [H]ard as it Gets

    Messages:
    18,277
    Joined:
    Jan 20, 2002
    I have a Server 2003 server running SQL Server 2008 and need to transfer it to a new server running Server 2008. What is the easiest way to do this? Can you just export the whole database and copy it to the new machine running another instance of SQL Server 2008?
     
  2. k1pp3r

    k1pp3r [H]ardness Supreme

    Messages:
    7,849
    Joined:
    Jun 16, 2004
    Detatch then move. Or use the copy database function if you can to copy it to the new server. But detatch works also
     
  3. 4saken

    4saken [H]ardForum Junkie

    Messages:
    10,795
    Joined:
    Sep 14, 2004
    ^^

    yep, literally can detach, then copy and paste to new location.
     
  4. AMD_Gamer

    AMD_Gamer [H]ard as it Gets

    Messages:
    18,277
    Joined:
    Jan 20, 2002
    What do you guys think about doing a P2V conversion into a VM and then upgrading to Server 2008 from 2003 instead of doing a clean install? All we have running on this box is Server 2003 and SQL Server 2008 with the databases. I would just do a P2V conversion and call it a day but my boss wants everything upgraded to Server 2008.

    Btw...either way the plan is to have this running on a VM with ESXi and a iSCSI Datastore. I read that with large environments it is not advised to run SQL Server in a VM but this is not used very much, only by a few people in the office so i think it will be ok.
     
  5. k1pp3r

    k1pp3r [H]ardness Supreme

    Messages:
    7,849
    Joined:
    Jun 16, 2004
    When its possible, do a clean install
     
  6. StarTrek4U

    StarTrek4U Gawd

    Messages:
    1,011
    Joined:
    Jan 8, 2003
    This.

    Also, I've ran Multiple SQL instances in a Virtual Environment using iSCSI and it ran fine- now that's provided you have it setup right. ;)
     
  7. YeOldeStonecat

    YeOldeStonecat [H]ardForum Junkie

    Messages:
    11,330
    Joined:
    Jul 19, 2004
    If this server you wish to upgrade is SQL server, I'd want to move to 64 bit. I'm assuming your current 2003 server is 32 bit? If so, no upgrade path, must be clean install.
     
  8. AMD_Gamer

    AMD_Gamer [H]ard as it Gets

    Messages:
    18,277
    Joined:
    Jan 20, 2002
    I spent my day at work researching the SQL Server 2008 installation process and that seems simple enough.

    Now for the migration to a new server , I learned that you can do either the backup/restore method or the detach and move. The detach and move method takes the database offline so I cannot do that during work hours so it looks like a backup/restore. I will still make sure nobody is accessing the DB when i do this.

    Now is there anything else I need to do to make sure that the clients that access the SQL server will still be able to access everything running on the new server as if we never moved it? What about the name of the server? does that have to remain the same as it is now? All i was going to do is change the IP address on the new server to what the old one is but my boss told me to be careful because some stuff in SQL links to the netbios name of the server? I saw some talk from searching on google that you need to do some things to make sure the paths are the same? this is where I am lost and none of the backup/restore tutorials i looked over cover this for a migration to a new machine?

    Also there seems to be about 10 or so different DB's on this server. Is there a way to backup/restore all of them at once instead of doing it for each DB?

    So as of now my plan is this:

    1) Setup new VM with Server 2008 R2 64bit and make the virtual disk large enough to hold the database with room to grow.
    2) Install SQL Server 2008 to this VM. Simple stuff
    3) Do the backup of each DB on the current SQL Server box and store it some place.
    4) Conduct the restore of these DB's on the new SQL server
    5) shutdown old SQL Server.

    Any help would be greatly appreciated.
     
    Last edited: Jun 21, 2011
  9. Jay_2

    Jay_2 2[H]4U

    Messages:
    3,583
    Joined:
    Mar 20, 2006
    I always do the backup restore but just be aware that it will still need to be "offline" as any changes to the database after the backup will not be moved to the new server.
     
  10. YeOldeStonecat

    YeOldeStonecat [H]ardForum Junkie

    Messages:
    11,330
    Joined:
    Jul 19, 2004
    Backup/Restore will still take it down. You'll shut down the database..so nobody will be accessing it...because it will shut off. Their client will error soon as you shut her down.
     
  11. Jay_2

    Jay_2 2[H]4U

    Messages:
    3,583
    Joined:
    Mar 20, 2006
    yup, check how the ODBC links are setup I expect they are using hostname so you will need to replicate that host name on the new server or go around and change all the ODBC links ( we use GPO to setup our ODBC so you may want to check that as well)

    You can't do an SQL migration without having down time, you really don't want users entering new data onto the old server after you have moved the backup or you will in effect roll them back when you online the new server.

    This is why time limited SQL migration are so stressful, you are given 24 hours to migrate the databases and if you don't do it in time you have to start again from scratch.
     
  12. AMD_Gamer

    AMD_Gamer [H]ard as it Gets

    Messages:
    18,277
    Joined:
    Jan 20, 2002
    is it ok to use the same hostname on a new server? I know not to do it with a Domain Controller.
     
  13. Jay_2

    Jay_2 2[H]4U

    Messages:
    3,583
    Joined:
    Mar 20, 2006
    yes as long as you remove the old server before you do it.
     
  14. AMD_Gamer

    AMD_Gamer [H]ard as it Gets

    Messages:
    18,277
    Joined:
    Jan 20, 2002
    So with the same hostname. IP address, and file structure (from installation) I should be ok and not affect the clients that connect to the sql server?

    Is that even the best practice for doing this? The tutorials and guides i find just say to do the backup/restore to the new server and no other problems you could run into like the name problem you mentioned?
     
  15. NetJunkie

    NetJunkie [H]ardForum Junkie

    Messages:
    9,683
    Joined:
    Mar 16, 2001
    I virtualize large database servers all the time. Just do it right. ;)
     
  16. AMD_Gamer

    AMD_Gamer [H]ard as it Gets

    Messages:
    18,277
    Joined:
    Jan 20, 2002
    So does it sound like I am doing it the right way?
     
  17. NetJunkie

    NetJunkie [H]ardForum Junkie

    Messages:
    9,683
    Joined:
    Mar 16, 2001
    I don't know anything about your config or database workload...so no idea. :)
     
  18. AMD_Gamer

    AMD_Gamer [H]ard as it Gets

    Messages:
    18,277
    Joined:
    Jan 20, 2002
    Just SQL server 2008 running on Server 2003. We have a few people in the office that connect to this using access and some other program. Nothing major or too complicated. I need to move it to a Server 2008 VM running on ESXi. See my post above for questions about this.
     
    Last edited: Jun 21, 2011