Joey on SQL Server

Build 2019: Microsoft Bets Big on Its Data Platform

Microsoft's ongoing investments in SQL Server are bearing fruit. Here's a rundown of the biggest announcements for database professionals from this year's conference.

• By Joey D'Antoni
• 05/06/2019

Microsoft's annual Build conference is taking place this week in Seattle. While most of the news coming from the conference is developer-focused, there are many announcements around Microsoft's data platform. All of these announcements and new features share a common theme: Microsoft being consistent in its development investments.

What does that mean? Whether you run SQL Server on Azure, on Windows, on Linux, in a container or on a Raspberry Pi, the applications you develop will run on any of those platforms with similar performance. Likewise, if you are using a Platform as a Service (PaaS) database, whether it's Azure SQL Database or PostgreSQL, you can expect to have a very similar set of fully featured options.

Azure SQL Database Edge
Internet of Things (IoT) devices and mobile phones have led to massive changes in the way IT organizations process data. The sheer volume of data and massive number of endpoints have led to technology changes both in front-end and back-end processing.

The brand-new Azure SQL Database Edge addresses this by allowing SQL Server to run on low-power ARM devices that can run either connected or offline. This computing model allows for some intelligence to be added to sensor data and allows filtering to happen on devices that may only be connected part of the time. This is the edge component.

We had an example of this with a customer last year. The customer had remote field locations running PCs with cellular cards, which had sporadic connectivity. When the computer was connected, data was processed and then replicated to a central database server. With SQL Database Edge, that data could be processed and aggregated locally, with a much smaller footprint and less network traffic. There are numerous edge processing scenarios that will benefit from a much smaller SQL footprint.

For a deeper dive into Azure SQL Database Edge, check out my Q&A here with Microsoft Senior Program Manager Sourabh Agarwal. Currently, Azure SQL Database Edge supports Linux only, but Windows support is expected. This feature is in private preview as of today.

Azure SQL Database Serverless
While I hate the name of this feature, I'm blaming it on Amazon Web Services (AWS), which has a version of this in its Database as a Service offering. Name notwithstanding, Azure SQL Database Serverless is cool, and its impact is such that I plan to implement it for at least two of my customers in coming weeks.

The easiest way to describe this feature (which is now in public preview) is as autoclose for Azure SQL Database. Normally, Azure SQL Database is always running; there is no option to pause or stop the service as there is with other services within Azure. However, Azure SQL Database Serverless will pause when there is no activity and resume when activity returns. You are only billed for the time that the database is in use.

One scenario I'm planning to deploy this in is a data warehouse that is fairly low-volume and infrequently used. The customer has budget concerns, and most of its data for reporting is stored in a Power BI model, so direct database access is not frequently needed.

Another scenario is for a business that sells data as a service, running on Azure SQL Database. If its customers are not using the database, there is no reason to pay for it. The length of time before the database pauses is adjustable. This is also a really good use case for testing and development environments.

Hyperscale All the Things
Microsoft announced at Build that SQL Database Hyperscale is now generally available for Azure SQL Database workloads and is in public preview for Azure PostgreSQL Database.

In case you're not familiar with it, SQL Database Hyperscale is a feature that allows Azure SQL Database to scale well beyond the traditional size limit of 4TB, up to volumes of 100TB or even more. This is done while maintaining performance using page servers, which act as local warm SSD-based caches. As your data grows, you will have roughly one page server per 1TB of data.

Given this distributed architecture in conjunction with Azure's object-based storage model, SQL Database Hyperscale can backup and restore multiterabyte databases in mere minutes, compared to the hours that would be expected in any other environment.

As opposed to Azure SQL Database Warehouse, SQL Database Hyperscale is optimized for online transaction processing (OLTP) workloads.

Business Intelligence
Azure SQL Data Warehouse has some big news, namely Result-set Caching and Materialized Views. Result-set Caching will allow for nearly instantaneous query processing, and should help drive SQL Data Warehouse to higher levels of concurrency. Materialized Views enable complex business logic for reports to be stored in the database and kept up-to-date as data changes.

Azure SQL Database also now has support for sorted columnstore indexes, which should improve performance in large aggregation queries.

In addition, the Mapping Data Flows capability in Azure Data Factory is now generally available. This allows Azure Data Factory to be a true ETL tool by providing a GUI on top of Azure Databricks (Microsoft's managed Spark service), and by performing transformations in-memory, without having to land data and then use another compute engine to process.

Drivers and Connectivity
There are always a few announcements around drivers and development options at Build. At this year's event, Microsoft announced an extension for PostgreSQL called pg_auto_failover that will allow developers to automate failovers. This is released under the PostgreSQL license, which will allow for community extensions.

Microsoft has also released an Oracle migration assistant for PostgreSQL. If you haven't worked with one of the database platform migration tools recently, I highly recommend giving them a shot for any migration projects. Microsoft has made big investments in these products and they are very good.

As you can see, Microsoft continues to innovate across the data platform, whether it's SQL Server, PostgreSQL or MySQL -- and we haven't even touched on some of the enhancements to Cosmos DB (hello, Spark API). If you've paid careful attention to Microsoft in recent years, you can see the progression of investments across the platform and far greater consistency in development than in the past. It's an exciting time to be a data professional.