• Share
  • Share

Azure SQL DW:
The next-generation
data warehouse

Here’s what sets Azure SQL Data Warehouse apart from its competitors.

Oliver Asmus | September 16, 2016

Today’s complex business challenges require a solid data warehouse—one that is fast, scalable, and flexible enough to meet the business needs of today and tomorrow while advancing our understanding of data. Microsoft’s Azure SQL Data Warehouse is at the forefront of this modern evolution.

What is Azure SQL Data Warehouse?

Azure SQL Data Warehouse (SQL DW) is Microsoft’s cloud-based, elastic data warehousing appliance. It’s geared toward high-performance analytics using massively parallel processing (MPP) techniques for warehouse workloads. Given its ability to add and remove resources as needed, it can be thought of as a DWaaS, or Data Warehouse as a Service.

The product’s key features include:

  • Binary large object (BLOB) storage—Gives Azure SQL DW the ability to store any type of data structure regardless of its source
  • Clustered columnstore indexes—Offers high data compression and even greater query performance
  • PolyBase comes built-in—Enables querying of large datasets or both the relational and non-relational (i.e. big data) types using plain T-SQL
  • Native integration—SSRS, SSAS, Tableau, Qlik, Azure ML, and Stream Analytics are just a few of the many connectors offered

Powering SQL DW is Microsoft’s chief relational database management system: SQL Server. SQL DW comes with all of the well-known features you would expect from SQL Server stand-alone, plus the additional features of the Azure cloud.

What makes SQL DW different?

True to any Microsoft product, SQL DW comes loaded with a heavy set of documentation. And while the devil is in the details when looking at any tool or product, I’ve found that there are two main points that make SQL DW special:

1) Analytics integration


In this case, analytics refers to reporting, visualizations, forecasting, predictive modeling, and any other use or consumption of data. What sets SQL DW apart is its ability to integrate and connect with many of today’s leading reporting and visualization platforms all while requiring no additional backend work. This functionality comes standard with the Azure subscription. Microsoft has an extensive list of external business intelligence providers who also have connectors for SQL DW within their own products.

2) Hybrid architecture


One of the popular selling points of the cloud is the ability to “scale up, scale out” as needed to meet business demands. This often implies a physical separation between on-premises and cloud-based infrastructure. A truly unique feature of SQL DW is the ability to scale into the architecture as demand grows. This enables businesses to continue to using their on-premises hardware investments while stepping into the cloud as needs arise.

For example, since SQL DW is built on SQL Server, you can use the Stretch DB function of SQL Server 2016 to scale into SQL DW and grow reporting capabilities into the cloud without sacrificing what’s been built on-premises. Not only does this feature apply to SQL DW, but it can be found in a number of Microsoft Azure products.

How does SQL DW compare?

In looking at the data warehouse appliance market today, there are three main competitors: Azure SQL DW, AWS Redshift, and Google BigQuery. All of these products provide the same basic offerings: low start-up overhead (can get started in minutes); on-demand scaling up or down; ability to integrate with leading big data mechanisms like MapReduce, HBase, and Spark; and pay-as-you-go pricing models.

But here’s where SQL DW excels:

  • Analytics-centric—Multiple native connectors and a continually-growing base of business intelligence solution providers
  • Hybrid architecture—While competitors offer independent infrastructure as a service (IaaS) products, SQL DW and the broader Azure product base can supplement existing on-premises resources
  • Integrate with existing Microsoft products—If you’re already a Microsoft customer, adding SQL DW into your portfolio is seamless
  • Familiar SQL Server backend—SQL DW was built using Microsoft SQL Server, so the internals of the product, and thus our institutional knowledge of the product, is transferrable

What’s next?

Companies’ ability to innovate and pivot is what keeps them in business—and Microsoft is no different. When looking at the evolution of the data warehouse, there are two themes that come to mind: 1) enabling the fullness of the data warehouse dimensional model using best-in-class resources and 2) applying tested-and-true data warehousing methods to the cloud.

Tomorrow’s data warehouses will continue to integrate and expand upon more advanced analytics and collaboration offerings. This will be realized when the lines between data management, analytics, and visualization products no longer exist. Microsoft is already doing this today with the SQL DW and SQL Server products. By building into these products the ability to gain insights and share content collaboratively, we’re poised to broaden our understanding of the data around us.

Slalom Consulting Oliver Asmus

Oliver Asmus is an enterprise data architect for Slalom New York's Information Management and Strategy practice. He’s a Microsoft Certified Professional and has over a decade of diversified experience delivering Microsoft solutions with an emphasis on data and cloud technologies. Follow Oliver on Twitter: @OliverAsmus.