PowerShell to create Azure SQL DBs

Recently I have been creating many Azure SQL databases via the portal where I started to find it quite tedious.

So I decided to find a scripted approach via PowerShell where I could just change some parameters and hit enter. I will go through the individual cmdlets that I use then provide the completed script.

The first step is to install Azure PowerShell; this will give you a set of modules that provide cmdlets to manage Azure.

You can download it from this link – https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

Once you have installed Azure PowerShell enter:


You will then be presented with a sign-in screen where you should enter your credentials.

Once you have signed in use the Get-AzureRmContext cmdlet to get the environment details used to authenticate Azure Resource Manager requests


From this you can confirm whether or not you are operating under the correct account and subscription ID.


If you have many subscriptions you should use Set-AzureRmContext cmdlet to select which subscription your PowerShell session should use.

Set-AzureRmContext -SubscriptionId "id"

So now we will do the following:

  • Create a resource group
  • Create a server
  • Create a firewall rule
  • Create the SQL database

First, let’s get a list of valid locations available to us.

Get-AzureRmResourceProvider -ListAvailable | Where-Object {$_.ProviderNamespace -eq 'Microsoft.Sql'}).Locations


We will now create a resource group within a valid location. For this example I will create MY_ENV resource group in North Europe.

New-AzureRmResourceGroup -Name "MY_ENV" -Location "North Europe"

Using the below I am creating a server called azuresqlserver01 within MY_ENV resource group.

New-AzureRmSqlServer -ResourceGroupName "MY_ENV" -ServerName "azuresqlserver01" -Location "North Europe" -ServerVersion "12.0"

I will now create a database called SQLDB12 that is standard S1 edition within my resource group MY_ENV on server azuresqlserver01.

New-AzureRmSqlDatabase -ResourceGroupName "MY_ENV" -ServerName " azuresqlserver01" -DatabaseName "SQLDB12" -Edition "Standard" -RequestedServiceObjectiveName "S1"


So next I will share the complete script that I personally use.



$Environment=(read-host "Please enter an Environment Name:"),

$server=(read-host "Please enter a SQL Server Name:"),

$databaseName=(read-host "Please enter a Database Name:"),


$DCLocation="North Europe",






New-AzureRmResourceGroup -Name $Environment -Location $DCLocation

New-AzureRmSqlServer -ResourceGroupName $Environment -ServerName $Server -Location $DCLocation -ServerVersion "12.0"

New-AzureRmSqlServerFirewallRule -ResourceGroupName $Environment -ServerName $Server -FirewallRuleName "clientFirewallRule1" -StartIpAddress $startIP -EndIpAddress $EndIP

New-AzureRmSqlDatabase -ResourceGroupName $Environment  -ServerName $Server -DatabaseName $databaseName -Edition "Standard" -RequestedServiceObjectiveName $databaseService





Write-Host "Something went wrong - confirm correct syntax see https://msdn.microsoft.com/en-us/library/azure/jj554330.aspx for help."

Write-Host "The error was: $LastError"

What I have done here is hard-code three parameters ( database edition, start IP address and end IP address) which for my situation won’t change but I have given the ability to pass in the environment name, SQL Server name and database name.

So a prompt will be presented to the user – here you should enter the relevant details and click enter.


So the environment gets created.


Next we get asked for the username and password for the SQL Server that you would like to setup.


Then finally the SQL Server and database gets created


Using the  above “completed” script the process is much quicker than using the portal. Next week I will share other cmdlets that I find useful.