Disaster Recovery Solution to Azure SQL Database

-- Listing 1
-- NTS_DR\INST01: MS SQL Instance for Disaster Recovery
-- replace “NTS_xxx” with the value appropriate to your environment 
--
USE [master]
RESTORE DATABASE [NTS_TESTDB] FROM DISK = N'NTS_BACKUP_DIR\NTS_TESTDB.BAK' WITH  FILE = 1, MOVE N'NTS_TESTDB' TO N'NTS_DATA_DIR\NTS_TESTDB.mdf',  MOVE N'NTS_TESTDB_log' TO N'NTS_LOG_DIR\NTS_TESTDB_log.ldf',  NOUNLOAD,  STATS = 5
GO

# Listing 2
# NTS_PROD\INST01: PRODUCTION MS SQL Server Instance
# NTS_DR\INST01: MS SQL Server Instance for Disaster Recovery. 
# Powershell
#clean up orphaned user. Should be run as regular task on your database.
Get-DbaDbOrphanUser -SqlInstance NTS_PROD\INST01
Remove-DbaDbOrphanUser -SqlInstance NTS_PROD\INST01

#export user login with permissions
Export-DbaUser -SqlInstance NTS_PROD\INST01 -FilePath C:\temp\NTS_PROD_users.sql

#If your demo DR environment is clean, normally after the restore step, all the user should be orphaned, as the user are not present in the DR MS SQL Instance.
# so clean up all orphaned user from your DR Instance
Remove-DbaDbOrphanUser -SqlInstance NTS_DR\INST01

#listing 3
# Create the BACPAC-File
# I share some error message with you in the screenshot.
SqlPackage /Action:Export /TargetFile:"N:\TS\Az\NTS_TESTDB.bacpac" /SourceConnectionString:"Server=tcp:NTS_DR\INST01,1433;Initial Catalog=NTS_TESTDB;Integrated Security=true;MultipleActiveResultSets=False;encrypt=false;TrustServerCertificate=False;Connection Timeout=30;" /DiagnosticsFile:"N:\TS\Az\NTS_TESTDB.log" /MaxParallelism:4

### listing 4
### I test using PowerShell
###

### the following parameter will be needed

$ntsSubscriptionId=""
$ntsLocation = ""
$ntsResourceGroup="nts-bacpac-rg-x1234"
$ntsStorageAC="ntsbacpacx1234"
$ntsStorageContainer="containerx1234"
$ntsStorageACKey=""

### the URI to the BACPAC in the storage account container 
$ntsBacpacStorageURI = ""

### login to azure
az login

### Search for your subscription. Just enter a part of the subscriptions name
### just if you need to choose another subscription

$ntsSubscriptionId = az account list --query "[?contains(name,'REPLACE_YOUR_STRING_TO_SEARCH')].{Company:name, TenantId:tenantId,   ID:id}  | [].ID" --output tsv

### check that the subscription is correct
$ntsSubscriptionId

### Search for the location you want to use

$ntsLocation = az account list-locations --query "[?contains(displayName,'Switzerland N')].{DisplayName:displayName, Name:name} | [].Name " --output tsv

### check that the location is correct
$ntsLocation


###
echo "Creating $ntsResourceGroup in $ntsLocation..."
az group create --name $ntsResourceGroup --location $ntsLocation 

### maybe you want to check if your name is already in use....
# az storage account check-name --name $ntsStorageAC

echo "Creating $ntsStorageAC..."
az storage account create --name $ntsStorageAC --resource-group $ntsResourceGroup --location "$ntsLocation" --sku Standard_LRS

### get the storage account key
$ntsStorageACKey = az storage account keys list --account-name $ntsStorageAC --resource-group $ntsResourceGroup  --query [0].value -o tsv 

### check the key
$ntsStorageACKey

### create the storage container
az storage container create --name $ntsStorageContainer --account-key $ntsStorageACKey --account-name $ntsStorageAC

# listing 5
# azcopy run in Powershell
# include all the information/credential to acces the container
# This Connection String can be get from see screenshot
$ntsBacpacDestination = "FULL_SAS_URI_GET_THE_VALUE_FROM_THE_PORTAL"

$ntsBacpacSource = " N:\TS\Az\NTS_TESTDB.bacpac"
# go to azcopy.exe dir
#cd C:\dbi\azcopy
.\azcopy.exe copy $ntsBacpacSource $ntsBacpacDestination  --recursive=false

### Listing 6

###
### I can start some preparation for the azure sql database
### Check which service-objective are available in the selected region
az sql db list-editions -a -o table -l $ntsLocation

$ntsServer = "ntssqlserverx1234"
$ntsAdminuser = "ntsadminsql"
$ntsAdminuserpassword = "NTSp@ssw0rd"

az sql server create -l $ntsLocation -g $ntsResourceGroup  -n $ntsServer -u $ntsAdminuser -p $ntsAdminuserpassword 

### please change the name to what you prefere
$ntsEmptyDB = "NTSDBTOIMPORT"
### Very Important: You should use for the import a high service-objective to make the import performant.
### After the import you can scale down the service-objective to the one you need
az sql db create -g $ntsResourceGroup -s $ntsServer -n $ntsEmptyDB --service-objective S9

### you can also get this information from the azure portal.
$ntsBacpacStorageURI = "https://ntsbacpacx1234.blob.core.windows.net/container-x1234/NTS_TESTDB.bacpac"
### create the database skeleton if not already exist
#az sql db create -g $ntsResourceGroup -s $ntsServer -n $ntsEmptyDB --service-objective S9

### !!! IMPORTANT!!! DO NOT FORGET TO ENABLE "azure service access"  >>> screenshot 5
az sql db import -s $ntsServer -n $ntsEmptyDB -g $ntsResourceGroup -p $ntsAdminuserpassword -u $ntsAdminUser  --storage-key-type StorageAccessKey --storage-uri $ntsBacpacStorageURI 	--storage-key $ntsStorageACKey 

### Listing 7
###
### Final step
### you should scale the service-objective to the appropriate one 
az sql db update -g $ntsResourceGroup -s $ntsServer -n $ntsEmptyDB --service-objective S1

# move to Cool access tier
$ntsBlobname =  '/NTS_TESTDB.bacpac'
az storage blob set-tier --account-name $ntsStorageAC --container-name  $ntsStorageContainer  --name  $ntsBlobname  --tier Cool  --account-key $ntsStorageACKey