Storing multiple copies of your database on several machines is not a foolproof solution to keeping your data available. Users must be mindful that they are using the latest version of the data, and the risk increases of data security being compromised.
Thankfully, Microsoft released new enhancements to protect data during the backup and restoration processes:
- SQL Server Backup Encryption
- SQL Server Backups to URLs
- SQL Server Managed Backups to Windows Azure
SQL Server Backup Encryption
Previously, backup encryptions were achieved through third-party products. Now, Microsoft includes this feature as a part of SQL Server 2014 and above in the Standard, Business Intelligence and Enterprise editions.
Encryption algorithms such as AES 128, AES 192, AES 256, and Triple DES are all supported by the enhancement.
A database Master Key (Symmetric key) is needed to protect the asymmetric keys and private keys of certificates in a database. An asymmetric key or a certificate is needed to implement it.
- Create the master key encryption using the following query:
- CREATE MASTER KEY ENCRYPTION BY PASSWORD='encryptmaster'
- Create a certificate in the master database:
- CREATE CERTIFICATE Certificate_Backup
- WITH SUBJECT =’encryption backup’;
- Backup the Service Master Key and Database Master Key:
- BACKUP SERVICE MASTER KEY
- TO FILE = 'D:\Backup\ServiceMasterKey.key'
- ENCRYPTION BY PASSWORD = ' ServiceMasterKey';
- BACKUP MASTER KEY
- TO FILE = 'D:\Backup\MasterKey.key'
- ENCRYPTION BY PASSWORD = ' MasterKey';
- Create a backup of the certificate:
- BACKUP CERTIFICATE Certificate_Backup TO FILE = 'D:\Backup\CertificateBackup.cer'
- WITH PRIVATE KEY (FILE = 'D:\Backup\Key_Certificate.key’,
- ENCRYPTION BY PASSWORD = ‘Key_Certificate’);
- Encryption during a backup is not supported in SQL Server Web and SQL Server Express. However, restoring an encrypted backup is possible.
- It is not possible to restore an encrypted backup to a previous version of SQL Server.
- It is not possible to append to an existing backup set option.
SQL Server backups to URLs
Cloud-enabled backup enables you to store the SQL Server backups outside your data center. This method designates a URL of the azure storage for backup, rather than a tape or a disk target. To use SQL Server Backup to URL, you need to have:
- a subscription to Azure
- access to Azure using portal / PowerShell
- an account for Azure storage
You must have credentials for Azure blob storage.
To backup to a URL, use the query below:
- TO URL = ‘http://azureaccount.blob.core.windows.net/container/sql.bak’
- WITH CREDENTIAL = ‘credential’,
- STATS = 5
To restore from a URL, use the query below:
- FROM URL == ‘http://azureaccount.blob.core.windows.net/container/sql.bak’
- WITH CREDENTIAL = ‘credential’
- Backup sizes are restricted to 1TB or less.
- It is not possible to have a logical device name - using sp_dumpdevice or SQL Management Studio to add a URL as a backup device is not supported.
- Backups to multiple blobs using a single backup operation is not allowed.
- It is not possible to specify the block size along with BACKUP.
- It is not possible to specify the MAXTRANSFERSIZE.
- A maximum of 259 character is allowed for the device name. A URL uses 36 characters to specify ‘https://.blob.core.windows.net//.bak’ – the remaining 223 characters are for the blob name, container and account.
SQL Server Managed Backups to Windows Azure
SQL Server 2014 allows us to use Cloud storage for database backups. Since windows Azure replicates the backup onto multiple data centers around the world, organizations like to use this as part of their disaster recovery plan.
This feature lets anyone utilize Azure storage and get a reliable offsite backup of databases.
You need a Microsoft Azure account, an Azure storage account, a Blob container, a Shared Access Signature and SQL Server Agent to implement this onto your database.
Benefits of Managed Backups to Windows Azure
Previously, automating backups for a database needed a backup strategy, schedule and custom code. When using SQL Server managed backups to Azure, you only need to configure the storage location and its retention period. Managed backups can be encrypted if additional security is needed!
You can configure the managed backups at the SQL Server instance level or at the database level. New databases can be backed up automatically while configuring it at the instance level.