I have five SQL Server databases with the same schema in five different geographical locations. These locations send periodic backups to the central server which I restore in the five respective databases.

The requirement now is that data from these five databases MUST be combined into one database for consolidation.

Any suggestion for the solution is most welcome.


There's some lack of detail in the question; for example, it would be helpful to know:

  • What is the reason to combine the data together into a single database? It makes no sense to me if there isn't a further goal behind doing so. Perhaps there is a better way to make that end goal happen with fewer intermediate step(s).

  • Is there some kind of boundary between the sources where the backups are taken, and the destination where the backups are restored that prevents using a more direct approach?

  • What kind of overlap in data is there between the source sites? Obviously the user data will be different, but things like default/pre-defined rows may be common (are they user-editable?).

  • How well-structured is the database schema? Are there sufficient surrogate and business keys defined to be able to uniquely identify rows, or merge rows by business meaning? This is critical if you choose a programmatic/dynamic approach to merge the data.

  • What is the data access strategy? Is it stored-procedure-based, or is it direct table access, or by views, etc.?

In any event, here are a few different architectures you could pursue based on the answers to the questions above.

  1. The most obvious, and probably least disruptive, is to add a piece of code to the existing environment that will merge the data together from the restored databases.

    Depending on the number of tables in the database, you could simply script it out (for a small number of tables), or see if there's an off-the-shelf piece of software to do it for you (for a large number of tables). Since you're asking this question, I'm assuming the number of tables isn't trivial.

    I've actually written a piece of software that essentially does this (we didn't use off-the-shelf because we have custom requirements) for our database, which is about 750 tables, with the requirement to merge common data. I will tell you now that this was not an easy task. If there are custom requirements, I would strongly consider manually scripting the transfer process, even for a relatively large number of tables. That may sound like a lot of work, and it is, but it's simpler to create and maintain -- the complexity is in size rather than code wizardry, which is much more difficult to debug and test.

  2. Merge replication. This could be accomplished directly from the source databases, assuming there isn't any barrier between the sources and target (see above).

    This introduces extra requirements, potential performance issues, and support complexity around the source databases. I would only recommend this if the schema is really clean and there is very little overlap in data between the sources.

  3. Shell database. Create a new (empty) database at the target location that contains views that mimic the source tables or views by using 3-part names to UNION or UNION ALL the data from the individual target databases.

    If the eventual plan is to do something like create a data warehouse and you're just going to end up scanning the target databases anyway, this strategy could work out really well, as it's completely transparent if you end up adding more databases down the line. It also requires very little additional storage space.

So those are a few of the architectures I could think of off the top of my head. There are undoubtedly others. What you ultimately end up doing will depend greatly on your specific environment and requirements.


Main problem you'll face - key management. I can humbly suggest the following - Replication:

  1. Set up you regional DBs as Publishers and you archive/target DB as a Subscriber

  2. For each table on each Publisher set KEY RANGE, that will be used only by that publisher. It will give you uniqueness of all keys assigned. To make it manageable create a management DB on you server, and executing code remotely control KEY RANGEs on Publishers

  3. Set up your archive DB as a Subscriber only, so it does not send any information out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.