SQL Server Backup to Cloud – Managing Interrupted backups

SQLOS Team — Tue, 12 Mar 2013 21:48:00 GMT

Writer: Karthika Raman

Technical Reviewers: Guy Bowerman, Pat Schaefer, Andrew Cherry

On January 24, 2013, the SQL Server engineering team released new functionality in SQL Server 2012 SP1 CU2, enabling SQL Server native backup to cloud. The steps to creating a SQL Server backup to Windows Azure Blob storage are simple, but if a backup is interrupted it can leave behind blob files that require additional steps to delete. Some of this information along with other best practices and troubleshooting tips is addressed in the Best Practices topic in SQL Server Books online. In this blog post, we focus on the interrupted backup scenario, identifying locked blobs that might be partial or corrupt as a result of interrupted backups, and deleting them.

The Blob, Blob Leases, and SQL Server section provides background information about blobs and leases in relation to SQL Server Backup to cloud. The Managing Interrupted Backup section discusses managing interrupted backups and a sample PowerShell code you can use to get started.

Blobs, Blob Leases, and SQL Server

In order to get exclusive write access to a blob, a lease is acquired. Acquiring a lease helps avoid accidental overwrites or deletes when there is already another process accessing a blob. Blob leases are two types: One type of lease has a set duration. This duration can be anywhere from 15 to 60 seconds. The lease is active for the time of duration, but can be reacquired before it expires, to complete the backup or restore process, by specifying the existing lease Id. The other type where there is no set duration is an infinite lease. Infinite leases are active unless explicitly released or broken. In effect, the infinite lease on a blob acts like a lock.

SQL Server acquires an infinite lease for backup and restore processes. In the case of backup, a unique lease Id is used, and in the case of restore, a known lease id ‘BAC2BAC2BAC2BAC2BAC2BAC2BAC2BAC2’ is used. Once the backup or restore process is successfully completed, the lease is released.

What happens when a backup or restore is interrupted?

During restores, the restore process always attempts to release the lease unless the network is interrupted. If the lease remains active, overwrites or deletes cannot be performed until the lease is broken or released through a subsequent restore. So an interrupted restore may not often be something that requires close monitoring. However, interrupted backups present a different scenario.

If the backup process is interrupted, it can result in a partial or corrupt blob with an active lease which remains, blocking any overwrites, or deletes. It also prevents any restores using this file, since the restore process needs to acquire a lease with the well-known lease Id, but the existing active backup lease prevents this – which is probably a desirable result as the file could be partial or corrupt. So with an active lease on the blob, the blob remains locked until the lease is explicitly broken.

Managing Interrupted Backups

As stated in the section above, interrupted backups need to be monitored and managed. This section walks through the process of monitoring interrupted backups, identifying locked blobs, and breaking the lease using PowerShell scripts.

Backups can be interrupted due to several reasons such as network failures, process canceled by the user, power outage etc. As interrupted backups can result in a partial blob with an active lease, in order to overwrite this file or delete it, you must first identify such blobs and break the lease.

One way to identify such blobs is to actively monitor backup return codes/errors and interrupted backups. Below is a list of error/return codes that are returned from SQL Server Backup and restore process, which you can use to monitor for blob files with active leases.

Msg 3202, Level 16, State 1, Line 1

Write on "https ://mystorage.blob.core.windows.net/mycontainer/test.bak" failed: Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (412) There is currently a lease on the blob and no lease ID was specified in the request.

BackupIoRequest::ReportIoError: read failure on backup device https://mystorage.blob.core.windows.net/mycontainer/test.bak '. Operating system error Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (409) Conflict..

This method won’t catch cases where the backup process is aborted or the machine goes down.

Another way is to run a script periodically to look for blobs with active leases in the storage account. The PowerShell script example included here is a good starting point. The PowerShell script establishes an authenticated connection to the storage account, looks for all files in a specified container that are in a ‘locked’ state and filters out restore leases using the well-known lease Id.

Below is the PowerShell example to identify locked blobs and breaking the lease. A downloadable version of the code is available on the TechNet code gallery.

Important Note:

This example is intended to show how to break a lease, but running this while a backup is running will cause the backup to fail as it will break the lease that SQL Server acquired to do the backup. Before running this script or scheduling it, ensure that no backup is running at the same time.

param(

[Parameter(Mandatory=$true)]

[string]$storageAccount,

[Parameter(Mandatory=$true)]

[string]$storageKey,

[Parameter(Mandatory=$true)]

[string]$blobContainer,

[Parameter(Mandatory=$true)]

[string]$storageAssemblyPath

)

# Well known Restore Lease ID

$restoreLeaseId = "BAC2BAC2BAC2BAC2BAC2BAC2BAC2BAC2"

# Load the storage assembly without locking the file for the duration of the PowerShell session

$bytes = [System.IO.File]::ReadAllBytes($storageAssemblyPath)

[System.Reflection.Assembly]::Load($bytes)

$cred = New-Object 'Microsoft.WindowsAzure.Storage.Auth.StorageCredentials' $storageAccount, $storageKey

$client = New-Object 'Microsoft.WindowsAzure.Storage.Blob.CloudBlobClient' "https://$storageAccount.blob.core.windows.net", $cred

$container = $client.GetContainerReference($blobContainer)

#list all the blobs

$allBlobs = $container.ListBlobs()

$lockedBlobs = @()

# filter blobs that are have Lease Status as "locked"

foreach($blob in $allBlobs)

