Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Aaron Bertrand Aug 3 '12 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 '12 at 23:26

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
The databases could be on the same server, I guess I should have mentioned that. –  IBC Aug 4 '12 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 '12 at 3:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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