locked
How to Migrate 300 Databases to Single Database RRS feed

  • Question

  • Hi All,

    I am having a requirement like customer having 300+ databases(each db size 10gb+) with same schema, now customer want's to make all 300 database into single Database.

    All 300 Databases are in SQL Server 2008R2,  now they want's to move all into SQL Server 2012.

    So could you please help me on this 

    what are the best ways to migrate the databases?

    what are the precautions we need to take care? 

    how system will behave after migrating?

    Thanks in Advance for your great help

    Santosh 

     

     

    Saturday, March 28, 2015 1:20 PM

Answers

  • The best way is to use a bulkcopy.  See below

    https://msdn.microsoft.com/en-us/library/ms162802.aspx?f=255&MSPPError=-2147217396

    in data_file | outdata_file | queryoutdata_file | format nul 
    Specifies the direction of the bulk copy, as follows:
    •
    in copies from a file into the database table or view.
    
    •
    out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.
    
    •
    queryout copies from a query and must be specified only when bulk copying data from a query. 
    
    •
    format creates a format file based on the option specified (-n, -c, -w, or -N) and the table or view delimiters. When bulk copying data, the bcp command can refer to a format file, which saves you from re-entering format information interactively. The format option requires the -f option; creating an XML format file, also requires the -x option. For more information, see Create a Format File (SQL Server). You must specify nul as the value (format nul).
    
    owner 


    jdweng

    Saturday, March 28, 2015 1:50 PM
  • So could you please help me on this 

    what are the best ways to migrate the databases?

    Hi,

    Best way to merge many databases having same schema is to first script out the tables using Database scripting wizard and run the script on destination server. You can script out tables, SP, triggers, and other SQL Server database related objects. See this link how to script out.

    what are the precautions we need to take care?

    how system will behave after migrating?

    You need to do the task patiently as such since you would not be touching existing tables there wont be much affect on original database. But PLEASE TAKE BACKUP OF ORIGINAL DATABASE BEFORE PROCEEDING

    I cannot tell upfront how it will behave


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Saturday, March 28, 2015 2:17 PM
  • While it is possible to merge several databases into one,and others have suggested options to do this. 

    you need to keep in mind that one of the factor while generating execution plans is data statistics and by merging all the data, you may get wide set of data and your execution plans could be more generic.

    where as if you had each database individually,you could get better plans since the execution plan is made for that narrow set of data.

    you would have to be careful to make sure that all the objects across the databases really have same definition\filters etc.

    also, your new DB would be 3TB(300*10), which would be considered as VLDB and if you have enterprise edition, you would want to look into partition and better performance and typically, you will end up adding more indexes. also, multiple filegroups\files and disks.. unless all this corresponds to one applications\client and really thought after, I would hesitate to make such a big change.

    also, your backups and maintenance can start to become a headache and you need to plan all that...

    do you mind telling us the business reason for this?


    Hope it Helps!!


    Saturday, March 28, 2015 4:29 PM

All replies

  • The best way is to use a bulkcopy.  See below

    https://msdn.microsoft.com/en-us/library/ms162802.aspx?f=255&MSPPError=-2147217396

    in data_file | outdata_file | queryoutdata_file | format nul 
    Specifies the direction of the bulk copy, as follows:
    •
    in copies from a file into the database table or view.
    
    •
    out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.
    
    •
    queryout copies from a query and must be specified only when bulk copying data from a query. 
    
    •
    format creates a format file based on the option specified (-n, -c, -w, or -N) and the table or view delimiters. When bulk copying data, the bcp command can refer to a format file, which saves you from re-entering format information interactively. The format option requires the -f option; creating an XML format file, also requires the -x option. For more information, see Create a Format File (SQL Server). You must specify nul as the value (format nul).
    
    owner 


    jdweng

    Saturday, March 28, 2015 1:50 PM
  • So could you please help me on this 

    what are the best ways to migrate the databases?

    Hi,

    Best way to merge many databases having same schema is to first script out the tables using Database scripting wizard and run the script on destination server. You can script out tables, SP, triggers, and other SQL Server database related objects. See this link how to script out.

    what are the precautions we need to take care?

    how system will behave after migrating?

    You need to do the task patiently as such since you would not be touching existing tables there wont be much affect on original database. But PLEASE TAKE BACKUP OF ORIGINAL DATABASE BEFORE PROCEEDING

    I cannot tell upfront how it will behave


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Saturday, March 28, 2015 2:17 PM
  • While it is possible to merge several databases into one,and others have suggested options to do this. 

    you need to keep in mind that one of the factor while generating execution plans is data statistics and by merging all the data, you may get wide set of data and your execution plans could be more generic.

    where as if you had each database individually,you could get better plans since the execution plan is made for that narrow set of data.

    you would have to be careful to make sure that all the objects across the databases really have same definition\filters etc.

    also, your new DB would be 3TB(300*10), which would be considered as VLDB and if you have enterprise edition, you would want to look into partition and better performance and typically, you will end up adding more indexes. also, multiple filegroups\files and disks.. unless all this corresponds to one applications\client and really thought after, I would hesitate to make such a big change.

    also, your backups and maintenance can start to become a headache and you need to plan all that...

    do you mind telling us the business reason for this?


    Hope it Helps!!


    Saturday, March 28, 2015 4:29 PM
  • An other possible solution is automatic migration my means of Ispirer MnMTK. 

    SQL Server could be upgraded to the latest version automatically along with database objects and data migration.

    Thursday, July 2, 2020 11:44 AM