none
Any reason to not support SQL CLR and Service broker in Azure? RRS feed

  • Question

  • Without Service Broker and CLR being enabled in Azure SQL Databases it is a great challenge to develop applications with transactional behavior in Azure. Currently, to overcome the problem, software  designers have to come up with pretty sophisticated compensation approaches that lead to over-complicated and expensive solutions. Is there any plan to bring CLR custom assemblies and Service Broker back to Azure SQL?

    Tuesday, April 3, 2018 7:27 AM

Answers

  • Good day Alexey,

    Azure services have different option and as you mentioned there is a solution for what you need as well (in preview at this time). Azure Managed Instance supports CLR and Service Broker.

    In order to use it at this time you will need to ask to register to the preview. Go to add services and search for "managed instance". Click on add and you will see option to register to the preview. With that being said, important to mention that not all request approved, so you might need to wait for the official release of this feature. 

    You can read more in this link:
    https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/03/07/what-is-azure-sql-database-managed-instance-2/

    If you want to use the service Azure Database then you can use different solution for messaging like Azure Queue storage. We need to understand your exact needs in order to point for the right services


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Tuesday, April 3, 2018 11:10 AM
    Moderator
  • Back in 2016, when CLR was disabled in Azure Database, it all sounded like a temporal step to resolve some security vulnerability. What is the status of today? Any plans to bring it back eventually?

    My guess is that the answer to that depends on how the "managed instance" (MI) option works out.

    If MI works out well I think that becomes 90% to 100% of the Azure SQL Server offering, the good news being it supports CLR and service broker.

    I expect it will work out well, but then I'm not clear on just why it has taken this long to get to full GA.

    Josh

    • Marked as answer by AlexeyE Wednesday, April 4, 2018 5:07 PM
    Wednesday, April 4, 2018 4:43 PM

All replies

  • Hi, you will have to take a look at Azure SQL Managed Instance if you need that functionality

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance


    Cheers Christophe - Kindly click Mark as Answer on the post that helps you - www.cloudcrusader.com

    Tuesday, April 3, 2018 8:11 AM
  • I am certainly interested in Managed Instances option as it is the closest one to the classical SQL Server. However I found the service pricing to be a bit out of bound, especially taking into account that even the SQL Server Express addition has both features for about 13 years I believe. Migrating to Managed Instance only because of CLR and Service broker  would not be justified from the customer perspective as alternatives like e.g. Aurora database are capturing quite a bit of attention at the moment. 

    Are there any plans to incorporate CLR and Service Broker (even without cross database communication) into the Azure SQL database?

    Thanks

    Tuesday, April 3, 2018 9:57 AM
  • Good day Alexey,

    Azure services have different option and as you mentioned there is a solution for what you need as well (in preview at this time). Azure Managed Instance supports CLR and Service Broker.

    In order to use it at this time you will need to ask to register to the preview. Go to add services and search for "managed instance". Click on add and you will see option to register to the preview. With that being said, important to mention that not all request approved, so you might need to wait for the official release of this feature. 

    You can read more in this link:
    https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/03/07/what-is-azure-sql-database-managed-instance-2/

    If you want to use the service Azure Database then you can use different solution for messaging like Azure Queue storage. We need to understand your exact needs in order to point for the right services


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Tuesday, April 3, 2018 11:10 AM
    Moderator
  • Hi Ronen,

    Thank you very much for your explanations. I had already subscribed for the preview. Hope to get the approval soon.

    Sure, we are using Storage, Service Bus etc and these are certainly great technologies. Unfortunately, when we need a strong transaction behavior together with high throughput requirements, nothing comes any close to capabilities of Service Broker empowered with CLR runtime. We can emulate some aspects of SODA architecture using available Azure services, however all comes at a cost of the system complexity as well at the total prices.  

    Thanks for the explanations.

    Regards,

    Alexey

    Tuesday, April 3, 2018 12:32 PM
  • You are most welcome :-)

    * I am one of the SQLCLR freaks, so I really understand the need of it. Check this ;-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, April 3, 2018 5:51 PM
    Moderator
  • SODA architecture?

    https://www.microsoft.com/en-us/research/wp-content/uploads/2005/09/tr-2005-129.pdf

    OK!  Had not looked at that in a long time.

    The bulk of Microsoft platform architecture has just gone in other directions since 2005.

    And the hardware platform technologies have evolved, too.

    I was just cringing a little when you kept mentioning that you need "strong transaction behavior" and felt that was not available in Azure SQL without CLR and service broker.  I wouldn't put it that way.  Transactions are just fine, thank you, with the C# code in a web app and the database running without those features.

    What you may not realize - what I had not realized - is how FAST the call time now is between a C# web app and an Azure SQL database.  It is now SO fast as to don't even worry about it, and that's because the computer room pipes are now 10ghz, 40ghz, and rising to 100ghz or faster.  And the call overhead has apparently gone down to match.

    We had a very chatty app, making tons of little calls, and I thought we could get a big improvement by reducing the calls by 99%.  Nope.

    So I think you can refactor your app into a conventional architecture now and see pretty much the same performance, and probably greater stability, without the CLR and all that.

    I hope that's good news.

    Josh

    Tuesday, April 3, 2018 5:56 PM
  • Hi Josh,

    Wow, I see we are speaking the same language :-)
    I am aware of the moves MSFT made getting to the Cloud and do understand and appreciate most of them. 
    Nethertheless, I do consider lack of the SSB/CLR support as crucial in Azure. 
    Unfortunately MSFT is not the only player over there, and if SODA is not an option in Azure, all kind of other matrices start playing a role. The speed of transactions is always welcome, but is not going to be a great differentiator in a number of situations. This days we are dealing with high volume transactions (versioned brand/product publishing), with blockchain smart contracts that land absolutely into MSFT's SODA Conversations and Conversation Groups, ML models that need to be retrained dynamically based on the application state and more. Hundreds of terabytes storage size is just enough for the systems we have to design this days. 
    Sure, compensations are great, but only if we have just a few of these in the whole system. How about 10**4 types of high volume transactions that need to be coordinated?
    We certainly can simulate some aspects of these without SSB/CLR, but it is pretty hard and expensive. Why should we?

    At this moment we are not looking for a way to redesign any of our solutions. Personally I am wandering whether MSFT has a plan to re-introduce SODA in Azure.

    Please let me know,

    Alexey

    Tuesday, April 3, 2018 8:01 PM
  • Great presentation!  Thanks!
    Tuesday, April 3, 2018 8:16 PM
  • How about 10**4 types of high volume transactions that need to be coordinated?

    Well, I hear that!

    Because it's pretty much what I had to do, too.

    I was able to wangle something within TSQL, but it wasn't easy, and I don't think it scales further.

    We thought we wanted it to scale further - then it turned out we had other bottlenecks anyway and my fix was good enough for the moment.

    But of course I told everyone - if you want to scale ANOTHER three or four or five orders of magnitude (and we did!) then building a bespoke engine was the way to go.  Sounds like that's where you began umpty years ago.  I never thought to try to build it around the service broker, that may be a bottleneck now.  Or not, I've never tried to measure it.

    Today's solution is to spin up N servers at the front end, and then to use some forms of optimistic processing on the back end.  Well, the backend part just doesn't always work, in your case or mine.

    But the front end part might.

    Though I can see nobody in their right mind would WANT to take on a rearchitecture like that! :)

    Josh

    ps - blockchain, huh?  OMG should have done that, would have tripled my salary!
    • Edited by JRStern Tuesday, April 3, 2018 11:36 PM
    Tuesday, April 3, 2018 11:34 PM
  • Have you also looked at other parts of the Azure ecosystem like Logic Apps and also using Elastic Query for SQL DB?

    Elastic Query should resolve your cross-database issues and Logic Apps can push some of the middle tier work and do something like the message based approach of Service Broker.

    It's not that the same functionality is not there - it's just package differently for Azure.


    Martin Cairney SQL Server MVP

    Tuesday, April 3, 2018 11:35 PM

  • >> see pretty much the same performance

    This is not accurate for all cases. There is a VERY good reason to use SQLCLR on local SQL Server which has nothing to do with how FAST the call time now is between a C# web app and an Azure SQL database.

    As always the decision should be done in case to case according to the specific needs and uses of the data.

    With that being said, it is true that in some cases you should not use SQLCLR. In fact by default you should not use SQLCLR :-) This is extension of our options if and when is needed


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Wednesday, April 4, 2018 3:57 AM
    Moderator
  • Back in 2016, when CLR was disabled in Azure Database, it all sounded like a temporal step to resolve some security vulnerability. What is the status of today? Any plans to bring it back eventually?
    • Edited by AlexeyE Wednesday, April 4, 2018 3:22 PM
    Wednesday, April 4, 2018 3:22 PM
  • Back in 2016, when CLR was disabled in Azure Database, it all sounded like a temporal step to resolve some security vulnerability. What is the status of today? Any plans to bring it back eventually?

    My guess is that the answer to that depends on how the "managed instance" (MI) option works out.

    If MI works out well I think that becomes 90% to 100% of the Azure SQL Server offering, the good news being it supports CLR and service broker.

    I expect it will work out well, but then I'm not clear on just why it has taken this long to get to full GA.

    Josh

    • Marked as answer by AlexeyE Wednesday, April 4, 2018 5:07 PM
    Wednesday, April 4, 2018 4:43 PM
  • You are most welcome :-)

    * I am one of the SQLCLR freaks, so I really understand the need of it. Check this ;-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Hello Ronen. As a fan of SQLCLR, you might be interested in the following two suggestions on UserVoice:

    1. Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE
       
    2. Security error connecting to SQL Server Express LocalDB in SQLCLR using EXTERNAL_ACCESS and "(localdb)\Instance" connection string

     

    I am requesting that you and others please vote for those suggestions to help greatly improve the usability of SQLCLR. They both provide significant benefit to SQL Server (especially #1) without requiring much development / testing effort, or adding much (if any) risk.

    Thanks and take care, Solomon...


    Wednesday, February 6, 2019 5:03 PM