How can I synchronize one set of data with another?

Question

I have an old database and a new database. The old records were converted to the new database recently. All our old applications continue to point to the old database, but the new applications point to the new database.

Currently the old database is the only one being updated, so throughout the day the new database becomes out of sync. It is acceptable for the new database to be out of sync for a day, so until all our applications are pointed to the new database I just need to write a nightly cron job that will bring it up to date.

I do not want to purge the new database and run the complete conversion script each night, as that would reduce uptime and would create a mess in our auditing of that table.

I'm thinking about selecting all the data from the old database, converting it to the new database structure in memory, and then checking for the existence of each record before inserting it in the new database. After that's done, I'd select everything from the new database and check if it exists in the old one, and if not delete it.

Is this the simplest way to do this?

Answer 1

I don't know if it is the simplest but surely, in you situation, is the safest.

And if you're using SQL2005 or 2008, I'd implement this in Integration Services.

Answer 2

I agree with Paulo, but depending on how long it is going to take for all apps to point to new DB you could look at using a tool to do this, you can look at SQL Data Compare http://www.red-gate.com/products/SQL_Data_Compare/index.htm this is a great tool and I use it to sync data between dev/pre-prod/prod servers.

Answer 3

This method double-comparison method will get unwieldy if you have very high volume at all. If you don't have high volume, then doing a complete replacement every night is "simpler".

Assuming that you do have to worry about volume, this is what the data warehouse guys call a change data capture problem. If your old database has a timestamp on the records, then you could at least save half of your comparison (you still might need to look for the deletes). If not, then here are a couple of other suggestions.

You could put a trigger on the old database to write out any changes (insert, update, delete) to the old database and then apply those to the new database every night.

Alternatively, your database system probably has a log (redo log, transaction log, etc.) that has a record of every database transaction. If you have access to this log, then you could query it every night for the transactions since the previous night. Then appy those transactions to the new database.

Answer 4

Since the old data structure can be "converted" to the new data structure, you have many options. Here is one to consider.

Create View(s) on the old data/table(s). Instead of transforming the data "in memory", create database views of the old data that perform these transformations. Then use the tSQL 2005 and later EXCEPT clause with a union to get all the changes. Store the results of the query below into a #temp table or a @tableVariable. Then perform deletes, inserts, and/or updates.

select 'inOldOnly' as whichServer, old.*
from OldDb.dbo.vwOldStructureTransformed as old

EXCEPT

select 'inOldOnly' as whichServer, new.*
from NewDb.dbo.NewStructure as new

UNION 

select 'inNewOnly' as whichServer, new.*
from NewDb.dbo.NewStructure as new

EXCEPT

select 'inNewOnly' as whichServer, old.*
from OldDb.dbo.vwOldStructureTransformed as old

This approach is fairly flexible. In practice I recommend enumerating the columns (versus using *). If we knew more details about the size of the old and new databases we could comment on if there is any benefit from filtering rows in the WHERE clause.