This week we have announced the General Availability of the Backup storage redundancy change options for Azure SQL Managed Instance and this post will dive into the details & implications of such change on a production instance.
To learn about the different choices and their implications, please visit the original article - Configuring backup storage redundancy in Azure SQL.
The process will begin with the creation of the new storage account and service processes associated with the selected option. Currently we have 3 options available for the Backup Storage - Locally-redundant backup storage (LRS), Zone-redundant backup storage (ZRS) and Geo-redundant backup storage (GRS). For example, for a Geo-redundant backup storage (GRS) it will create a Storage Account that will be automatically asynchronously replicated to the paired region.
The most important thing will happen after that - changing Backup Storage redundancy will immediately force the execution of the new FULL backup for all user databases, bringing an immediate impact to your SQL Managed Instance workloads and you should consider the timing of this operation - a point in time where final users & processes will be less affected would be a great choice.
After initiating the change of the Backup Storage Redundancy, the previous original backup destination will not be affected, and the backup files stored there will not be instantly removed. They will peacefully age until they will become obsolete according to the policies defined for your SQL Managed Instance and then will be eventually removed.
Backup storage redundancy change options for Azure SQL Managed Instance are affecting only the Point In Time Restore (PITR) backups and has no effect over the Long-Term-Retention (LTR) Backups and their respective policies.
Example of changing backup storage redundancy in Azure SQL MI
For better understanding the implications, let us consider a following example where a customer has no Long-Term-Retention (LTR) Backups policies defined and is using a default policy of 7 days Point In Time Restore (PITR) backups retention.
Customer has created a SQL Managed Instance with a definition of a Locally-redundant backup storage, but had their requirements updated to use Geo-redundant backup storage.
With the introduction of the Backup storage redundancy change options for SQL MI, customer can direct themselves to the portal and change the setting, which as of today is to be found ‘Compute + Storage’ blade, as shown on the image below:
After selecting the new Backup Storage redundancy configuration and clicking on the Apply button, it is important to understand what will happened next:
- A new storage account with the desired storage type shall be created. The respective keys and secrets shall be allocated and stored securely for the connection between SQL MI and the new backup storage account.
It can take 10-15 minutes for this operation to complete.
2. A full Backup of all user databases will be taken to the newly created Geo-redundant storage.
3. No Backups deletion on the original Locally-redundant backup storage will take place
4. The previous backups from the Locally-redundant backup storage will be removed after they will become obsolete.
In this case, the default 7 days PITR backup policy has been applied to all user databases.
The configuration for the PITR can be found in the Database Management – “Backups” blade, and then, selecting the “Retention Policies” configuration as shown on the picture below:
By selecting the databases in question (can be either one, or multiple), and clicking on “Configure policies” option at the top of the screen, presenting the screen on the image below – where one can define the number of days that the PITR backups for the selected database(s) will be kept.
5. Over the course of the time defined for the Point In Time Restore (PITR) policies – 7 days in our example case, the older backups will be slowly removed from the originally defined storage (Locally-redundant Storage) and the new backups will be stored in the new storage (Geo-redundant Storage) with our current retention policy.
Eventually, after expiring completely, the old Backup Storage account will be removed completely, leaving only the new Geo-redundant Storage as active.
6. Long-Term Retention Backups (LTR) will not be affected by any of the above-described changes, as they follow their own path and do not support selection of the storage options currently.
Azure Portal
A little detail that has been added to the Azure Portal with the introduction of this change. Now you accompany this process with the new notification - by selecting the "Overview" blade and scrolling down to the Notification/Managed Instance features, you can select notifications and observe that there is an ongoing operation affecting SQL MI in course:
After clicking on the Info(1) button, an additional notification will be shown on the right side of the screen with the details of the operation, as shown on the picture below (we just have removed the name of the impacted Managed Instance that appears before the text (SQL managed instance):
This way you can discover what is going on with your impacted SQL Managed Instance and which exact action is affecting it.
This article has described the details of the process for changing backup storage redundancy in Azure SQL MI, and it will help you understanding the immediate and the middle-term implications of such changes - including the immediate Full Backups and what it means for your restore processes, once you change the redundancy level.
Make sure your backups correspond your organization, your business and your legal requirements and be mindful about the impact that any change will bring.
Microsoft
