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.
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.
Transaction Log Backup: SQL Server Managed Backup to Windows Azure schedules a log backup if any of the following is true:
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.
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.
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.