Creating centralized DB

Question

I have 2 databases(A) with same name in different servers( B & C). Both the databases have same schema. (sql server 2008 r2)

Task 1: Copy(transfer) both the databases into 3rd server (D) with the names (A_B and A_C).

Task 2: Merge both the databases into one database(A_D). (I don't know how will I handle keys)

Task 3: On daily basis I have to get data from servers B & C and put in centralized server D.

Any help would be appreciated.

Thanks.

Ritesh

Answer 1

Here are a few ideas:

Task 1: Transfer databases by doing a backup an restore to server D.

Task 2: I think this will involve ETL processes and creating new surrogate keys in database A_D. Keep keys from original source in a data source id column. I think a MERGE statement would be helpful.

Task 3: Leverage logic in Task 2

Update for Task 2:

Say a source Table1 in database A and B has an key column named Table1_ID. In database A_D add columns Table1_SourceID and Table1_Source. Populate Table1_SourceID with the key from source database, and use Table1_Source to indicate the source database.

Use Table1_ID as the key for Table1, and is unique to database A_D. This will account for collisions for key columns in the source databases. Also, you can track the row to the source database.

Answer 2

Task 1: Create destination Databases with no structures. I'd use tasks -> export function on the source databases with create structures option in SSMS. After export you will have exact copies in destination.

Task 2: In each table of A_D create a new key column (SurKey). It has to be a combination of values which will give unique values in the whole table. E.g. source table abbreviation + PK column + date.

For each table create two Data Flows in SSIS Package, which will load data from A_B and A_C. Put a Derived Column component, which will add a new column - SurKey.

In A_B DataFlow put the A_B as an abbreviation, A_C in the second one.

Task 3: Use Data Flows you created. Script a Job in SSMS, add it to the daily plan.