question

Tom Harens avatar image
Tom Harens asked

Synching Data Between Databases

We have a data set that is updated for each release of our software projects. We need to modify data between releases of software. We are at a point where we need to make changes to data for a future release, but have not finished a current release, and we do not want to affect the data for the current release. We are looking at creating a copy of the database to add the new data. Our problem is we need to continue to add data to our previous database, and bring this data forward. We also may be making Schema changes to the new database that are not part of the previous database.

This seems like an issue that others would have solved, but I am not able to find a solution. We have investigated SQL Replication, but that looks like our Schema needs to stay the same across databases so that won't work for us.

Thanks,

Tom Harens

replicationdata
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered

For the data comparison part, you can consider Red Gate SQL Data Compare or Apex SQL Data Diff, I haven't finished mine yet!

For the schema comparison part, you can consider Atlantis Schema Inspector (my tool), Red Gate SQL Compare or Apex SQL Diff.

Each of those tools will allow you to do filtering in different ways, but you should be able to come up with enough to meet your requirements...

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Harens avatar image Tom Harens commented ·
This would be a manual process, is there a way that we can automatically move data forward to the next version of the database. When data is added to a database we can have as many as 1K records added in mutiple tables, which I think would be hard to mange manualy. Also we do not need to compare schema. We will be putting schema changes into subsequent databases and they do not need to be put into the previous versions.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
How would using those data comparison tools be a manual process? Set them up, use the command line interface, scheduled task. Job done...
0 Likes 0 ·
sp_lock avatar image
sp_lock answered

You are also use the TABLEDIFF application MS ship with 2005 and above. Its free.

TABLEDIFF

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Harens avatar image Tom Harens commented ·
I was wondering if Transactional Replication would work. I know there can be problems with adding schema to the subscriber, but I believe that would only exist if the data I am trying to merge is being merged into the table I applied the schema change.
0 Likes 0 ·
CFL_DBA avatar image
CFL_DBA answered

RedGate works well with Data Compare and SQL Compare. They have very intuitive UIs that will help you with your comparison.

If you would rather use free intrinsic tools - TABLEDIFF, as noted, is delivered with SQL Server 2005 - and it is command-line based.

I have used each of these tools and they all do exactly as intended.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.