Solved

Identify DML changes between two databases

Posted on 2013-01-30
27
372 Views
Last Modified: 2013-02-17
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.
0
Comment
Question by:Easwaran Paramasivam
  • 8
  • 6
  • 3
  • +3
27 Comments
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 38838729
Try Atlantis Schema Inspector. It's free and it generate the sync scripts you're looking for.

Hope that helps.
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38838761
I'm looking for generate script for DML changes not for DDL changes. The tool that you suggested for DLL changes. Please do advise.
0
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 38838768
Oh, then try Atlantis Data Inspector.
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38838950
Atlantis Data Inspector is free tool?
0
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 38838951
Mmmm... no, you have a 14-day trial with complete features.
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38839061
Is there any free version available to compare the data between two databases and to extract the sync script?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38839220
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 ?
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 400 total points
ID: 38839301
There is a tool (paid - but not much) from devart that does the data compare : http://www.devart.com/dbforge/sql/datacompare/

There is a Visual Studio facility : http://www.codeproject.com/Articles/146461/SQL-Server-Data-Comparison-Tool-in-Visual-Studio-2

Shareware version does data as well : http://www.sqldbtools.com/Tools.aspx?ProductId=1

And then the free one that probably should be listed first : http://www.w3enterprises.com/software/sql-table-compare.aspx
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38839320
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.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 400 total points
ID: 38840311
Well, scripts are not a bad way to go. They can be scheduled.

SSIS jobs are easier to incorporate flow controls and scheduling.

There are quite a few tutorials out there : http://msdn.microsoft.com/en-au/library/ms169917.aspx

But I have always held a belief that if you cannot do something manually, then you really shouldnt be trying to automate, or, use tools that you are unfamiliar with.

If the scripts work, and it is a transition stage while you upgrade, then I would be inclined to simply use the scripts.

I would have thought that you would simply backup and restore the db and apply whatever DDL scripts are needed for the new structures - unless you are doing a staggered rollout where some people (or departments) start sooner than others using the new version.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38844445
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.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 400 total points
ID: 38844639
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"

hence some of those questions earlier to see if it is two way convergance.

Here is the MS link for tablediff : http://msdn.microsoft.com/en-us/library/ms162843(v=sql.100).aspx and the "how to" link at the bottom provides an easier to understand article.

@ScottPletcher, EaswaranP (the Asker) has already stated they have RedGate Data Compare, so I didnt bother following that one up. I always thought it could create scripts, but EaswaranP has indicated that they couldnt... Maybe that is worthwhile following up ? RedGate are generally fantastic products - and I am missing them at the moment :)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38844745
>> 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"?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 38844906
>> 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_modified > target_db.table_a.datetime_modified

If target_DB was simply a repository, then no where near the problems...
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38844955
RedGate's Data Compare certainly used to gen scripts on demand.  Can't believe they'd really remove that capability from the tool.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38847863
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/
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38847946
Thanks acperkins, good to know that it still does :)
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38883681
@RolandDeschain: Atlantis Data Inspector is not free one!

If free one please let me know from where I could download the installer.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 38892874
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38893968
@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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38895095
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 .  :)
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 400 total points
ID: 38895656
Well, we know that db2 is the new version (say version 13) of the db and the apps (say version 12).

We also know that there has been a lot of structural changes (tables added, some dropped, and there is a schema tool in use)

We can "guess" that the new version was seeded from the old version. We dont know if each has a life of their own. We dont know if version 12 is still the ultimate source of truth, or, version 13 has new activity that needs preserving, or possibly retrofitting into 12.

If there is an upgrade path, then why not simply re-upgrade 12 to 13 ? I am guessing that there has been additional work in 13 that now needs to be preserved.

So, it could be far from being a simple script. There are additional pieces of the puzzle that needs to be described.

Based on the information provided so far, and some of the questions being raised, it is very hard to simply make a suggestion further than redgate and tablediff.

Heck, scripting the differences is not the hard part (as far as I am concerned) and have had to script this type of things dozens of times. Getting it right to meet the business rules is the hard part.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38896769
So, it could be far from being a simple script.
I agree with you 100%.  My comment was more tongue in cheek.
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 100 total points
ID: 38897055
From the question:

"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?"

Later confirmation:

"I'm looking for generate script for DML changes not for DDL changes. The tool that you suggested for DLL changes. Please do advise."

and

"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?"

Sounds pretty clear to me, the asker wants a data sync rather than something else. I still say that using SQL 2008 R2 the MERGE solution could work very well.

EaswaranP, the idea behind MERGE is exactly to sync a target table with a source where source can be a table, view, a subquery basically anything that produces a result set. In one run the target is compared with the source and where it finds matches or not matches will execute the insert, delete or update statements in the target. Total control for you and is the best solution performance wise as well. The only problem in your case is that you deal with tables on 2 different servers, which means you have 3 solutions:

1. link the source with the target
2. use the OPENROWSET function to query source from target server
3. replicate the databases from source to target and then you can execute the MERGE locally.

Any tool you will use will execute very poorly in terms of performance because it would be an application that will compare one row at a time with the source table. If the tables are not big you're fine but otherwise it will take tones of time. This is data comparation we are talking about, schema would be fine.
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 38899169
Thanks.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 38899184
Just out of curiosity, which way will you go?
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now