DB-EnginesInfluxDB: Focus on building software with an easy-to-use serverless, scalable time series platformEnglish
Knowledge Base of Relational and NoSQL Database Management Systemsprovided by solid IT

Blog > Post

Azure SQL Data Warehouse, the hub for a trusted and performance optimized cloud data warehouse

by  Arnaud Comet, Microsoft (sponsor) , 1 November 2017
Tags: Data warehouse, Microsoft Azure SQL Data Warehouse, Relational DBMS

This is a sponsored post.

Today, with the proliferation of data sources and the need to be data-driven, it is hard for companies to avoid siloed sources of information. Siloed data marts can represent an important increase in cost and potentially business opportunities. Centralizing data to provide more complete datasets to your internal departments, suppliers or customers can become a competitive advantage. In the context of Hub & Spokes, Azure SQL Data Warehouse is optimized to provide the greatest experience as a hub thanks to its native integration with Azure SQL Database and Analysis Services (the spokes).

Azure Hub and Spoke

#AzureSQLDW is Microsoft’s SQL analytics platform, the backbone of your Enterprise Data Warehouse. The service is designed to allow customers to elastically (and independently) scale compute and storage with massively parallel processing. SQL DW integrates seamlessly with big data stores and acts as a hub to your data marts and cubes for an optimized and tailored performance of your EDW. Azure SQL DW offers guaranteed 99.9% high availability, compliance, advanced security, and tight integration to upstream and downstream services so you can build a data warehouse that fits your needs. Azure SQL DW is the first and only service enabling enterprises to replicate their data everywhere with a global availability in more than 30 regions.

Building a Data Warehouse on Azure goes beyond the use of SQL DW. The next few blog posts that we will publish on Azure Blog will demonstrate the benefits of the Hub & Spoke model for your data warehouse solution. With Azure, you can truly customize your data warehouse solution and optimize for enterprise dashboarding, reporting, or data discovery. Every Cloud Data Warehouse solution has inherent challenges that these Hub & Spoke architectures are designed to overcome. SQL DW stands out over any competing cloud alternative in building a Hub & Spoke very easily. These blog posts will focus around the integration between SQL DW, SQL DB, Azure Analysis Service and Azure Functions. With these four components, you can build a data warehouse solution that will eliminate any concerns on concurrent user access while providing a fine grain control over price and performance. We will give you a step-by-step approach, 1-click template and code to optimize your data warehouse. We are making the integration between SQL DW and other key cloud services seamless and easy. We will close this blog mini-series with a discussion of the unique cost benefits of building such an architecture.

Today, before explaining the Hub & Spoke architecture, you should think of which service can fit your analytics needs.

Right choice between #SQL DW, SQL DB and Analysis Services


Right choice between #SQL DW, SQL DB and Analysis Services

As a Massive Parallel Processing data warehouse, SQL DW works the best with some scale (at least 2 TBs) as the data will be distributed across many nodes that run in parallel. SQL DW has a variety of optimizations to provide the best query performance against very large data sets. It provides an excellent scale-out option as your data and query complexity grows. However, if you do not plan to store more than 2-3TBs at any point in-time, we recommend leveraging SQL DB. For extremely low latency and high-performance needs, we recommend caching your data with Azure Analysis Services (AAS). AAS can work directly with SQL DW or with SQL DB.

Use cases for SQL DB, SQL DW and Analysis Services


Hub and Spoke Model: customize and tune up your Data Warehouse!

Many enterprise data warehouse owners find themselves to be stewards of solutions at one or the other end of the spectrum, between something centralized and really hard to manage or an architecture chaotic with siloed data marts. Whether your warehouses are rigidly monolithic or chaotically distributed, they can be locked into heavy investments and spiraling costs. The need for change is triggered by degradation in performance, changes in business practices, increasingly stringent governance, increases in users, or any combination of these scenarios, all of which underscore the importance of a scalable, agile environment. 
Hub & Spoke technology from Azure allows you to choose a flexible, scalable environment that aligns with both management and delivery needs. Microsoft Cloud Data Platform tools architectural features such as Elastic Query make this a fast and low risk process. By offloading your data warehouse and enabling auto-scale through Azure Functions, you will truly lower your TCO while providing an optimal performance based on the workloads.

Hub and Spoke – How to structure your data warehouse


Direct Benefits from Hub and Spoke architecture on Azure

We have a customer that had initially very fragmented data on-premises due to siloed data marts. By moving to the cloud, they wanted to use SQL DW to be the central repository of their Data Warehouse. Initially, they relied on a monolithic data warehouse where SQL DW would be covering all the analytics need within the company. Concerns started rising around cost, performance and concurrency. Our team helped them pivot their monolithic Data Warehouse into a Hub and Spoke model that provided the right architecture and SLAs to all the department and teams while having a central repository to control the information flow of its enterprise. Thanks to a decrease in provisioning of SQL DW capacity, the overall cost of the solution dropped (despite the additional resource needed with SQL DB and Azure Analysis Services). More to come around the cost benefit and more technical information in the next few weeks on Azure Blog!
If you need our help for a POC, contact us directly by clicking here. Stay up-to-date on the latest Azure SQL DW news and features by following us on Twitter @AzureSQLDW. Next week, we will feature the deeper integration between Azure Analysis Services and SQL DW.


About the Author:
Arnaud Comet

Arnaud Comet is a Program Manager for Azure SQL Data Warehouse. His objective is to make the Big Data and Data Warehouse experience simple, affordable and accessible to any size of organization. Prior to covering Azure SQL Data Warehouse, Arnaud was a Program Manager for Azure Cosmos DB and a strategist for the Azure Data Platform.


Share this page

Featured Products

MariaDB logo

SkySQL, the ultimate
MariaDB cloud, is here.

Get started with SkySQL today!

Datastax Astra logo

Bring all your data to Generative AI applications with vector search enabled by the most scalable
vector database available.
Try for Free

Redis logo

The world’s most loved real‑time data platform.
Try free

SingleStore logo

The database to transact, analyze and contextualize your data in real time.
Try it today.

Neo4j logo

See for yourself how a graph database can make your life easier.
Use Neo4j online for free.

Present your product here