Post reply

Merging of SQL Server databases into one??

  • September 15, 2014 at 12:38 pm

    #299434

    Hi,

    Hope you guys can help...

    We created an application that is using multiple dbs, one for each client using the application. All dbs are identical, each db holds the client's data, they all use the same tables and structure.

    For convenience, we then decided to change our model and use one single db for ALL clients. So, we added columns to identify the client to all tables.

    All is fine till now, but now we are trying to merge all our current clients, who have each their own db, into this new one db, but we're not sure how to best go about it.

    Any suggestions?

    We thought of doing it manually and import each client and each table at a time, but this would take forever.

    Thanks

    G.

  • September 15, 2014 at 9:39 pm

    #1745651

    I'm not sure if I understand your requirements, correctly... Can you clarify?

    Regards,
    SQLisAwe5oMe.

  • September 15, 2014 at 10:36 pm

    #1745653

    dexdev (9/15/2014)

    Hi,

    Hope you guys can help...

    We created an application that is using multiple dbs, one for each client using the application. All dbs are identical, each db holds the client's data, they all use the same tables and structure.

    For convenience, we then decided to change our model and use one single db for ALL clients. So, we added columns to identify the client to all tables.

    All is fine till now, but now we are trying to merge all our current clients, who have each their own db, into this new one db, but we're not sure how to best go about it.

    Any suggestions?

    We thought of doing it manually and import each client and each table at a time, but this would take forever.

    Thanks

    G.

    Quick questions,

    1. is the referential data, that is the non-client specific data the same (i.e. location_id = 1 is NewYork )?

    2. will you have to expand primary and foreign key definitions due to overlapping sequence (identity) values?

    3. have you evaluated and assessed the impact of multiple clients in a database designed for a single client? This can lead to some nasty triangular joins and hefty performance penalties.

    I have hardly ever come across a single subject(client) database design which scaled painlessly up to multiple subjects, most common problems being Cartesian produce (set multiplications) and loss of performance.

    To properly advice on the matter, one needs at least a mid level ERD and basic numbers (cardinality/distribution etc.)

    😎

  • September 16, 2014 at 9:44 am

    #1745819

    Probably wrong way to do it, but (in Test Env with backup copies) with replication could you have each client db setup as a publisher and then they all replicate to one subscriber? Just thinking of a quick win for the OP.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply