Create an Azure Database using Powershell

In my presentation on Windows Azure SQL Database and SSIS I show how to create a database using the Azure Portal and using T-SQL from SQL Server Management Studio. Using the portal us about as simple as it gets so I’m not going to go through the steps here. To use T-SQL you have to be connected to the master database and then you can quickly create a new database with the CREATE DATABASE command.

CREATE DATABASE <database_name>
       (EDITION = 'Web', MAXSIZE=1GB)

The edition can be either Web or Business and the only difference I am aware of is the maximum database size. The Web edition can only be up to 5GB and a Business edition has a max size of 150GB.

In my presentation I always mention that PowerShell can also be used to create a database, but I don’t show include a PowerShell example. I don’t use PowerShell as often as I probably should, but this seemed like an easy script to put together. As with most things in the world of programming there is more than one solution. My first example here  uses PowerShell, but it is really just a wrapper for the CREATE DATABASE statement I used above. If you are following along at home, to run this script the IP address of the machine where it is run must be added to the allowed IP address list in the Azure portal.

$connectionString = "Server=tcp:[ServerName]; Database=master;User ID=[UserName]@[ServerName];Password=[Password]; Trusted_Connection=False;Encrypt=True;" 
$connection = New-Object System.Data.SqlClient.SqlConnection 
$connection.ConnectionString = $connectionString 
$command = New-Object System.Data.SQLClient.SQLCommand 
$command.Connection = $connection 
$command.CommandText = "CREATE DATABASE SQLMD_PS_TEST (Edition = 'Web',MAXSIZE = 1GB)" $command.ExecuteNonQuery() 

With some quick changes this could be configured to pass in the servername, user info, and database name as parameters. This does give it some advantage over the T-SQL method, but you have to hard code the password in a script which doesn’t seem like a good method.

There are Azure cmdlets that you can use to create a database, but you need to download them first – Windows Azure PowerShell. Fair warning it took me about 20 minutes and two reboots to install these.

$cred = Get-Credential 
$ctx = New-AzureSqlDatabaseServerContext -ServerName "[ServerName]" -Credential $cred 
New-AzureSqlDatabase $ctx -DatabaseName "[DatabaseName]" -MaxSizeGB 1 -Edition Web

This is a short script that uses new Azure cmdlets to first create a server context and then create a database on that server. There are cmdlets to create a new server if you don’t have one, but I used an existing one in my testing and replaced [ServerName] with my WASD Server name.

Of course this is sort of a boring use for PowerShell. Since the strength of PowerShell is to manage large numbers of database I decided to write a script to create a lot of databases. 150 is the limit for the number of databases on an Azure Server so that seemed like a good number.

$cred = Get-Credential 
function CreateAzureDB($DBName, $Edition, $MaxSize, $cred) 
{ $ctx = New-AzureSqlDatabaseServerContext -ServerName "[ServerName]" -Credential $cred 
New-AzureSqlDatabase $ctx -DatabaseName $DBName -MaxSizeGB $MaxSize -Edition $Edition } 

$DB_Name = "[DatabaseName]" 
do { CreateAzureDB $DB_Name$i Web 1 $cred 
$i++ } 
while ($i -lt 150)

The script used a short function, CreateAzureDB, to call the New-AzureSqlDatabase cmdlet and created 150 databases (DatbaseName0,DatabaseName1, …,DatabaseName148) in about 7 minutes. Actually, it created 149 databases because this experiment with PowerShell brought to my attention something I didn’t know until now – the master database is included in the 150 database limit. When my script attempted to create DatabaseName149 I got an error saying I had reached the limit for the number of databases on this server.

I should point out that these scripts have the user interactively log in with an Azure database user that has enough permission to create a table. To have it run without interaction you would need to set up a certificate in Azure and use that as the credential, which is a topic for another day.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s