Subscribe

VMWare SQL database snapshots using Snapprotect

Wondering if there's a way to stop getting 'I/O is frozen on database.....' type SQL log messages while doing snapshot backups of SQL Servers on VMWare. Those simply occur because snapprotect locks the database in order to assure backup integrity but this locks out users specially while doing the master database. SQL database backups are usually done with SQL jobs or maintenance plans that save database backups under a different folder than where they reside. Our usual server backup method was to backup the server while excluding the 'live' database folders (where the mdf reside) in order not to lock them but I'm told this can't be done while taking snapshot of VMWare systems.

So basically, can you exclude folders from VMWare systems' snapshots and/or can you take SQL backups without locking the files?

Re: VMWare SQL database snapshots using Snapprotect

is this one that your team handles or should the question be asked in the NetApp Support Community?

Re: VMWare SQL database snapshots using Snapprotect

There is a checkbox in the subclient properties... "Application aware backup for granular recovery".  You could disable that to prevent the database from being quiesced, but I assume you want the database integrity during the VMware backup.  I don't know of another option given SQL on VMDKs.

Re: VMWare SQL database snapshots using Snapprotect

I’m not familiar with the application but looking at it from the DBA perspective. I don’t need to have the snapshot take a backup of the databases since I have SQL jobs do that. Those backups are saved under a different folder than where the mdf reside. So basically, can the snapshot for virtual SQL servers under VMWare be configured to exclude the folders where the mdf are located?

Re: VMWare SQL database snapshots using Snapprotect

The VMware backup will take a snapshot for all datastores associated to the VM.  Snapshots are done at a NetApp volume level so there is no way to exclude something from a snapshot.  Unchecking that checkbox should prevent I/O freeze on the database.  However, unchecking that box will affect any VMs associated with that subclient.  Whoever manages your SnapProtect environment should be able to help determine if this is the appropriate action to take.

Re: VMWare SQL database snapshots using Snapprotect

You can create multiple VMDKs in the same Volume/Datastore....they will get backed up together since we do snapshots at the volume layer.

If you place databases in separate VMDKs in that volume, then SMSQL will use a single file snaprestore (SFSR) to restore the database if it is the only database in that VMDK.

If you place more than one database in a single VMDK, then the restore path is a copy out from the snapshot to the active file system.

Thanks,

Tom

Re: VMWare SQL database snapshots using Snapprotect

Sounds like this is SnapProtect... not SMSQL.

Re: VMWare SQL database snapshots using Snapprotect

I guess you answered it best along with how I think the backups (snapshots) to be done for VMware systems. The system will just take the VW file basically and not open it up to see what’s inside and go from there. It is not aware of what resides within that virtualised system so can’t accommodate for it.

Re: VMWare SQL database snapshots using Snapprotect

Well... it can be aware of what's inside the VM, but that process is something that takes place after the snapshot is created. 

Re: VMWare SQL database snapshots using Snapprotect

Hi Serge ,

It is the  SQL Writer which freezes IO for the SQL databases when a snapshot operation is initiated. As snapshot is taken at datastore level ,what you can do is keep SQL db files and VM/OS related files on different datastores .This will help you avoid SQL db files from being frozen during VM level snapshot and you can uncheck the subclient property "Application aware backup for granular recovery" option.

Regards,

Abhishek