Necessity is the Mother of all Inventions. Well, I say Necessity is the mother of all Inventions and Automation.

This article will help you to create your first runbook, which was my fear factor for over an year. I had to create the runbook for reporting automation as I was too busy to run this script manually. This script will assume that you have some basic knowledge of working in Azure and PowerShell. I will detail as much as possible while describing the steps.

To begin you will need to have an Azure Account (no brainer). You will login to the portal.azure.com and see a welcome screen as shown. 

Unlike normal SQL Server where a batch script automation can be done using a SQL Server Agent Job, we require a Runbook in Azure. A Runbook is a set of tasks that perform some automated process in Azure Automation. It may be a simple process such as starting a virtual machine and creating a log entry, or you may have a complex runbook that combines other smaller runbooks to perform a complex process across multiple resources or even multiple clouds and on premise environments.

For example, you might have an existing manual process for truncating an Azure SQL database if it is approaching the maximum size. The Runbook would include multiple steps, such as connecting to the server, connecting to the database, get the current size of database, check if some threshold has been exceeded, then truncate the database, and notify a user. Instead of manually performing each of these steps, you could create a runbook that would perform all of these tasks as a single process. You would start the runbook, provide the required information such as the SQL server name, database name, and recipient e-mail and then sit back while the process completes.

In Azure you will need an Automation account to start your automation. An Automation account is an Azure resource that you create. You can manage all your Azure, cloud, and on-premises resources with a single Automation account. An Automation account is a container for the items you need to make your automation work. These are runbooks, modules, assets like credentials and schedules, and configurations. To create an automation account, you can follow this link.

Once you have the automation account in place you can proceed to create the credentials and runbooks as required. Once you create the automation account the page will look like this. 

We need to create some credentials to connect to Azure while executing the runbook. The reason to create credential is that it can be reused in other runbooks we create in future and also encapsulation of credentials.  You will need to create these account in your azure active directory with something@yourdomain.onmicrosoft.com like credentials which you can check on this link.

On the same lines we will create one more credential for email communications. After you have created the credentials you Automation account page with credentials should look like. 

Let’s create the Runbook for the reporting automation. We will use the name SQLReportingAutomation for the runbook and give a suitable description which will help us to understand the usage of this later on. 

After creating the runbook you can start writing code. You can save the intermediate code and come back anytime using the edit option in the existing runbooks. 

To start with we have to get the credentials loaded for the azure authentication and email authentication. After which we will define the variables that will be required in the scripts.  Here is my code

######################################### Code Begins ###################################
#Define an array which will store the details of the databases for all the resources
$db_info = New-Object System.Collections.ArrayList
[double]$TotalSizeByte = 0
[String]$TotalSizeGB = ''
# Get all the db Servernames (resource groups) in the subscription
$AzureDBServerNames = Get-AzureSqlDatabaseServer | Select ServerName -ExpandProperty ServerName
# Run a loop to extract the database details for the report
ForEach($AzureDBServerName in $AzureDBServerNames)
{
  $AzureDatabases = Get-AzureSqlDatabase -ServerName $AzureDBServerName | Select Name –ExpandProperty Name
  <# Get all the database related information in each server (DatabaseName, Current Limit in GB, Current Value in GB)  and store in the array defined (db_info) #>
  ForEach($AzureDatabase in $AzureDatabases)
  {
    $DatabaseSizes = Get-AzureSqlDatabaseUsages -ServerName $AzureDBServerName -DatabaseName $AzureDatabase | Select ResourceName, Limit, CurrentValue
    [string]$ResourceName = $DatabaseSizes.ResourceName
    $CurrentLimit = ([long]$DatabaseSizes.Limit)/(1024*1024*1024.00)
    $CurrentValue = ([long]$DatabaseSizes.CurrentValue)/(1024*1024*1024.00)
    $Value = $ResourceName + ',' + $CurrentLimit + ',' + $CurrentValue
    #$Value >> $DBSizeDetailsFile
    $db_info.Add($Value)
  }
}
# Here is the total information for the databases under all the resource groups in the subscription
echo $db_info
foreach ($db in $db_info)
{
  ## Here we are summing up the data space usage of the individual databases
  [double]$TotalSizeByte = [double]$TotalSizeByte + $db.Substring($db.LastIndexOf(",")+1)   
}
######################################### Code Ends ###################################

Once we have the code in place we just need to test it. Just save on the top left corner of the pane and then click on the Test icon. The test page should load up like shown below.

Now click on the start button on the top left to start the test. By default, the last test results show up in the test pane. So remember every time you come to the test pane you have to click on the start button. Once you click start you will see the screen below.

The Test Results pane should look like this:

Finally, you should get the mail like shown below.

Now there are two more parts to automation using a runbook. We need to publish and then schedule it. We need to go to the editor once more and then click on publish. You will get a warning that the previous version of the script will be over-ridden.

Accept the warning and then click on schedule button. You will get an option to create a schedule and link it as well. 

So now one part of my automation is complete. I can now focus on my other areas of automation in azure.

Thanks,

Tushar Kanti