Several events each year, such as Microsoft Build in the late spring and Microsoft Ignite in the fall, were traditionally marked by a bevy of new product features and previews. While this is still true, it is no longer a requirement to launch new features at industry gatherings. Microsoft has further decoupled feature announcements from specific “big events” with its cloud-first development strategy. In the old days, it was important to link new features to conferences because you had to press tens of thousands of CDs or DVDs to ensure widespread distribution. That’s no longer necessary, so it’s possible for us to see important new capabilities released to the public at just about any time. Let’s talk more about a few recent and important announcements.
Change Data Capture for Azure SQL Databases in Public Preview
For decades, users of SQL Server have requested a feature to capture historical change information and tracking for SQL Data Manipulation Language (DML) changes and changes to the data itself. Microsoft has provided several ways to do this for many years for on-premise SQL Servers and more recently for Azure SQL Managed Instances. But the change data capture (CDC) feature is now available in public preview for Azure SQL Databases.
Technically, CDC uses the transaction log reader functionality, long in use for replication, to read changes in the transaction log and write those changes to relevant change tables. The change tables, in turn, provide a historical account of changes made over time to the source tables. If you already have Azure SQL Databases, you can enable the CDC feature to be higher than the Standard 3 (S3) tier by running the stored procedure sys.sp_cdc_ enable_db. Once enabled for the database, a schema called cdc is created, along with metadata tables and system objects. You must then use the stored procedure sys.sp_cdc_enable_table to enable tracking of all changes done to the table and/or its data.
You can also apply fine-grained control to what you track, for example, to track data changes only within specific columns of a table. You can easily report on changes using the CDC metadata tables. And you can even send CDC change data to other destinations, such as other databases and data warehouses, or as streaming data to reporting systems such as Qlik. For more about this, read the Microsoft Tech Community blog post at https://aka.ms/CDCAzureSQLDB.
Azure SQL Database Ledger Using Blockchain Is Available in Public Preview
Imagine you have an application of the utmost importance and it’s essential to provide proof that its data has never been tampered with by malicious parties. Azure SQL Database Ledger can help. Azure SQL Database Ledger uses the power of blockchain to strengthen trust between disparate parties, while providing the simplicity and ease of use of SQL. In essence, the features use updatable ledger tables to capture the historical value of changed or deleted rows, along with details about which transaction(s) initiated the change and who made the changes. The feature also supports insert-only workloads with append-only ledger tables. There’s a lot more to learn than what I can provide in this quick overview, so read the post at https://techcommunity.microsoft.com/t5/azure-sql/announcing-azure-sql-database-ledger/ba-p/2200401 for more details.
Suspended—SQL Server on Windows Containers
Containers are amazing, as I’ve described in this column in the past. Imagine having all the benefits of a virtual machine, but with added advantages such as the ability to spin one up in 5 seconds or less. Microsoft has supported containers on Linux and on Windows for quite a while. The SQL Server on Windows Container Beta program, for example, kicked off back in 2017. While SQL Server on Linux containers has achieved widespread popularity and enjoys continued support for production environments, SQL Server on Windows Containers encountered quite a few obstacles. Consequently, Microsoft has decided to suspend the program.
If you have started to build solutions on SQL Server for Windows Containers, you have a couple of alternatives. First, you may switch to SQL Server on Linux Containers—I’ve had great experiences using RHEL in these scenarios. You can also run Windows Server Core running SQL Server using Docker Containers. Finally, I also really like the containers from Windocks (https://windocks.com/lps/howitworks), which provide a great SQL Server-on-Windows experience along with full compatibility with Active Directory, Windows Authentication, and all the other tooling and processes you’re used to in the Windows ecosystem.