PowerShell How-To

Creating an Azure SQL Database with PowerShell

• By Adam Bertram
• 11/06/2018

When you need a SQL database fast, the cloud is the best way to go. Azure provides a SQL database as a Platform as a Service (PaaS) that eliminates the need to get an entire SQL Server up and running.

There are different ways to create a SQL database in Azure. We can use the Azure portal, or we can use PowerShell. Both methods have their advantages, but if you're writing a script to automate a task, PowerShell is the way to go.

To follow along with this article to create an Azure SQL database with PowerShell, you'll need:

• An Azure subscription
• An administrative account that has permissions to create a SQL database
• The AzureRm PowerShell modules from the PowerShell Gallery (Install-Module -Name AzureRm)
• A resource group

If you're not authenticated to Azure already, you can open up a PowerShell console and run Add-AzureRmAccount. This will prompt you for a username and password. Once authenticated, we can get down to business.

We first need to create an Azure SQL Server to host the database. We can do that using the New-AzureRmSqlServer command. In the below command, I'm creating an Azure SQL Server called ADBSQL in a resource group called DEMOSQL with an admin username of sqladmin and a password of p@$$word. Note that the name must be unique.

Notice on the first line that I have to convert the password to a secure string and create a PSCredential object. The SqlAdministratorCredentials parameter requires a PSCredential object.

PS> $cred = $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList 'sqladmin', $(ConvertTo-SecureString -String 'p@$$w0rd' -AsPlainText -Force))

PS> $parameters = @{
>>>    ResourceGroupName = 'DEMOSQL'
>>>    ServerName = 'ADBSQL'
>>>    Location = 'East US'
>>>    SqlAdministratorCredentials = $cred
>>>}
PS> New-AzureRmSqlServer @parameters

ResourceGroupName        : DEMOSQL
ServerName               : adbsql
Location                 : eastus
SqlAdministratorLogin    : sqladmin
SqlAdministratorPassword :
ServerVersion            : 12.0
Tags                     :
Identity                 :
FullyQualifiedDomainName : adbsql.database.windows.net

Next, I need to be able to access my SQL database on the server so I'll add a firewall exception to allow this. In the example below, I'm referencing the SQL Server I just created and setting the only IP address allowed to be X.X.X.X, which is my public IP address. Notice that the server name is lowercase. This is intentional. The cmdlet will not allow any uppercase letters.

PS> $parameters = @{
>>>    ResourceGroupName = 'DEMOSQL'
>>>    ServerName = 'adbsql'
>>>    FirewallRuleName = 'AllowedIps'
>>>    StartIpAddress = '74.128.210.166'
>>>    EndIpAddress = '74.128.210.166'
>>>}
PS> New-AzureRmSqlServerFirewallRule @parameters

ResourceGroupName : DEMOSQL
ServerName        : adbsql
StartIpAddress    : 74.128.210.166
EndIpAddress      : 74.128.210.166
FirewallRuleName  : AllowedIps

Finally, we can create the SQL database placing it on the SQL Server we just created. In this example, I've chosen to make it with an S0 performance level.

PS> $parameters = @{
>>>    ResourceGroupName = 'DEMOSQL'
>>>    ServerName = 'adbsql'
>>>    DatabaseName = 'demodb'
>>>    RequestedServiceObjectiveName = 'S0'
>>>}
PS> New-AzureRmSqlDatabase @parameters

ResourceGroupName             : DEMOSQL
ServerName                    : adbsql
DatabaseName                  : demodb
Location                      : East US
DatabaseId                    : ec35b513-b1f3-4f8d-9951-17912ab8b6c1
Edition                       : Standard
CollationName                 : SQL_Latin1_General_CP1_CI_AS
CatalogCollation              :
MaxSizeBytes                  : 268435456000
Status                        : Online
CreationDate                  : 5/12/2018 4:09:49 PM
CurrentServiceObjectiveId     : f1173c43-91bd-4aaa-973c-54e79e15235b
CurrentServiceObjectiveName   : S0
RequestedServiceObjectiveId   : f1173c43-91bd-4aaa-973c-54e79e15235b
RequestedServiceObjectiveName :
ElasticPoolName               :
EarliestRestoreDate           : 5/12/2018 4:40:33 PM
Tags                          : {}
ResourceId                    : /subscriptions/5abad358-f34a-4fdd-bd5b-5098154e267f/resourceGroups/DEMOSQL/providers/Microsoft.Sql/servers/adbsql/databases/demodb
CreateMode                    :
ReadScale                     : Disabled
ZoneRedundant                 : False

At this point, your newly created Azure SQL database is ready to go!