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.
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.
