Skip to content

Latest commit

 

History

History
326 lines (242 loc) · 9.42 KB

day.86.deploying.azure.sql.srv.azure.cli.md

File metadata and controls

326 lines (242 loc) · 9.42 KB
Raw

Day 86 - Deploying Azure SQL Server using the Azure CLI

Today we will cover how to deploy an Azure SQL Server in Azure and how to and how to import a Database using a BACPAC file using the Azure CLI.


NOTE: This article was tested and written for a Linux Host running Ubuntu 18.04 with Azure CLI installed.


The steps for today's article are below.

Deploy a new Resource Group
Generate a Password for the Azure SQL Server
Generate a 4-character Alphanumeric Surname for the SQL Server
Deploy the Azure SQL Server and Blank Database
Create a Storage Account to use to Import SQL Databases
Upload a Database BACPAC File
Import the Database into the Azure SQL Server
Things to Consider
Conclusion

SPONSOR: Need to stop and start your development VMs on a schedule? The Azure Resource Scheduler let's you schedule up to 10 Azure VMs for FREE! Learn more HERE

Deploy a new Resource Group

Using Azure CLI, run the following command to create a new Resource Group.

az group create \
--name 100days-azuredb \
--location westeurope

You should get back the following output:

{
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/100days-azuredb",
  "location": "westeurope",
  "managedBy": null,
  "name": "100days-azuredb",
  "properties": {
    "provisioningState": "Succeeded"
  },
  "tags": null,
  "type": "Microsoft.Resources/resourceGroups"
}

Generate a Password for the Azure SQL Server

Run the following command to generate a Password for the Azure SQL Server Admin User.

SQL_SRV_ADMIN_PASSWORD=$(cat /proc/sys/kernel/random/uuid)

Generate a 4-character Alphanumeric Surname for the SQL Server

Next, run the following command to generate a random 4-character set of alphanumeric characters.

RANDOM_ALPHA=$(cat /dev/urandom | tr -dc 'a-z0-9' | fold -w 4 | head -n 1)

If you are using a Mac, use the command below instead.

RANDOM_ALPHA=$(LC_CTYPE=C tr -dc 'a-z0-9' < /dev/urandom | fold -w 4 | head -n 1)

Deploy the Azure SQL Server and Blank Database

Run the following command to deploy the Azure SQL Server.

az sql server create \
--name "100days-azuresqlsrv-$RANDOM_ALPHA" \
--resource-group "100days-azuredb" \
--location "westeurope" \
--admin-user "sqladmdays" \
--admin-password $SQL_SRV_ADMIN_PASSWORD \
--query '[name,state]' \
--output tsv

You should get back something similar to the response below.

100days-azuresqlsrv-st4c
Ready

Next, run the following command to create a new Database on the SQL Server.

az sql db create \
--name "wide-world-imports-std" \
--resource-group "100days-azuredb" \
--server "100days-azuresqlsrv-$RANDOM_ALPHA" \
--edition Standard \
--family Gen5 \
--service-objective S2 \
--query '[name,status]' \
--output tsv

You should get back the following response when the database is finished deploying.

wide-world-imports-std
Online

Run the following command to Allow Azure Services and resources to access the SQL Server.

az sql server firewall-rule create \
--name "allow-azure-services" \
--resource-group "100days-azuredb" \
--server "100days-azuresqlsrv-$RANDOM_ALPHA" \
--start-ip-address "0.0.0.0" \
--end-ip-address "0.0.0.0"

You should get back a response similar to the output below.

{
  "endIpAddress": "0.0.0.0",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/100days-azuredb/providers/Microsoft.Sql/servers/100days-azuresqlsrv-st4c/firewallRules/allow-azure-services",
  "kind": "v12.0",
  "location": "West Europe",
  "name": "allow-azure-services",
  "resourceGroup": "100days-azuredb",
  "startIpAddress": "0.0.0.0",
  "type": "Microsoft.Sql/servers/firewallRules"
}

Next, run the following command to retrieve your Public IP Address.

MY_PUB_IP=$(dig +short myip.opendns.com @resolver1.opendns.com)

NOTE: You can also use a third-party site or Google "what is my ip address" to retrieve your Public IP Address and set it to the MY_PUB_IP variable.


Run the following command to create a new Firewall Rule on the Azure SQL Server to grant yourself access.

