World Wide Distributed SQL Server Service Broker Instances + C# Dev + Azure Service Bus-EventHub + Stream Analytics + PowerBI.com Scale-Out Challenge

Hello!

As a frequent flyer I like to sleep on the airplane but since sleep is overrated and I’m in a long term relationship with Data Platforms 🙂 , I decided to write about one of the projects that I’m working on.

Can’t disclosure all the details about it (NDA kicking in…), but I can I share a high level architecture of it, an howto and some interesting references.

High level architecture:

1. Messages/events stored in a Receive Queue from SQL Server Service Broker (instances running from multiple geographic locations)
2. Windows Services (C#) running at each site to get messages from the SQL Server Service Broker Receive Queue.
   Talking advantage of SQL Server 2016 Format Query Results as JSON with FOR JSON when reading messages from the above queue so when they are sent using the service, there is no need to do any type of string manipulation, basically, just send message 🙂
3. System must be able to handle no less than thousands of messages per minute
4. The C# service will stream this data to Event Hubs (Azure Service Bus service) that is able to ingress all this events at a low latency and high reliability at a massive scale, more detailed information about partitions and throughput units, read here.
5. After the data being stream to Event Hubs, it is time to query and aggregate data in Azure Stream Analytics, basically a highly available and scalable complex event processing over streaming data in the cloud
6. Azure Stream Analytics output sink to PowerBI is GA since April, this technology will be use to develop real-time dashboards.
   I’m using Q&A intelligence on my reports/dashboard, data is updated real time.

Hope I ignited some ideas for you 🙂 !

Regards,
Paulo Condeça.