Azure Automation: Your SQL Agent in the Cloud

The ability to schedule regular maintenance or administrative jobs makes life significantly easier for anyone that manages one or more databases.  SQL Server Agent affords this ability to those that utilize SQL Server on premise or in a virtual machine on Azure.  However, for those that prefer the PaaS offering of Azure SQL Database, it does not take long to recognize that SQL Server Agent is not an available feature.  As a result, many have expressed the desire for us to deliver this functionality in various forums.

Recently introduced as a public preview service, Azure Automation brings a powerful, much needed PowerShell Workflow execution service to the Azure platform.  Those once difficult maintenance tasks are now possible to automate, and conveniently encapsulated within the common Azure portal experience.  Simply author a PowerShell Workflow (called a “runbook” in Azure Automation), upload it to the cloud, and schedule when you want the runbook to execute. It’s that simple.

Given this context, it begs the question — can Azure Automation play the role of SQL Server Agent for Azure SQL DB?  The short answer is yes.  In this blog, I’ll show you how to leverage the Azure Automation service to accomplish a customer requested scenario of truncating tables when a particular database approaches its maximum size capacity.  Although targeted towards a specific scenario, the highlighted example provides the framework to enable one to accomplish countless Azure SQL DB scenarios that one would normally need a SQL Server Agent to perform.

There have been a few getting started guides for Azure Automation, so we will cut right to the chase (if you do have any Azure Automation specific questions, please post them here).

Below is an example of a runbook that iterates through the database for a given logical server and obtains an array of the database names as well as their current size (runbook can be downloaded here [link]).  Then the runbook will query each of the respective database for their maximum size and, if the database’s size is within a specified range of the maximum size, the runbook will truncate a given table.

workflow Remove-DataFromSqlDbTable 
{
    param
    (
        # Fully-qualified name of the Azure DB server 
        [parameter(Mandatory=$true)] 
        [string] $SqlServerName,

        # Credentials for $SqlServerName stored as an Azure Automation credential asset
        # When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter
        [parameter(Mandatory=$true)] 
        [PSCredential] $Credential
    )

    inlinescript
    {

        # Setup credentials   
        $ServerName = $Using:SqlServerName
        $UserId = $Using:Credential.UserName
        $Password = ($Using:Credential).GetNetworkCredential().Password

        # Setup threshold for % of maximum DB size
        $Threshold = 0.8

        # Create connection to Master DB
        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $MasterDatabaseConnection.ConnectionString = "Server = $ServerName; Database = Master; User ID = $UserId; Password = $Password;"
        $MasterDatabaseConnection.Open();

        # Create command to query the current size of active databases in $ServerName
        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
        $MasterDatabaseCommand.CommandText = 
            "
                SELECT 
                       database_name,
                       storage_in_megabytes [SizeMB]
                FROM 
                       [sys].[databases] as db
                INNER JOIN
                       [sys].[resource_usage] as rs
                ON
                       rs.database_name = db.name
                WHERE
                       [time] = (SELECT Max([time]) FROM [sys].[resource_usage] WHERE database_name = db.name)
                GROUP BY 
                       database_name, storage_in_megabytes
            "
        # Execute reader and return tuples of results 
        $MasterDbResult = $MasterDatabaseCommand.ExecuteReader()

        # Proceed if there is at least one database
        if ($MasterDbResult.HasRows)
        {
            # Create connection for each individual database
            $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
            $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand

            # Iterate through each database under $ServerName
            while($MasterDbResult.Read())
            {
                $DbName = $MasterDbResult[0]
                $DbSize = $MasterDbResult[1]

                # Apply conditions for user databases (i.e., not master DB)
                if($DbName -ne "Master")
                {
                    # Setup connection string for $DbName
                    $DatabaseConnection.ConnectionString = "Server=$ServerName; Database=$DbName; User ID=$UserId; Password=$Password;"
                    $DatabaseConnection.Open();

                    # Create command for a specific database $DBName
                    $DatabaseCommand.Connection = $DatabaseConnection
                    $DatabaseCommand.CommandText = "SELECT DATABASEPROPERTYEX ('$DbName','MaxSizeInBytes')"

                    # Execute query and return single scalar result 
                    $DbResultBytes = $DatabaseCommand.ExecuteScalar()
                    $MaxDbSizeMB = $DbResultBytes/(1Mb)

                    # Calculate $TargetDbSize
                    $TargetDbSize = $MaxDbSizeMB * $Threshold

                    # When the current $DbSize is greater than a percentage ($Threshold) of the $MaxDbSizeMB
                    # then perform a certain action, in this example, truncate a table on that database
                    if($DbSize -gt $TargetDbSize) 
                    {
                        Write-Output "Perform action on $DbName ($DbSize MB > $TargetDbSize MB)"

                        # ExampleTable is a place holder for a table that holds a large volume of less important and expendable data
                        # that can be truncated to save space on the database.

                        $DatabaseCommand.CommandText = "TRUNCATE TABLE [dbo].[ExampleTable]"
                        $NonQueryResult = $DatabaseCommand.ExecuteNonQuery()
                    }
                    else
                    {
                        Write-Output "Do not perform action on $DbName ($DbSize MB <= $TargetDbSize MB)"
                    }

                    # Close connection to $DbName
                    $DatabaseConnection.Close()        
                }
            }
        } 

        # Close connection to Master DB
        $MasterDatabaseConnection.Close() 
    }    
}

Once uploaded to Azure Automation, the runbook can be modified, tested, executed on-demand, or linked to a schedule.  In fact, the entire runbook can be developed in the Azure Portal.  Azure Automation provides a convenient authoring experience complete with intellisense, syntax coloring, and text search capabilities.  Shown in the screenshot below is a schedule that executes the previously described runbook every night at midnight.

As you can see, Azure  Automation provides a very extensible PowerShell Workflow execution engine and job scheduler.  Even beyond the functionality I’ve already demonstrated, one can also use the Azure PowerShell cmdlets that ship in Azure Automation to perform higher-level SQL DB tasks, such as provisioning new databases or SQL servers, from within Azure Automation runbooks.  Whether it is database provisioning, capacity management, index maintenance, or increasing/decreasing the performance level of a database, together Azure Automation’s features afford cloud dev/ops the primitives needed to automate their Azure SQL DB management and maintenance tasks.

Not an Azure Automation user yet? Sign up for the preview and then check out the Getting Started guide.