Automated Backup & Automated Patching Best Practices
We recently released the Automated Backup and Automated Patching features. These features automate the processes of backing up and patching your SQL Virtual Machine to provide an added level of convenience for your VMs. We’d like to outline some best practices for these features to ensure that you get the most out of these features.
Automated Backup
Backup of Encryption Certificates and Data
When backup encryption is enabled, we strongly recommend that you ascertain whether the encryption certificate has been successfully created and uploaded to ensure restorability of your databases. You can do so by creating a database right away and checking the encryption certificates and data were backed up to the newly created container properly. This will show that everything was configured correctly and no anomalies took place.
If the certificate failed to upload for some reason, you can use the certificate manager to export the certificate and save it. You do not want to save it on the same VM, however, as this does not ensure you have access to the certificate when the VM is down. To know if the certificate was backed up properly after changing or creating the Automated Backup configuration, you can check the event logs in the VM (Figure 1), and if it failed you will see this error message:
Figure 1: Error Message Shown in Event Log in VM
If the certificates were backed up correctly, you will see this message in the Event Logs:
Figure 2: Successful Backup of Encryption Certificate in Event Logs
As a general practice, it is recommended to check on the health of your backups from time to time. In order to be able to restore your backups, you should do the following:
- Confirm that your encryption certificates have been backed up and you remember your password. If you do not do this, you will not be able to decrypt and restore your backups. If for some reason your certificates were not properly backed up, you can accomplish this manually by executing the following T-SQL query:
BACKUP MASTER KEY TO FILE = <file_path> ENCRYPTION BY PASSWORD = <password>
BACKUP CERTIFICATE [AutoBackup_Certificate] TO FILE = <file_path> WITH PRIVATE KEY (FILE = <file_path>, ENCRYPTION BY PASSWORD = <password>) - Confirm that your backup files are uploaded with at least 1 full backup. Because mistakes happen, you should be sure you always have at least one full backup before deleting your VM, or in case your VM gets corrupted, so you know you can still access your data. You should make sure the backup in storage is safe and recoverable before deleting your VM’s data disks.
Disaster Recovery
It is recommended that you select a storage account in a different region for your backups to provide Disaster Recovery for your data. Putting your backups in another region is critical for scenarios when a datacenter goes down and you need uninterrupted access to your data. However, if you have more interest in a short recovery time, rather than disaster recovery, then it may be better to store your backups in the same region. This decision depends on your specific requirements.
Encryption Password
Be sure to use a strong password to protect your certificates. Have some method of ensuring that you remember the password when the time comes to decrypt and restore your backup.
Automated Patching
Schedule
Be sure to schedule the Patching window during a time with low workload, but when the VM is still active. If you schedule during a window where the VM will be down, patching will not take place.
Windows Update compatibility boundaries
If you would like to manually install a specific update that you see in the Windows Update UI, you can do this with no interference to Automated Patching. However, keep in mind that turning Windows Update into automatic install mode will cause Automated Patching to be disabled. Despite this, all settings will persist, and you should manually re-enable Automated Patching to continue using it.
Azure only
Both Automated Backup and Automated Patching heavily rely on Azure VM Agent infrastructure. This means that there is no support for on-premises solution. If you plan to move your VM from Azure to any other environment, plan on uninstalling both SQL Server IaaS Agent and Azure VM Agent.
Try these features out for yourself at https://portal.azure.com.
If you haven’t already, start a free trial on SQL Server in Virtual Machines today.
