Automating A Heartbeat Check Against an Azure-SQL Database Using PowerShell

By: Tim Smith   |   Updated: 2015-06-04   |   Comments   |   Related: > Azure

Problem

We have moved some of our SQL Server databases to Azure and we want to do periodic checks to make sure the databases are responsive. We want to setup a scheduled heartbeat check against the Azure databases. What options are there to automate this type of check?

Solution

One option that comes to mind is to use PowerShell to periodically check the status of Azure databases. In this tip we will look at some PowerShell scripts that can be used to perform this function.

We'll look at a solution that will verify that the database is (1) present, (2) not restoring, (3) online, and (4) access is not prevented by current resource usage.

With some Azure environments, an Azure heartbeat failure might be a major issue (for instance, a financial environment), whereas in other environments, we may expect a heartbeat to fail (especially if we have a low timeout range). I've run into many situations where a heartbeat failed, even though everything was online because of the current usage; in these cases, it's important to determine how to re-structure the environment, re-write the necessary code/application logic to prevent this, but the database can still be online during this failure - a multiple heartbeat failure is almost always indicative that it's offline.

Here is the PowerShell script:

Function Heartbeat-AzureDBs {
    Param(
    [string]$server
    , [string]$database
    , [string]$username
    , [string]$password
    )
    Process
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "Data Source=$server.database.windows.net;Initial Catalog=$database;Connection Timeout=10;User ID=$username;Password=$password;"
        
		$cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $scon
        $cmd.CommandText = "SELECT NEWID()"
        
		$timer = New-Object System.Diagnostics.StopWatch
        
		try 
        {
            $timer.Start()
            $scon.Open()
            $sqlread = $cmd.ExecuteReader()
            while ($sqlread.Read())
            {
                $returnvalue = $sqlread.GetValue($value)
                $time = $timer.Elapsed.ToString()
            }
            Write-Host "$database.  Verification number: $returnvalue ($time)."
        }
        catch [Exception]
        {
            ### Other alert
            Write-Warning $_
        }
        finally
        {
            $scon.Dispose()
            $cmd.Dispose()
            $timer.Stop()
        }
    }
}

Heartbeat-AzureDBs -server "" -database "" -username "" -password ""

A couple of key points for the above code:

• This does not read from a specific table, but if the database is offline and you set an alert, it will alert you.
• I keep the timeout short, but this can be changed although I would avoid using an infinite timeout for a heartbeat (0).
• I return the time based on the timer, saving the elapsed time to after the query response is read.
• This can be helpful for detecting patterns, especially if some of your Azure processes are done at specific points during the day, week or month.
• If I want to heartbeat multiple databases on the same Azure server, I can use the SMO library and loop through all the non-master databases (see below example).
• If we verify that one failed heartbeat was limited by resource usage, we write a while loop in PowerShell that runs multiple times (like twice) with a timed interval that does n number of heartbeats and after the final nth heartbeat, alert us. Initially, I suggest one because even if the database is online and a heartbeat fails, you want to identify and correct the bottleneck.

Here is additional code to loop through multiple databases:

###  SMO database loop, excluding the master database
Function Loop-AzureDBs {
    Param(
    [string]$server
    , [string]$smolibrary
    , [string]$username
    , [string]$password
    )
    Process
    {
        Add-Type -Path $smolibrary
        
        $smoscon = "Data Source=$server.database.windows.net;Initial Catalog=master;User ID=$username;Password=$password;"
        $servercon = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
        $servercon.ConnectionString = $smoscon
        $srv = New-Object Microsoft.SqlServer.Management.SMO.Server($servercon)
        
        foreach ($db in $srv.Databases | Where-Object {$_.Name -ne "master"})
        {
            $dbname = $db.Name
            Heartbeat-AzureDBs -server $server -database $dbname -username $username -password $password
        }
    }
}

Next Steps

• Test on an individual Azure-DB; verifying when you remove/restore/etc.
• I'd highly recommend tracking the time of any alerts; these can be useful, especially if the issue is related to resource usage.
• Check out more Azure tips and PowerShell tips

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips