Voice of the DBA

Tag: Azure

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

  • T-SQL Tuesday #82–Azure SQL Database

    tsqltuesdayIt’s T-SQL Tuesday time and this month we have Jeffrey Verheul giving us the topic of the cloud, or Azure SQL Database. It’s a good topic, given how many people are talking about working in the cloud. There are also the tremendous resources that Microsoft is investing in Azure, so cloud database posts are a good thought for this month.

    Whether you are writing today (Sept 13) or at some point in the future, it’s fun and informative to participate in the T-SQL Tuesday blog party. Learn something and write about it.

    Azure SQL Database

    I have done a little work with Azure SQL Database, but not a log. I have a few ideas, but finding time and focusing has been a challenge. When I saw this topic I had all sorts of ideas, but since I was traveling and busy, I ended up arriving at the deadline without any work done.

    However, there was one thing I’ve been wanting to try in Azure, so I decided to make this the post. I wanted to create an Azure SQL Database using Powershell. This is a topic that I think lends itself to better orchestration and development, so here goes.

    The first step was a quick Google search, which led me to this post on how to Manage Azure SQL Database with Powershell. That’s what I really wanted to do, so I read a bit. The first step was to download the latest Azure Powershell. I did that and ran an install. This required a reboot, something I rarely like, but I swallowed my annoyance and restarted the desktop.

    Once that was done, I ran a couple cmdlets to get connected and check what resources I had. First, I had a login prompt.

    2016-09-13 14_10_50-Windows PowerShell credential request

    Then I got results. As you can see, I had a few, and the list actually scrolls down quite far.

    2016-09-13 14_23_23-powershell

    I then tested another cmdlet, Get-AzureRMResourceProvider, to get a list of regions.

    2016-09-13 14_25_22-powershell

    That worked, so let’s create a database. I used the New-AzureRMSqlDatabase cmdlet, which worked well. I provided the resource group name and servername I already had with a new database.

    2016-09-13 14_14_44-powershell

    That completed, and things looked good. I connected to the Portal, and sure enough, I had a new database.

    2016-09-13 14_15_23-dkranchapps - Microsoft Azure

    Of course, an S1 will eat up all my credits, so I changed that right away.

    2016-09-13 14_15_57-TSQLTuesday - Microsoft Azure

    That was pretty easy. I can see how I might now string together the creation of a database with some other work, perhaps adding data, certainly using the Redgate DLM Automation or ReadyRoll scripts to create objects, and then I can run things and tear them down.

    Speaking of which.

    2016-09-13 14_32_24-powershell

  • Learning Azure Data Platform

    One of the issues I’ve seen with Azure is that is changes rapidly. It’s cool in that new things get added and problems get addressed. There’s also a buzz with learning and working with Azure from many people, including lots of MVPs. This means we get lots of things written by technical experts, Microsoft, and casual experimenters.

    However that volume of stuff gets outdated quickly. I know in looking for answers why a few things in TFS on Visual Studio Team Services (VSTS) didn’t work correctly had me wading through lots of fixes that weren’t applicable anymore. In fact, most of the information I found wasn’t helpful because that version of TFS online didn’t exist anymore.

    Grant Fritchey has an ambitious project to try and help cut through some of the clutter. He’s build a GitHub repo that has lots of community based resources.  He’s looking to get people to submit resources, take pull requests and hopefully keep this up to date.

    I’m not sure that he can handle the volume of information, but he can always give repo access to others and crowdsource the information. Assuming people get along, this should do well.

    Anyway, take a look, give feedback, and if you find good articles that help you learn about Azure, add them.

  • The Data Dewey Decimal System

    As a small child, I learned about the Dewey Decimal System in school. Those were the days when you wanted to look up some information or check a fact, you needed to peruse the dead tree books in a library, with information organized by the Dewey Decimal System in the US. At least, all libraries I used in school adhered to this.

    These days we usually use a computer of some sort for learning, research, or really most any work with data. Often I start with Google to find my way to the source of information, but that’s not something that necessarily works well with finding sets of data. It certainly doesn’t work well within a an organization.

    I saw recently that Microsoft announced the general availability of the Azure Data Catalog, which is designed to provide a catalog of data sets. In essence the Data Catalog is an index of the data sets that might be produced by your organization, with the information about the data filled in by the producer of data. Users that are looking for data can query the catalog instead of asking coworkers, wandering through the enterprise databases, or even relying on their own memory of where data might be located.

    At first this seems silly, after all, don’t people inside of an organization know where data is kept? Don’t they learn the servers, databases, and connection methods? Certainly many do, but as with the pace of change these days, as well as the rapidly growing number of ways to publish data these days, it’s entirely possible that many people aren’t aware of all the data sources available inside of an organization. Even at Redgate Software, with a few hundred employees, it is fairly difficult to keep track of what data exists in which location.

    The functionality of the Data Catalog seems a bit basic, and really almost like an extension of adding extended properties to various tables. Certainly things are centralized here, which is good. There are also ways to add other sources, such as SSRS reports, files, and even other relational sources. I’ll have to experiment a bit and see what’s available, and I might encourage you to do the same. The product relies on crowdsourcing, which can go really well, or really poorly, depending on how cooperative your crowd is.

    In any case, I do like the idea of having a central catalog that individuals can update as they produce data sources for others to consume and change what’s available. If it works well, with good searching and tagging, it might eliminate some of the redundant work often performed to surface data inside of any organization and let employees know how to find the answers to their questions.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.9MB) podcast or subscribe to the feed at iTunes and LibSyn. feed