Identify DML changes between two databases
Hi Experts,
I've existing DB called A which contains 410 tables. I created another DB called B from the backup of A and modified the database B such as creating some new table and modifying existing tables and drop of some tables and so on. Now B database contains 548 tables.By the meantime A database as well undergone some changes. Say for example, in database A, in tableA some rows has been inserted. In database B, in tableA some rows has been deleted/modified.
I would like to know the data changes between the two databases.
My objective is that in order to sync the Database A with Database B I need to create some scripts. To identify the schema changes I've tool. No issue at that point. But I worry about DML changes. How to create DML scripts such as Insert, update, delete queries which make my Database A sync with Database B. How to achieve this?
Please do guide me.
Note: I'm using SQL SERVER 2008 R2.
Please note that I've downloaded the Red Gate's data compare tool which shows the difference but did not provide the script to sync it.
I've existing DB called A which contains 410 tables. I created another DB called B from the backup of A and modified the database B such as creating some new table and modifying existing tables and drop of some tables and so on. Now B database contains 548 tables.By the meantime A database as well undergone some changes. Say for example, in database A, in tableA some rows has been inserted. In database B, in tableA some rows has been deleted/modified.
I would like to know the data changes between the two databases.
My objective is that in order to sync the Database A with Database B I need to create some scripts. To identify the schema changes I've tool. No issue at that point. But I worry about DML changes. How to create DML scripts such as Insert, update, delete queries which make my Database A sync with Database B. How to achieve this?
Please do guide me.
Note: I'm using SQL SERVER 2008 R2.
Please note that I've downloaded the Red Gate's data compare tool which shows the difference but did not provide the script to sync it.
ASKER
I'm looking for generate script for DML changes not for DDL changes. The tool that you suggested for DLL changes. Please do advise.
Oh, then try Atlantis Data Inspector.
ASKER
Atlantis Data Inspector is free tool?
Mmmm... no, you have a 14-day trial with complete features.
ASKER
Is there any free version available to compare the data between two databases and to extract the sync script?
OK, so, how do you want to synch ?
If DB_B.table_A has deleted rows, then you need to delete DB_A.table_A
If DB_A.table_A has inserted rows then you need to insert DB_B.table_A
What if DB_A.table_A has deleted rows, do they need to be deleted as well ?
What if DB_B.table_A has inserted rows, do they need to be inserted as well ?
What about changes in any of the existing rows ?
How many tables are like that and do they have matching unique ID's / primary keys ?
How often does this need to happen ?
If DB_B.table_A has deleted rows, then you need to delete DB_A.table_A
If DB_A.table_A has inserted rows then you need to insert DB_B.table_A
What if DB_A.table_A has deleted rows, do they need to be deleted as well ?
What if DB_B.table_A has inserted rows, do they need to be inserted as well ?
What about changes in any of the existing rows ?
How many tables are like that and do they have matching unique ID's / primary keys ?
How often does this need to happen ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well. I have A database 12.0 version which is referred by application App1 version 12.0.
I branch out from there database B 13.0 which is referred by application App2 version 13.0.
In between many build has been gone such as 12.2.
Now 12.0 version Application and database A has to upgrade with 13.0 database. Whatever data should be persist in the database 12.0 should be present to database 13.0. If data in both the table are same no issue. If they differ I need to prepare data upgrade scripts such a way that my 12.0 database data is pushed into 13.0. How this could be achieved?
One more advice please: To push the data which approach would be better? My colleague says that SSIS packages is better than providing simple update(CRUD) scripts.
Please note that I don't know about SSIS packages. It would be great if you guide me where I could get start.
Please do suggest.
I branch out from there database B 13.0 which is referred by application App2 version 13.0.
In between many build has been gone such as 12.2.
Now 12.0 version Application and database A has to upgrade with 13.0 database. Whatever data should be persist in the database 12.0 should be present to database 13.0. If data in both the table are same no issue. If they differ I need to prepare data upgrade scripts such a way that my 12.0 database data is pushed into 13.0. How this could be achieved?
One more advice please: To push the data which approach would be better? My colleague says that SSIS packages is better than providing simple update(CRUD) scripts.
Please note that I don't know about SSIS packages. It would be great if you guide me where I could get start.
Please do suggest.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
MS itself also provides a free command-line tool, "tablediff.exe". If you're not used to c-l tools, it's awkward at first, but it does work well enough once you get used to it.
RedGate Data Compare is an excellent product that is inexpensive.
RedGate Data Compare is an excellent product that is inexpensive.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> tablediff is a good tool, but it is only one way - the scripts generated are to bring the "target" server into convergance with the "source" <<
Can't you just run tablediff again swapping which server is the "target" and which is the "source"?
Can't you just run tablediff again swapping which server is the "target" and which is the "source"?
>> Can't you just run tablediff again swapping which server is the "target" and which is the "source"?
Yes, you can, but there are problems with two way convergance. Consider this :
1) Source_db.table_a has rows "1","2","3"
2) Synch to Target_db.table_a
3) Target_db.table_a now deletes row "2" and adds row "4"
Now if we synch again from Source to Target then row "2" reappears and row "4" disappears
So, we need to synch from Target to Source before we again synch from Source to Target. If we did that before step 2 above, then all rows would have been deleted.
Now it is an oversimplification and possibly unrealistic, but the problem is that they seem to be maintained at the same time, so it makes it very difficult to decide which one needs to take priority. Especially if say row "3" has been altered - which instance of row "3" is the correct one ? Or, because they both have a row "3" does it simply get ignored ?
One way to help decide is a datetime of when changes were made. But to tablediff, it is simply a difference. Thats when scripts might have to be written so you can also consider that actual values. e.g. update row "3" if source_db.table_a.datetime
If target_DB was simply a repository, then no where near the problems...
Yes, you can, but there are problems with two way convergance. Consider this :
1) Source_db.table_a has rows "1","2","3"
2) Synch to Target_db.table_a
3) Target_db.table_a now deletes row "2" and adds row "4"
Now if we synch again from Source to Target then row "2" reappears and row "4" disappears
So, we need to synch from Target to Source before we again synch from Source to Target. If we did that before step 2 above, then all rows would have been deleted.
Now it is an oversimplification and possibly unrealistic, but the problem is that they seem to be maintained at the same time, so it makes it very difficult to decide which one needs to take priority. Especially if say row "3" has been altered - which instance of row "3" is the correct one ? Or, because they both have a row "3" does it simply get ignored ?
One way to help decide is a datetime of when changes were made. But to tablediff, it is simply a difference. Thats when scripts might have to be written so you can also consider that actual values. e.g. update row "3" if source_db.table_a.datetime
If target_DB was simply a repository, then no where near the problems...
RedGate's Data Compare certainly used to gen scripts on demand. Can't believe they'd really remove that capability from the tool.
RedGate's Data Compare certainly used to gen scripts on demand.
Both Standard and Pro Editions still produce SQL Scripts. See here:
http://www.red-gate.com/products/sql-development/sql-data-compare/
Both Standard and Pro Editions still produce SQL Scripts. See here:
http://www.red-gate.com/products/sql-development/sql-data-compare/
Thanks acperkins, good to know that it still does :)
ASKER
@RolandDeschain: Atlantis Data Inspector is not free one!
If free one please let me know from where I could download the installer.
If free one please let me know from where I could download the installer.
If you use SQL 2008 you should consider to use MERGE statement
http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/
And
http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/
You can find more examples on the net.
It is not as complicate as it may look. With MERGE you actually establish the rules what happens when the target table data doesn't match the source table data as opposed to a tool where it just try to sync the 2 tables.
You would have to link the 2 servers to make it easier for you.
http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/
And
http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/
You can find more examples on the net.
It is not as complicate as it may look. With MERGE you actually establish the rules what happens when the target table data doesn't match the source table data as opposed to a tool where it just try to sync the 2 tables.
You would have to link the 2 servers to make it easier for you.
@EaswaranP,
I did ask a few questions for you to clarify.
There is also a discussion about 2-way convergance that you might like to comment on (if it applies to you).
There is also the comment about redgate tools that DOES do the scripting and you already have those tools.
So, we (well, me at least) need your help to clarify a few things so we might be able to better help you...
Synch two databases which apparently start from a common "base" but then have a life of their own is not an easy / ready made solution. You may well need to create your own scripts (quite possibly using merge) depending on the complexity and your business rules / requirements.
I did ask a few questions for you to clarify.
There is also a discussion about 2-way convergance that you might like to comment on (if it applies to you).
There is also the comment about redgate tools that DOES do the scripting and you already have those tools.
So, we (well, me at least) need your help to clarify a few things so we might be able to better help you...
Synch two databases which apparently start from a common "base" but then have a life of their own is not an easy / ready made solution. You may well need to create your own scripts (quite possibly using merge) depending on the complexity and your business rules / requirements.
Synch two databases which apparently start from a common "base" but then have a life of their own is not an easy / ready made solution.
Actually providing the schema for the tables are identical, there are no Foreign Key constraints, you are using primary keys and the values for the Primary Keys are kept in sync, then it may not be all that difficult. You just have to learn how to generate the script to populate all the data and reseed any IDENTITY columns.
But then, if you are not using Foreign Key constraints or if you are using IDENTITY columns for your Primary Keys you have bigger problems . :)
Actually providing the schema for the tables are identical, there are no Foreign Key constraints, you are using primary keys and the values for the Primary Keys are kept in sync, then it may not be all that difficult. You just have to learn how to generate the script to populate all the data and reseed any IDENTITY columns.
But then, if you are not using Foreign Key constraints or if you are using IDENTITY columns for your Primary Keys you have bigger problems . :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So, it could be far from being a simple script.
I agree with you 100%. My comment was more tongue in cheek.
I agree with you 100%. My comment was more tongue in cheek.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
Just out of curiosity, which way will you go?
Hope that helps.