News

Improvements Coming to Azure SQL, SQL Server 2019 Preview

• By Kurt Mackie
• November 08, 2018

A raft of improvements are coming to Microsoft's database products, including migration support to Azure SQL Database, Apache Kafka Support on Azure Event Hubs, Azure SQL Data Warehouse improvements and yet another preview of the coming SQL Server 2019 product.

Microsoft announced the improvements at this week's PASS Summit event for database administrators and developers.

SQL Server 2019 CTP 2.1
SQL Server 2019 is still at the preview stage, although it's now reached Community Technology Preview (CTP) 2.1, according to Rohan Kumar, Microsoft's corporate vice president for Azure Data, in the announcement. Microsoft is touting built-in support for Apache Spark and the Hadoop Distributed File System in the coming SQL Server 2019 product.

The latest preview of SQL Server 2019, namely CTP 2.1, adds support for deploying Python- and R-based applications on clusters. It brings the ability to place a "buffer pool in persistent memory," which is thought to speed up I/O operations. There's also improved diagnostics for troubleshooting long-running queries that have gotten blocked, among other perks.

Another feature that will be coming to SQL Server 2019 in a future preview release will be a new "Accelerated Data Recovery" feature, Kumar noted.

Hybrid and Migration Support
Kumar said that Microsoft has been "architecting for hybrid" to support customers that plan to run "data workloads in on-premises [servers] and in the cloud." To that end, Microsoft has been making it easier for SQL Server users to migrate toward Azure SQL services.

Kumar pointed to the Azure SQL Database Managed Instance service as a currently available solution for some organizations running SQL Server 2008 and SQL Server 2008 R2, which both will fall out of "extended support" on July 9, 2019, becoming unsupported products. Azure SQL Database Managed Instance, which is a service managed by Microsoft, is described as being a space in Microsoft's datacenters that is "dedicated for your needs and not shared with any other customers," according to this Microsoft blog post. Azure SQL Database Managed Instance uses the SQL Server 2017 Enterprise edition, which has "backward compatibility with SQL Server 2008 and newer" server products, Microsoft indicated.

Microsoft also is planning to add a new Business Critical tier to the Azure SQL Database Managed Instance service for organizations running "mission-critical business apps with high I/O requirements." This Business Critical tier is expected to reach "general availability" (commercial release) on "December 1," according to Kumar. It'll also have a lower-cost "reserved capacity" prepaid option that'll be based on a "1 or 3-year term commitment" by organizations, he added.

Azure Event Hubs for Apache Kafka
Microsoft announced on Wednesday that Azure Event Hubs for Apache Kafka has reached general availability.

Essentially, Microsoft added support for running the Apache Kafka open source platform on Azure Event Hubs, which is Microsoft's "cloud-native serverless solution" for handling "large-scale, real-time stream ingestion." Microsoft is touting the use of Apache Kafka on Azure in this way because organizations won't have to bother with configuring it, nor will they have to manage servers or networks. Organizations won't have to change their Kafka-based applications to make it work with Azure Event Hubs, Microsoft promised.

Azure SQL Data Warehouse
Microsoft had lots to say in an announcement about improvement to its Azure SQL Data Warehouse product, which supports running queries across "petabytes of data." The "Gen 2" version of Azure SQL Data Warehouse was launched back in April, the announcement noted.

A coming "Workload Importance" feature will help optimize query execution on Azure SQL Data Warehouse. It'll let organizations "use a single SQL Data Warehouse database to more efficiently run multiple workloads, taking away the complexity of separate data warehouses for each solution," Microsoft explained. The Workload Importance feature will be available "later this year at no additional cost."

A new Row-Level Security feature of Azure SQL Data Warehouse has now reached general availability status, Microsoft announced on Wednesday. Row-Level Security gives organizations greater control over who can access data, based on the data's location in rows of tables. This feature is supported by both "SQL authentication and Azure Active Directory authentication," the announcement noted. Organizations create security policies based on "filter predicates" to control access, although Microsoft plans to add support for "block predicates" soon. Row-Level Security is currently available in "a few" Azure regions, but it's expected to be deployed worldwide "within the next two weeks." It'll be available at no additional charge, the announcement indicated.

Microsoft added management support to Azure SQL Data Warehouse via Azure Advisor and Azure Monitor, according to a Wednesday announcement. Both of those tools are "configured by default" to work with Azure SQL Data Warehouse.

Azure Advisor now has an "Adaptive Cache" feature for determining when to scale the cache. It also has a "Table Distribution" feature for addressing workload performance issues. Microsoft added a "Tempdb" feature to Azure Advisor so that IT pros can "understand when to scale and configure resource classes." Azure Monitor now shows "near real-time metrics in the overview blade" for Azure SQL Data Warehouse, the announcement indicated.

Additionally, Azure Monitor now has a "Diagnostic Logs" capability that can be configured to show "historical query performance troubleshooting" information, according to a Wednesday announcement. These diagnostic log views are equivalent to what can be seen with the current Dynamic Management Views approach, but apparently they aren't as limited. For instance, the announcement explained that "DMVs have a limit of 10,000 rows that can easily be exceeded for intensive enterprise data warehouse workloads with heavy query activity." 

Developers now have access to a preview of a "SQL Server Data Tool" in Visual Studio, which is bringing change management support to Azure SQL Server Data Warehouse, according to a Wednesday announcement. It's a big deal for the following reasons, according to the announcement:

Currently, change management and deployment for SQL DW is a non-trivial effort where customers must build SQL and PowerShell scripts. This becomes an unmanageable experience as modern data warehouse solutions can have over hundreds of data pipelines and thousands of database objects. This issue is exacerbated as data warehouse deployments typically have multiple environments for development, tests, and production.

Microsoft is promising that the SQL Server Data Tool in Visual Studio will add "a first-class enterprise-grade development experience for your modern data warehouse," with no more need for manual scripting.