Side Note: Want to learn SQL <\/a>or Python <\/a>for free. In less then 10 minutes a day and less than an hour total? Signup for my free classes delivered daily right to your email inbox for free! <\/em><\/p>\n\n\n\n Now back to the article…<\/p>\n\n\n\n For this series, we will be looking at Azure Event Hubs or\nIoT Hubs. These were designed to capture fast streaming data of millions of\nrows from IoT devices or streaming data like Twitter. But why should this tool\nbe limited to these use cases? Most businesses have no need or requirement for\nthis use case, but we can use this technology to create a live streaming ETL to\nyour data warehouse or your reporting environment with out sacrifice\nperformance or creating a strain on your source systems. This architecture can\nbe used to perform data synchronization between systems and other integrations\nas well, and since we are not using it to its full potential of capturing\nmillions of flowing records, our costs end up being pennies a day!<\/p>\n\n\n\n Others have emulated this sort of process by using triggers on their source table, but this can potentially add an extra step of processing and overhead to your database. By enabling change data capture natively on SQL Server, it can be much lighter than a trigger. You can then take the first steps to creating a streaming ETL for your data. If CDC is not available, simple staging scripts can be written to emulate the same but be sure to keep an eye on performance. Let\u2019s take a look at the first step of setting up native Change Data Capture on your SQL Server tables<\/p>\n\n\n\n sys.sp_cdc_enable_db<\/p>\n\n\n\n EXECUTE sys.sp_cdc_enable_table <\/p>\n\n\n\n @source_schema = N’dbo’ <\/p>\n\n\n\n , @source_name = N’Task’ <\/p>\n\n\n\n , @role_name = N’cdc_Admin’; <\/p>\n\n\n\n GO <\/p>\n\n\n\n This step will enable CDC on the database as well as add it to the table \u201cTask\u201d. SQL Agent must be running as two jobs are created during this process, one to load the table and one to clean it out.<\/p>\n\n\n\n Once the CDC is enabled, SQL Server will automatically create the following tables \u201cschema_tablename_CT\u201d in the System folder section. This table will now automatically track all data changes that occur on that table. You can reference the _$operationcode to determine what change occurred with the legend below. If you wish to capture changes to only certain fields, see the Microsoft documentation on CDC to see how that can be set. If you are hand writing your SQL, this can also be programmed in when building your staging query. <\/p>\n\n\n\n 1 = delete Now once you add, edit or delete a record, you should be able to find it in the new CDC table. Next, we will look at scanning this table and turning the data to JSON to send to an Event Hub!<\/p>\n\n\n\n For more information on SQL CDC please see their documentation here<\/a>.<\/p>\n\n\n <\/p>\n\n\n Be sure to check out my full online class on the topic. A hands on walk through of a Modern Data Architecture using Microsoft Azure. For beginners and experienced business intelligence experts alike, learn the basic of navigating the Azure Portal to building an end to end solution of a modern data warehouse using popular technologies such as SQL Database, Data Lake, Data Factory, Data Bricks, Azure Synapse Data Warehouse and Power BI. Link to the class can be found <\/a>here<\/a>. <\/p>\n\n\n <\/p>\n\n\n Part 1 – Navigating the Azure Portal<\/a><\/p>\n\n\n\n Part 2 – Resource Groups and Subscriptions<\/a><\/p>\n\n\n\n Part 3 – Creating Data Lake Storage<\/a><\/p>\n\n\n\n Part 4 – Setting up an Azure SQL Server<\/a><\/p>\n\n\n\n Part 5 – Loading Data Lake with Azure Data Factory<\/a><\/p>\n\n\n\n Part 6 – Configuring and Setting up Data Bricks<\/a><\/p>\n\n\n\n Part 7 – Staging data into Data Lake<\/a><\/p>\n\n\n\n Part 8 = Provisioning a Synapse SQL Data Warehouse<\/a><\/p>\n\n\n\n![\"\"](\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2023\/02\/image-1.png\")
<\/a><\/figure>\n\n\n\n![\"\"](\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2023\/02\/image-2.png\")
<\/a><\/figure>\n\n\n\nSteps <\/u><\/strong><\/h2>\n\n\n\n
![\"\"](\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2019\/03\/image.png\")
<\/figure>\n\n\n\n![\"\"](\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2019\/03\/image-1.png\")
<\/figure>\n\n\n\n
2 = insert
3 = update (captured column values are those before the update operation). This value applies only when the row filter option ‘all update old’ is specified.
4 = update (captured column values are those after the update operation)<\/p>\n\n\n\n![\"\"](\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2019\/03\/image-2.png\")
<\/figure>\n\n\n\n