{

$blobProperties = $blob.Properties

if($blobProperties.LeaseStatus -eq "Locked")

{

$lockedBlobs += $blob

}

if ($lockedBlobs.Count -eq 0)

{

Write-Host " There are no blobs with locked lease status"

}

if($lockedBlobs.Count -gt 0)

{

write-host "Breaking leases"

foreach($blob in $lockedBlobs )

{

try

{

$blob.AcquireLease($null, $restoreLeaseId, $null, $null, $null)

Write-Host "The lease on $($blob.Uri) is a restore lease"

}

catch [Microsoft.WindowsAzure.Storage.StorageException]

{

if($_.Exception.RequestInformation.HttpStatusCode -eq 409)

{

Write-Host "The lease on $($blob.Uri) is not a restore lease"

}

Write-Host "Breaking lease on $($blob.Uri)"

$blob.BreakLease($(New-TimeSpan), $null, $null, $null) | Out-Null

}

How to test the interrupted backup scenario using the script.

The following is a self-guided walkthrough of creating a locked blob by interrupting a backup process, and using the script to release the lease to successfully delete the blob file that resulted from the interrupted backup.

The script prompts for storage account name, storage access key, container name, and the path and file name of the Windows Azure Storage Assembly. You can either choose to provide these interactively or you can replace the params () section like this:

#provide values for parameters

$storageAccount = "mycloudstorage"

$storageKey = "<primary/secondary access key value of the storage account>”

$blobContainer = "sqlbackup"

$storageAssemblyPath = "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Microsoft.WindowsAzure.Storage.dll"

Note: The storage assembly is stored in the Binn folder of the SQL Server 2012 SP1 CU2 instance. In the above example, it is the default SQL Server instance.

Create a SQL Server Backup to Windows Azure blob storage which completes successfully.

Run the PowerShell script. It returns a message that says that there are no locked blobs.

Now run another SQL Server backup but stop it before it can complete to simulate interrupted backup scenario.

Check to see if the file was created on the storage account.

Try to overwrite the file – see error:

Try to delete – see error:

Run the PowerShell script again. This time you should see that locked blob is identified, and after checking that it is not a restore lease, the lease is broken.

Try overwriting or deleting the file. It should work!

Here are some ways you can enhance this script to automate this process:

Output the list of all blobs that are locked to a report (ex: excel file).
Break the active leases and report out the list.
Break the active leases and report out the list, but remove the filter for restore lease.
Delete the blobs with active backup leases and report out the list.

NOTE: If you are running PowerShell 2.0, you may have problems loading the Microsoft WindowsAzure.Storage.dll assembly. We recommend that you upgrade to Powershell 3.0 to solve the issue. You may also use the following workaround for PowerShell 2.0:

Create or modify the powershell.exe.config file to load .NET 2.0 and .NET 4.0 assemblies at runtime with the following:

<?xml version="1.0"?>

</startup>

</configuration>

For more information on best practices, see SQL Server Backup and Restore Best Practices (Windows Azure Blob Storage Service)

Originally posted at http://blogs.msdn.com/b/sqlosteam/

When will my SQL Server Evaluation Edition expire?

SQLOS Team — Mon, 18 Feb 2013 00:43:00 GMT

The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. The edition has a built in expiry of 6 months from the time that you install it. I often install an Eval edition on a machine to set up a test or demo, and then 6 months later it comes as a complete surprise when it suddenly stops working.

Needless to say this can be mildly inconvenient, as it typically expires on the day you plan to demo it. It would be nice to get some kind warning.

This problem is exacerbated in the SQL Server virtual machine images in the Windows Azure gallery. While Azure VM's are in preview the SQL Server platform images are created with Evaluation edition, but the 6 month counter starts ticking when the VM is created rather than when you deploy it. This came to a head recently as a few months went by between platform image refreshes, so people deploying SQL VM images in Windows Azure were seeing them expire a few days later. See this thread for more details. Since then the SQL Server platform image has been refreshed so it won't be expiring for several months.

The problem with Azure VM's will go away soon as fully licensed pay-per-hour images will be available soon, and free trial offers will be available.

What happens when your SQL instance does expire? What are the options? Particularly if you've developed a working solution and want to put it into production?

One option is to purchase a license and enter the license key in SQL Server Installation Center->Maintenance->Upgrade License.

If you're using an Azure VM another option would be to migrate your application to a new VM.

One thing we've learned from this is that as long as time-bombed images are being used we need to be better at communicating and setting expectations about their expiration.

If you're running an Evaluation edition now, how can you tell when it's due to expire? One way is to run this query...

sp_configure 'show advanced options', 1;
RECONFIGURE
GO

sp_configure 'Agent XPs', 1;
RECONFIGURE
GO

DECLARE @daysleft int
DECLARE @instancename sysname
SELECT @instancename = CONVERT(sysname, SERVERPROPERTY('InstanceName'))
EXEC @daysleft = xp_qv '2715127595', @instancename
SELECT @daysleft 'Number of days left'

That will give you an answer in days until expiry. As of today, the SQL Server gallery image I launched a few days ago has 122 days left, which gives me a few months to do my demos and then forget that it's going to expire. Hopefully by that time I'll have either upgraded it or started with a refreshed or pay per hour image.

- Guy

Originally posted at http://blogs.msdn.com/b/sqlosteam/

Search results matching tag 'Azure VM'

SQL Server Backup to Cloud – Managing Interrupted backups

Blobs, Blob Leases, and SQL Server

Managing Interrupted Backups

How to test the interrupted backup scenario using the script.

When will my SQL Server Evaluation Edition expire?