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:

Add-AzureRmAccount

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

Get-AzureRmContext

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

qq1

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

qq2

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"

qq3

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

Param

(

$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:"),

$databaseService="S1",

$DCLocation="North Europe",

$startIP="10.xx.x04.xx",

$EndIP="10.00.0x0.xx"

)

Try

{

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

}

Catch

{

$LastError=$Error[0].ToString()

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.

qq4

So the environment gets created.

qq7

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

qq9

Then finally the SQL Server and database gets created

qq10

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.