Time Testing SQL Azure Point In Time Database Restores

Problem

We need to do a time test for restoring an Azure SQL database from a point in time. Can we automate this through PowerShell and what guidelines would you recommend?

Solution

I’d recommend testing Azure’s point in time recovery, and automating it through PowerShell, to get an idea of how it works, as well as the time required before the database is online and accessible. PowerShell can do the reconnaissance work and developers can measure the results after testing enough to develop a proof of concept for Azure, or adjust their architecture and tools if they find it too long. Depending on the case, the delay with Azure may meet your needs; in some situations, like algorithmic trading where everything must happen in less than a second, it won’t.

In the below one-line call, I’m using the Azure PowerShell function Start-AzureSqlDatabaseRestore, provided by Microsoft, to restore the database to a point in time. Once that’s confirmed, I execute the second set of functions, which will begin communicating to the restoring database. When it’s online and ready, it will return the time before the database was fully restored.

### $server is the server for both databases, $restore is the database name that will be restored and $time is the point in time of restore
Start-AzureSqlDatabaseRestore -SourceServerName $server -SourceDatabase $database -TargetServerName $server -TargetDatabaseName $restore -PointInTime $time

When I receive a confirmation that the database is being restored to the point in time I pass in, I will then begin timing how long before I can access the database. The below functions (1) attempt to get the top name from the sys.tables view and (2) time the length of the response. In the second function, I use the StopWatch class, which includes Start and Stop methods for starting and stopping the stop watch, and an Elapsed property to obtain the full time. The second function, Time-AzureResponse, continues to loop until it finally obtains a result from the function Read-AzureID.

###  
Function Read-AzureID {
    Param (
    [string]$azureserver
    , [string]$database
    , [string]$username
    , [string]$password
    )
    Process
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "Data Source=$azureserver.database.windows.net;Initial Catalog=$database;User ID=$username;Password=$password;"
        $cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $scon
        $cmd.CommandText = "SELECT TOP 1 name FROM sys.tables WHERE [type] = 'U'"
        try
        {
            $scon.Open()
            $sqlread = $cmd.ExecuteReader()
            while ($sqlread.Read())
            {
                $returnvalue = $sqlread.GetValue(0)
            }
        }
        catch
        {
            $returnvalue = $null
        }
        finally
        {
            $cmd.Dispose()
            $scon.Dispose()
        }
        return $returnvalue
    }
}

###  
Function Time-AzureResponse {
    Param (
    [string]$server
    , [string]$database
    , [string]$username
    , [string]$password
    )
    Process
    {
        $x = $true
        $timer = New-Object System.Diagnostics.StopWatch
        $timer.Start()
        while ($x -eq $true)
        {
            try
            {
                $result = Read-AzureID -azureserver $server -database $database -username $username -password $password
            
                if ($result -ne $null)
                {
                    $time = $timer.Elapsed.ToString()
                    $timer.Stop()
                    $x = $false
                }
            }
            catch
            {
                $x = $true
            }
        }
###  This is wrapped for insertion into a document store, but can be returned like:
# return $time 
        return '{ "restoreTime": "' + $time + '" }'
    }
}
Time-AzureResponse -server "OurServer" -database "RestoredDatabase" -username "OurUsername" -password "OurPassword"

Alternatively, I can change the query text to select from a table. This can be helpful to get an idea of the speed of restoring a database from a point in time. Relative to the size of the database, my results ranged from 5 to 14 minutes, with an average of 7 minutes. If you can’t be offline at all, then depending on what you get from repeated tests may change how you approach your restoration architecture.

Some suggestions for testing restore times:

1. Test the impact of database sizes; you can populate extra data in your database and then test it at double, triple, quadruple, etc. the size.
2. Test different point in times; for instance, how long it takes for three days ago versus one day ago.
3. Test the length at different times, especially times when you suspect you may receive high traffic.

Not only does this help you know how to plan if you need to restore to a point in time, you can also communicate to your users if you know the time delay might raise concerns.

Next Steps

• Restore a database to a point in time.
• Test the time it takes to restore and how that affects your current situation.