locked
Migration of Very Large Database RRS feed

  • Question

  • Hi,

    Our current production system database in SQL Server 2000 is of size around 450 GB.

    We are planning to migrate this db to SQL Server 2008 R2(side by side migration).

    What is the best way to migrate such a huge database?

    If we use backup/restore, copying a backup file of size around 400 GB across the network is a very cumbersome process

    Any idea/suggestion on this is highly appreciated.

    Thanks.

     

     

    Tuesday, December 6, 2011 12:54 PM

Answers

  • Hi sraja,

    450G DB is not very large these days. Curently most DBs are above 1TB.

    For migrating SQL Server 2000 to SQL Server 2008 R2, I suggest you to run Upgrade Advisor firstly to detect syntactical problems and compatiblity issues by testing the execution. And then backup and restore your SQL Server databases.

    For migrating Maintenance plans,please refer to  http://msdn.microsoft.com/en-us/library/bb153838.aspx

    For login transfer ,please refer to  http://www.sqlmag.com/article/query-analyser/sql-server-login-transfer

    For moving DTS packages from one server to another ,please refer to http://www.sqldts.com/204.aspx


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Tuesday, December 13, 2011 7:49 AM
    Thursday, December 8, 2011 2:58 AM
  • In additional Peja's post, you should have a read of "Overview: Upgrading from SQL Server 2000 to SQL Server 2008" http://download.microsoft.com/download/2/0/B/20B90384-F3FE-4331-AA12-FD58E6AB66C2/SQL%20Server%202000%20to%202008%20Upgrade%20White%20Paper.docx

    It's for 2008 however most of the things discussed in the document related to 2008 R2


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Marked as answer by Peja Tao Tuesday, December 13, 2011 7:49 AM
    Thursday, December 8, 2011 3:06 AM
  • Hi Peja,

    Thanks for your reply.

    We are doing migration in several phases and in first phase we have successfully migrated databases of size around 250 GB. 

    We ran upgrade advisor, fixed certain issues and tested the db in Pre-Prod and finally moved it to production.

    The problem we faced during phase 1 was while moving the backup file from SQL Server 2000  machine to the new server(SQL 2008) , we encountered occasional backup file copy failure(copying the file over the network)with backup file of size around 230 GB.

    Right now we are into phase 2 where in we would do all the pre-migration activities such as running upgrade advisor and fixing the reported issues but the question is how to copy the backup file of size 450 GB over the network?

    Is backup/Restore the only way of migration for this large db if not is there any other alternative in copying such a file from SQL 2000 server to SQL Server 2008 server?

    Thanks very much.

     

     


    @sraja

    Your problem is located on your network? What is its bandwidth? If the network is slow, you could copy the backup file to a portable hard desk and restore it on the destination server.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Tuesday, December 13, 2011 7:49 AM
    Thursday, December 8, 2011 6:25 AM

All replies

  • Hi sraja,

    450G DB is not very large these days. Curently most DBs are above 1TB.

    For migrating SQL Server 2000 to SQL Server 2008 R2, I suggest you to run Upgrade Advisor firstly to detect syntactical problems and compatiblity issues by testing the execution. And then backup and restore your SQL Server databases.

    For migrating Maintenance plans,please refer to  http://msdn.microsoft.com/en-us/library/bb153838.aspx

    For login transfer ,please refer to  http://www.sqlmag.com/article/query-analyser/sql-server-login-transfer

    For moving DTS packages from one server to another ,please refer to http://www.sqldts.com/204.aspx


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Tuesday, December 13, 2011 7:49 AM
    Thursday, December 8, 2011 2:58 AM
  • In additional Peja's post, you should have a read of "Overview: Upgrading from SQL Server 2000 to SQL Server 2008" http://download.microsoft.com/download/2/0/B/20B90384-F3FE-4331-AA12-FD58E6AB66C2/SQL%20Server%202000%20to%202008%20Upgrade%20White%20Paper.docx

    It's for 2008 however most of the things discussed in the document related to 2008 R2


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Marked as answer by Peja Tao Tuesday, December 13, 2011 7:49 AM
    Thursday, December 8, 2011 3:06 AM
  • Hi Peja,

    Thanks for your reply.

    We are doing migration in several phases and in first phase we have successfully migrated databases of size around 250 GB. 

    We ran upgrade advisor, fixed certain issues and tested the db in Pre-Prod and finally moved it to production.

    The problem we faced during phase 1 was while moving the backup file from SQL Server 2000  machine to the new server(SQL 2008) , we encountered occasional backup file copy failure(copying the file over the network)with backup file of size around 230 GB.

    Right now we are into phase 2 where in we would do all the pre-migration activities such as running upgrade advisor and fixing the reported issues but the question is how to copy the backup file of size 450 GB over the network?

    Is backup/Restore the only way of migration for this large db if not is there any other alternative in copying such a file from SQL 2000 server to SQL Server 2008 server?

    Thanks very much.

     

     

    Thursday, December 8, 2011 6:03 AM
  • Hi Peja,

    Thanks for your reply.

    We are doing migration in several phases and in first phase we have successfully migrated databases of size around 250 GB. 

    We ran upgrade advisor, fixed certain issues and tested the db in Pre-Prod and finally moved it to production.

    The problem we faced during phase 1 was while moving the backup file from SQL Server 2000  machine to the new server(SQL 2008) , we encountered occasional backup file copy failure(copying the file over the network)with backup file of size around 230 GB.

    Right now we are into phase 2 where in we would do all the pre-migration activities such as running upgrade advisor and fixing the reported issues but the question is how to copy the backup file of size 450 GB over the network?

    Is backup/Restore the only way of migration for this large db if not is there any other alternative in copying such a file from SQL 2000 server to SQL Server 2008 server?

    Thanks very much.

     

     


    @sraja

    Your problem is located on your network? What is its bandwidth? If the network is slow, you could copy the backup file to a portable hard desk and restore it on the destination server.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Tuesday, December 13, 2011 7:49 AM
    Thursday, December 8, 2011 6:25 AM