Modern Data Architecture – Part 8 – Provisioning a Synapsis SQL Data Warehouse

Azure Synapse is a limitless analytics service that brings together enterprise data warehousing and Big Data analytics. It gives you the freedom to query data on your terms, using either serverless on-demand or provisioned resources—at scale. Azure Synapse brings these two worlds together with a unified experience to ingest, prepare, manage, and serve data for immediate business intelligence and machine learning needs.

With Azure Synapse, data professionals can query both relational and non-relational data using the familiar SQL language. This can be done using either serverless on-demand queries for data exploration and ad hoc analysis or provisioned resources for your most demanding data warehousing needs. A single service for any workload.

From the Azure Portal Search, Find and select “Azure Synapse Analytics” and select “+ Add”

From the Setup Screen, Configure the Basic Settings
- Subscription – The subscription you previously setup
- Resource Group = “training_resourcegroup_yourname”
- SQL pool name – “training_sqlpool_yourname’
- Server – “training-sqlserver-yourname”
- Performance – Be sure to scale this down to DW100c to limit cost as this will incur charges when not in use

Select “Review and Create” to provision the resource. This may take a few minutes to complete.
Once completed, in the dashboard for your new Synapse environment you will be able to pause and resume the service. This will help with costing and scaling. In the common task, you can explore some options but we do not have any data yes so we will come back to this later in the labs.

Open SQL Server Management Studio and log back in using the same server credentials from lab 4. Setting up a SQL Server.

You will not see your Synapsis SQL Server Environment listed

Run the script “Create_DW_Tables.sql” located in the Datafiles folder included with this lab. This will create the empty tables we need for later in the lab. Be sure to set the Database to “training_sqlpool_yourname” before running the query. Once complete, you can refresh your database and will see the tables listed on the left hand side.

Be sure to check out my full online class on the topic. A hands on walk through of a Modern Data Architecture using Microsoft Azure. For beginners and experienced business intelligence experts alike, learn the basic of navigating the Azure Portal to building an end to end solution of a modern data warehouse using popular technologies such as SQL Database, Data Lake, Data Factory, Data Bricks, Azure Synapse Data Warehouse and Power BI. Link to the class can be found here or directly here.

Part 1 – Navigating the Azure Portal

Part 2 – Resource Groups and Subscriptions

Part 3 – Creating Data Lake Storage

Part 4 – Setting up an Azure SQL Server

Part 5 – Loading Data Lake with Azure Data Factory

Part 6 – Configuring and Setting up Data Bricks

Part 7 – Staging data into Data Lake

Part 8 = Provisioning a Synapse SQL Data Warehouse

Part 9 – Loading Data into Azure Data Synapse Data Warehouse

Modern Data Architecture – Part 8 – Provisioning a Synapsis SQL Data Warehouse