How to propagate changes to another database?

I want to let any data changes, made to several tables, in one database be coppied (mirrored?) to similar tables of the other database. Both databases (when talking about tables I want to be syncronized) have similar tables’ structure (schema). These tables contain rarely changed data, but if data does change, I’d like the changes to be propgated at the same time.

These tables contain some kind of reference data. The original database is the main one. The other one is used for some operational (online??) tasks, but it needs a couple of reference tables from the original database. So, is it admissible to do this copying (if “yes”, than how)? Or the approach is wrong initially?

  • Catch the sp_executesql table Result
  • SQL query multiple columns in SELECT - one needs to be DISTINCT
  • Extract Data from Excel File and Store in SQL Server database
  • Adding two column values in SQL Server to populate a third column, can this be done without a trigger/stored procedure?
  • Find closest date in SQL Server
  • Using Pivot on SQL
  • It seems that mirroring database is a more complicated operation than I need. And simple triggers seem to be more appropriate to do that. But I’m a little bit confused on how to do that exactly and whether this is the way I should do that.

    The original database is on the ms sql server 2005. The target database is on the ms sql server 2000.

  • SqlException timeout expired without being reached
  • Mirroring-like functionality in SQL Express via replication?
  • What are the scenarios for using mirroring, log shipping, replication and clustering in SQL Server
  • How to replicate a shared MSSQL 2005 db for development purposes?
  • Simplest solution for high availability of SQL server 2008?
  • Restoring from backup files on the principle server in a synchronous mirroring environment
  • One Solution collect form web for “How to propagate changes to another database?”

    You cannot do this with triggers (at least not with only triggers). You need to account for unavailability and have capability to queue updates. The right technology is replication, either Transactional Replication or Merge Replication.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.