Azure SQL Data Warehouse is an analytics platform known to be the backbone of an enterprise data warehouse. It allows for massively parallel processing while elastically and independently scaling compute and storage. Being a hub to a number of data marts and cubes SQL data warehouse integrates seamlessly with big data stores featuring tailored performance. High availability, compliance, advanced security, and tight integration fit most of the customer needs. Its global availability enables enterprises to replicate their data in over 30 regions.

Benefits of SQL Database as a spoke in a hub-and-spoke architecture with SQL Data Warehouse

  • increased overall solution concurrency while generating fewer queued queries;
  • improved analytics with Increased T-SQL language surface area;
  • globally distributed SQL Database instances with preserved elastic queries;
  • native SQL DW queries via external tables in SQL databases.

Such solutions represent the core business functions. Clean data is then used for reporting and analytics being scoped in aggregate form to certain time frames. It is possible to scale-out the aggregate data in a performant cost-effective fashion with the help of integration between the two services with an elastic query which in turn provides the ability to manage a hub and spoke solution on a global scale. Storing aggregate data in SQL database instances while maintaining a direct connection to SQL Data Warehouse to unleash the power of massively parallel processing will enable storage of even greater amounts of data:

Steps to Create SQL Database spoke

Step 1: deploying the template

Step one is the deployment of a template with a logical server name, location, admin username, admin password, database name (pre-existing SQL Data Warehouse), Num Spoke Db (the number of spoke database instances to be deployed) and the Deployment GUID (a GUID for the Runbook Deployment).

Step 2: deployment verification

The verification of deployment is the next step of SQL Database spoke creation. The deployment timing depends on the number of tables in the data warehouse and the numbers of databases provisioned. Upon successful deployment, one should see an automation account, runbook, elastic pool, and SQL Databases.

Step 3: verification of connections

Verification of connections and views and external tables is required after the deployment of everything. Meta schema with objects will appear at the end of the verification:

Step 4: elastic query

Check whether or not the query works as per your need by generating the query from external table to SQL Data Warehouse instances.

Learn more about the template and the integration with an elastic query. It helps to accelerate and demonstrate hub and spoke with SQL Warehouse. Code sample requirements can be learned with GitHub and user can alter the sample code to tailor the production needs.