Netreo is now BMC. Read theBlog

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

By: Netreo
June 12, 2018

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:

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

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.

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

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:

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

Step 4: elastic query

Check whether 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.

Find out how Netreo can help with all Azure monitoring needs. Request a Demo Today

Ready to get started?

Get in touch or schedule a demo

Get Started Learn More