In this Essential Guide to SQL Server 2014 series I’ve been covering the most important new features and enhancements that Microsoft added to SQL Server 2014. In the last column I covered the enhancements to SQL Server 2014’s Resource Governor. In this column I’m going to cover the main backup enhancements found in SQL Server 2014. While SQL Server 2014 has a number of availability technologies like AlwaysOn Availability Groups and Failover Cluster Instances backup and recovery remain the core fundamental disaster recovery technologies.
SQL Server Backup Encryption
One of the most long awaited enhancements to SQL Server backup was the ability to encrypt your backups and therefore protect your data at rest. This capability has been present in the various third party backup products for some time but Microsoft first included as part of SQL Server’s built-in backup with SQL Server 2014. Backup encryption is support by the SQL Server 2014 Standard, Business Intelligence and Enterprise editions. The supported SQL Server 2014 backup encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES. To use backup encryption you need to create a Database Master Key which is a symmetric key that is used to protect the private keys of certificates and the asymmetric keys that are in the database. Plus, you need to create a certificate or asymmetric key that will be used for database backup encryption.
SQL Server Backup to URL
Cloud integration was one of Microsoft’s main priorities with the SQL Server 2014 release and like you might expect SQL Server 2014’s backup has become cloud-enabled. SQL Server 2014 backups have the ability to be directed to a URL pointing to Azure Storage. This essentially means that the backup device will be an Azure Storage URL rather than a disk target or tape. Backup to URL enables you to easily store your SQL Server backups offsite and outside your main datacenter. To use SQL Server Backup to URL you need:
-
An Azure subscription
-
Access to Azure using the Azure Portal or PowerShell
-
An Azure Storage Account
-
A container in the Azure Storage account
SQL Server Managed Backups
Designed primarily with the Small-to-Medium-Business (SMB) in mind SQL Server Managed Backup to Windows Azure essentially automates SQL Server backups and directs them to Windows Azure storage. SQL Server Managed Backups can be setup at the database level or at the instance level to manage all the databases on server. The SQL Server instance can be running on-premises, in a VM or as a Windows Azure VM. The backup strategy used by SQL Server Managed Backup to Windows Azure is based on the retention period and the transaction workload on the database. Like Backup to URL, SQL Server Managed Backups to Windows Azure require an Azure subscription and storage account.
SQL Server Managed Backup to Windows Azure agent schedules a full database backup when:
-
SQL Server Managed Backup to Windows Azure is enabled with default settings at the instance level
-
SQL Server Managed Backup to Windows Azure enabled for the first time for a database
-
The log growth since the last full database backup is equal to or larger than 1 GB
-
One week has passed since the last full database backup
-
The log chain is broken
SQL Server Managed Backup to Windows Azure schedules a transaction log backup when:
-
There is no log backup history
-
The transaction log space used is 5 MB or larger
-
2 hours have passed since the last log backup
-
When the transaction log backup is lagging behind a full database backup
You can learn more about SQL Server 2014’s backup enhancements on MSDN at SQL Server Backup to URL Best Practices and Troubleshooting and SQL Server Managed Backup to Windows Azure. Plus you can dig into the specifics of backup encryption on the SQL Server Pro site at SQL Server Backup Encryption.
