A robust backup strategy is always required even if an organization employs high-availability, disaster-recovery, and hybrid-cloud strategies to protect its mission-critical data. SQL Server 2014 makes several investments in backup operations, including using Windows Azure for simplified backups and native encryption to protect both on-premises or cloud backups.
Managed backup was introduced in SQL SERVER 2014, and allowed an automatic database backup to Microsoft Azure, based on changes done in the database. This feature schedules, performs and maintains the backups–all a DBA needs to do is to specify a retention period.
This topic includes best practices and troubleshooting tips for SQL Server backup and restores to the Windows Azure Blob service.
SQL Server 2012 SP1 CU2 enabled SQL Server backup and restore functionality to Windows Azure Blob Storage. Unfortunately, this feature was available only when issuing BACKUP and RESTORE statements with Transact-SQL, PowerShell, or Sql Server Management Objects (SMOS). In SQL Server 2014, the ability to back up to and restore from Windows Azure Blob Storage has been added to SQL Server Management Studio via the Backup And Restore wizard and the Maintenance Plan wizard. Transact-SQL and PowerShell can still be used if you prefer them.
Related Page: Working With SQL Server Management Studio In SSIS
Figure 3.12 illustrates an on-premises SQL SERVER backup to Windows Azure Blob Storage by using a URL.
FIGURE 3.12 : Leveraging the Windows Azure Blob Storage service to back up an on-premises database to Windows Azure.
Backing up SQL Server databases to the cloud offers a tremendous number of benefits to organizations, including:
To use Windows Azure Blob Storage for backups, a Windows Azure subscription is required. You need to create a storage account and a container in Windows Azure and then create a SQL Server credential that holds critical information, such as the container’s policy information and an access signature that is shared. The next section illustrates how to back up to a URL by using the Backup Task in SQL Server Management Studio. Later, we show how to perform the same operation with Transact-SQL.
SQL Server Managed Backup to Windows Azure and at the same time, automates SQL Server backups to Microsoft Azure Blob storage. You can choose to allow SQL Server to determine the backup schedule based on the transaction workload of your database. Or you can use advanced options to define a schedule. The retention settings determine how long the backups are stored in Azure Blob storage. SQL Server Managed Backup to Windows Azure supports point in time restore for the retention time period specified.
In SQL Server Management Studio, expand Databases, and then select a user database that you want to back up.
FIGURE 3.13 : Using the Back Up Database wizard to backup an on-premises database to Windows Azure using a URL prefix.
5. On the Media Options and Backup Options pages, enter additional backup information, such as reliability options, compression, and encryption.
So, if you have chosen to do everything yourself, then below you will find the necessary steps which you have to do in order to backup to Windows Azure storage service.
The following Transact-SQL example illustrates how to back up a SQL Server database to a URL.
BACKUP DATABASE [SalesForcast] TO URL = N’https://sqlazurebk.blob.core.windows.net/SalesForcastDB/SalesForcast_backu GO
This topic provides an overview of the encryption options for SQL Server backups.
Starting in SQL Server 2014, SQL Server has the ability to encrypt the data while creating a backup. By specifying the encryption algorithm and the Encryptor when creating a backup, you can create an encrypted backup file. All storage destinations: on-premises and Window Azure storage are supported. In addition, encryption options can be configured for SQL Server Managed Backup to Windows Azure operations, a new feature introduced in SQL Server 2014.
To encrypt during A backup, you must specify an encryption algorithm, and an Encryptor to secure the encryption key. The following are the supported encryption options:
For many years, organizations and customers have requested that native encryption for backups must be included along with SQL Server. In the past, encryption for backups could be achieved through third-party solutions or with transparent data encryption (TDE). Third-party solutions added additional costs to the overall solution, and TDE would encrypt not only the backup, but also the whole database, which was not always a requirement of an organization or practical, given the additional administrative and performance overhead.
In SQL Server 2014, organizations can encrypt data while creating a backup. This is achieved by specifying the encryption algorithm and the Encryptor when you create the backup. Encrypted backups are supported whether you create the backup on-premises or with Windows Azure. In addition, encryption can be applied when you use the Back Up Database wizard, the Maintenance Plan wizard, or with Transact-SQL.
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.
The is_master_key_encrypted_by_server column of the sys.databases catalog view in master indicates whether the database master key is encrypted by the service master key.
Here, the first step in successfully encrypting a backup file is to create a database master key. The following Transact-SQL example creates a database master key and stores the complex password in the database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘2.48h]zD>qJ~NfL_L6dh’;
In SQL Server, certificates are stored within the database in which they were created. That means that they are backed up together with the database. However, as a certificate might hold the “key” to a lot of your data, it is often advisable to have a separate backup.
Certificate backups also provide a simple way to move or copy a certificate from one database to another.
The next step here, is to create the backup certificate in the master database. The following Transact-SQL example creates a certificate named MyDBBackupEncryptCert.
Use Master GO CREATE CERTIFICATE MyDBBackupEncryptCert WITH SUBJECT = ‘MyDB Backup Encryption Certificate’; GO
The need to keep data secure grows dramatically. Along with data, you need to be sure your database backup files are also secured, especially those on a server file system. The data inside the native SQL Server backup files is stored as a plain text on the file system.
Follow these steps to encrypt a database by using the Backup Database wizard:
FIGURE 3-14 Specifying the encryption type and certificate or an asymmetric key to encrypt a database backup.
Backing up the database using T-SQL setup requirements, are the same as when using SQL Server Management Studio (SSMS).
The following Transact-SQL statement would encrypt the backup based on the inputs specified in the preceding example:
ACKUP DATABASE [SalesForcast]
TO DISK = N’C:Tempdb_backup_cxvirmpz.wtq.bak’
WITH FORMAT, INIT,
MEDIANAME = N’SalesForcastMediaSet’,
NAME = N’SalesForcast-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD,
ENCRYPTION(ALGORITHM = AES_128,
SERVER CERTIFICATE = [MyDBBackupEncryptCert]), STATS = 10
Managed Backup is a new feature introduced in SQL Server 2014 working with Windows Azure. This feature allows to manage and automate SQL Server backups (from your on-premise or Azure, SQL Server instance), configurable by … script only (T-SQL or PowerShell)!
Microsoft recommends to use Managed Backup for Windows Azure virtual machines.
This topic describes how to enable SQL Server Managed Backup to Windows Azure with default settings at both the instance and database level.
SQL Server 2014 introduces SQL Server Managed Backup to Windows Azure to further save on storage and administration while achieving additional off-site data protection with the Windows Azure Blob Storage service. SQL Server Managed Backup to Windows Azure as a SQL Server feature that automates database backup and maintains the backups based on the retention period. When using this new functionality, there isn’t a need to manage backup policies, and the backup strategy measures database usage patterns to set the frequency of backups to Windows Azure. The main differentiators and benefits for SQL Server Managed Backup to Windows Azure compared with traditional backups to the cloud includes the following:
Currently automating backups for multiple databases requires developing a backup strategy, writing custom code, and scheduling backups.
‘SQL Server Managed Backup to Windows Azure’ can be configured at the database or at an instance level. If configured at the instance level, the settings are applicable to any database created thereafter.
Additional granular control can be achieved for each database backup because you can override default instance-level settings when creating policies at the database level. For example, setting the retention period at the database level allows you to override the default settings at the instance level.
Specifying the retention period, and SQL Server Managed Backup to Windows Azure determines the type and frequency of backups for a database and stores the backups on the Windows Azure Blob Storage service. The supported values are in the range of 1-30 days.
When a backup is configured to use encryption, you have additional security for the backed up data.
Enabling SQL Server Managed Backup to Windows Azure at the instance level
The system stored procedure smart_backup.set_instance_backup is used to enable SQL Server Managed Backup to Windows Azure and configure the default settings at the instance level. The value 1 must be specified for the @enable_backup parameter to enable backups and set the default configurations. As indicated earlier, once the settings have been configured at the instance level, the settings will be applied to all new databases created on this same instance. The following items need to be specified as well: retention period (from 1 to 30 days), the SQL credential used to authenticate to the Windows Azure storage account, and whether to use encryption.
The following Transact-SQL syntax can be used to enable SQL Server Managed Backup to Windows Azure at the instance level. A retention period of 30 days and AES 128 encryption has been specified.
Use msdb; Go EXEC smart_admin.sp_set_instance_backup @storage_url = ‘https://mystorageaccount.blob.core.windows.net’ ,@retention_days=30 ,@credential_name=’MyCredential’ ,@encryption_algorithm =’AES_128’ ,@encryptor_type= ‘Certificate’ ,@encryptor_name=’MyBackupCert’ ,@enable_backup=1; GO
Related Page: Azure SQL Data Warehouse
Enabling SQL Server Managed Backup to Windows Azure for a database
The system stored procedure smart_admin.sp_set_db_backup is used to enable SQL Server Managed Backup to Windows Azure for a specific database. The following items need to be specified along with enabling the backup: the name of the database, the retention period from 1 to 30 days, the SQL credential is used to authenticate to the Windows Azure storage account, and whether to encrypt the backups.
The following Transact-SQL syntax can be used to enable SQL Server Managed Backup to Windows Azure at the database level. A retention period of 30 days and AES 256 encryption has been specified.
Use msdb; GO EXEC smart_admin.sp_set_db_backup @database_name=’TestDB’ ,@enable_backup=1, ,@storage_url = ‘https://mystorageaccount.blob.core.windows. ,@retention_days =30 ,@credential_name =’MyCredential’ ,@encryption_algorithm =’AES_256’ ,@encryptor_type= ‘Certificate’ ,@encryptor_name=’MyBackupCert’ GO
Get Updates on Tech posts, Interview & Certification questions and training schedules