Here, we will show you how you can use the asynchronous execution mechanism offered by SQL Server Service Broker to ‘queue’ up data inside SQL Server which can then be asynchronously passed to a Python script, and the results of that Python script then stored back into SQL Server.
This is effectively similar to the external message queue pattern but has some key advantages:
- The solution is integrated within the data store, leading to fewer moving parts and lower complexity
- Because the solution is in-database, we don’t need to make copies of the data. We just need to know what data has to be processed (effectively a ‘pointer to the data’ is what we need).
Service Broker also offers options to govern the number of readers of the queue, thereby ensuring predictable throughput without affecting core database operations.
There are several interconnected parts here, and Arvind walks through the entire scenario.