Syncronize tables without Replication 1

[tourcd]

Hi,

I've got the following problem and not sure where to start with it.

I've got a database on ServerA with a table called Customers. I want to copy this table to ServerB, I cannot use replication. I want to schedule a copy twice a day.

I created a DTS package that copies the table, fine. However it copies the whole lot, all 500K records. I don't have the bandwidth for this so need a solution that only copies updated and new records.

Looking around I thought implementing SQL Server Log Shipping might help but if a failure occurs it requires manual intervention to get the target server up and running, this is no good.

Does anyone have suggestions on how I might solve my problem?

Is going down the DTS route the right thing to do?

Thanks

 

[JohnTcolo]

Why not write a trigger than copies/replaces each record in the 2nd database each time an original Customer record is updated or added?

Hope this helps.

 

[JohnTcolo]

Ooops. Meant copies/updates record in the 2nd db. Of course, you may want to delete records from the 2nd db when deleted from the original.

 

[tourcd]

Thanks, JohnTcolo. Not sure what mechanism you'd use to achieve the copy/update when a record gets changed in 1st DB. Can you elaborate?

 

[RiverGuy]

Do you have something to the effect of a LastModified column on your Customers table? A column that gets updated with the current time each time a record is added or updated (preferably via a trigger)? If you do, then all you need to do in your DTS package is get the maximum LastModified date in your destination table, and use that as a parameter in your select statement against your source table.

 

[JohnTcolo]

DTS is fine for scheduled processing, but if you want live updates, a trigger is the best way to go. Your update trigger code will be keyed to any change to records in the original db.

Hope this helps, there is a wealth of online info outlining code examples for all types of SQL triggers.

 

[RiverGuy]

I would be hesitant to code cross server database operations in a trigger.

 

[JohnTcolo]

RiverGuy, your idea is best. I overlooked the fact he merely needs to run the updates twice a day. DTS is the way to go, good call.

I have done cross server triggers with no problems, not relevant in this case.

Thanks,

John T.

 

[tourcd]

Great suggestion RiverGuy, simple solution. Unfortunately I don't have a datetime stamp in my DB

Is it possible to do a compare of the tables and then apply the compare to the DB on ServerB?

 

[RiverGuy]

Yes it is. The drawback to this is that to do the compare, you still have to look at a lot of data.

One thing to look at in SQL Server would be the CHECKSUM statement. Info here:

http://msdn.microsoft.com/en-us/library/ms189788.aspx.

You can compare hash value to hash value to find which records have changed. However, it's not guaranteed 100% to produce unique checksums.

In this scenario, you would do a join (probably full outer join) on both tables and compare the checksums. Differing checksums mean changed records.

A less network intensive method here would be to also house your table copy in your source database to do the checksums locally, and then only send the changed records over the network, while updating your local "copy" as well.