March 25, 2012 at 7:33 am
Hi Folks,
I Have a query on data migration, now i have a two tables (A & A1) where A is parent table and A1 is child table. This is setup on Database A where data is dumped everyday. The data in A & A1 is deleted completely everyday prior to insertion of new data, so it is clean refresh everyday. Now we have another database B where is the data from Database A needs to synced into tables A & A1 with same structure.
Note: In A & A1 i have one unique identifier (Natural key) besides the identity column on both tables. Now what should be our strategy to ensure sync of data between these two databases with minimum number of transactions.
Waiting for your replies
Thanks
March 25, 2012 at 12:27 pm
krishnavamshi24 (3/25/2012)
I Have a query on data migration, now i have a two tables (A & A1) where A is parent table and A1 is child table. This is setup on Database A where data is dumped everyday. The data in A & A1 is deleted completely everyday prior to insertion of new data, so it is clean refresh everyday. Now we have another database B where is the data from Database A needs to synced into tables A & A1 with same structure.Note: In A & A1 i have one unique identifier (Natural key) besides the identity column on both tables. Now what should be our strategy to ensure sync of data between these two databases with minimum number of transactions.
For starters, if tables A and A1 in database A are fully refresh on each run, tables should be truncated rather than rows being deleted.
Assuming the requirement is to have a copy of A..A and A..A1 as B..A and B..A1 I would probably do the same, truncate B..A1 and B..A then load them in the proper order e.g. first the parent table then the child table - referential integrity constraints can be disabled during this process so to make it faster.
_____________________________________
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.March 25, 2012 at 9:28 pm
Thanks for the reply,Yes i understand truncate and insert would be simple way to do this but i want to minimize the number of inserts / deletes by rather using updates since this tables A & A1 would then be used for Sync Service.
currently my approach is like
Step 1 Check if any records don't match in the parent in Table A on Server B with the fresh data on Table A on Server A and then delete the records which don't match in child first in table A1 on Server B and then parent records (Based on Unique column)
Step 2 Check for additional records in Child records and delete additional records and insert any missing records
Step 3 Insert new records based on unique column, in parent and the corresponding child on Server B
Step 4 Check for changes in other columns in parent table based on unique column and update the table in server B
Awaiting your replies on the approach
March 26, 2012 at 7:40 am
krishnavamshi24 (3/25/2012)
Thanks for the reply,Yes i understand truncate and insert would be simple way to do this but i want to minimize the number of inserts / deletes by rather using updates since this tables A & A1 would then be used for Sync Service.currently my approach is like
Step 1 Check if any records don't match in the parent in Table A on Server B with the fresh data on Table A on Server A and then delete the records which don't match in child first in table A1 on Server B and then parent records (Based on Unique column)
Step 2 Check for additional records in Child records and delete additional records and insert any missing records
Step 3 Insert new records based on unique column, in parent and the corresponding child on Server B
Step 4 Check for changes in other columns in parent table based on unique column and update the table in server B
Awaiting your replies on the approach
How big are these tables and what percentaje of changes happens every day?
If tables are not 50,000,000 rows or more in size and changes lower than 5-10% you will probably end up doing more I/O using your approach.
Not difficult to test both approaches, trace both processes and see what they actually do.
_____________________________________
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.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply