{"id":1971,"date":"2023-05-15T04:30:39","date_gmt":"2023-05-15T11:30:39","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=1971"},"modified":"2023-05-14T21:17:59","modified_gmt":"2023-05-15T04:17:59","slug":"change-data-streams-with-azure-sql-db","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/change-data-streams-with-azure-sql-db\/","title":{"rendered":"Implement Change Data Streams with Azure SQL Database, Change Tracking, and Azure SQL Bindings"},"content":{"rendered":"<p>Building on my first<a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/getting-started-with-azure-sql-bindings-part-1\/\"> Azure SQL Bindings blog post<\/a> and taking inspiration from <a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/author\/drskwier\/\">Drew Skwiers-Koballa&#8217;s<\/a> post on <a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/developing-with-azure-sql-bindings-and-azure-sql-trigger-for-azure-functions\/\">SQL Bindings<\/a>, we are going to create a change data stream with Azure SQL Database. Now, usually, the first part of these blogs is a prerequisite section that goes over what you need to install locally to get started. From talking to many of you in person and getting questions\/emails on this process, this post is going to use GitHub codespaces; nothing to install locally or configure. I&#8217;ve created a project <a href=\"https:\/\/github.com\/JetterMcTedder\/codespace-for-DB-Devs\">here<\/a> that you can fork and create a codespace to get you up and running in minutes.<\/p>\n<h2>Starting the codespace<\/h2>\n<p><div class=\"alert alert-info\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Info\"><\/i><strong>GitHub Account Needed!<\/strong><\/p>You do need a free GitHub account to use codespaces. You can sign up for one <a href=\"https:\/\/github.com\/signup?ref_cta=Sign+up&amp;ref_loc=header+logged+out&amp;ref_page=%2F&amp;source=header-home\">here<\/a>!<\/div><\/p>\n<p>To start a codespace, just fork the project located <a href=\"https:\/\/github.com\/JetterMcTedder\/codespace-for-DB-Devs\">here<\/a>. Then, click the green <strong>Code button<\/strong> on the forked repository. Click the <strong>Codespaces tab<\/strong> followed by clicking the green <strong>Create codespace on main<\/strong> button.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-064043.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2042\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-064043.png\" alt=\"Image Screenshot 2023 05 12 064043\" width=\"409\" height=\"405\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-064043.png 409w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-064043-300x297.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-064043-150x150.png 150w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-064043-24x24.png 24w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-064043-48x48.png 48w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-064043-96x96.png 96w\" sizes=\"(max-width: 409px) 100vw, 409px\" \/><\/a><\/p>\n<p>This will create the codespace using the\u00a0<strong>devcontainer.json<\/strong> file in the <strong>.devcontainer directory<\/strong>. Once the codespace is up and running, it will then run the<strong> install-dev-tools.sh<\/strong> file in the <strong>scripts directory<\/strong> to configure the codespace with all the necessary tools for this blog post and for developing with the Azure SQL Database in general.<\/p>\n<h2>Change data stream components<\/h2>\n<p>For this project, we are going to use the following services\/features locally in the codespace and in Azure.<\/p>\n<ul>\n<li><a href=\"https:\/\/learn.microsoft.com\/azure\/azure-sql\/azure-sql-iaas-vs-paas-what-is-overview?view=azuresql\">SQL Server 2022\/Azure SQL Database<\/a>: This will be created using docker in docker within the codespace.<\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/azure\/azure-functions\/functions-overview\">Azure Functions<\/a>. Azure Functions will act as the bridge between the database and the <a href=\"https:\/\/learn.microsoft.com\/azure\/event-hubs\/event-hubs-about\">Azure Event Hub<\/a>.<\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/azure\/azure-functions\/functions-bindings-azure-sql-trigger\">SQL Trigger Bindings<\/a> will be watching a table in the database for changes (via <a href=\"https:\/\/learn.microsoft.com\/sql\/relational-databases\/track-changes\/about-change-tracking-sql-server\">Change Tracking<\/a>).<\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/azure\/event-hubs\/event-hubs-about\">Azure Event Hub <\/a>\u00a0will recieve the data change onces the trigger files and the function passes it over. There we will capture the change event and place it in <a href=\"https:\/\/learn.microsoft.com\/azure\/storage\/blobs\/storage-blobs-introduction\">Azure Blob Storage.<\/a><\/li>\n<\/ul>\n<h2>Create a table with change tracking<\/h2>\n<p>Once, the codespace has started up, go to the terminal and issue the following commands. We are going to create a directory for our database objects and a SQL Database Project:<\/p>\n<div>\n<pre>mkdir database\r\ncd database\r\ndotnet new sqlproj -n \"devDB\" -tp \"SqlAzureV12\"\r\n\r\n<\/pre>\n<\/div>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-065645.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2047\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-065645.png\" alt=\"Image Screenshot 2023 05 12 065645\" width=\"857\" height=\"145\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-065645.png 857w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-065645-300x51.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-065645-768x130.png 768w\" sizes=\"(max-width: 857px) 100vw, 857px\" \/><\/a><\/p>\n<p><div class=\"alert alert-primary\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Info\"><\/i><strong>sqlproj in a nutshell<\/strong><\/p><\/p>\n<p>Here&#8217;s a breakdown of the command:<\/p>\n<ul>\n<li><strong>dotnet new sqlproj:<\/strong> This is a command from the .NET CLI used to create a new SQL Server Database Project. It generates a project file with the .sqlproj extension, which can be used to manage database schemas, scripts, and related artifacts.<\/li>\n<li><strong> -n &#8220;devDB&#8221;:<\/strong> This is an optional parameter specifying the name of the project or the database. In this case, it&#8217;s set to &#8220;devDB,&#8221; indicating that the project or database will be named &#8220;devDB.&#8221;<\/li>\n<li><strong> -tp &#8220;SqlAzureV12&#8221;:<\/strong> This is another optional parameter that specifies the target platform for the project. In this case, it&#8217;s set to &#8220;SqlAzureV12,&#8221; indicating that the project is targeting SQL Server Azure version 12.<\/li>\n<\/ul>\n<p><\/div><\/p>\n<div>Now, click on the <strong>SQL Database Projects extension<\/strong> in codespaces<\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-065855-1.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2050\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-065855-1.png\" alt=\"Image Screenshot 2023 05 12 065855\" width=\"55\" height=\"178\" \/><\/a><\/div>\n<div><\/div>\n<div>and click the <strong>Open existing<\/strong> green button. The project file is located at <strong>\/workspaces\/codespace-for-DB-Devs\/database\/devDB\/<\/strong> and named<strong> devDB.sqlproj.<\/strong><\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-070223.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2051\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-070223.png\" alt=\"Image Screenshot 2023 05 12 070223\" width=\"1287\" height=\"186\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-070223.png 1287w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-070223-300x43.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-070223-1024x148.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-070223-768x111.png 768w\" sizes=\"(max-width: 1287px) 100vw, 1287px\" \/><\/a><\/div>\n<div><\/div>\n<div>Use the next command in the terminal to create a SQL Server 2022 database right in your codespace. Yes, you heard that right&#8230;we can create a full SQL Server 2022 database in a codespace!<\/div>\n<div><\/div>\n<div><span style=\"color: #292b2c;font-family: Menlo, Monaco, Consolas, 'Liberation Mono', 'Courier New', monospace;font-size: 14.4px;text-align: var(--bs-body-text-align)\">sqlcmd create mssql -u devDB &#8211;accept-eula<\/span><\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-085551.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2053\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-085551.png\" alt=\"Image Screenshot 2023 05 12 085551\" width=\"763\" height=\"319\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-085551.png 763w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-085551-300x125.png 300w\" sizes=\"(max-width: 763px) 100vw, 763px\" \/><\/a><\/div>\n<div><div class=\"alert alert-success\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Lightbulb\"><\/i><strong>go-sqlcmd<\/strong><\/p>Learn all about the new sqlcmd <a href=\"https:\/\/techcommunity.microsoft.com\/t5\/sql-server-blog\/go-sqlcmd-v1-0-create-connect-to-and-query-sql-server\/ba-p\/3813982\">here<\/a>!<\/div><\/div>\n<div>In a few short minutes, the database will be created within docker and ready to be used. Before we move on from the terminal, run the following command to get the <strong>username<\/strong> and <strong>password<\/strong> of the database:<\/div>\n<div><\/div>\n<pre>sqlcmd config connection-strings<\/pre>\n<div>This command will provide the connect strings for various languages: ADO.NET, JDBC, GO, ODBC, and SQLCMD.<\/div>\n<div><\/div>\n<h3>Create a table in SQL Database Projects<\/h3>\n<div>Back in the <strong>SQL Database Projects extension<\/strong>, right click the project and select <strong>Add Table<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090245.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2054\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090245.png\" alt=\"Image Screenshot 2023 05 12 090245\" width=\"400\" height=\"257\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090245.png 400w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090245-300x193.png 300w\" sizes=\"(max-width: 400px) 100vw, 400px\" \/><\/a><\/div>\n<div><\/div>\n<div>In the Add Table dialog window, name the table <strong>customer<\/strong> and press enter.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090521.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2055\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090521.png\" alt=\"Image Screenshot 2023 05 12 090521\" width=\"638\" height=\"96\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090521.png 638w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090521-300x45.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/a><\/div>\n<div><\/div>\n<div>It creates a skeleton customer table script for you.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090534.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2056\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090534.png\" alt=\"Image Screenshot 2023 05 12 090534\" width=\"346\" height=\"162\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090534.png 346w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090534-300x140.png 300w\" sizes=\"(max-width: 346px) 100vw, 346px\" \/><\/a><\/div>\n<div><\/div>\n<div>Replace that code with the following:<\/div>\n<div><\/div>\n<div>\n<div>\n<pre>CREATE TABLE [dbo].[customer] (\r\n  \u00a0 [customer_id] \u00a0 \u00a0 \u00a0 \u00a0INT \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED ([customer_id] ASC),\r\n  \u00a0 [customer_name] \u00a0 \u00a0 \u00a0NVARCHAR (200) NOT NULL,\r\n  \u00a0 [customer_email] \u00a0 \u00a0 NVARCHAR (200) NOT NULL,\r\n  \u00a0 [customer_address]   NVARCHAR (500) NOT NULL\r\n);<\/pre>\n<\/div>\n<\/div>\n<div>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090711.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2058\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090711.png\" alt=\"Image Screenshot 2023 05 12 090711\" width=\"907\" height=\"188\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090711.png 907w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090711-300x62.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090711-768x159.png 768w\" sizes=\"(max-width: 907px) 100vw, 907px\" \/><\/a><\/p>\n<p>and <strong>SAVE<\/strong> the file.<\/p>\n<h3>Publish to the local database<\/h3>\n<p>SQL Database Projects provides the ability to publish the database code added to the project to a database. Using this ability, the code can be deployed into the local SQL Server 2022 image that was created in this codespace with sqlcmd. Start by right clicking the project and select <strong>Publish<\/strong>.<\/p>\n<\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090955.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2059\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090955.png\" alt=\"Image Screenshot 2023 05 12 090955\" width=\"441\" height=\"162\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090955.png 441w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-090955-300x110.png 300w\" sizes=\"(max-width: 441px) 100vw, 441px\" \/><\/a><\/div>\n<div><\/div>\n<div>After selecting Publish, the Publish dialog box will appear. For the first step, s<span style=\"font-size: 1rem;text-align: var(--bs-body-text-align)\">elect &#8220;<strong>Publish to an existing Azure SQL logical server<\/strong>&#8220;<\/span><\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091221.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2061\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091221.png\" alt=\"Image Screenshot 2023 05 12 091221\" width=\"617\" height=\"128\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091221.png 617w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091221-300x62.png 300w\" sizes=\"(max-width: 617px) 100vw, 617px\" \/><\/a><\/div>\n<div><\/div>\n<div>\n<div>Next, select &#8220;<strong>Don&#8217;t use profile<\/strong>&#8220;<\/div>\n<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091356.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2062\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091356.png\" alt=\"Image Screenshot 2023 05 12 091356\" width=\"611\" height=\"124\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091356.png 611w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091356-300x61.png 300w\" sizes=\"(max-width: 611px) 100vw, 611px\" \/><\/a><\/div>\n<div><\/div>\n<div>The next series of steps will create a connection profile to the locally running database. In the dialog, select &#8220;<strong>Create Connection Profile<\/strong>&#8220;<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091407.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2064\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091407.png\" alt=\"Image Screenshot 2023 05 12 091407\" width=\"613\" height=\"82\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091407.png 613w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-091407-300x40.png 300w\" sizes=\"(max-width: 613px) 100vw, 613px\" \/><\/a><\/div>\n<div><\/div>\n<div>Now, Use &#8220;<strong>localhost<\/strong>&#8221; as the server name, then press <strong>Enter<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092322.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2065\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092322.png\" alt=\"Image Screenshot 2023 05 12 092322\" width=\"610\" height=\"81\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092322.png 610w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092322-300x40.png 300w\" sizes=\"(max-width: 610px) 100vw, 610px\" \/><\/a><\/div>\n<div><\/div>\n<div>Use &#8220;<strong>devDB<\/strong>&#8221; as the database name, then press <strong>Enter<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092422.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2066\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092422.png\" alt=\"Image Screenshot 2023 05 12 092422\" width=\"614\" height=\"81\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092422.png 614w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092422-300x40.png 300w\" sizes=\"(max-width: 614px) 100vw, 614px\" \/><\/a><\/div>\n<div><\/div>\n<div>In the <strong>Authentication Type<\/strong> dialog box, select &#8220;<strong>SQL Login<\/strong>&#8220;.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092546.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2067\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092546.png\" alt=\"Image Screenshot 2023 05 12 092546\" width=\"610\" height=\"123\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092546.png 610w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-092546-300x60.png 300w\" sizes=\"(max-width: 610px) 100vw, 610px\" \/><\/a><\/div>\n<div><\/div>\n<div>Here is where it is best to refer back to the command &#8220;<span style=\"font-family: terminal, monaco, monospace\">sqlcmd config connection-strings<\/span>&#8221; to find the username and password. Run it again at the terminal to get the values if needed.<\/div>\n<div>In the <strong>User name (SQL Login)<\/strong> dialog box, enter the user from the connect strings. It should be <strong>vscode<\/strong>, then press <strong>Enter<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093030.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2068\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093030.png\" alt=\"Image Screenshot 2023 05 12 093030\" width=\"608\" height=\"79\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093030.png 608w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093030-300x39.png 300w\" sizes=\"(max-width: 608px) 100vw, 608px\" \/><\/a><\/div>\n<div><\/div>\n<div>and provide the password from the connect strings in the <strong>Password (SQL Login)<\/strong> dialog box, then press <strong>Enter<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093207.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2069\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093207.png\" alt=\"Image Screenshot 2023 05 12 093207\" width=\"609\" height=\"80\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093207.png 609w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093207-300x39.png 300w\" sizes=\"(max-width: 609px) 100vw, 609px\" \/><\/a><\/div>\n<div><\/div>\n<div>Select &#8220;Yes&#8221; so that the password is saved (encrypted) on the connection profile<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093256.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2070\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093256.png\" alt=\"Image Screenshot 2023 05 12 093256\" width=\"607\" height=\"105\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093256.png 607w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093256-300x52.png 300w\" sizes=\"(max-width: 607px) 100vw, 607px\" \/><\/a><\/div>\n<div><\/div>\n<div>Provide a profile name in the last dialog box but this step is optional. Press <strong>Enter<\/strong> to finish the connection profile process.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093516.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2071\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093516.png\" alt=\"Image Screenshot 2023 05 12 093516\" width=\"609\" height=\"76\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093516.png 609w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093516-300x37.png 300w\" sizes=\"(max-width: 609px) 100vw, 609px\" \/><\/a><\/div>\n<div><\/div>\n<div>After pressing Enter and the connection profile is verified, a warning box will appear on the lower right of the screen. This warning is indicating that due to new security features within the database, you need to enable the self-signed certificate. Click the <strong>Enable Trust Server Certificate green button<\/strong> to continue.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093551.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2072\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093551.png\" alt=\"Image Screenshot 2023 05 12 093551\" width=\"461\" height=\"227\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093551.png 461w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-093551-300x148.png 300w\" sizes=\"(max-width: 461px) 100vw, 461px\" \/><\/a><\/div>\n<div><\/div>\n<div>You may be asked to choose a database. If so, choose <strong>devDB<\/strong><\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094216.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2074\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094216.png\" alt=\"Image Screenshot 2023 05 12 094216\" width=\"608\" height=\"129\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094216.png 608w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094216-300x64.png 300w\" sizes=\"(max-width: 608px) 100vw, 608px\" \/><\/a><\/div>\n<div><\/div>\n<div>For the Action step, there are two options. Choose the <strong>Generate Script<\/strong> option to create publish scripts for the project. This is very useful for adding to CICD flows. Choose <strong>Publish<\/strong> to immedietly deploy the objects to the database chosen in the connection profile. For this project, choose <strong>Publish<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094243.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2075\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094243.png\" alt=\"Image Screenshot 2023 05 12 094243\" width=\"604\" height=\"121\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094243.png 604w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094243-300x60.png 300w\" sizes=\"(max-width: 604px) 100vw, 604px\" \/><\/a><\/div>\n<div><\/div>\n<div><\/div>\n<h3>Viewing the published table<\/h3>\n<div>Upon choosing Publish, the project will build and then deploy to the database. Once finished<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094545.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2076\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094545.png\" alt=\"Image Screenshot 2023 05 12 094545\" width=\"453\" height=\"46\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094545.png 453w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094545-300x30.png 300w\" sizes=\"(max-width: 453px) 100vw, 453px\" \/><\/a><\/div>\n<div><\/div>\n<div>click the SQL Server extension<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094604.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2077\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094604.png\" alt=\"Image Screenshot 2023 05 12 094604\" width=\"52\" height=\"205\" \/><\/a><\/div>\n<div><\/div>\n<div>to view the connection profile we just created<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094756.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2078\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094756.png\" alt=\"Image Screenshot 2023 05 12 094756\" width=\"295\" height=\"277\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094756.png 295w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094756-24x24.png 24w\" sizes=\"(max-width: 295px) 100vw, 295px\" \/><\/a><\/div>\n<div><\/div>\n<div>Expand the Table folder to see the customer table and it&#8217;s columns<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094814.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2079\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-094814.png\" alt=\"Image Screenshot 2023 05 12 094814\" width=\"300\" height=\"263\" \/><\/a><\/div>\n<div><\/div>\n<h3>Enable change tracking<\/h3>\n<div>In the SQL Server extension, right click on the connection profile and select <strong>New Query<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-095051.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2082\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-095051.png\" alt=\"Image Screenshot 2023 05 12 095051\" width=\"508\" height=\"262\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-095051.png 508w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-095051-300x155.png 300w\" sizes=\"(max-width: 508px) 100vw, 508px\" \/><\/a><\/div>\n<div><\/div>\n<div>This will open up a new query sheet where SQL commands can be run. The table needs change tracking enabled for the SQL bindings trigger to work correctly.<\/div>\n<div><\/div>\n<div>The first command to be run will <strong>enable change tracking<\/strong> in the database.<\/div>\n<div><\/div>\n<div>\n<div>\n<pre>ALTER DATABASE [devDB]\r\nSET CHANGE_TRACKING = ON;\r\nGO<\/pre>\n<\/div>\n<\/div>\n<div>then enable change tracking on the customer table:<\/div>\n<div><\/div>\n<div>\n<div>\n<pre>ALTER TABLE [dbo].[customer] ENABLE CHANGE_TRACKING;\r\nGO<\/pre>\n<\/div>\n<\/div>\n<div>In this section, you created a SQL Database project, created a database right in your codespace with a single command, and published objects from the SQL Database project directly into the locally running database. The next section will create an Azure Function in which to house the SQL Bindings Trigger to watch for data changes.<\/div>\n<div><\/div>\n<h2>Create an Azure Function<\/h2>\n<div>Back in the terminal at the bottom of the page,<\/div>\n<div>\n<p><div class=\"alert alert-info\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Info\"><\/i><strong>Back to the terminal<\/strong><\/p>You may have to click the terminal tab on the window bar on the bottom of the page. When you deployed the SQL Database Project, it may have moved from the terminal window to the Output window.<\/p>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100117.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2087\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100117.png\" alt=\"Image Screenshot 2023 05 12 100117\" width=\"558\" height=\"43\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100117.png 558w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100117-300x23.png 300w\" sizes=\"(max-width: 558px) 100vw, 558px\" \/><\/a><\/div>\n<p><\/div><\/p>\n<\/div>\n<div><\/div>\n<div>issue the following command to change the directory back to the top level of this project:<\/div>\n<div><\/div>\n<pre>cd \/workspaces\/codespace-for-DB-Devs<\/pre>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100517.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2089\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100517.png\" alt=\"Image Screenshot 2023 05 12 100517\" width=\"772\" height=\"71\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100517.png 772w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100517-300x28.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100517-768x71.png 768w\" sizes=\"(max-width: 772px) 100vw, 772px\" \/><\/a><\/div>\n<div><\/div>\n<div>then, issue the following command to start the function creation process:<\/div>\n<div><\/div>\n<pre>func init<\/pre>\n<div>Upon issuing that command, you will be presented with a choice of frameworks for this function to use.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100727.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2090\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100727.png\" alt=\"Image Screenshot 2023 05 12 100727\" width=\"519\" height=\"136\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100727.png 519w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100727-300x79.png 300w\" sizes=\"(max-width: 519px) 100vw, 519px\" \/><\/a><\/div>\n<div><\/div>\n<div>The framework\/runtime for this project is <strong>dotnet<\/strong> so <strong>enter 1<\/strong>, then press <strong>enter<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100604.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2091\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100604.png\" alt=\"Image Screenshot 2023 05 12 100604\" width=\"540\" height=\"163\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100604.png 540w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100604-300x91.png 300w\" sizes=\"(max-width: 540px) 100vw, 540px\" \/><\/a><\/div>\n<div>The next option is to choose a <strong>language<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100959.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2092\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-100959.png\" alt=\"Image Screenshot 2023 05 12 100959\" width=\"236\" height=\"68\" \/><\/a><\/div>\n<div><\/div>\n<div><strong> C#<\/strong> is used for this project so again, <strong>enter 1<\/strong>, then press <strong>enter<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101054.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2093\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101054.png\" alt=\"Image Screenshot 2023 05 12 101054\" width=\"226\" height=\"74\" \/><\/a><\/div>\n<div><\/div>\n<div>When this process is finished, click the File Explorer extension to see the new files that were created for you.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101239.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2094\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101239.png\" alt=\"Image Screenshot 2023 05 12 101239\" width=\"368\" height=\"322\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101239.png 368w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101239-300x263.png 300w\" sizes=\"(max-width: 368px) 100vw, 368px\" \/><\/a><\/div>\n<div><\/div>\n<h3>Adding necessary libraries to the project<\/h3>\n<div>Next, we need to add some<strong> package references<\/strong> to the project (for SQL Bindings and Azure Event Hub). The following commands will add these references to the <strong>codespace-for-DB-Devs.csproj<\/strong> file.<\/div>\n<div>Run the following commands in the terminal:<\/div>\n<div><\/div>\n<div>\n<pre><code class=\"language-dotnetcli\">dotnet add package Microsoft.Azure.WebJobs.Extensions.Sql --prerelease<\/code><\/pre>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101626.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2095\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101626.png\" alt=\"Image Screenshot 2023 05 12 101626\" width=\"946\" height=\"73\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101626.png 946w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101626-300x23.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101626-768x59.png 768w\" sizes=\"(max-width: 946px) 100vw, 946px\" \/><\/a><\/p>\n<pre class=\" prettyprinted\"><span class=\"pln\">dotnet <\/span><span class=\"kwd\">add<\/span> <span class=\"kwd\">package <\/span>Azure.Messaging.EventHubs<\/pre>\n<pre><code class=\"language-dotnetcli\">dotnet add package Microsoft.Azure.WebJobs.Extensions.EventHubs<\/code><\/pre>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101700-1.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2098\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101700-1.png\" alt=\"Image Screenshot 2023 05 12 101700\" width=\"904\" height=\"61\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101700-1.png 904w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101700-1-300x20.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-101700-1-768x52.png 768w\" sizes=\"(max-width: 904px) 100vw, 904px\" \/><\/a><\/p>\n<h3>Create the customer class object<\/h3>\n<p>We are going to create a customer class object file. To create a new file in codespaces, <strong>right click below the files in the file explorer extension<\/strong> and select <strong>New File<\/strong>.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-102750.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2101\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-102750.png\" alt=\"Image Screenshot 2023 05 12 102750\" width=\"356\" height=\"458\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-102750.png 356w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-102750-233x300.png 233w\" sizes=\"(max-width: 356px) 100vw, 356px\" \/><\/a><\/p>\n<p>Name this file <strong>Customer.cs<\/strong> and press enter.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-102912.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2102\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-102912.png\" alt=\"Image Screenshot 2023 05 12 102912\" width=\"302\" height=\"345\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-102912.png 302w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-102912-263x300.png 263w\" sizes=\"(max-width: 302px) 100vw, 302px\" \/><\/a><\/p>\n<p>If the new file has not opened up for you in codespaces, select this file by right clicking on it. <strong>Copy and paste<\/strong> the following code into the <strong>Customer.cs<\/strong> file to create the customer class object.<\/p>\n<div>\n<div>\n<pre>namespace Company.Function;\r\npublic class Customer\r\n{\r\n  \u00a0 public int customer_id { get; set; }\r\n  \u00a0 public string customer_name { get; set; }\r\n  \u00a0 public string customer_email { get; set; }\r\n  \u00a0 public string customer_address { get; set; }\r\n}<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-130007.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2137\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-130007.png\" alt=\"Image Screenshot 2023 05 12 130007\" width=\"436\" height=\"259\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-130007.png 436w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-130007-300x178.png 300w\" sizes=\"(max-width: 436px) 100vw, 436px\" \/><\/a><\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-103252.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2104\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-103252.png\" alt=\"Image Screenshot 2023 05 12 103252\" width=\"480\" height=\"297\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-103252.png 480w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-103252-300x186.png 300w\" sizes=\"(max-width: 480px) 100vw, 480px\" \/><\/a><\/div>\n<div><\/div>\n<div><span style=\"font-size: 1rem;text-align: var(--bs-body-text-align)\">and <strong>SAVE<\/strong> the file.<\/span><\/div>\n<div><\/div>\n<h3>Create the SQL trigger function<\/h3>\n<div>The next step is to create an Azure Function. Start by pressing F1 or Shift-Ctrl-P to bring up the command <span class=\"ui-provider a b c d e f g h i j k l m n o p q r s t u v w x y z ab ac ae af ag ah ai aj ak\" dir=\"ltr\">palette<\/span>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105414.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2107\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105414.png\" alt=\"Image Screenshot 2023 05 12 105414\" width=\"610\" height=\"551\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105414.png 610w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105414-300x271.png 300w\" sizes=\"(max-width: 610px) 100vw, 610px\" \/><\/a><\/div>\n<div><\/div>\n<div>Enter &#8220;<strong>create function<\/strong>&#8221; into the text field and then select<strong> Azure Functions: Create Function<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105520.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2108\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105520.png\" alt=\"Image Screenshot 2023 05 12 105520\" width=\"607\" height=\"143\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105520.png 607w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105520-300x71.png 300w\" sizes=\"(max-width: 607px) 100vw, 607px\" \/><\/a><\/div>\n<div><\/div>\n<div>A dialog box will appear in the center of the screen asking to &#8220;Initialize project for use with VS Code?&#8221;. Click <strong>Yes<\/strong> in the dialog box.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105712.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2109\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105712.png\" alt=\"Image Screenshot 2023 05 12 105712\" width=\"526\" height=\"169\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105712.png 526w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-105712-300x96.png 300w\" sizes=\"(max-width: 526px) 100vw, 526px\" \/><\/a><\/div>\n<div><\/div>\n<div>In the following dialog box, Select &#8220;<strong>HTTP Trigger<\/strong>&#8221; as the<strong> function template.<\/strong><\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110427.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2110\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110427.png\" alt=\"Image Screenshot 2023 05 12 110427\" width=\"617\" height=\"363\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110427.png 617w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110427-300x176.png 300w\" sizes=\"(max-width: 617px) 100vw, 617px\" \/><\/a><\/div>\n<div><\/div>\n<div>Now, on step 2, name the Function <strong>changeDataStreams<\/strong> in the next dialog box, and then press <strong>Enter<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110556.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2111\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110556.png\" alt=\"Image Screenshot 2023 05 12 110556\" width=\"606\" height=\"100\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110556.png 606w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110556-300x50.png 300w\" sizes=\"(max-width: 606px) 100vw, 606px\" \/><\/a><\/div>\n<div><\/div>\n<div>Step 3 is for the <strong>function namespace<\/strong>. Accept the default namespace of <strong>Company.Function<\/strong>, and then press <strong>Enter<\/strong>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110711.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2112\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110711.png\" alt=\"Image Screenshot 2023 05 12 110711\" width=\"604\" height=\"104\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110711.png 604w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110711-300x52.png 300w\" sizes=\"(max-width: 604px) 100vw, 604px\" \/><\/a><\/div>\n<div><\/div>\n<div><span class=\"ui-provider a b c d e f g h i j k l m n o p q r s t u v w x y z ab ac ae af ag ah ai aj ak\" dir=\"ltr\">In step four, select &#8220;<strong>Anonymous<\/strong>&#8221; for <strong>AccessRights<\/strong>.<\/span><\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110726.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2113\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110726.png\" alt=\"Image Screenshot 2023 05 12 110726\" width=\"606\" height=\"144\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110726.png 606w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110726-300x71.png 300w\" sizes=\"(max-width: 606px) 100vw, 606px\" \/><\/a><\/div>\n<div><\/div>\n<div>Looking at the file explorer, there will be a new file called <strong>changeDataStream.cs<\/strong> which should also automatically open up in codespaces for you.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110941.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2114\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-110941.png\" alt=\"Image Screenshot 2023 05 12 110941\" width=\"301\" height=\"223\" \/><\/a><\/div>\n<div><\/div>\n<h3>Adding the SQL Bindings code<\/h3>\n<div>If the file is not already open, open the file by right clicking on it. Replace the code in the file with the following:<\/div>\n<div><\/div>\n<div>\n<div>\n<pre>using System.Collections.Generic;\r\nusing Microsoft.Azure.WebJobs;\r\nusing Microsoft.Extensions.Logging;\r\nusing Microsoft.Azure.WebJobs.Extensions.Sql;\r\nusing System.Threading.Tasks;\r\nusing System.Text.Json;\r\nnamespace Company.Function;\r\npublic static class streamCustomers\r\n{\r\n  \u00a0 [FunctionName(\"changeDataStream\")]\r\n  \u00a0 public static async Task RunAsync(\r\n  \u00a0 \u00a0 \u00a0 [SqlTrigger(\"[dbo].[customer]\", \"SqlConnectionString\")]\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IReadOnlyList&lt;SqlChange&lt;Customer&gt;&gt; changes,\r\n  \u00a0 \u00a0 \u00a0 ILogger logger)\r\n \u00a0 {\r\n\u00a0 \u00a0 \u00a0 foreach (SqlChange&lt;Customer&gt; change in changes)\r\n\u00a0 \u00a0 \u00a0 {\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 var customer = JsonSerializer.Serialize(change.Item);\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 var message = $\"{change.Operation} {customer}\";\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 logger.LogInformation(message);\r\n  \u00a0 \u00a0 \u00a0 }\r\n  \u00a0 }\r\n}<\/pre>\n<\/div>\n<\/div>\n<div>and <strong>SAVE<\/strong> the file. This code uses the SQL trigger binding to watch the customer table for changes. When it sees a change, it will fire and so something for each change. Here, in this code, we are just logging the data change to the terminal.<\/div>\n<div><\/div>\n<div>Now that the function code is done, we need to provide it a value for <strong>SqlConnectionString<\/strong>. This variable can be places in the <strong>local.settings.json<\/strong> file and contain the connect string for our locally running database.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112130.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2116\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112130.png\" alt=\"Image Screenshot 2023 05 12 112130\" width=\"305\" height=\"180\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112130.png 305w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112130-300x177.png 300w\" sizes=\"(max-width: 305px) 100vw, 305px\" \/><\/a><\/div>\n<div><\/div>\n<div>Open the local.settings.json file and add the following line just below the <strong><span style=\"font-size: 1rem;text-align: var(--bs-body-text-align)\">&#8220;Values&#8221;: { <\/span><\/strong><span style=\"font-size: 1rem;text-align: var(--bs-body-text-align)\">section:<\/span><\/div>\n<div><\/div>\n<pre>\"SqlConnectionString\": \"Server=localhost,1433;Database=devDB;User ID=vscode;Password=XXXXX;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;\",<\/pre>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112130-Copy.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2117\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112130-Copy.png\" alt=\"Image Screenshot 2023 05 12 112130 8211 Copy\" width=\"1318\" height=\"118\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112130-Copy.png 1318w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112130-Copy-300x27.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112130-Copy-1024x92.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112130-Copy-768x69.png 768w\" sizes=\"(max-width: 1318px) 100vw, 1318px\" \/><\/a><\/p>\n<p>and be sure to replace the <strong>XXXXX<\/strong> with the password of the database that was created before. If you forgot the password, run the following in the terminal to find it again:<\/p>\n<div>\n<pre>sqlcmd config connection-strings<\/pre>\n<p><span style=\"color: #52595e;font-family: Arimo, 'Helvetica Neue', Arial, sans-serif;font-size: 1rem;text-align: var(--bs-body-text-align)\">And remember to <strong>SAVE <\/strong>the file when done.<\/span><\/p>\n<h3>Test the trigger<\/h3>\n<p>Time to test the trigger and see if it catches changes to the customer table in the database. At the terminal run the following command to start the Azure Function:<\/p>\n<pre>func host start<\/pre>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112618.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2119\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112618.png\" alt=\"Image Screenshot 2023 05 12 112618\" width=\"584\" height=\"60\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112618.png 584w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112618-300x31.png 300w\" sizes=\"(max-width: 584px) 100vw, 584px\" \/><\/a><\/p>\n<p>and once the function is started, open a new query sheet for the devDB database<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-095051.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2082\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-095051.png\" alt=\"Image Screenshot 2023 05 12 095051\" width=\"508\" height=\"262\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-095051.png 508w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-095051-300x155.png 300w\" sizes=\"(max-width: 508px) 100vw, 508px\" \/><\/a><\/p>\n<p>and issue the following SQL insert statement:<\/p>\n<div>\n<pre>insert into dbo.customer values(N'Bill', N'bill@computer.commm', N'Anytown, Anycity 12345');<\/pre>\n<\/div>\n<div>You should see the following in the terminal window indicating the trigger binding did see the change:<\/div>\n<div><\/div>\n<pre>[2023-05-11T19:18:03.124Z] Executing 'changeDataStream' (Reason='New change detected on table '[dbo].[customer]' at 2023-05-11T19:18:03.0902305Z.', Id=541ff09e-54ac-48e8-8d17-bbcc9a451432)\r\n[2023-05-11T19:18:03.176Z] <strong>Insert{\"customer_id\":1,\"customer_name\":\"Bill\",\"customer_email\":\"bill@computer.commm\",\"customer_address\":\"Anytown, Anycity 12345\"}<\/strong>\r\n[2023-05-11T19:18:03.195Z] Executed 'changeDataStream' (Succeeded, Id=541ff09e-54ac-48e8-8d17-bbcc9a451432, Duration=93ms)\r\n\r\n<a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112957.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2120\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112957.png\" alt=\"Image Screenshot 2023 05 12 112957\" width=\"1330\" height=\"235\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112957.png 1330w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112957-300x53.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112957-1024x181.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112957-768x136.png 768w\" sizes=\"(max-width: 1330px) 100vw, 1330px\" \/><\/a><\/pre>\n<h2 class=\" prettyprinted\">Passing change data to the Azure Event Hub<\/h2>\n<\/div>\n<div><div class=\"alert alert-warning\">This next section uses an Azure Event Hub for passing the change data events from the database to the hub for processing\/routing. You can use this <a href=\"https:\/\/learn.microsoft.com\/azure\/event-hubs\/event-hubs-create\">quickstart<\/a> to create an Azure Event Hub in the Azure Portal and only need to create one in the Basic Tier; the lowest cost tier.<\/div><\/div>\n<div><\/div>\n<div>In the terminal window, press <strong>Ctrl-C to stop the function<\/strong> and open the changeDataStream.cs file if not already open in codespaces.<\/div>\n<div><\/div>\n<div>Replace the code in that file with the following new code:<\/div>\n<div><\/div>\n<div>\n<div>\n<pre>using System.Collections.Generic;\r\nusing Microsoft.Azure.WebJobs;\r\nusing Microsoft.Extensions.Logging;\r\nusing Microsoft.Azure.WebJobs.Extensions.Sql;\r\nusing System.Threading.Tasks;\r\nusing Azure.Messaging.EventHubs;\r\nusing System.Text.Json;\r\nnamespace Company.Function;\r\npublic static class streamCustomers\r\n{\r\n  \u00a0 [FunctionName(\"changeDataStream\")]\r\n  \u00a0 public static async Task Run(\r\n  \u00a0 \u00a0 \u00a0 [SqlTrigger(\"[dbo].[customer]\", \"SqlConnectionString\")]\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IReadOnlyList&lt;SqlChange&lt;Customer&gt;&gt; changes,\r\n  \u00a0 \u00a0 \u00a0 [EventHub(\"HUB_NAME\", Connection = \"EventHubConnectionAppSetting\")]\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IAsyncCollector&lt;EventData&gt; outputEvents,\r\n  \u00a0 \u00a0 \u00a0 ILogger logger)\r\n  \u00a0 {\r\n  \u00a0 \u00a0 \u00a0 foreach (SqlChange&lt;Customer&gt; change in changes)\r\n  \u00a0 \u00a0 \u00a0 {\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 var customer = JsonSerializer.Serialize(change.Item);\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 var message = $\"{change.Operation} {customer}\";\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 logger.LogInformation(message);\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 await outputEvents.AddAsync(new EventData(message));\r\n  \u00a0 \u00a0 \u00a0 }\r\n  \u00a0 }\r\n}<\/pre>\n<\/div>\n<\/div>\n<div>Replace <strong>HUB_NAME<\/strong> with the name of the hub that was created when the Azure Event Hub was set up.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114000.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2124\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114000.png\" alt=\"Image Screenshot 2023 05 12 114000\" width=\"878\" height=\"189\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114000.png 878w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114000-300x65.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114000-768x165.png 768w\" sizes=\"(max-width: 878px) 100vw, 878px\" \/><\/a><\/div>\n<div><\/div>\n<div>The difference between this new code and the previous version is that once we get into the section <span style=\"font-family: terminal, monaco, monospace\"><strong>foreach (SqlChange&lt;Customer&gt; change in changes)<\/strong><\/span>, not only are we still logging the result to the terminal, but we are also sending this change event over to an Azure Event Hub using <a href=\"https:\/\/learn.microsoft.com\/azure\/azure-functions\/functions-bindings-event-hubs?pivots=programming-language-csharp&amp;tabs=in-process%2Cextensionv5\">Event Hub bindings for Azure Functions<\/a>.<\/div>\n<div><\/div>\n<div>\n<div><span style=\"font-size: 1rem;text-align: var(--bs-body-text-align)\"><span style=\"font-size: 1rem;text-align: var(--bs-body-text-align)\">Next, open the local.settings.json file again and add the following entries under the <\/span><\/span><span style=\"font-size: 1rem;text-align: var(--bs-body-text-align)\"><strong>SqlConnectionString<\/strong> entry:<\/span><\/div>\n<\/div>\n<div><\/div>\n<div>\n<pre>\"EventHubConnectionAppSetting\": \"XXXXX\",\r\n\r\n<a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-113721.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2123\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-113721.png\" alt=\"Image Screenshot 2023 05 12 113721\" width=\"745\" height=\"191\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-113721.png 745w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-113721-300x77.png 300w\" sizes=\"(max-width: 745px) 100vw, 745px\" \/><\/a><\/pre>\n<\/div>\n<div>Where <strong>XXXXX<\/strong> is the <strong>Event Hub Connection String<\/strong> that can be found in the <a href=\"https:\/\/learn.microsoft.com\/azure\/event-hubs\/event-hubs-get-connection-string#azure-portal\">Azure Portal<\/a>, <a href=\"https:\/\/learn.microsoft.com\/azure\/event-hubs\/event-hubs-get-connection-string#azure-powershell\">Azure Powershell<\/a> or using <a href=\"https:\/\/learn.microsoft.com\/azure\/event-hubs\/event-hubs-get-connection-string#azure-cli\">Azure CLI<\/a>.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114054.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2125\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114054.png\" alt=\"Image Screenshot 2023 05 12 114054\" width=\"584\" height=\"558\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114054.png 584w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114054-300x287.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114054-24x24.png 24w\" sizes=\"(max-width: 584px) 100vw, 584px\" \/><\/a><\/div>\n<div><\/div>\n<div>Not sure on where to find the connection string? You can read how to find it <a href=\"https:\/\/learn.microsoft.com\/azure\/event-hubs\/event-hubs-get-connection-string\">here<\/a>.<\/div>\n<div><\/div>\n<div>Once the value have been replaced in the local.settings.json file, <strong>SAVE<\/strong> the file and restart the function:<\/div>\n<div><\/div>\n<pre>func host start\r\n\r\n<a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112618.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2119\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112618.png\" alt=\"Image Screenshot 2023 05 12 112618\" width=\"584\" height=\"60\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112618.png 584w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-112618-300x31.png 300w\" sizes=\"(max-width: 584px) 100vw, 584px\" \/><\/a><\/pre>\n<div>Using the query sheet again, issue another SQL insert statement:<\/div>\n<div><\/div>\n<pre>insert into dbo.customer values(N'Ellie', N'ellie@computer.commm', N'Anytown, Anycity 12345');<\/pre>\n<div>In the terminal, there should be another JSON message indicating that the trigger binding has fired<\/div>\n<div><\/div>\n<pre>[2023-05-12T21:47:03.447Z] Executing 'changeDataStream' (Reason='New change detected on table '[dbo].[customer]' at 2023-05-12T21:47:03.4465111Z.', Id=5c7077d5-cec9-424a-bc51-17f08b8677bb)\r\n[2023-05-12T21:47:03.448Z] Insert {\"customer_id\":2,\"customer_name\":\"Ellie\",\"customer_email\":\"ellie@computer.commm\",\"customer_address\":\"Anytown, Anycity 12345\"}\r\n[2023-05-12T21:47:03.561Z] Executed 'changeDataStream' (Succeeded, Id=5c7077d5-cec9-424a-bc51-17f08b8677bb, Duration=114ms)<\/pre>\n<div><\/div>\n<div>and if you look at the Azure Event Hub in the Azure Portal, you can see a message has been queued.<\/div>\n<div><\/div>\n<div><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114944.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-2127\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114944.png\" alt=\"Image Screenshot 2023 05 12 114944\" width=\"581\" height=\"374\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114944.png 581w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/05\/Screenshot-2023-05-12-114944-300x193.png 300w\" sizes=\"(max-width: 581px) 100vw, 581px\" \/><\/a><\/div>\n<div><\/div>\n<div>Congratulations! You have created a change data stream using SQL Server 2022\/Azure SQL Database, Azure Functions, Azure Event Hub, and SQL Bindings! It is pretty amazing the small amount of code you need to work with services within Azure. Both the interaction with the database and the event hub we single lines of code; so easy and simple to understand and implement.<\/div>\n<div><\/div>\n<h2>Passwordless Authentication<\/h2>\n<div>In this project, we placed the connect string of the database into the local.settings.json file with a password. While this is fine for local development, it will not be appropriate for production deployments in Azure. To solve this challenge, we can replace the user\/password in the file with a managed identity. This will allow the connect string to look like the following:<\/div>\n<div><\/div>\n<pre>Server=productiondatabase.database.windows.net; Authentication=Active Directory Managed Identity; Database=prodDB<\/pre>\n<div>This is a much more secure option and removes the ability for someone to find a password and user in a connect string. There is a tutorial on how to set this up <a href=\"https:\/\/learn.microsoft.com\/azure\/azure-functions\/functions-identity-access-azure-sql-with-managed-identity\">here<\/a>. You can also migrate away from SAS tokens with the Event Hub connection string to managed identity as well. The tutorial on how to used functions with managed identity is <a href=\"https:\/\/learn.microsoft.com\/azure\/azure-functions\/functions-identity-based-connections-tutorial-2\">here<\/a>.<\/div>\n<div><\/div>\n<h2>Outbox pattern with stored procedures<\/h2>\n<div>A popular pattern for change data capture is the outbox pattern. Here, you create a table that contains committed transactions that separate from the base table where the transactions originate. This helps with transaction integrity and assists with rollbacks. To implement this pattern here, I would create a set of stored procedures for the CRUD operations on a table (Sometimes called Table APIs). In these stored procedures, not only do you write to the main table, but you also write to this outbox table. You can even include the operation that was performed and a message ID in this outbox for better routing and sorting on the hub as well as keeping the transactions in order.<\/div>\n<div><\/div>\n<div>Keep an eye out for either a follow up to this post with these two topics discussed in depth or a full scale workshop\/lab encompassing everything here.<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Building on my first Azure SQL Bindings blog post and taking inspiration from Drew Skwiers-Koballa&#8217;s post on SQL Bindings, we are going to create a change data stream with Azure SQL Database. Now, usually, the first part of these blogs is a prerequisite section that goes over what you need to install locally to get [&hellip;]<\/p>\n","protected":false},"author":95874,"featured_media":2143,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[444,555,1,566,92,546,556,473,533],"tags":[244,443,510,541,465,469,508,568,448,30,93,94,530],"class_list":["post-1971","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-net","category-azure-functions","category-azure-sql","category-change-tracking","category-devops","category-github","category-sql-bindings","category-streaming","category-visual-studio-code","tag-net","tag-automation","tag-azure-sql-database","tag-azure-functions","tag-azuresql","tag-azuresqldb","tag-change-data-capture","tag-change-data-streaming","tag-ci-cd","tag-developers","tag-devops","tag-github","tag-visual-studio-code"],"acf":[],"blog_post_summary":"<p>Building on my first Azure SQL Bindings blog post and taking inspiration from Drew Skwiers-Koballa&#8217;s post on SQL Bindings, we are going to create a change data stream with Azure SQL Database. Now, usually, the first part of these blogs is a prerequisite section that goes over what you need to install locally to get [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1971","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/95874"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=1971"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1971\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/2143"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=1971"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=1971"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=1971"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}