Category Archives : Data Warehouse



Adaptive caching powers Azure SQL Data Warehouse performance gains

Today we made Azure SQL Data Warehouse (SQL DW) Compute Optimized Gen2 Tier generally available to our customers. Even though data and data sources grow exponentially, organizations continue to demand faster and faster insights. Azure SQL DW Compute Optimized Gen2 tier delivers on this need with major performance improvements made possible through adaptive caching.

Analytics workload performance is typically determined by two major factors, I/O bandwidth to storage and repartitioning speed, also known as shuffle speed. This blog post looks under the hood of how Azure SQL DW exploits the latest hardware trends to improve effective I/O bandwidth available.

One of the recent hardware innovations becoming widely available are NVM Express (NVMe) solid-state drive (SSD) devices. NVMe SSDs offer significantly more I/O bandwidth than SATA SSDs or hard drives. A typical single NVMe device used in Azure, generally offers up to 2GB/sec of local I/O bandwidth, with multiple devices available per physical host, resulting in bandwidth previously reserved only to very high-end storage systems. Azure SQL DW Compute Optimized Gen2 tier fully takes advantage of NVMe devices through adaptive caching of recently used data on NVMe. With this breakthrough on customer workloads, we have observed up to five times the



Turbocharge cloud analytics with Azure SQL Data Warehouse

Data is transformative. The ability to turn data into breakthrough insights is foundational to remain relevant in an increasingly competitive market.

To help our customers deliver fast insights from exponentially growing data, today we are announcing the general availability of the Compute Optimized Gen2 tier of Azure SQL Data Warehouse. With this performance optimized tier, we are bringing the best of Microsoft software and hardware innovations to dramatically accelerate query performance and concurrency for our customers.

Fast, flexible, and secure cloud data warehouse

We launched Azure SQL Data Warehouse three years ago to make a powerful SQL based MPP (massively parallel processing) architecture data warehousing accessible to all. It was the first data warehouse that helped customers reduce costs by enabling them to scale compute and storage independently, and by offering pause and resume capabilities. This flexibility, combined with fast query performance, comprehensive data security, and governance capabilities has led to adoption by thousands of customers like Adobe, Toshiba, and LG Electronics. To keep up with customer demand, we have expanded the service to 33 Azure regions and it is now the most globally available of all cloud data warehouse services.

Azure SQL Data Warehouse Compute Optimized Gen2 tier



Blazing fast data warehousing with Azure SQL Data Warehouse

Today, we announced general availability of Azure SQL Data Warehouse (SQL DW) Compute Optimized Gen2 tier, the new generation of Azure SQL DW. Azure SQL DW is a fast, flexible, and secure cloud data warehouse tuned for running complex queries fast and across petabytes of data.

We see two key trends that drive data warehousing decisions, the amount of data continues to grow exponentially and the need to deliver insights from all this data is even more urgent. Azure SQL DW Compute Optimized Gen2 tier is designed to help customer accomplish just this by delivering dramatic query performance improvement. In addition, SQL DW now supports up to 128 concurrent queries while being able to provision five times more computing power compared to the previous product generation.

“After upgrading to the Gen2 of SQL Data Warehouse, our data warehouse workload has seen an average of 5.4 times performance improvement. This enhancement to the service is phenomenal and helps us deliver key customer insights for our business” said Brent Niezgocki, Senior Software Engineer for the Azure Active Directory analytics team at Microsoft.

Fast query performance through adaptive caching

As organizations look to accelerate time to insight, performance in the domain of



Replicated Tables now generally available in Azure SQL Data Warehouse

We are excited to announce that Replicated Tables, a new type of table distribution, are now generally available in Azure SQL Data Warehouse (SQL DW). SQL DW is a fully managed, flexible, and secure cloud data warehouse tuned for running complex queries fast and across petabytes of data.

The key to performance for large-scale data warehouses is how data is distributed across the system. When queries join across tables and data is distributed differently, data movement is required to complete the query. The same can be said when transforming data to load, enrich, and apply business rules. With Replicated Tables, the data is available on all compute nodes, hence data movement is eliminated, and queries run faster. In some cases, such as small dimension tables, choosing a Replicated Table versus a Round Robin table, can increase performance because data movement is reduced. As with all optimization techniques, performance gains may vary and should be tested.

Reducing data movement to boost performance

During the public preview of Replicated Tables, SQL Data Warehouse customers are seeing up to 5x performance gains while transforming data with Replicated Tables when compared to using Round Robin distribution.

Taking a look at an example of



Transparent Data Encryption with customer managed keys in Azure SQL Database generally available

Today, we are excited to announce the general availability of Transparent Data Encryption (TDE) with Bring Your Own Key (BYOK) support for Azure SQL Database and Azure SQL Data Warehouse. This is one of the most frequently requested features by enterprise customers looking to protect sensitive data and meet regulatory or compliance obligations that require implementation of specific key management controls. TDE with BYOK support is offered in addition to TDE with service managed keys, which is enabled by default on all new Azure SQL Databases.

TDE with BYOK support uses Azure Key Vault, which provides highly available and scalable secure storage for RSA cryptographic keys backed by FIPS 140-2 Level 2 validated Hardware Security Modules (HSMs). Key Vault streamlines the key management process and enables customers to maintain full control of encryption keys and allows them to manage and audit key access.

Customers can generate and import their RSA key to Azure Key Vault and use it with Azure SQL Database and Azure SQL Data Warehouse TDE with BYOK support. Azure SQL Database handles the encryption and decryption of data stored in databases, log files, and backups in a fully transparent fashion by using a symmetric Database Encryption Key



