asked
how to update a local ms sql database when remote mssql db is updated
You're providing no information here on what you are looking for. You could be looking at doing distributed updates using a trigger which will require a properly configured MSDTC or you could be looking at something as simple as transactional replication. You need to give further details around what you actually need before someone can provide any level of assistance.
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.
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.
Excellent suggestion. I keep forgetting about that as an option.
10 People are following this question.
