Problem
As DBAs, we have the responsibility to backup databases on a regular basis, but we also have to ensure that backups are stored securely and safely, so databases can be restored when needed. With the current method it is sometimes difficult to control that these backup files are readily accessible when needed. I heard that SQL Server 2012 now supports backups directly to Windows Azure Blob Storage and restores can also be done directly from Windows Azure Blob Storage. Can you tell me more about this new feature and advantages of using it over traditional backup options?
Solution
In addition to DISK and TAPE, which have been supported by SQL Server for a long time, now you can create a backup of your databases directly to and also restore directly from Windows Azure Blob Storage (Windows Azure cloud storage service).
SQL Server 2012 Service Pack (SP) 1 Cumulative Update (CU) 2 introduced support for creating a backup and restoring your database directly from blob storage using Windows Azure storage services. This can be can done using T-SQL and SMO.
SQL Server 2012 Service Pack 1 Cumulative Update 4 adds additional support for SQL Server backup and restore with Windows Azure Blob Storage using PowerShell by enhancing existing PowerShell cmdlets and by adding new PowerShell cmdlets. With this enhancement, you can backup SQL Server databases on an on-premises instance or SQL Server running on a Windows Azure Virtual Machine.
Advantages of using cloud storage services for backup and restore
There are several advantages of using Windows Azure Blob storage service for storing database backup files and restoring when needed:
- It can be an offsite storage option for the on-premises database backups (reducing the overhead of maintaining and securing DISK and TAPE), or used when you migrate an on-premises database to an instance of SQL Server running in a Windows Azure Virtual Machine (VM).
- This offsite storage reduces the overhead of hardware management and ensures protection from hardware failure.
- Windows Azure Blob storage has built in support for replication even over different geographical locations. This ensures that you can restore your databases after disaster for the site or the region. In other words, Windows Azure Blob Storage provides high availability of your database backup files and uses geo-replication to provide an extra layer of protection in the event of a disaster that could affect an entire region.
- Ease of migration – Backup files are available from anywhere, at any time and can easily be accessed for restores for a SQL Server instance running a Windows Azure VM or on-premises machine.
- You don’t need to learn a different technique or use different tools to use this new feature, SQL Server has extended the existing way of creating a backup (and restoring too) to ensure there is a minimal learning curve or code changes.
What are different ways to do backup and restore to and from Windows Azure Blob storage
These are different ways, you can use this new feature:
- TSQL – BACKUP DATABASE and RESTORE DATABASE commands have been enhanced to have two new clauses (CREDENTIAL and URL). CREDENTIAL is used to specify SQL Server credentials (it contains connection information to connect to Windows Azure Blob Storage) whereas URL is used to specify the URL (Uniform Resource Locator or Universal Resource Locator) which provides a mechanism to provide the Windows Azure Blob Storage location and backup file name.
- SMO (SQL Server Management Objects) – SMO is a collection of namespaces which in turn contains different classes, interfaces, delegates and enumerations to programmatically work with and manage a SQL Server instance. This has also been enhanced to add support for creating a backup on the Windows Azure Blob Storage and restoring from there when needed. Backup and Restore classes have a new property to specify the name of the SQL Server credential whereas AddDevice method has been enhanced to accept the Windows Azure Blob Storage URL.
- PowerShell – PowerShell cmdlets have also been improved to add support for creating a backup and restoring from Windows Azure Blob Storage. Backup-SqlDatabase and Restore-SqlDatabase are the two main PowerShell cmdlets introduced in SQL Server 2012 to do backup and restore operations. These cmdlets have been further enhanced in SQL Server 2012 SP1 CU4 to allow direct backup and restore of your database to Windows Azure Blob Storage.
More information about backup and restore with Windows Azure Blob Storage services
- Its recommended to use compression while taking a backup, so the network traffic and storage requirements can be minimized.
- The largest backup file size supported is 1 TB (1024 GB) as of now.
- You can backup to and restore from the Windows Azure Blob Storage service be it either FULL, DIFFERENTIAL or TRANSACTION LOG backups.
- Though SQL Server Management Studio (SSMS) wizard, in the current release, does not support backup to and restore from the Windows Azure Blob Storage service, you can use T-SQL, SMO and PowerShell.
- You cannot append to an existing backup file (you can use date or datetime as part of the file name to create multiple files); though you can overwrite a file by using the WITH FORMAT option.
- Specification of MAXTRANSFERSIZE, RETAINDAYS, EXPIREDATE and block size is not supported.
- You can use the Windows Azure pricing calculator to estimate the cost needed for Windows Azure Storage.
In this tip, I talked about the new feature of creating backups of your databases to Windows Azure Blob Storage service and restoring as needed. I also talked about the advantages of using this feature and different ways you can do this. In my next tips, I will demonstrate how you can implement these new methods.
Next Steps
- Review Backup and Restore enhancements in SQL Server 2012.
- Review Getting started with SQL Server Management Objects (SMO)
- Review Move an On-Premises SQL Server Database to the SQL Azure Cloud
- Review other SQL Azure related tips
- Review my previous tips
