Creating Premium, Standard, and Basic Databases with PowerShell

  • Article

With the introduction of the new Azure SQL DB service tiers comes the desire to programmatically provision these new service tiers – more specifically, Basic, Standard (S1, S2), and Premium (P1, P2, and P3) databases. This blog post will provide the basic steps to create a new SQL DB service tier (and old) via a PowerShell script.  While the example script below demonstrates the creation of a Standard S2 database, the script can easily be modified to create any Basic, Standard, Premium, Web or Standard database.

Prerequisites:

  • Install PowerShell (example script below was performed on PowerShell version 4.0 – current PowerShell version can be found by executing $PSVersionTable from the PowerShell command line
  • Install Azure PowerShell
  • Register for the New Service Tiers preview feature for SQL Database
  • SQL database server that supports database editions “Basic, Standard, Premium”

PowerShell Script:

<# .SYNOPSIS Create a Standard database with an S2 performance level .PARAMETER Name N/A .NOTES Last Updated: 5/25/2014 #> # Azure SQL Database server (Ex: bzb98er9bp) $ServerName = "<ServerName>" # Username for Azure SQL Database server $ServerLogin = "<ServerLogin>" # Password for Azure SQL Database server $serverPassword = "<ServerPassword>" # Name of new database to be created $NewDbName = "MyS2Db" # Establish credentials for Azure SQL Database Server $Servercredential = new-object System.Management.Automation.PSCredential($ServerLogin, ($ServerPassword | ConvertTo-SecureString -asPlainText -Force)) # Create connection context for Azure SQL Database server $CTX = New-AzureSqlDatabaseServerContext -ManageUrl “https://$ServerName.database.windows.net” -Credential $ServerCredential # Specify the specific performance level for the $NewDbName $PerformanceLevel = Get-AzureSqlDatabaseServiceObjective -Context $CTX -ServiceObjectiveName "S2" # Create new Basic database New-AzureSqlDatabase -ConnectionContext $CTX -DatabaseName $NewDbName -Edition "Standard" -MaxSizeGB 10 -ServiceObjective $PerformanceLevel  

Success looks like the following:

The creation of the new database will take a few moments to complete and presence of the new database can be seen in the Azure Portal immediately.  The progress of the readiness of the new database can further be tracked with the following command:

Get-AzureSqlDatabase $CTX –DatabaseName $NewDbName

Once the Standard S2 database has been fully provisioned, success looks like the following:


 

To enumerate all the available database service tiers and their respective performance levels, one augment the example script with the following (and omit the New-AzureSqlDatabse command):

Get-AzureSqlDatabaseServiceObjective -Context $CTX

The same script can be also be used to provision Basic or Premium database too. To create a Basis database, the example script can be modified as follows:

# Specify the specific performance level for the $NewDbName
$PerformanceLevel = Get-AzureSqlDatabaseServiceObjective -Context $CTX -ServiceObjectiveName "Basic"

# Create new Basic database
New-AzureSqlDatabase -ConnectionContext $CTX -DatabaseName $NewDbName -Edition "Basic" -MaxSizeGB 2 -ServiceObjective $PerformanceLevel