In-Depth

Creating Azure SQL Databases with PowerShell and Azure Resource Manager Templates

Microsoft's premade templates allows for the deployment of SQL databases without having to use complex PowerShell commands.

• By Adam Bertram
• 09/19/2017

There are lots of ways to create MSSQL databases via PowerShell. We could invoke a T-SQL query using the CREATE DATABASE term, we could write some code to use the [Create() method with SMO] or, if we're putting this SQL server in Azure, we could even use the New-AzureRmSqlDatabase cmdlet included with the AzureRm PowerShell module. There are lots of ways to create SQL databases, but especially in Azure, there's a much better way that doesn't include knowing a bunch of PowerShell commands and parameters. By using Azure Resource Manager (ARM) templates, we are able to define the SQL database and how we want it created in a simple JSON text file. Once all of the attributes have been defined inside of the text file, we can then send this single file up to Azure and instruct it to perform its magic to get it done.

First of all, if you're not familiar with ARM templates, I highly suggest you start with this template post by Microosft. This article will go over the basics with you and explain the concept behind ARM templates in general. Once you're comfortable with creating ARM templates and how they're structured, you should then be familiarized with all of your options when it comes to creating Azure SQL Databases with ARM templates. We'll be covering everything necessary to create a simple database here, but we won't go over every option available to you.

Before you perform any Azure task with PowerShell, you must install the AzureRm PowerShell module (Install-Module -Name AzureRm) and authenticate to Azure properly using the Add-AzureRmAccount command. Also, I'm going to assume you've already got a SQL Server already brought up in Azure. We'll be provisioning a SQL database and attaching it to an existing SQL server. Once you're authenticated and have your SQL server created, you're now ready to begin building your SQL database ARM template.

ARM templates are built around resources. In ARM, everything is a resource. SQL databases are no exception. SQL databases are defined in ARM with the type of Microsoft.Sql/servers/databases and, at a minimum, must have five elements associated with it; name, apiVersion, location and a properties element with multiple optional elements available within the properties element.

For this article, we're going to be focusing on the minimum amount of effort to provision an Azure SQL Database. Refer to the options link above if you need to change any values.

To get started, I'll create an ARM template and call it MyFirstSQLDatabase.json. It will look something like this:

{

  "$schema":  "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",

  "contentVersion": "1.0.0.0",

  "parameters": {

  "databaseName": {

  "type": "string"

  },

  "serverName": {

  "type": "string"

  },

  "serverLocation": {

  "type": "string"

  },

  },

  "resources": [

  {

  "type": "Microsoft.Sql/servers/databases",

  "apiVersion": "2014-04-01-preview",

  "location": "[parameters('serverLocation')]",

  "name": "[concat(parameters('serverName'), '/',  parameters('databaseName'))]",

  "properties": {

  }

  }

  ]

  }

Notice that I've chosen to parameterize a lot of the values I'll be using to create the SQL Server. Creating parameters is a great habit to get into so that you can use the same template to create multiple SQL servers, all with different configurations.

After you've got the ARM template created, we now need to figure out a way to first replace those parameterized values with real values and send the template up to Azure to make it perform the creation. The only PowerShell command you're going to need to know to invoke an ARM deployment is the New-AzureRmResourceGroupDeployment command. Among other things, this command can fill in those parameterized values in your template and invoke an API request to Azure instructing it to do its bidding.

The New-AzureRmResourceGroupDeployment command has a TemplateParameterObject parameter where we can pass in the values to create our SQL database. Assuming that:

1. the database should be called MyFirstDatabase
2. the database should be put it in the West US Azure region
3. the SQL server name we built earlier is called mcmpagsqlserver
4. the resource group for the database should be adbtesting
5. the ARM template is located at C:.json

…we can fill in each of these parameters as hashtable keys in the TemplateParameterObject hashtable.

$paramObject = @{

  databaseName    =  'MyFirstDatabase'

  serverLocation  = 'westus'

  serverName      =  'mcmpagsqlserver'

}

We can now create a hashtable of all of the remaining parameters we need to pass to the command and finally invoke the operation.

$params = @{

  TemplateParameterObject = $paramObject

  ResourceGroupName = 'adbtesting'

  TemplateFile  = 'C:\MyFirstSQLDatabase.json'

  }

$result = New-AzureRmResourceGroupDeployment @params

For proper error control, we can also optionally check to ensure the deployment was successful by looking at the ProvisioningState properly after the deployment is complete.

if  ($DeploymentResult.ProvisioningState -eq 'Succeeded')

  {

  Write-Host  'SQL database deployment was successful!' -ForegroundColor 'Green'

  }

  else

  {

  Write-Error  -Message "SQL database deployment failed with error?:  $($result.Error)"

}

If you see the beautiful "SQL database deployment was successful!" text in your console, you know that the deployment was a success. Congrats! You're now the owner of a shiny, new Azure SQL Database and, more importantly, the owner of an ARM template that allows you to create an additional database on a whim.