Enable SQL Service Broker in SQL Azure
Service Broker should be available in Windows Azure.
Support service broker
Today we don’t have a cloud equivalent of SQL service broker outside of either service bus or SQL Server in a VM. I’d love to understand the scenarios that you have in mind. Please send me email at firstname.lastname@example.org if you’d like to continue the conversation.
David Lean commented
1. Async Internal SQL Operations.
Our “Comms Input” Worker Roles are streaming results to the database. It is time sensitive, it needs very short & predictable write operations. Occasional a set of values will trigger an event which results in thousands of customers being affected. SQL now needs to update multiple tables to update their account balances. This can take a few minutes, but can’t delay the injection of the streaming input. Service Broker lets me decouple this work. It also changes the security context to keep these very sensitive finance table updates isolated from web users. (or any other user)
2. Existing Azure Queue alternatives are merely unintelligent queues.
Azure Storage Queue required you to poll it. This becomes expensive if you want latency < 100 ms. It burns CPU unnecessarily.
Azure Service Bus is better but requires an WCF app to process messages on arrival. You need to roll your own poison message handling & in-order queue delivery etc. It becomes hard to get multiple readers to “play nice”. If a Reader is shutdown it can take many seconds for any unprocessed messages to appear back on the queue so another reader can process them.
Service Broker is much simpler to write. Every message received is processed inexpensively with low latency. Meaning at night when I may only get a message every few minutes they are still processed as rapidly as in the middle of the day when we get thousands of messages(updates) a minute.
James Hatton commented
Selecting Service Broker to communicate between the facets of a system is a fundamental architectural decision; the system's operating environment is wholly constrained by the decision to use Service Broker. If SQL Azure does not support Service Broker then it cannot be used; Azure is precluded as a deployment option, unless SQL Server is installed on a VM. Clearly, this suggests SQL Azure-based applications can benefit by having Service Broker available in SQL Azure.
One scenario is to synchronize data between databases, one of which is a master identity database that holds the identities for the same persons in the separate systems. The number of potential scenarios is unlimited.
Jeffrey Clark commented
SQL Service broker and sql dependency allows our web applications to serve real time data to connected clients driven by changes at the database level. Without the service broken on Azure it is impossible for us to migrate our applications from traditional VM's without significant changes and re-development.
Greg Forsyth commented
Using a Patient Queueing service using SQL Dependencies and company wants to move to an Azure set up and currently there does not seem to be any way to work around this.
Service Broker should be in Azure.
Christopher Quest commented
We use SQL dependency in an occasionally connected android application were part of a transaction is started on one tablet and completed on another. We use SQL dependency to tie these events together. Currently we use a RESTful web service to communicate to the backend but would really like to use Azure Mobile Data Services, but we need to keep the SQL dependency capability.
I have implemented Audit (Saving Old Value, New Value, User ID) on all of our tables using SQL Broker. Not only that i have event triggers run on this audit using SQL Broker which executes Stored Procedures. We are hold on moving to cloud because cloud does not have SSB Enabled.
Agree, service broker is great for simple and complex queue. E.g. I would like to open a sql connection and wait for messages to be received instead of polling every x seconds.
Paulo Umetsu commented
Does someone now if with Azure Virtual Machine I can run the SignalR (SQL Dependency, and set broker on the database)? I also cannot upload files and I think with a virtual machine that wouldn't be a problem.
Kevin Christensen commented
Service Broker support would be a huge plus when deciding between on-prem and Sql Azure.
We use SQL dependency in asp.net for caching database data. Maybe it is not necessary and can be replaced by other mechanism, but we develop an application and this is part of it. What I need is a kind of smooth transition and even ability to work against normal SQL or Azure. Otherwise there is all the time the question whether Azure is worth the necessary effort.
Stephen Dillon commented
Have you considered Azure's IAAS offer instead of SQL Azure? There is a Windows Azure Queue capability as well as Topics and Subscriptions.
Regarding SQL Azure, a.k.a. SQL Database circa 2013, it is not "SQL Server". It is a "NewSQL" database, sometimes also referred to as a Service Database, that allows for horizontally scalable relational databases within the Windows Azure. It is fitting that the role of the SQL Broker is not implemented when using SQL Azure by its very nature of the Cloud.
If you were to use Azure's IAAS (Infrastructure as a Service) offer then you could implement an Azure VM within the Cloud. You will lose the SQL Azure scalability and retain the SQL Broker messaging.
Richard Kiessig commented
+1 for SqlDependency. Caching is that much more important in a multi-tenant environment like SQL Azure / Windows Azure -- and query notifications enable much more efficient caching in many cases, even across multiple web app instances.
We need something like a messaging system on the Azure.Sometimes a query is heavy and we need to run it of the server,but we do not like to wait and we need to close the app and see the result later so it is necessary.
Yes! I also need SqlDependency for Caching purposes.
News -- Now Available: Windows Server 2008 R2 on Amazon EC2
Without service broker on azure, it forces us to use AWS windows instances since we cannot duplicate the functionality any other way in azure. Since there is total silence on a roadmap for ssb on azure, AWS is our current plan forward.
ssb transport and dialog security should be part of azure...real advantages would be realized if this was implemented!!!
Need SqlDependency for Caching purposes.
Jamie Thomson commented
If I can access AppFabric Service Bus using T-SQL, then yes. If not, no.
Guess which it is!! :)
Besides, queues in the DB offer benefits over Service Bus such as being backed up with the DB. Not saying Service Bus is not valuable but its not an either/or - both are suited to certain scenarios.
Does Azure AppFabric Service Bus not meet the scenario requirements for those folks looking to use SErvice Broker?