Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I manage a 280gb SQL Server database.

The structure contains a single database file and many schemas. Each customer has a schema and an associated set of tables. so :

MainDatabase : 

Schema1: 
    Table 1
    Table 2
    Sprocs:
       sproc 1
    Users
       Schema 1 user

 Schema2 -> 
    Table 1
    Table 2
    Sprocs:
       sproc 1
    Users
       Schema 2 user

 Schema3 -> 
    Table 1
    Table 2
    Sprocs:
       sproc 1
    Users
       Schema 3 user

etc...

We have around 2600 schemas within the database, over time it has gotten unwieldy and we need to break it down.

I want to move towards a horizontal growth approach, chopping the database up in 500 schema chunks. Breaking it up into 4 and continuing growth in this way.

What SQL statements could I use to achieve this? I can manually make the new databases but I need to initialise a move of this data into its new database, retaining all objects, data, stored procedures, users etc...

share

migration rejected from stackoverflow.com Dec 3 '13 at 21:51

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as too broad by Mark Storey-Smith, RolandoMySQLDBA, Michael - sqlbot, Jon Seigel, Mike Walsh Dec 3 '13 at 21:51

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Some background on what this system does and why the schema per customer approach exists would be useful. Are there any shared objects i.e. tables that are used by all? –  Mark Storey-Smith Nov 28 '13 at 16:38
2  
We have a similar situation and we do it 1 database per client - having all schema, sp's, triggers, etc. That is much more efficient in terms of maintaining backups per client, doing maintenance tasks like index defragmentation, stats as well as moving the databases around as some clients will be using the database more while some are idle. This approach works fine for us and is much more scalable. –  Kin Nov 28 '13 at 19:07
    
Moving data in multiple tables from old database to one table in new database will be useful. Its a mix of SSIS and BCP & BULK INSERT. –  Kin Nov 28 '13 at 19:18
    
each customers has seperate database for customisation and data segregation. The data is very sensitive, There are is a single set of shared tables that are stored within the dbo schema. –  MWard Nov 29 '13 at 8:40