The bug specifically affects the SQL Enterprise feature of Change Data Capture (CDC). This week we discovered a new SQL Bug that affects all SQL Versions from SQL Server 2008 to SQL Server 2016 (GA Release). Please read below for methods to identify the issue and a work around. Unfortunately this issue now affects all SQL Server versions and patch levels from SQL Server 2008 to the current GA version. (As at writing CDC is not supported on SQL 2017 Linux) We received confirmation the Microsoft Support Team the bug can be reproduced – however at this point in time they have confirmed that there will be NO FIXīug reproduced in SQL Server 2016 (GA release).īug reproduced in SQL Server 2017 (GA release) (Windows) 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!Īnd so, lets get into some CDC to Event Hub data streaming action! Once landed in the Event Hub an Azure Stream Analytics (ASA) Job distributes the changes into the multiple outputs. 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. See here – Īnd so, the final architecture looks something like this… 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. If you dont know about CDC then see here – 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. Visualise the real-time change telemetry on a Power BI dashboard ( specifically the number of Inserts, Updates, Deletes over time).Store the delta changes as TXT files in Azure Data Lake Store (ADLS).Transmit data securely and real-time into Azure.Captured intermediate DML operations on tables in an on-prem SQL database. ![]() Specifically we needed to create a streaming ETL solution that … 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. 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.Īnd so, lets get into some CDC to Power BI streaming action! If interested in how the SQL CDC and Event Hubs work together, then read here from my previous post – 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. ![]() 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. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |