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.

  • Why do you need two copies of the data? Why can't B just have a view or synonym so that you don't have to eternally maintain two redundant copies? What you want to do is clear, but why is not so clear. Aug 3, 2012 at 23:13
  • I updated the question with the problem background. It has to do with replacing legacy software. It's not a fun problem and I'm not looking forward to solving it.
    – IBC
    Aug 3, 2012 at 23:26

1 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.

  • The databases could be on the same server, I guess I should have mentioned that.
    – IBC
    Aug 4, 2012 at 0:51
  • As one is SQL 2000 and the other is SQL 2008 they will be different instances. The same problems apply because they are different instances. Doesn't matter if they are on the same physical server or not.
    – mrdenny
    Aug 5, 2012 at 3:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.