SQL Server 2014 CTP2 and Azure Backups
With the latest release of SQL Server 2014, I wanted to delve into the latest features of the product that will allow Organizations a much more integrated experience with their Data and the Cloud.
Integration with Windows Azure Storage
One of the latest features with SQL Server 2014, is its tight integration with Windows Azure Storage. This will allow Organizations to be able to backup and restore their Databases from copies stored in Windows Azure, which can quickly and easily provide off-site storage for all backups, without the need to worry about Physical media offsite storage, or leveraging 3rd party software to perform backup and restores to different locations. For this example, I will use the new SQL Server Managed Backup Command to automate Backups from SQL Server 2014 to Windows Azure. The SQL Server Managed Backup allows the control and automation of backups by the SQL Software. This allows for an entirely automated backup experience and allows the software to manage your backups. Per Microsoft, these backups are implemented when the following is true: (Full notes are located here: http://msdn.microsoft.com/en-us/library/dn449496(v=sql.120).aspx#Concepts
Full Database Backup: SQL Server Managed Backup to Windows Azure agent schedules a full database backup if any of the following is true.
- A database is SQL Server Managed Backup to Windows Azure enabled for the first time, or when SQL Server Managed Backup to Windows Azure is enabled with default settings at the instance level.
- The log growth since last full database backup is equal to or larger than 1 GB.
- The maximum time interval of one week has passed since the last full database backup.
- The log chain is broken. SQL Server Managed Backup to Windows Azure periodically checks to see whether the log chain is intact by comparing the first and last LSNs of the backup files. If there is break in the log chain for any reason, SQL Server Managed Backup to Windows Azure schedules a full database backup. The most common reason for log chain breaks is probably a backup command issued using Transact-SQL or through the Backup task in SQL Server Management Studio. Other common scenarios include accidental deletion of the backup log files, or accidental overwrites of backups.
Transaction Log Backup: SQL Server Managed Backup to Windows Azure schedules a log backup if any of the following is true:
- There is no log backup history that can be found. This is usually true when SQL Server Managed Backup to Windows Azure is enabled for the first time.
- The transaction log space used is 5 MB or larger.
- The Log space used percentage is more than 50%.
- The maximum time interval of 2 hours since the last log backup is reached.
- Any time the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.
Step 1: Create a Windows Azure Blob Storage
In order to create Backups from SQL Server 2014 to Windows Azure storage, you will first need to create a new Blob Storage location. To do this, log into the Windows Azure Management Console, Click the ‘New’ Button and click on ‘Data Services’ -> ‘Storage” -> ‘Quick Create’.
Choose a URL and Affinity group, and click OK. Your new Storage account will be created. Make a note of the full URL, it will be used later.
Step 2: Create a SQL Credential to store your Windows Azure Authentication
To allow SQL Server to authenticate to the Windows Azure Storage, you will need to create a Credential to allow SQL Server to properly authenticate. Open a new Query in SQL Server Management studio using the following example as the syntax:
CREATE CREDENTIAL credentialname
WITH Identity= 'Storage Account Name'
, SECRET = '<insert Storage key here>'
Replace credential name, Storage Account Name, and <insert storage key here> with the proper settings for your environment and execute the command.
Step 3: Configure SQL Server Managed Backup to Windows Azure
Next, the managed backup needs to be configured to allow backup to SQL Azure. Run the following Command to configure (Note: substitute the variables for your own environment. For details on Managed Backup Encryption options, please review the TechNet article at: http://msdn.microsoft.com/en-us/library/dn451013(v=sql.120).aspx
EXEC smart_admin.sp_set_db_backup @database_name='ProductionData'
,@storage_url = 'http://<your storage account>.blob.core.windows.net'
When the command is executed, you will receive the following message: SQL Server Managed Backup to Windows Azure is configured for the database, 'ProductionData', with credential 'credentialname', retention period 5 day(s), encryption is off, and backup is on.
The latest version of SQL Server can truly provide immediate value, by providing quick and easy integration between the SQL Server and Windows Azure, as well as provide automation and flexibility in the day-to-day administration of your Database nd Database instances. For more information, visit http://www.enpointe.com/microsoft/sql-server-2012.