October 6, 2014 at 10:07 am
Hi all, I have zero experience with Service Broker, but from what I've read on the subject, it may be a solution to the problem I need to solve.
I've got multiple instances of an application, each with its own MySQL database. I am currently using stored procedures and SQL Agent to query these MySQL database to retrieve the most recent realtime data once every 5 minutes. This works, but it's not going to scale. The job takes up to 5 minutes to run on occasion (usually takes about 45 seconds). It's not a massive amount of data, and it really should take less time than it is.
I've basically got a stored procedure that loops through the instances that need to be queried, and then executes a stored procedure for each table to synchronize, providing an Instance ID and Linked Server Name to each of those stored procedures that are pulling data from the MySQL Instance.
My thinking is that instead of running the stored procedures individually within that loop, it would instead add those stored procedures with the provided parameters to a queue, and have that same instance of SQL server process those messages, with each server instance being synchronized in parallel. In case it matters, the version of SQL Server I am using is SQL 2012 Standard.
Is this something that Service Broker would be suitable for? Is there possibly a better solution?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply