We've 3 databases which are physically separated by region, one out of LA, SF and NY. All of the databases share exactly the same schema but contain data specific for their region. We are searching to merge these databases into one and mirror it. We have to preserve the information for every region but merge them into one db. This presents a number of issues for all of us, for instance we'll certainly have duplicate Primary Secrets, and Foreign Secrets is going to be potentially invalid.
I am wishing to locate somebody who has had knowledge about an activity such as this who could provide some suggestions, methods and words of expertise how we are able to accomplish the merge.
For instance, one idea ended up being to create composite secrets after which change our code and sprocs to obtain the data through the composite key (region/original pk). But this involves us to alter our code and sprocs.
Also try this ended up being to just import the information and allow it to generate new PK's after which update all of the FK references towards the new PK. By doing this we potentially do not have to change any code.
Any experience is welcome, thanks!
I've no first-hands knowledge about this, however it appears in my experience like you need to have the ability to distinctively map PK -> New PK for every server. For example, generate new PKs so that data from LA server has PK % 3 == 2, SF has PK % 3 == 1, and NY has PK % 3 == . And also, since, when i understood your question anyway, each server only stores FK associations to the own data, you are able to update the FKs in identical fashion.
NewLA = OldLA*3-1 NewSF = OldLA*3-2 NewNY = OldLA*3
After that you can merge individuals and also have no duplicate PKs. This really is basically, while you already stated, just producing new PKs, but constructing it by doing this enables you to definitely trivially improve your FKs (presuming, when i did, the data on each server is isolated). Best of luck.
BEST: give a column for RegionCode, and can include it in your PKs, but you won't want to do all of the groundwork.
HACK: in case your IDs are INTs, a fast fix is always to give a fixed value according to region to every key on import. INTs is often as large as: 2,147,483,647
local server data:
LA IDs: 1,2,3,4,5,6 SF IDs: 1,2,3,4,5 NY IDs: 1,2,3,4,5,6,7,9
add 100000000 to LA's IDs
add 200000000 to SF's IDs
add 300000000 to NY's IDs
combined server data:
LA IDs: 100000001,100000002,100000003,100000004,100000005,100000006 SF IDs: 200000001,200000002,200000003,200000004,200000005 NY IDs: 300000001,300000002,300000003,300000004,300000005,300000006,300000007,300000009
I've carried this out and I only say improve your secrets (choose a method) instead of altering your code. Almost always you'll either miss a saved procedure or introduce a bug. With data changes, it's pretty simple to write tests to search for orphaned records in order to verify that things were matched up up properly. With code changes, especially code that's working properly, it's too simple to miss something.
One factor you could do this is to establish the tables with regional data to make use of GUID's. This way, the main secrets in every region are unique, and you will combine data (import data in one region to a different). For that tables that have shared data (like type tables), you can preserve the main secrets how they are (since they must be exactly the same everywhere).
A few words about GUID's: http://www.sqlteam.com/article/uniqueidentifier-vs-identity
Maybe SQL Server Management Studio allows you are converting posts to make use of GUID's easily. I really hope so!
All the best.
things i have completed in a scenario such as this is:
- produce a new db with similar schema only tables. no pk fk, inspections etc.
- transfer data from DB1 for this source db
- for every table in target database discover the top number for that PK
- for every table within the source database update their pk, fk etc beginning using the (top number + 1) in the target db
- for every table in target database set identity place to on
- import data from source db to focus on db
- for every table in target database set identity place to off
- obvious source db
- repeat for DB2