Rubikloud leverages Azure SQL Data Warehouse to disrupt retail market with accessible AI

In the modern retail environment, consumers are well-informed and expect intuitive, engaging, and informative experiences when they shop. To keep up, retailers need solutions that can help them delight their customers with personalized experiences, empower their workforce to provide differentiated customer experiences, optimize their supply chain with intelligent operations and transform their products and services.

With global scale and intelligence built in to key services, Azure is the perfect platform to build powerful apps to delight retail customers, the possibilities are endless. With a single photo, retailers can create new access points for the customer on a device of their choice. Take a look at this example of what’s possible using Microsoft’s big data and advanced analytics products

AI can be complex, this is where Rubikloud comes in. Rubikloud is focused on accessible AI products for retailers and delivering on the promise of “intelligent decision automation”. They offer a set of SaaS products, Promotion Manager and Customer Lifecycle Manager, that help retailers automate and optimize mass promotional planning and loyalty marketing. These products help retailers reduce the complexities of promotion planning and store allocations and better predict their customers intention and behavior throughout their retail life cycle.

As Rubikloud



Enhanced capabilities to monitor, manage, and integrate SQL Data Warehouse in the Azure Portal

Azure SQL Data Warehouse (SQL DW) continues to introduce updates to the Azure portal to provide a seamless user experience when monitoring, managing, and integrating your data warehouse.

Support for Azure Monitor metrics

SQL DW now supports Azure Monitor which is a built-in monitoring service that consumes performance and health telemetry for your data warehouse. Azure monitor not only enables you to monitor your data warehouse within the Azure portal, but its tight integration between Azure services also enables you to monitor your entire data analytics solution within a single interface. For this release, data warehouse metrics have been enabled to enables you to identify performance bottlenecks and user activity:

Successful/Failed/Blocked by firewall connections CPU IO DWU Limit DWU Percentage DWU used

These metrics now have a one-minute frequency for near real-time visibility into resource bottlenecks of your data warehouse. There is a default retention period of 90 days for all data warehouse metrics with Azure Monitor.

Configure metric charts in the Azure monitor service through the Azure Portal or programmatically query for metrics via PowerShell or REST:

Pin configured charts for your data warehouse through Azure dashboards:

Safely manage costs by pausing

The pause feature for SQL



How to configure Azure SQL Database Geo-DR with Azure Key Vault

Azure SQL Database and Data Warehouse offer encryption-at-rest by providing Transparent Data Encryption (TDE) for all data written to disk, including databases, log files and backups. This protects data in case of unauthorized access to hardware. TDE provides a TDE Protector that is used to encrypt the Database Encryption Key (DEK), which in turn is used to encrypt the data. With the TDE and Bring Your Own Key (BYOK) offering currently in preview, customers can take control of the TDE Protector in Azure Key Vault.

Taking advantage of TDE with BYOK for databases that are geo-replicated to maintain high availability requires to configure and test the scenario carefully. This post will go over the most common configuration options.

To avoid creating a single point of failure in active geo-replicated instances or SQL failover groups, it is required to configure redundant Azure Key Vaults. Each geo-replicated server requires a separate key vault, that must be co-located with the server in the same Azure region. Should a primary database become inaccessible due to an outage in one region and a failover is triggered, the secondary database is able to take over using the secondary key vault.

For Geo-Replicated Azure SQL databases, the



Seamlessly upgrade Azure SQL Data Warehouse for greater performance and scalability

Azure SQL Data Warehouse recently announced the preview release of the Optimized for Compute performance tier providing customers with an enhanced offering of the service. With this major release, the service now has a 5X increase in compute scalability and unlimited storage for columnar data. Along with the increased capacity, customers are realizing an average increase of 5X in performance for query workloads. For existing Optimized for Elasticity customers wanting to capitalize on these benefits, there is now an option to seamlessly upgrade via the Azure Portal. The easy to use upgrade experience via the Azure Portal has no downtime associated with exporting and reimporting of the data.

Upgrade to optimize for performance

You can now upgrade to the latest performance tier within the Azure Portal. This will result in no change to your connection string details:

To learn more about the upgrade process, visit our upgrade documentation. If you need help for a POC, contact us directly. Stay up-to-date on the latest Azure SQL DW news and features by following us on Twitter @AzureSQLDW.



Accelerate Data Warehouse Modernization to Azure with Informatica’s AI-Driven Platform

Data is central to digital transformation. We have seen many customers moving their data workloads to Azure which benefits from the inherent performance and agility of cloud. Enterprises are moving on-premises workloads to public cloud at an increasing rate. Results from the 2016 Harvey Nash/KPMG CIO Survey indicate that cloud adoption is now mainstream and accelerating as enterprises shift data-intensive operations to the cloud. Specifically, Platform-as-a-Service (PaaS) adoption is predicted to be the fastest-growing sector of cloud platforms according to KPMG, growing from 32 percent in 2017 to 56 percent adoption in 2020.

Cloud data warehouse is one of the fastest growing segments. Azure SQL Data Warehouse (SQL DW) allows customers to unleash the elasticity and economics of cloud while maintain a fast, flexible and secure warehouse for all their data.
Microsoft has partnered with Informatica, the leader in Enterprise Cloud Data Management, to help you modernize your data architecture with intelligent data management. So that you can build a cloud data warehouse solution that easily adapts and scales as your data types, volume, applications and architecture changes.

Informatica’s AI-driven Intelligent Data Platform, with solutions purpose-built for Azure, is a modular micro services architecture that accelerates your Azure SQL