Assuming the title is the question, there are a couple of ways of doing this that I would recommend looking into. I would not suggest triggers. You're going to have to run them through linked servers and that can be very inefficient. Instead, I'd suggest you look into mirroring, which can copy one database to another. If you're running SQL Server 2012 you can use the Availability Groups which allow for read only copies in mirroring.
If you need to have a read only copy but you only want to move a few tables or a few columns within some tables, then I'd look at replication. With replication procedures you can pick and choose what gets moved through replication at a very granular level.Both these topics are available in detail in the books online.
answered Jul 16, 2012 at 08:11 AM
Grant Fritchey ♦♦
I wholly support the ideas and advice from @Grant Fritchey and would add that it might be worth investigating Service Broker as a solution. This would make sure that the updates are made, and in a consistent order but neither system is going to impact on the other within a transaction. This would assist in keeping server performance consistent.It all depends on what your system needs to achieve and what best suits your scenario.
answered Jul 16, 2012 at 08:46 AM