Analytics, Azure Synapse Analytics
Flashback 2016 – Highlights from Azure SQL Data Warehouse
By John Macintyre Director of Product, Azure Data
5 min read
Earlier this year we announced the general availability of Azure SQL Data Warehouse, offering a SQL-based fully managed, petabyte-scale cloud solution for data warehousing. SQL Data Warehouse is highly elastic, enabling you to provision in minutes and scale capacity in seconds. You can scale compute and storage independently, allowing you to burst compute for complex analytical workloads or scale down your warehouse for archival scenarios, and pay based off what you're using instead of being locked into predefined cluster configurations. Unlike other cloud data warehouse services, SQL Data Warehouse offers the unique option to pause compute, giving you even more freedom to better manage your cloud costs.
We are excited to see the customer momentum across industries and we have continued to bring new features to even further enhance the customer experience.
Leading digital advertising company MediaBrix uses Azure SQL Data Warehouse to run analysis across billions of rows of data – drilling deep down into 30 TB of data. SQL Data Warehouse not only helps MediaBrix gain fast insights, but it also hooks up to technologies like Azure Machine Learning to create predictive models, change data in real time, and deliver the right ads to the right people. Learn more about how MediaBrix gets answers from data with Microsoft Azure platform.
As 2016 ends, here are some of the highlights from a memorable year.
General availability now across 23 regions world wide
Since announcing general availability in 14 regions in July, we have now extended to 9 additional regions bringing the total to 23 – more than any other major cloud provider. Now customers across following regions can use Azure SQL Data Warehouse: North Europe, North Central US, Central US, East US, East US 2, South Central US, West Central US, West US, West US 2, Canada Central, Canada East, West Europe, Germany Central, Germany Northeast, East Asia, Southeast Asia, Australia Southeast, Central India, South India, China East, China North, Japan East, and Brazil South.
Industry Leading Performance for Analytic Queries
Azure SQL Data Warehouse is powered by SQL Server underneath and with GA we went live with SQL Server 2016. The industry leading SQL Server 2016 columnstore implementation is at the core of serving analytic queries in SQL Data Warehouse. We significantly improved the data compression and segment elimination to reduce the IO when processing large number of rows.
Batch mode execution on top of columnstore speeds up queries by orders of magnitude. With SQL Server 2016, we added batch mode support for common analytic operators, such as order by and windowing aggregates. In addition, we now support aggregate pushdown and string predicate pushdown to the scan node.
“We can tell customers who’s actually consuming their advertising. For example, we might say that to target women aged 24 to 35 who have children, they’ll need to do so between 6 and 8 AM on the East Coast, preferably in Pennsylvania or New Jersey. It’s mind-blowing to tell them that, because they’re not getting that level of intelligence from anybody else.”
– Christopher Beach, Senior Vice President of Engineering, MediaBrix
Learn more about how an advertising company gets answers from data with Microsoft Azure platform.
Fast Loading with ADF and PolyBase
We recently shared how you can use Azure Data Factory Copy Wizard to load 1TB data in under 15 mins into Azure SQL Data Warehouse, at over 1.2 GB per second throughput. Azure Data Factory is a data movement service in the cloud enabling ingestion of data from multiple on-premises and cloud sources – SQL Data Warehouse has deployed a single-click integration with Data Factory to make data movement even easier. Using the staging blob feature, you can achieve high load speeds from all types of data stores besides Azure Blob storage, which the Polybase supports by default.
“Using Azure SQL Data Warehouse, we’re able to do near-real-time compute so they can see data from the last hour. In some cases, we can even bypass our default system and go into a true live system that shows how many people are in the room at that time.”
– Tom Sheppard, Chief Executive Officer, Presence Orb
Learn more about real-time benefits with Azure SQL Data Warehouse.
Enhanced migration, monitoring, and SQL tooling experience
Azure SQL Data Warehouse has introduced updates to the Azure portal and SQL Server Management Studio (SSMS) to provide a seamless experience when loading, monitoring, and developing your SQL Data Warehouse. The updates include integrated support for loading from 20+ data stores on premise and in the cloud, a simple process to troubleshoot common issues. The updates also bring highly requested functionality within SSMS further enhancing the experience for SQL users, like enabling the execution of Generate Scripts wizard for database users and user defined functions.
SQL Data Warehouse introduced a new top level resource blade that allows you to quickly manage all your databases. You can use the SQL Data Warehouse resource blade to quickly scan through your data warehouse for details like the name, status, server, pricing tier, location, and subscription.
Accelerated look up queries
SQL Data Warehouse now supports the creation of secondary B-Tree indexes on column store tables. Most analytic queries aggregate large amounts of data and are served well by scanning the column store segments directly. However, there is often a need to look for a “needle in a haystack”, which translates to a query that does a lookup of a single row or a small range of rows. Such look up queries can get orders of magnitude (even 1000x) improvement in response time and potentially run in sub-second if there is a B-Tree index on the filter column.
Easy integration with Azure Active Directory authentication and other services within Azure
Azure AD provides an alternative to SQL Authentication enabling centralized identity and group management. It enables a single sign-on experience using SQL Data Warehouse for federated domains. Azure AD can be used to authenticate against a growing number of Azure and other Microsoft services and helps customers prevent the proliferation of users and passwords.
“Switching from Amazon Redshift was not just about a direct comparison to Azure SQL Data Warehouse. The overall Azure offering provided a lot of motivation.”
– Bill Sabo, Managing Director of Information Technology, Integral Analytics
Learn more about Integral Analytics switch to Azure from AWS.
To enable greater adoption within the health industry, Azure SQL Data Warehouse is now a covered service on the Microsoft HIPAA Business Associate Agreement (BAA). The Health Insurance Portability and Accountability Act (HIPAA) is an US healthcare law that establishes requirements for the use, disclosure, and safeguarding of individually identifiable health information. For details on achieving a HIPAA compliant solution, please see the FAQ section for further instructions. Additional Microsoft HIPAA related details can be found by watching the Understanding HIPAA Compliance with Azure video.
New products from Partners for easy experience
We have had great partners join us to help customers on their journey to experience and adopt the service – including building custom product to enhance data migration and management experience. For example, Redgate, a long-time partner that delivers SQL Server tools, has created Data Platform Studio (DPS) which provides a simple and reliable way to migrate on-premises SQL Server databases to Azure SQL Data Warehouse.
Exclusive free trial
At the annual PASS Summit in October, we announced a SQL Data Warehouse exclusive free trial – enabling customers to experience this cloud-based service for free for a month.
It’s been a great year and thanks to everyone who has joined us in the journey. We are excited for the next year and look forward to helping you solve your most important data warehousing challenges and bringing you even more compelling features and service enhancements. Till then, wish you the best start to 2017.
Check out the many resources for learning more about SQL Data Warehouse, including:
What is Azure SQL Data Warehouse?
SQL Data Warehouse best practices