commvault.com commvault.com
Question

Frozen MS SQL database IO during VM backups?

  • 3 October 2023
  • 5 replies
  • 780 views
C
Rank
Userlevel 1
Badge +5

Good morning,

We have 3 subclients clients; VM, Filesystem, and MS SQL on a VM hosted on Hyper-V.  I’m looking at the backup history in the msdb database, and my MS SQL backups kicked off from Commvault are working as intended, and I see “CommVault Galaxy Backup” as the backup set name.

However… the VM incremental backup kicks off at 10pm, and I see message like this in the SQL alert log:  I/O is frozen on database MyDatabase. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup. Then a few seconds later: I/O was resumed on database MyDatabase. No user action is required.

 

 

The backup at 9:25 was the incremental backup, while the 9:02 AM backup was me calling a full backup from the console.

This might be a better example, this shows the 10pm incremental and a database backup:

 

Can someone help me understand this?  The MS SQL datafiles are supposed to be excluded from the VM backups, so why is the differential VM backup freezing IO on my datafiles?

Thanks!

 

5 replies

M
Rank
Userlevel 3
Badge +6

Hi @Chris Newman 

 

This can happen when you are taking application consistent snap shots with the VSA backups, as those engage the VSS Writers.  If you switch to crash consistent backups, that should stop that from happening.

C
Rank
Userlevel 5
Badge +14

Hello @Chris Newman 

It sounds like you are using AppAware in your VSA Backups. AppAware backups will pause the database so the VSA snapshot can be taken. Once taken, the Application is resumed.
 

Application-Aware Backups - https://documentation.commvault.com/2023e/essential/106239_application_aware_backups.html
Application-aware backups capture the state of application data at the time of the backup, including data in memory and pending transactions, making it easier to restore the application for continued use. An application-aware backup for a virtual machine quiesces the application, flushes its memory, and completes pending writes, then creates a software snapshot of application data. After the software snapshot is created, the application resumes.

Thank you,

Collin

C
Rank
Userlevel 1
Badge +5

Thanks Collin!  I have a couple follow ups if that’s OK.

These backups would be in addition to the sql agent database backups correct? 

Databases captured with the VSA would only be useful for a PITR to the exact time the snapshot was taken, correct?

Is there any practical use/need for the VSA backup on databases that are being backed up through the SQL agent? 

One of our proposed instance upgrade techniques is taking a VM snapshot, taking full instance backup, then upgrading in place.  If databases are included in the VSA, it makes the VM backups take far longer.  Does this sound correct?

If I’ve stated the above correctly, would it still make sense to include the MS SQL system databases in the VSA snapshot to facilitate recovery? IE, VM restored, now I can start my database restores.

Thank you, I really, really appreciate the help on this.  It’s been puzzling us for some time.

  • Chris

 

S
Rank
Userlevel 6
Badge +14

@Chris Newman 

No application aware backups allow a VSA backup of the VM then SQL application backup all with an automated workflow. If needing to restore a SQL database, you would browse from the SQL agent on that VM.

Is there any practical use/need for the VSA backup on databases that are being backed up through the SQL agent? 

 

Generally no the SQL agent backup is all you need to restore the DBs to any location.

Remember while a VSA backup will capture everything on server that does not mean the databases are consistent at the time of backup as they are constantly in use. This is why you need SQL agent backup to ensure the database backup is consistent. Also, for managing/truncating transaction logs.

For system databases they would be included in all backups by default. important note here, if you recover the entire VM there is NO guarantee that MSSQL services will come up after restore and you might be required to perform some type of SQL service rebuild.

 https://documentation.commvault.com/2023e/essential/18336_full_system_recovery_sql_server.html

C
Rank
Userlevel 1
Badge +5

Perfect, this helps, thanks!

Thanks- Chris

Reply


Terms & ConditionsCookie settings