Modern Data Architecture – Part 4 – Setting up a SQL Server

Before we move on to loading data into our Azure Data Lake, we will need a data source to simulate pulling data from as to mirror a production system. For this exercise, we will walk through creating your own SQL server database and loading it with sample data.

This is the first resource we care creating that will bill ongoing in the background, so we will want to be sure we deprovision this resource once we are done with it. The tier we will be using is the basic database at $5.00 a month, so this lab should only cost a few cents.

Prerequisites

You will want to install and set up SQL Server Management Studio prior to this completing this section

Create a SQL Server and SQL Database in Azure

In the search bar, search for “SQL Server”

Select “+ Add” to create a new SQL Server
Fill in the necessary details and select “Review and Create”
- Subscription – The default subscription you set up for the demo
- Resource Group – The training group resource group “training_resourcegroup_yourname” you created for the lab
- Database Details
  - Database Name – “training_sqldatabase_yourname”
  - Server – Create New
    - Server Name – “training-sqlserver-yourname”
      - Underscores are not allowed, use dashes
    - Server Admin Login – create a unique id
      - Record this somewhere as we will need it for later in the lab
      - sqlserveradmin for this lab sample if needed
    - Password – secure password
      - Record this somewhere as we will need it for later in the lab
    - Location – US East 2
Want to Use SQL Elastic Pool – No
Compute + Storage
- Select Configure.
- Select Basic. This will ensure you are using the cheapest tier of databases to mitigate costs.

Once completed, the SQL Server and Database resource will be available from the dashboard. Click on the server to navigate to details.

Setting Security and Firewalls

Click on the server name to begin to configure database rules

Select “Show Firewall Settings”

Select “Add client IP” to whitelist the current IP of your computers location and select “Save”. Add any other IP that may need access. A simple google search of “what is my IP” will return the IP address required.

Set Allow Azure Services and resources to access server to “Yes”. This will allow Azure Data Factory and other data connections to connect.

Creating a Sample Database

Open SQL Server Management Studio. In the login prompt, enter the server name from Azure and the username and password that was configured. Be sure to use “SQL Server Authentication” in the Authentication Setting

Upon successful login, your databases should be available

Select “New query and set the database to your training database

From the Sample Data Folder, run the following two scripts. Be sure the database connection does not revert back to “master” as updated in the previous step.
- BikeStores Sample Database – create objects
- BikeStores Sample Database – load data
Tables will now appear in your database which you can query and review once the scripts complete running.

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 4 – Setting up a SQL Server