Clarification on Backup Consistency and Best Practices for SQL VM Upgrades

You can choose in the VM group settings of what level of consistency you want.

https://docs.metallic.io/metallic/modifying_settings_for_azure_vm_group.html

In a nutshell - crash consistent, the machine is captured in its current state, but any data in memory is lost. An analogy I like to use is notepad. Type some stuff in notepad, click save, type some more stuff, close notepad without saving - you are missing the stuff after the save. In reality an application and the OS could be flushing data to disk thousands of times per second - if you have crash consistent you don’t really know what state the machine will be recovered in because you haven't taken care of the transactions that are waiting to be flushed to disk.

File system and application consistent - this is the option you want in most cases. It prepares the operating system and application to pause for just a moment, and write all outstanding transactions and data in memory to the disk before the snapshot of the VM is done. This more or less guarantees a consistent state if you had to recover the VM from this snapshot/backup. It accomplishes this by using VSS (volume shadow copy); most critical applications on windows plug into the VSS framework. In my notebook analogy, the save button is clicked just before each backup. This operation is also partially coordinated by the Azure VM agent which is on every windows build deployed in Azure by default - so you shouldn't need to know or care about it.

Application-based backups -  This uses the option above but goes a step further. It requires extra software to be deployed on the machine that understands the application. E.g the SQL agent. This allows the capture of metadata during the backup to help provide more granular recovery options. For example, with “File system and application consistent” option, you can roll back the VM to a point in time, but you can’t really just recover SQL on its own. This option allows you to restore the database to a point in time independent of the OS. It will also help facilitate transaction log backups (assuming its in full mode) in the case of SQL so you get more granular application recovery points.

The downside of the latter is the VM is no longer really considered ‘agentless’ - there is a bit of software on there helping facilitate things that requires a small amount of planning (i.e it will require network access to a Media Agent and the CommServe).

Hope that helps. I think in your case a backup prior to upgrade, I think taking a backup of the machines before the SQL upgrade is sufficient to provide a rollback point should things go wrong - but I don't have the full scope of your situation to make a concrete suggestion.

Hi there,

please remember to deploy Azure extension “Azure Monitoring Agent” on the VMs, it’s similar to e.g. Hyper-V Guest agent or VMware Tools - Install and manage Azure Monitor Agent - Azure Monitor | Microsoft Learn. Please remember that old monitoring extension called “Microsoft Monitoring Agent” is EOL and shouldn’t be used. 

Later VMs should be switched to “File system and application consistent” mode at VM group level. Backups will be switched from Azure snapshots to Restore Collection Point based method - Use virtual machine restore points - Azure Virtual Machines | Microsoft Learn

Then SQL agent needs to be deployed to meet best practices and provide DB level transaction log truncation. By that you will make sure that t-logs are properly managed and with Full recovery models you would be able to meet default t-log 4h RPO (or better) - Recovery models (SQL Server) - SQL Server | Microsoft Learn

We protect a lot of things in that setup, in case of any questions feel free to ask here or PM directly.