locked
16 GB Table migration from one SQL server to another server RRS feed

  • Question

  • HI,

    Please  suggest how to move 16 GB table from one SQL server to another SQL server and provide how much time is required to excute. Please suggest step for table migration

    Tuesday, December 8, 2015 7:28 PM

Answers

  • It really depends. You need to know

    • Do you have relationships (foreign key constraints) on the table that will affect the migration?
    • What is your allowable downtime?
    • Are your source and destination physical or virtual?
    • Do your source and destination have similar hardware/compute and storage resources?

    Daniel already provided some ideas, BCP is the recommended one. The reason I asked if the source and destination are physical or virtual machines is because you can simply store the exported data from the source on a storage subsystem that you can mount/unmount between the source and destination. This allows you to "transfer" the data from the source to the destination without really sending it across the network


    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Saturday, December 12, 2015 1:01 AM
  • Hi,

    I am completed this activity using import and export (its take45 min)

    Monday, December 14, 2015 11:58 AM

All replies

  • HI,

    Please  suggest how to move 16 GB table from one SQL server to another SQL server and provide how much time is required to excute. Please suggest step for table migration

    There are a lot of ways to do this:

    1. SSIS package to transfer from SQL to SQL
    2. BCP data out - import data into new instance
    3. Linked server (not recommended)
    4. ..
    5. ...
    6. ....

    The time it takes to migrate depends on a lot as well:

    1. How fast is the network?
    2. How fast is the source and destination storage?

    It would take about 2 m 20 s to copy 16 GB over a 1 Gb network provided nothing else is causing a slowdown. It would take 24 min over a 100 Mb network. Next you have to import the data into the new instance. How long will that take? It depends on your new instance and how fast the storage is and what else is going on. I don't think there is a golden answer here. It depends on your hardware and environment.

    ps. Don't forget to configure any permissions the new table may need.


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)


    • Edited by Daniel Janik Tuesday, December 8, 2015 7:37 PM
    • Proposed as answer by Ice Fan Wednesday, December 9, 2015 11:15 AM
    Tuesday, December 8, 2015 7:36 PM
  • It really depends. You need to know

    • Do you have relationships (foreign key constraints) on the table that will affect the migration?
    • What is your allowable downtime?
    • Are your source and destination physical or virtual?
    • Do your source and destination have similar hardware/compute and storage resources?

    Daniel already provided some ideas, BCP is the recommended one. The reason I asked if the source and destination are physical or virtual machines is because you can simply store the exported data from the source on a storage subsystem that you can mount/unmount between the source and destination. This allows you to "transfer" the data from the source to the destination without really sending it across the network


    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Saturday, December 12, 2015 1:01 AM
  • Hi,

    I am completed this activity using import and export (its take45 min)

    Monday, December 14, 2015 11:58 AM
  • Hi Karthik,

    Thanks for your sharing, you can mark your reply as answer, other community members could
    benefit from your solution.

    Thanks,

    Ice fan



    Ice Fan
    TechNet Community Support







    • Edited by Ice Fan Wednesday, December 16, 2015 9:26 AM
    Tuesday, December 15, 2015 9:51 AM