The Seven Coolest Features of Azure SQL Database and SQL Data Warehouse

By Marcus Crast

Relational database-as-a-service is one of Microsoft Azure’s biggest differentiators when compared to other public cloud vendors. There are many PaaS database options available in the market today, but no other cloud vendors come close to matching what Microsoft is doing in the relational database area. Microsoft has taken their world-class SQL Server product and built a PaaS service around it, and that’s just the beginning.

Web analytics information and development website statistic - vector illustration
Web analytics information and development website statistic – vector illustration

Within the last few months, the database-as-a-service option in Azure has gotten even better with the public preview release of Azure SQL Data Warehouse. This is essentially the Microsoft Analytics Platform System (APS) PaaS offering in Azure. In general, Azure’s big-data-related PaaS offerings are increasingly able to handle the most challenging enterprise scenarios such as failover, large volumes, and high-availability disaster recovery.

Microsoft is rolling out many great new features for Azure SQL Database and Azure SQL Data Warehouse, but if I have to pick my favorites, here are the seven that I think offer the most value to Azure users:

1.  Azure SQL Data Warehouse Elasticity – I first saw this feature in action at the recent Cortana Analytics Workshop in Redmond. I knew immediately that it would become a big part of what our data and insights data engineers do here at 10th Magnitude. We have a number of customers who have made big investments in BI and for whom long-running queries are a core part of their data warehousing environment. As the name implies, long-running queries take a while to run (hours or even days) and tax the compute and storage resources of a SQL Server instance.

Microsoft has enabled elasticity in Azure SQL Data Warehouse by separating the compute and storage components of the service, allowing you to scale compute independently. Now a long-running query can run with whatever resources it needs. You can even enhance or remove resources while the query is running! Most services that offer any type of similar functionality will require you to restart the query or the service to leverage the new resources.

2.  Azure SQL Database Security – Azure SQL security components are evolving fast. Most of us here at 10th Magnitude are Azure Advisors, a program that gives Azure practitioners early access to private/public preview announcements for features and products. Among the security-related preview features, data masking, row-level security and Always Encrypted are my favorites because I think they’ll provide the most value. Auditing is another super-cool area: you can just turn it on and then check a Power BI dashboard (via the Auditing Content Pack) to analyze and research the auditing you just enabled on your Azure SQL Database. Let’s face it, security features are the most thankless, no-fun features a vendor can add to their product. But given the breaches happening almost daily in all industries, I am glad that Microsoft is putting such a heavy emphasis on the security components.

3.  Azure Key Vault Integration with SQL Server IaaS VMs – Microsoft just released the SQL Server Connector for Azure Key Vault as a preview feature. This connector enables SQL Server Encryption to use the Azure Key Vault as SQL Server’s EKM for secure key protection. This means you no longer have to manage and store those keys yourself; Microsoft’s robust infrastructure will do it for you.

4.  V12 Portability – This is a core benefit of using the Azure SQL Database platform: the ability to move a database to and from the cloud and in and out of IaaS and PaaS. This is a really big deal because in the past, organizations had to spend a lot of time developing a migration strategy so they could have portability for a given database. Microsoft now provides many tools that will help you easily upgrade and/or migrate from SQL Server to Azure SQL and from Azure SQL to SQL Server.

5.  Query Store – Part of the portability I just mentioned is the ability to view the performance of a database across many different sets of hardware or service tiers. The Query Store is available in Azure SQL Database today and will be part of SQL Server 2016. Think of the Query Store as a data warehouse for your database performance metrics; it tracks all history across different host platforms and hardware. Query Store tracks performance and provides a comparative view into different time periods and configurations.

6.  Polybase in the Cloud – This is a feature of Azure SQL Data Warehouse and is also a component of the APS that allows you to query non-relational storage within a T-SQL context, making it easy to combine data from all over the cloud. Now you can easily join blobs in Azure Blob Storage to a SQL Server table. Polybase used to only be available in the APS (formerly PDW) Appliance. Hadoop isn’t as prevalent in on-premises data centers as in the public cloud, so by releasing Polybase in Azure Microsoft is really getting serious about Hadoop.

7.  Scalability of Azure SQL Database – The general scale options for Azure SQL Database are so impressive I couldn’t pick just one as my favorite. My top three are:

  • Horizontal and geographical scale, which enables database sharding
  • Service tier scale, which lets you change service tiers on-demand, on a schedule, programmatically
  • Readable secondaries – like AlwaysOn, Azure SQL database has native support for readable secondaries

I could go on and on about the different features of Microsoft’s relational database-as-a-service options. These are my favorites today, but Microsoft releases new features regularly and what impresses us today might be an expectation six months from now. With all the options now available, I encourage you to really maximize and “right fit” your choice of platform, including database-as-a-service PaaS options (both OLTP and DW), SQL Server in IaaS and SQL Server in your own data center.