Create an Azure SQL database with PowerShell

• Author
• Recent Posts

Adam Bertram

Adam Bertram is a 20-year IT veteran, Microsoft MVP, blogger, and trainer.

Latest posts by Adam Bertram (see all)

• Create a certificate-signed RDP shortcut via Group Policy - Fri, Aug 9 2019
• Monitor web server uptime with a PowerShell script - Tue, Aug 6 2019
• How to build a PowerShell inventory script for Windows Servers - Fri, Aug 2 2019

To do anything in Azure with PowerShell, you need to download the Azure Resource Manager (AzureRM) PowerShell module. This module is available in the PowerShell Gallery. You can download and install it with the Install-Module -Name AzureRm command. After installing this module, you'll then get all the commands necessary to create an Azure SQL database.

The first step is authenticating to your Azure account using the Connect-AzureRmAccount command, providing your username and optional password if this is the first time you're authenticating to Azure.

After authenticating, it's time to get down to the good stuff! Because I'll just be performing a demo, I'll create a new resource group to place my Azure SQL database in. This is an optional step. If you already have a resource group, you can use that one if you'd like.

New-AzureRmResourceGroup -Name TechSnips-SQL -Location EastUS

After creating the resource group, it's now time to create an Azure SQL server. All Azure SQL databases need to have an associated Azure SQL server to run under. To do that, we'll use the New-AzureRmSqlServer command. This command requires a few parameters. These include the resource group name to place it under, the name of the server, the location to provision it in, the administrator username and password, and the SQL server version this server will be running.

I first need to create a PSCredential object to use as the username and password for the SQL administrator account.

$sqlCred = Get-Credential

Once I have this, I can then pass all the values to the parameters to create the Azure SQL server.

$parameters = @{
    ResourceGroupName           = 'TechSnips-SQL'
    ServerName                  = 'snipsql'
    Location                    = 'EastUS'
    SqlAdministratorCredentials = $sqlCred
}
New-AzureRmSqlServer @parameters

This may take a minute to run, but when it finishes, it should return an object with all the attributes you had defined.

Creating an Azure SQL server

After creating the Azure SQL server, it's now time to create the database. The command to create the database is New-AzureRmSqlDatabase. This command is just as easy to run as New-AzureRmSqlServer is. It just requires the resource name again where to place it, the server name we just created earlier, and the name of the database. We can also define a parameter called RequestedServiceObjectiveName to define what kind of pricing or performance tier to place this database in.

$parameters = @{
    ResourceGroupName             = 'TechSnips-SQL'
    ServerName                    = 'snipsql'
    DatabaseName                  = 'demodb'
    RequestedServiceObjectiveName = 'S0'
}
New-AzureRmSqlDatabase @parameters

Once this completes, you should see an output similar to the below screenshot.

Creating an Azure SQL database

At this point, we've created the database, but you won't be able to connect to it. By default, Azure blocks all access to the database using their SQL server firewall rules. Luckily, we can punch a hole for us through this by using the New-AzureRmSqlServerFirewallRule command. With this command, we can provide the resource group name, the Azure SQL server name, the name of the firewall rule to create, and finally the start and end IP addresses.

I'd like to lock down access only to my own public IP address. If you don't know yours offhand, you can use a handy PowerShell one-liner to reach out to a web service to grab it:

$ipAddress = Invoke-RestMethod http://ipinfo.io/json | Select -exp ip.

You can then pass all the necessary parameter values to New-AzureRmSqlServerFirewallRule to create the rule and open up SQL access from your current IP address.

$parameters = @{
    ResourceGroupName = 'TechSnips-SQL'
    ServerName        = 'snipsql'
    FirewallRuleName  = 'AllowedIps'
    StartIpAddress    = $ipAddress
    EndIpAddress      = $ipAddress
}
New-AzureRmSqlServerFirewallRule @parameters

You should then be able to connect to your database with SQL Server Management Studio or whatever other method you prefer.