More Azure Database Creation in Powershell

I wrote about Azure SQL Database for T-SQL Tuesday #82, but I had a few mistakes in my process, at least from a cost standpoint. Since I’ll probably use this a bit more as I flesh out some CI/CD, I decided to document a bit more about the database creation process.

The Old Code

One of the problems I noticed right away was that I created an S2 level database in my post, which was too large. That wasn’t needed, and it might eat up my database credits. My original code was:

New-AzureRMSqlDatabase –ResourceGroupName “Default-SQL-WestEurope” –ServerName “dkranchapps” –DatabaseName “TSQLTuesday” –Edition Standard –RequestedServiceObjectiveName “S1”

In here, there are a few things that are obvious to me, and some that might not be. I won’t delve into detail on how to choose some of these, but I’ll define what they mean. There is a doc page for the cmdlet, which I used to check what I knew.

ResourceGroupName

This is the way you organize Azure resources. In my case, I have one resource group, which I named “Default-SQL-WestEurope”. This was the first group I made, intending this to be for SQL servers, and it’s in the West Europe region. Hence the name. 

ServerName

This is self explanatory, but you create servers to host resources in Azure. The one I created a long time ago for experiments was “dkranchapps”. I re-used it here, though I could have created another one.

DatabaseName

I’m not defining this. If you don’t know, stop reading.

Edition

This is the basic edition for your database. There are multiple options, but I used “Standard”, which was in the sample documentation code. However, a Standard incurs a charge from $0.0202/hr to $0.2016/hr. That doesn’t sound like much, but there are 720 hours in a 30 day month. That’s $14.44 to $145.15 a month. Again, not much, but this is per database. This could cause me issues with my $150/month credit.

ServiceObjectiveName

This is the scale within the Edition. I’m not sure why this the Edition is needed if we specify this here, but we can use a name or a value (GUID). In my case, I neglected to check the S2 syntax when I made the database. What I’d want for most things is a Basic or maybe an S level if this is short lived.

Other Options

There are other options. I can specify the subscription in a parameter, do a “WhatIf” to see what this would do, Force the command without user conformation, set a maximum size, and more. 

While you might not need these options, I’d encourage you to look through the list and make sure that it’s not your intention to omit something that might be important in your situation.

Creating a Basic Database

One of the things I want to try is scripting the creation of a database in my CI process. That’s not quite simple, and it’s a task for another day, but here’s a basic call that I can work on adding to my builds.

New-AzureRMSqlDatabase –ResourceGroupName “Default-SQL-WestEurope” –ServerName “dkranchapps” –DatabaseName “” –Edition Basic –RequestedServiceObjectiveName “Basic”

Once I run this, I can check the portal, and sure enough, I see my database at the correct size.

2016-09-21 10_52_49-SQL databases - Microsoft Azure

That’s the first step to automating some builds. Now I need to worry about security and API keys, but for now, I can build a database.

And, of course, remove it with Remove-AzureRMSqlDatabase.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

3 Responses to More Azure Database Creation in Powershell

  1. blobeater says:

    Steve, did you manage to check out my code for creating Azure SQL DB’s ? Feel free to take it.

  2. way0utwest says:

    I have not. Almost went to your blog to figure out this for T-SQL Tuesday, but thought you might have it all cookie cuttered and I wanted to learn something on my own. I am planning on doing more, and will likely check your blog (or email) for help.

Comments are closed.