I’ve created a new GitHub repository to track various Azure DB projects I’m working on in my spare time. Azure DB’s fully managed nature makes it somewhat different than traditional Infrastructure as a Service (IaaS) installs. There are some features (like ETL) which are not available to end users which means customers may need to reconsider some architectural decisions. My plan is to work on the basics in addition to more advanced topics related to the architectural changes. Like any side project, I can’t quite commit to timelines, but as long as I keep coming up with questions I’d like to answer, I’ll keep adding. The first project is to build a SQL DB via code. The beauty of doing this in the public via GitHub is that anyone can fork the code, or contribute.

POC 1 - Create an Azure DB

Before anything can happen, the DB must be created. Using code to create the DB allows the DB to be consistently created every time. The code for this project can be found here https://github.com/nathaniel-avery/Azure_SQL_DB_Lessons . The code is a modified version of code found on Microsoft’s site. The original version can be found at this link https://docs.microsoft.com/en-us/azure/sql-database/scripts/sql-database-create-and-configure-database-powershell

The differences are minor. I’ve changed a few of the variables, so that they more appropriately fit my use of Azure. For example, I set the $location variable to “useast” instead of “southcentralus.“ Other changes include the $resourcegroupname, and $servername.

The script follows a logical pattern of setting up the variable, then completing the actions.

Variable Setup

# Set the resource group name and location for your server

$resourcegroupname = "rg-AzureSQLDB-JSON"

$location = "eastus"

# Set an admin login and password for your server

$adminlogin = "ServerAdmin"

$password = "ChangeYourAdminPassword1"

# Set server name - the logical server name has to be unique in the system

$servername = "serverazuresqldb01"

# The sample database name

$databasename = "mySampleDatabase"

# The ip address range that you want to allow to access your server

$startip = "0.0.0.0"

$endip="0.0.0.0"

Actions

# Create a resource group

$resourcegroup = New-AzureRmResourceGroup -Name $resourcegroupname -Location $location

# Create a server with a system wide unique server name

$server = New-AzureRmSqlServer -ResourceGroupName $resourcegroupname `

          -ServerName $servername `

          -Location $location `

          -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

# Create a server firewall rule that allows access from the specified IP range

$serverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname `

        -ServerName $servername `

        -FirewallRuleName "AllowedIPs" -StartIpAddress $startip -EndIpAddress $endip

# Create a blank database with an S0 performance level

$database = New-AzureRmSqlDatabase  -ResourceGroupName $resourcegroupname `

       -ServerName $servername `

       -DatabaseName $databasename`

As stated earlier, this project is 100% open. Let me know if there are any suggestions for improvement, or for future projects based on Azure DB.

Resources

Designing Extract, Load, and Transform (ELT) for Azure SQL Data Warehouse - https://docs.microsoft.com/en-us/azure/sql-data-warehouse/design-elt-data-loading

Use PowerShell to create a single Azure SQL database and configure a firewall rule - https://docs.microsoft.com/en-us/azure/sql-database/scripts/sql-database-create-and-configure-database-powershell