It has been some time since I last wrote about Azure SQL Database1It helps to keep in mind that the same database engine powers SQL Server, Azure SQL Database, Managed Instance, and Synapse.. Although it has been more than three years since SQL Server 2017 was released, Microsoft have not been resting on their laurels. Here is a list of features in public preview that you can start testing and including in your future plans for your Azure SQL Database implementation.
Tamper evidence in your database
Probably the most exciting feature for me is the public preview of Azure SQL Database ledger, which leverages the same technology as Temporal Tables to produce evidence of tampering using a cryptographic hash of every transaction. This is interesting because it demonstrates to internal and external partners whether any data tampering has occurred in your database. Every time a transaction is committed, it stores a hash which is in turn linked to the previous cryptographic hash. These linked hashes leave a trail — or ledger — which can be examined for tampering.
Of course, it should go without saying that this is only one level of defence and that you should implement auditing and security layers in addition to the ledger, but it is great to see innovation in this space.
Change Data Capture (CDC)
Traditionally, CDC requires access to your transaction log files as well as the SQL Agent, but with a platform-as-a-service (PaaS) offering like Azure SQL Database, we do not have access to SQL Agent. The public preview of CDC in Azure SQL Database was announced earlier this month, and you can read more about it here. If you have a database on a tier higher than S3 (Standard 3), you can enable CDC on it, and even use Azure Data Factory to move that data to other targets.
Query Store hints
Several of my fellow data platform friends have already written in more detail about this, but another feature in public preview is Query Store hints, which allows you to change the shape and behaviour of query plans in the Query Store by adding query hints to them. Once you find the query you want to modify, you run a system stored procedure to add one or more query hints.
For example, you can do any of the following2this list comes from the above link: recompile a query on each execution; cap the memory grant size for a bulk operation; limit maximum degree of parallelism; disable row goal for a TOP query; use a different join algorithm (e.g., LOOP instead of HASH); use a lower database compatibility level; or even use the legacy cardinality estimator. Truly powerful stuff that gives you more leeway in managing code you can’t change (for instance in a vendor application).
Maintenance windows per region
Also in public preview, Maintenance window allows you choose a maintenance window outside of that region’s business hours. As Microsoft puts it, this feature “is intended for production workloads that are not resilient to database or instance reconfigurations and cannot absorb short connection interruptions caused by planned maintenance events.” While their SLA does provide for full uptime during business hours, there are short interruptions where Microsoft might fail over your database to perform maintenance on it, so this feature allows you to choose a window outside of your peak hours.
I know not everyone can build in resilience into their application — especially with a lift-and-shift scenario — so this is one more way for Microsoft to keep your database humming along as long as possible, and still balancing a reasonable effort of automated maintenance. Note that Dev / Test environments are not supported.
Share the features you’re most excited about in the comments below.