az sql server firewall-rule create \
--name "my-public-ip" \
--resource-group "100days-azuredb" \
--server "100days-azuresqlsrv-$RANDOM_ALPHA" \
--start-ip-address $MY_PUB_IP \
--end-ip-address $MY_PUB_IP

You should get back a response similar to the output below.

{
  "endIpAddress": "000.000.000.000",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/100days-azuredb/providers/Microsoft.Sql/servers/100days-azuresqlsrv-st4c/firewallRules/my-public-ip",
  "kind": "v12.0",
  "location": "West Europe",
  "name": "my-public-ip",
  "resourceGroup": "100days-azuredb",
  "startIpAddress": "000.000.000.000",
  "type": "Microsoft.Sql/servers/firewallRules"
}

NOTE: Without this firewall rule, you won't be able to import the SQL Database later.


Create a Storage Account to use to Import SQL Databases

Run the following command to create a new Storage Account

az storage account create \
--name "100daysqlimport$RANDOM_ALPHA" \
--resource-group "100days-azuredb" \
--location "westeurope" \
--query '[provisioningState,statusOfPrimary]' \
--output tsv

After the Storage Account has successfully provisioned, you should get back something similar to the response below.

Succeeded
available

Next, run the following command to create a container for the SQL BACPAC file(s).

az storage container create \
--name "bacpac-files" \
--account-name "100daysqlimport$RANDOM_ALPHA"

You should get back the following response.

{
  "created": true
}

Next, run the following command to retrieve the Storage Account Primary Key to use later.

AZ_STORAGE_PRIMARY_ACCOUNT_KEY=$(az storage account keys list \
--account-name "100daysqlimport$RANDOM_ALPHA" \
--query [0].value \
--output tsv)

Upload a Database BACPAC File

Run the following command to download the WideWorldImporters-Standard.bacpac file from GitHub.

wget https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Standard.bacpac

Next, run the following command to upload the WideWorldImporters-Standard.bacpac file to the Storage Account

az storage blob upload \
--name "WideWorldImporters-Standard.bacpac" \
--container-name "bacpac-files" \
--account-name "100daysqlimport$RANDOM_ALPHA" \
--file WideWorldImporters-Standard.bacpac

When the upload is finished, you should get back the following response.

Finished[#############################################################]  100.0000%
{
  "etag": "\"0x8D7A2884878390D\"",
  "lastModified": "2020-01-26T17:50:51+00:00"
}

Import the Database into the Azure SQL Server

az sql db import \
--name "wide-world-imports-std" \
--server "100days-azuresqlsrv-$RANDOM_ALPHA" \
--resource-group "100days-azuredb" \
--admin-user "sqladmdays" \
--admin-password $SQL_SRV_ADMIN_PASSWORD \
--storage-key-type "StorageAccessKey" \
--storage-key $AZ_STORAGE_PRIMARY_ACCOUNT_KEY \
--storage-uri "https://100daysqlimport$RANDOM_ALPHA.blob.core.windows.net/bacpac-files/WideWorldImporters-Standard.bacpac"

The Import process will take a few minutes to run. When it's completed you should get output similar to what is shown below.

{
  "blobUri": "https://100daysqlimportst4c.blob.core.windows.net/bacpac-files/WideWorldImporters-Standard.bacpac",
  "databaseName": "wide-world-imports-std",
  "errorMessage": null,
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/100days-azuredb/providers/Microsoft.Sql/servers/100days-azuresqlsrv-st4c/databases/wide-world-imports-std/extensions/import",
  "lastModifiedTime": "1/26/2020 9:50:51 PM",
  "name": "import",
  "queuedTime": "1/26/2020 9:36:48 PM",
  "requestId": "0f30e0e9-fcd9-4e02-b6fb-ad964e334050",
  "requestType": "Import",
  "resourceGroup": "100days-azuredb",
  "serverName": "100days-azuresqlsrv-st4c",
  "status": "Completed",
  "type": "Microsoft.Sql/servers/databases/extensions"
}

Things to Consider

We recommend that you review how Firewall Rules behave in Azure SQL Databases and what to keep in mind when importing BACPAC files to a database in Azure SQL Databases.


Conclusion

In today's article we covered how to deploy an Azure SQL Server in Azure and how to and how to import a Database using a BACPAC file. If there's a specific scenario that you wish to be covered in future articles, please create a New Issue in the starkfell/100DaysOfIaC GitHub repository.