December 29, 2010 at 1:29 am
Hi!
I have 2 different database (A and B) in a sql server 2008. Databse A has around 100
tables out of which i want to synchronize 15 tables from database A to database B. So
if any addition/deletion/updation happens on those 15 tables then those entries
should be updated on database B.
But here the challenge is i will have different Primary key data in source and
target, i.e. lets say one row is added to Database A-Table A with pk value as 10
then when i will copy that row to Database B- Table A, PK might be auto generated and can have any value other than 10.
Same way i need to keep track of update and delete.
Can anybody suggest me what will be the best solution?
December 29, 2010 at 2:50 am
I would seriously consider redesigning so both databases will use the same primary key and then I'd simply use replication. If this is not possible, then you can develop your own mechanism, but this will result in lots of work. You can create tables that will hold data about modified records. For every insert it will hold the values of each column. For every delete it will hold the old values of all columns belonging to the deleted record. For every update it will hold both old and new values for each column. Each table will triggers for update, delete and insert that will insert this data to a logging table. Then you can have a job that synchronizes the data from the logging table with the tables in the target database. The insert is very simple. In order to delete and update the correct record you'll have to compare all columns (except for the primary key's columns) from both the target table and the logging table in the where clause. As I wrote this will cause you lots of work and I strongly recommend that to use the same primary keys and replication.
Adi
December 29, 2010 at 11:10 am
ami.shah (12/29/2010)
Hi!I have 2 different database (A and B) in a sql server 2008. Databse A has around 100
tables out of which i want to synchronize 15 tables from database A to database B. So
if any addition/deletion/updation happens on those 15 tables then those entries
should be updated on database B.
But here the challenge is i will have different Primary key data in source and
target, i.e. lets say one row is added to Database A-Table A with pk value as 10
then when i will copy that row to Database B- Table A, PK might be auto generated and can have any value other than 10.
Same way i need to keep track of update and delete.
Can anybody suggest me what will be the best solution?
Research "replication".
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2010 at 5:54 pm
As Adi an Paul say, replication may be your best choice if you're not too far down the road. However, if you're dealing with something like clients having their own database and the need to merge the data into a single system, then you'll need to reassign surrogate keys when you bring the data in. In addition, you will need to know, without a doubt, the business key on each table.
If the primary key for a record in a table in one database is different than the same record (business key) and the same table in a different database, than the business key needs to be compared when brought over and reassigned as well as ALL of the other tables that use that primary key value reference.
It's not a difficult task, just time consuming. While working with the DoN, we brought applications into a common database and had to deal with this very issue. It just comes down to a design pattern. Whether your merging multiple like databases into one or from one to another you can use the same pattern.
If your moving your data from A to B, you start from the outer most tables, working inward. In other words, start with the tables that DON"T reference other tables. Once those are done, move the tables over that have all the tables they reference, already loaded to the target system. When you move a record over, compare the business key, NOT THE PRIMARY KEY. If it is a new record, then you'll get the new primary key from the B database.
As you get deeper into your schema bringing over records from tables that have references to other tables you WILL have a different pk values, so join in the source and bring the business key attributes over so that you can compare them with the target system (B) tables to return the new primary key (surrogate key).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply