Tuning Throughput from Azure Event Hub to Azure Stream Analytics

Recently I had a requirement to load streaming JSON data to provide a data feed for near real-time reporting.  The solution streamed data into an “Ingress” Azure Event Hub, shred the JSON via Azure Stream Analytics and then push subsections of data as micro-batches (1 sec) into a “Egress” Azure Event Hub (for loading into a stage table in Azure SQL DW).

In Event Hubs and Stream Analytics there are only a few performance levers to help tune a solution like this, or said another way, doing nothing with these levers can affect your ongoing performance!

So this blog is to show the performance differences when using different Azure Event Hub partition configurations and the Azure Stream Analytics PARTITION BY clause.

Continue reading

Advertisements

Microsoft Ignite US 2017 – Major Azure Announcements

Microsoft Ignite is probably the biggest technical event that Microsoft host yearly with many major announcements across the entire solutions portfolio suiteand this year 2017 was certainly no exception to that!

This year it was held in Orlando, FL over 5 days (25 – 29 Sep) and was attended by more than 30,000 people across the two major events of Ignite and Envision.  The event covers all areas of Microsoft solutions including Azure, Office, Power BI, SQL Server, Windows, Dynamics, etc, etc and is a world of technical goodness!

The announcements across the Azure Cloud space in particular are significant and very exciting, and provide a strong lead as to the direction Microsoft are taking their technologies today – and in the very near future.

I have prepared a summary deck of what I think are the major announcements specifically across the Azure Infrastructure and Data space which are important to be aware of.  There are of course even more announcements that this across other solutions areas I mentioned above that I haven’t covered in this deck.

You can download the Azure Data & Infra announcements deck from [MY PRESENTATIONS] page here on my blog site Ignite US 2017 Announcements – https://mrfoxsql.files.wordpress.com/2017/10/azure_igniteus2017_whatsnew_v0-21.pdf

 

In addition to all the technical goodnessSatya also released a new book called “Hit Refresh” which outlines the inside story of Microsoft’s own digital transformation.

Hit Refresh is about individual change, about the transformation happening inside of Microsoft and the technology that will soon impact all of our lives—the arrival of the most exciting and disruptive wave of technology humankind has experienced: artificial intelligence, mixed reality, and quantum computing.

You can read about it here – and also grab a copy if interested to learn more – https://news.microsoft.com/hitrefresh/

 

Happy reading!

…AND of course, as I always say, please review and validate this yourself as your required outcomes may vary!


Disclaimer: all content on Mr. Fox SQL blog is subject to the disclaimer found here

Query Azure CosmosDB from a SQL Server Linked Server

Recently I had a requirement to combine data that I already had in SQL Server (2016) with JSON document data already stored in Azure CosmosDB.  Both databases were operational and continuously accepting data so I didn’t want to go to the trouble of doing the delta load thing between them, instead I just wanted to be able to query directly on demand.

And so – the purpose of this article is to outline the method to connect direct to Azure CosmosDB from SQL Server using a SQL Linked Server.

Finally … SQL & NoSQL … together at last!

For those interested to learn more about Azure CosmosDB, check out my previous blog post here – https://mrfoxsql.wordpress.com/2016/05/11/azure-documentdb-preparing-loading-querying-data/

Or the official documentation here – https://docs.microsoft.com/en-us/azure/cosmos-db/

And so right up front – this solution only works for SQL Server on VM/IaaS – and is not supported for Azure SQL DB (ASDB) – mainly as ASDB doesn’t support SQL Linked Servers! (Damn, they say!)

Continue reading

Streaming Reporting: SQL Change Data Capture (CDC) to Power BI

Extending on my previous post about redirecting SQL CDC changes to Azure Event Hub, I have had a few people ask for details/options to stream SQL data into the Power BI API.

Specifically – they were looking for an easy method to leverage the ADD ROWS functionality of the Power BI API so they could push real-time data into a Power BI service dataset.

This method provides the ability to update the Power BI Dataset with new rows every few seconds, instead of a Power BI report having to either use Direct Connect or Scheduled data refresh capability which can be very limiting.

If interested in how the SQL CDC and Event Hubs work together, then read here from my previous post – https://mrfoxsql.wordpress.com/2017/07/12/streaming-etl-send-sql-change-data-capture-cdc-to-azure-event-hub/

The purpose of this post is to quickly show how to extend and explore pushing new SQL data rows via Azure Stream Analytics into Power BI.

And so, lets get into some CDC to Power BI streaming action!

Continue reading

Streaming ETL: SQL Change Data Capture (CDC) to Azure Event Hub

I had a recent requirement to capture and stream real-time data changes on several SQL database tables from an on-prem SQL Server to Azure for downstream processing.

Specifically we needed to create a streaming ETL solution that …

  1. Captured intermediate DML operations on tables in an on-prem SQL database
  2. Transmit data securely and real-time into Azure
  3. Store the delta changes as TXT files in Azure Data Lake Store (ADLS)
  4. Visualise the real-time change telemetry on a Power BI dashboard (specifically the number of Inserts, Updates, Deletes over time).

The first part was easy; SQL has a feature called Change Data Capture (CDC) which does an amazing job of tracking DML changes to seperate system tables.  If you dont know about CDC then see here – https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server

The second part wasn’t easy, and after some searching I came across this blog post by Spyros Sakellariadis which gave me inspiration and starter code for my streaming ETL solution.  Excellent post.  See here – https://azure.microsoft.com/en-us/resources/samples/event-hubs-dotnet-import-from-sql/

And so, the final architecture looks something like this…

The solution picks up the SQL data changes from the CDC Change Tracking system tables, creates JSON messages from the change rows, and then posts the message to an Azure Event Hub.  Once landed in the Event Hub an Azure Stream Analytics (ASA) Job distributes the changes into the multiple outputs.

What I found pretty cool was that I could transmit SQL delta changes from source to target in as little as 5 seconds end to end!

And so, lets get into some CDC to Event Hub data streaming action!

Continue reading

Making Phone Calls from Azure Event Hub Messages

Recently I did a presentation at our local SQL Server User Group (SSUG) on Managing Streaming Data Pipelines Using Azure Data Services and as such wanted to build a compelling Azure demo that worked with simple streaming data which under certain event conditions would trigger an outbound phone call.

If interested the presentation deck is here – SSUG Melbourne – Building Streaming Data Pipelines Using Azure Cloud Services

The solution had several key components and stages outlined in the architecture below.

 

  1. A mobile phone app which generates JSON events with the X, Y, Z location of the device and G (g-force) detected in the device during movement.
  2. An Azure IoT Hub (AIH) which accepts the JSON events posted from the mobile device
  3. An Azure Stream Analytics (ASA) job that queries the Event Hub and routes the event data to several outputs, including…
    • Azure Blob Storage (for archive of all event data)
    • Power BI (for a live dashboard of all event data)
    • Azure SQL Database (ASDB) (for tabular storage of all event data)
    • Azure Event Hub + Azure Function (AF) (for queuing events which have a G Force reading greater than 3 and then triggering a phone call back to the original device from which the event originated)

The entire demo solution is actually really interesting (tsk, of course!) – and I will blog about other separate parts of this presentation at some point later.  However the part of the demo that received the most interest was the external phone call integration with Azure Functions.  

To be clear up front – Azure itself does not have native phone capability – so to make outbound phone calls I leverage an external “Twilio” API from within an Azure Function, and “Twilio” connects the outbound call.

And so, lets see the Twilio phone setup and c# Function code in action!

Continue reading

What Exactly is the “Data Platform” Nowadays?

A couple of months ago I was presenting at SQL Saturday Melbourne (582) on Azure Cognitive Services and got chatting with some of the other presenters about our sessions.

I co-presented with Krissy Rumpff from Microsoft Data Platform Team (https://www.linkedin.com/in/krumpff/) – and for those interested our session is here – http://www.sqlsaturday.com/582/Sessions/Details.aspx?sid=56483    …or…    you can look at the recording here – https://channel9.msdn.com/Events/Ignite/Australia-2017/DA321

Anyway, whats interesting is that some of the other presenters were asking why we were presenting on Cognitive Services, when in fact this was SQL Saturday?  And, you know, Cognitive is not the Data Platform?

This is actually an interesting point – and since then I have had a pretty good think about what this means – and so this is the purpose of this blog post!

Continue reading

A Collection of SQL Server Help Scripts

Like probably every SQL DBA, consultant, architect etc etc out there that has ever worked on or used SQL Server they will likely have their own personal collection of SQL Server Help Scripts.

So not unsurprisingly I also have such a collectionand so this is the purpose of this post!

Continue reading

Azure Cognitive Services Text Analytics – An API Calling Application

Just last week we had the fantastic opportunity to present at Microsoft Ignite 2017 in the Gold Coast on Azure Cognitive Services – and we had an absolute blast of a time!

I co-presented with Kristina Rumpff who works at Microsoft in the Data Platform team as a Solution Architect.  I focused on an overview of the suite of Azure Cognitive Services along with a deep dive into the Text Analytics service, and Krissy focused on the LUIS service coupled together with Azure Bots.

 

Fast Start to Azure Text Analytics Cognitive API’s

Leading up to, and since, the session I had a few people ask if there is anything pre-canned application wise which can call the Text Analytics API’s which they can just use.

The answer to that is kind of yes

However apart from that I didn’t find anything else out there which people can quickly leverage to do this for them… so I wrote one!

UPDATE:  I have since found this app handy myself when I need to quickly process a stack of once-off random text that someone sent to me for some other downstream reporting.  (…AND this was a good chance to learn more c# coding!)

And so, lets see the application code in action!

Continue reading

SQL Saturday 582 Melbourne (11 Feb 2017) and Ignite 2017 (14-17 Feb 2017)

For those not aware there are 2 cool local and national events coming to both Melbourne and Gold Coast

  • SQL Saturday 582 (Sat 11 Feb 2017).  Melbourne.  For those looking for some great free local SQL (and related!) learning, you simply cannot go past a SQL Saturday anywhere in the world!  And this one right here in Melbourne will again be no exception.  There is a lineup of fantastic local speakers including Microsoft and MVP’s as well as international speakers too.  http://www.sqlsaturday.com/582/EventHome.aspx
  • Ignite 2017 (14-17 Feb 2017).  Gold Coast.  For those wanting more than a day and across a wider variety of topics, then Ignite will also be another you simply cannot miss.  As above there is a fantastic lineup of both local Microsoft and local MVP/experts as well as an impressive list of international speakers.  This is a 4 day back-to-back set of technical sessions. https://msftignite.com.au/

UPDATE: Ignite 2017 has come and gone – and so here’s some links!

 

I want to focus this short blog post on SQL Saturday 582 as there are some great things happening right here locally in Melbourne… 

SQL Saturday 582 – Melbourne

SQL Saturday is an excellent free learning resource for all things SQL Server – all costs are covered by donations and sponsorships.  Some of the excellent sponsors this year are Microsoft, Wardy IT, Idera and PASS.

Some of the session focus areas include SQL 2016 (many deep dives across almost all facets!), SQL DB/DW in Azure, Azure Cognitive Services and Machine Learning, R, Agile Methods, Power BI, Powershell, BIML …and more!

Furthermore – Some of the top Microsoft product group (PG) speakers will also be attending:

  • Lindsey Allen
  • Sunil Agarwal
  • Matt Masson
  • Julie Koesmarno
  • Ajay Jagannathan

Sunil will also be helping to run a SQL Clinic on the day where you can talk directly to Microsoft about your biggest pain points or suggestions for the next versions of SQL Server.

For those wanting to come along here are the links you need to know.  Please go to the website and register to attend.

The event is being held at Monash University (Caulfield Campus, 888 Dandenong Road, Caulfield East, Victoria)

 

The Rise of the Machines…

For those attending – I am co-presenting a pretty cool session with the awesome Krissy Rumpff (from local Microsoft SQL Team) on Azure Cognitive Services.  

Session Details here – http://www.sqlsaturday.com/582/Sessions/Details.aspx?sid=56483

We’ll talk though what they are, why you should care and how to interact with them (via API’s).  We hope to show some pretty fun demos and draw some relevant use cases – and if time permits will look for some unwilling audience participation!

Feel free to pop in and introduce yourself!

 

I hope to see you all in Melbourne at SQL Saturday!


Disclaimer: all content on Mr. Fox SQL blog is subject to the disclaimer found here