1

We need to push all sql data changes to our local caching infrastructure in real time. We are using SQL Server 2012 Availability Groups. In the process of avoiding triggers for population SQL Service broker queues, I am researching the use of SQL jobs as explained in this article: Async SQL execution

By calling this sql procedure from insert/update/delete stored procedures should avoid the usage of triggers. Creating/deleting sql jobs dynamically from stored procedure is valid/allowed in sql availability groups?

The SQL Agent Job will allow the data to be manipulated prior to sending the message to the Service Broker Queue.

0
1

SQL Agent jobs are not contained within the Availability Group. When using SQL Agent jobs to manipulate a database within an Availability Group, there are all sorts of special considerations that need to be taken, particularly related to failover.

What happens if the job is running during failover? The job will likely fail, as the replica it is running on is now a non-writable secondary, and probably needs to be re-run on the now-primary replica. What if a job has been created, but not started when there is a failover?

What happens if a row is inserted, then immediately updated? The two SQL Agent jobs would have a dependency in the order in which they run. This is an issue with using SQL Agent jobs in general, regardless of whether an AG is part of the equation, but would be exacerbated if the initial INSERT were "lost" in a failover.

Additionally in your specific scenario, you will be generating one SQL Agent Job per insert/update/delete. SQL Agent will only start one job per second. This means you will have a self-imposed limitation of one insert/update/delete operation per second. This will be a serious scalability issue, in addition to the headaches of job management.

Instead, have your CRUD procedures insert directly into the Service Broker Queue. The process to enqueue a message for Service Broker will likely perform much better than creating a SQL Agent job (but I've not tested it to give specific numbers). The Service Broker queue is contained within the database, so you do not have to worry about messages being "lost" in a failover.

If you need to do data manipulation, that can be done from within your Service Broker queue before additional processing/sending is done. Because that manipulation is being done within the Service Broker, it would all be asynchronous to your CRUD operations.

Using SQL Agent jobs for your scenario should not be necessary at all.

0
1

I would investigate building a .Net application that makes use of SQL Server Query Notifications to invalidate the cached data, and repopulate it from the source data.

From the MSDN page linked above:

Query notifications are used for applications that need to refresh displays or caches in response to changes in underlying data. Microsoft SQL Server allows .NET Framework applications to send a command to SQL Server and request notification if executing the same command would produce result sets different from those initially retrieved. Notifications generated at the server are sent through queues to be processed later.

Query notifications, since they use Service Broker, are by definition asynchronous, and do not require you to create triggers on the affected tables.

As AMtwo noted in his answer, using SQL Server Agent for this is problematic in several ways, not least of which would be what happens if SQL Server Agent stops running, or can no longer access the database for some reason?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.