Azure SQL Data Warehouse is Microsoft’s SQL analytics platform, the backbone of your enterprise data warehouse (EDW). 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 with 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 global availability in more than 30 regions.

One of the benefits of building solutions in the cloud is the flexibility that is gained from having numerous technologies at your disposal. This blog post overviews the usage of SQL Database as a spoke in a hub-and-spoke architecture with SQL Data Warehouse.

Benefits of using SQL Database with SQL DW

• Fewer queued queries while increasing overall solution concurrency
• Increased T-SQL language surface area for analytics
• Ability to query SQL DW natively through external tables in SQL Database
• Ability to globally distribute SQL Database instances while preserving elastic query

Data warehouse solutions exist to represent a clean integrated data set representing core business functions. The clean data is then used for reporting and data analytics. Reporting data is generally scoped to certain time windows, such as the last year or year’s worth of data in aggregate form. Data analytics is ad-hoc, and is over far more granular data.

If you would like to scale-out the reporting of aggregate data in a performant cost-effective fashion, our integration between the two services with elastic query provides the ability to manage a hub and spoke solution on a global scale. Store your hotter/aggregate data in highly concurrent SQL Database instances to serve reporting data while maintaining direct connection to SQL DW to harness the power of massively parallel processing (MPP), and the ability to store far greater amounts of data!

The example in the above diagram shows a data warehouse solution with the subject area SQL Database instances, which offload and maintain the more recent subject area data for fast reporting and insight. For users that need more data, they can always access the data warehouse data through elastic query. For the power users and data scientists, the data warehouse remains the source of truth for 360^o analysis of the business.

Create a SQL Database spoke instantly

If you’d like to explore creating a SQL Database as a spoke, consider deploying the template below. This template will deploy on a pre-existing SQL DW instance and will create a defined number of SQL Database spoke instances with all external tables referencing the SQL Data Warehouse tables pre-created based on a control table. The template deploys a user defined number of PremiumRS SQL Databases in an elastic pool, automation account, and a runbook.

Step 1 – Deploy the template

• Logical server name 
• Logical server location 
• Logical server admin user name
• Logical server admin password
• SQL DW database name: Name of your pre-existing SQL Data Warehouse
• Num Spoke Db: The number of spoke database instances you would like deployed
• Deployment Guid: A GUID for the Runbook Deployment. This must be unique per deployment. There are several services online to generate a GUID.

Step 2 – Verify the deployment

The deployment timing varies based on the number of databases provisioned and the number of tables in your SQL Data Warehouse. After deployment, you should see an automation account, runbook, elastic pool, and SQL Databases.

Step 3 – Verify the connections

Once everything has been deployed, verify that all the external tables, views, and stored procedures have been created. You should see several objects under the meta schema, within both the SQL Data Warehouse and SQL Database.

Within each SQL Database, you should find the meta.RemoteTableDefinitionView followed by an external table definition for each table within your SQL Data Warehouse.

Step 4 – Elastic query

Query one of the generated external table and validate that you can query from your SQL Database to your SQL Data Warehouse instance.

Step 5 – Learn more

Visit our documentation to learn more about hub and spoke architecture and elastic query. This template is a sample to help accelerate and demonstrate hub and spoke with SQL Database and SQL Data Warehouse. Specifically, this sample is used to demonstrate control tables to manage DB Elastic Query access, and generating external table definitions. Please stay tuned for more information on how to manage the materialization of data in these DBs to create a true concurrency reducing hub and spoke solution. Visit our GitHub to view and learn more about the code sample. We recommend that you alter the code samples to fit your needs before deploying to a production system.

If you need our 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.