VMWare SQL database snapshots using Snapprotect

[TWOCASES2GO]

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?

[crocker]

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

[bwood]

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.

[TWOCASES2GO]

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?

[bwood]

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.

[curtish]

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

[bwood]

Sounds like this is SnapProtect... not SMSQL.

[TWOCASES2GO]

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.

[bwood]

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

[abhisek]

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

[TWOCASES2GO]

Good question Abhishek,

Looks like it could be but check the messages thread below in the order they appear in Windows Events... similar messages are in the SQL log.

Those messages appear of course for each database and in a matter of seconds for the entire SQL instance. Not only does NetApp lock the databases, it doesn’t even back them up since it ends up in failure.

........................

I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=1660. Thread=2452. Client. Instance=. VD=Global\{615F56B5-34A4-4081-9044-5085DFE2B238}28_SQLVDIMemoryName_0.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I/O was resumed on database master. No user action is required.

BACKUP failed to complete the command BACKUP DATABASE master. Check the backup application log for detailed messages.

BackupVirtualDeviceFile::SendFileInfoBegin: failure on backup device '{615F56B5-34A4-4081-9044-5085DFE2B238}22'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).

Which would coincide with the SQL Writer message...

Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013

Error state: 1, Severity: 16

Source: Microsoft SQL Native Client

Error message: BACKUP DATABASE is terminating abnormally.

SQLSTATE: 42000, Native Error: 3271

Error state: 1, Severity: 16

Source: Microsoft SQL Native Client

Error message: A nonrecoverable I/O error occurred on file...Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013

Error state: 1, Severity: 16

Source: Microsoft SQL Native Client

Error message: BACKUP DATABASE is terminating abnormally.

SQLSTATE: 42000, Native Error: 3271

Error state: 1, Severity: 16

Source: Microsoft SQL Native Client

Error message: A nonrecoverable I/O error occurred on file...

....................