Scripting database differences only

[PaulBUK]

Internally, my company utilises MS SQl 7.0, but external customers receive a database copy shipped in full periodically in SQLBase, SQLServer (7 or 2000) or (potentially) Oracle.

We need to simplify things, and cut costs, so we want to ship the changes only (new/updated/deleted records). PKs exist on all tables as do datetimes of amendment/creation.

I'm toying with the following..........

Create a trigger on each table which logs the deleted PK, and the time of deletion when a record is deleted. All deletions since date X can then be recreated for application on the external machines.

As there is a datetime field called Updated on each table, Amends/Inserts can be found by simply selecting where Updated > date X.

For application on the customer machines, run the delete script first, then the insert/updates.

Its plausible and easy, but have I missed anything out? Is there a better way? MS Replication is NOT an option here.


Just looking for reassurance I guess!

 

[RickCole]

Just a quick thought on this one:

I assume the tables which are updated at your end do not get changed at the customers end (apart from your scripts). If they do then it's possible your scripts will hit refeferintal integrity problems.

Rick.

 

[PaulBUK]

You are correct in assuming that the tables involved should not get changed at the customers by anyone except us. Of course, if the customers end is changed by someone else, we might not know about it.

With SQLBase its not a problem - embedded users and passwords which cannot be hacked easily. With SQLServer its unprotected.

CRCing data to check for changes could be used to allow us to scan for this. - sorry I'm going slightly off topic now.

Paul

 

[RickCole]

Just had another thought on this one:

On your main database: Rec_1 inserted at some stage in the future Rec_1 deleted.

Scripts sent to Customer and delete script runs. Rec_1 not deleted, as it doesn't exist. Rec_1 then inserted after insert script runs. Therefore Customer database out of step.

I think the only safe way of doing what you want is if you, essentially create you own transaction log of SQL statements in the order they are executed.

Rick.