How can I keep two tables in two different databases with different schemas the same in real-time?

Question

I have two databases A, and B, which will reside on the same server. A and B have different schemas. Database B will contain different numbers of tables, and sometimes tables with the same name will contain extra columns in database B compared to A.

For some of the tables in Database B, Whenever its corresponding table in Database A is changed, the table in B needs to be updated immediately to reflect that change. In the case of extra columns in the Database B table, I might need to write additional logic to populate them.

If Database B cannot be updated, I want the update to Database A to fail too.

How could I set up such a scheme? Can I set up a trigger to automatically send the same update/insert/delete statement to Database B? Is there another way?

BTW, Database A = Sql server 2000, Database B = Sql server 2008.

Thanks in advance!

Background: There is a huge database of ~500 tables. There are many frontends that use this big database. I'm replacing the frontends one at a time. To prove that it integrates with all the legacy frontends I need to run a second system in parallel with their production system. To keep double data entry to a minimum I want to copy table modifications from the production system for all tables that aren't updated by my replacement frontend.

Answer 1

As there are two servers there are no real good options. Pretty much your only choice will be to use triggers in both databases that write to the remote database. You'll need logic in the trigger so that the trigger does nothing if the call is coming from the other SQL instance. Otherwise you'll end up with a deadlock every time.

If one instance is down than nothing will work until that server is back up and running.