We have an sql database where the performance is being impacted by a number of secondary applications performing complex queries for reporting and other business requirments off the database
We have considered a solution where when an update is made to the primary database we use an asynchronous trigger to kick off a SP. The SP will then use service broker to send a message to a secondary database which updates the data on that secondary database.
Note the data on the secondary database is a denormalised subset of the data.
We think Service broker is the right solution as we get the updates to the secondary database asynchronously, reliably and in order.
Is this a good solution for Service broker or can anyone advise a better solution.
thks
Hi Chris,
There are a number of out-of-the-box answers to this problem:
- transactional replication
- log shipping
- database mirroring
Service Broker would require you to write code to achieve the desired result. Have you looked at these out-of-the-box solutions first?
HTH,
~ Remus
