Data Backup and Recovery

MSSQL backup with SnapCenter Plugin for vSphere vs SnapCenter plugin for MSSQL

JS75
2,378 Views

Hello All,

 

My infra is 100% virtualized with vSphere. What are the differences/advantages of taking MSSQL server backups with just SnapCenter Plugin for VMware vSphere (with VM consistency ON) vs SnapCenter plugin for MS SQL? The SCPV is triggering MSSQL VSS quiescing/snapshot via VMware Tools, so there should be no downsides here. I understand it does not have an option to backup TLogs, but is it the only downside?

 

regards,

Jacek

1 ACCEPTED SOLUTION

Ontapforrum
2,185 Views

In general, Microsoft VSS framework coordinates Snapshot copy-based backup and restore operations and includes these components:

 

MS Framework:
VSS Requestor = The VSS requestor is a backup application, i.e SnapCenter-SQL_Plugin (Application specific).

VSS backup application  will explicitly select the databases from the metadata that is returned from the "SQL writer". The snapshot set should include all the volumes necessary to back up those databases.

 

VSS Writer = The SQL writer is a VSS writer provided by the SQL Server. The VSS writer that owns and manages the data to be captured in the Snapshot copy, i.e SQL_Writer for MSSQL.

 

VSS provider = The VSS provider is responsible for creating and managing the Snapshot copy, in this case Data_ONTAP_VSS_Hardware_Provider. (To ensure that the Data ONTAP VSS Hardware Provider works properly, do not use the VSS software provider on Data ONTAP LUNs.)

 

In order for Application-consistency: VSS Requestor coordinates with VSS, which in turn coordinates with Writer (SQL Writer) to ensure write requests are frozen and write caches are flushed before you make backup copies. If there is no plug-in, this will not be coordinated and you may end up taking VSS file-system snapshot, which could mean the backup is not 100% application consistent and may not be able to restore to the same state (Especially for the DB files hosted on the NetApp LUN.)

View solution in original post

8 REPLIES 8

Ontapforrum
2,353 Views

SCV, will only provide Crash & VM-Consistent Backup : Crash consistent by taking a snapshot of all the data found on a disk at the exact same time without any awareness about the underneath file-system. VM consistent backups will provide file-consistent backup with the help of "VMware Tools to quiesce the VM file system" here MS VSS come into picture. However, it is still unaware about the Applications(DBs Pending IOs).

To ensure SQL DB is consistent (hosted inside a VM), there must be a application-aware components such as SCSQL (Which coordinates with MS VSS), inorder to pause & and flush the in-transit/pending IOs to the disk, before the application-aware consistent backup is taken.

 

SnapCenter Plug-in for VMware vSphere, works with SnapCenter Server to support application-based backup and restore operations in VMware environments for SnapCenter application-specific plug-ins. Therefore, plug-ins for respective Applications(Databases) are necessary. The Plug-in for SQL Server automates SQL Server database backup, verification, restore, and clone operations in your SnapCenter environment.

https://docs.netapp.com/us-en/sc-plugin-vmware-vsphere/

 

Related kb:
Do SnapCenter VMware backups conflict with SnapCenter for SQL backups ?
https://kb.netapp.com/Advice_and_Troubleshooting/Data_Protection_and_Security/SnapCenter/Do_SnapCenter_VMware_backups_conflict_with_SnapCenter_for_SQL...

 

JS75
2,226 Views

As far as I understand - SCV works with VMware Tools, which quiesce the filesystem and create VSS snapshot. Since MS SQL is VSS aware, the MSSQL backup with just SCV has to be app-consistent. SCV can't do TLog backups (obvious, as it's agentless), but there is no reason point-in-time DB state would be inconsistent, provide VSS Writer in the OS does it's job.

elementx
2,222 Views

> Since MS SQL is VSS aware, the MSSQL backup with just SCV has to be app-consistent. 

 

Earlier comment by OntapForum:

 

> To ensure SQL DB is consistent (hosted inside a VM), there must be a application-aware components such as SCSQL (Which coordinates with MS VSS), inorder to pause & and flush the in-transit/pending IOs to the disk, before the application-aware consistent backup is taken.

JS75
2,220 Views

That's exactly what VMware Tools does when VM consistency is set to 'Yes' in SCV. It's triggering VSS snapshot, which pauses and flushes data to disk. For reference this one - Solved: snapshots, VMware Tools, VSS and application consi... - VMware Technology Network VMTN 

elementx
2,214 Views

I see your point now, when IO is completely quiesced and RAM also saved to disk, there should be no difference how it was quiesced and flushed. I'm not 100% sure about that - it may be true, but I also notice that when you look at other app-consistent backups, there are pre-freeze  scripts and as the name says, they are supposed to complete before quiesce commences. So I'm not entirely sure if OS-level quiesce w/o RAM being saved as well (vSphere Client 6 ignores Quiescing if Memory snapshot and Quiescing are selected at the same time, not sure about later versions) would result in identical consistency as with a SQL-aware snap (with pre-freeze, etc.).

 

I think it's mainly that SQL-aware operations are available, as highlighted by OntapForum, e.g.:

 

SQL-specific:

https://docs.netapp.com/us-en/snapcenter/protect-scsql/concept_what_you_can_do_with_the_snapcenter_plug_in_for_microsoft_sql_server.html

 

vs. generic Windows:

https://docs.netapp.com/us-en/snapcenter/protect-scw/reference_what_you_can_do_with_the_snapcenter_plug_in_for_microsoft_windows.html

 

SQL-specific features may save time on running SQL-specific commands to verify, or clone/restore. It may also (not sure, see [1]) help you get app-consistent snaps on Win OS  with SQL data on dynamic volumes.

 

Some time ago (2-3 years ago) I tested VM-level quiesce  + snapshot under load 10 -20 times in a row, and never experienced a problem with SQL Server coming back. But I recall some transactions would roll back, indicating that they were available in memory, but didn't make it to on-disk transaction log.

You could check in your environment: make a clone for testing, get top INSERTs and run them in a loop while taking one of each (VM-level vs. VM with SQL plugin), and then clone those and observe SQL Server log on each clone's restore and SQL startup.

 

[1] https://vdc-repo.vmware.com/vmwb-repository/dcr-public/8f96698a-0e7b-4d67-bb6c-d18a1d101540/ef536a47-27cd-481a-90ef-76b38e75353c/doc/GUID-12AFEE64-442...

Ontapforrum
2,186 Views

In general, Microsoft VSS framework coordinates Snapshot copy-based backup and restore operations and includes these components:

 

MS Framework:
VSS Requestor = The VSS requestor is a backup application, i.e SnapCenter-SQL_Plugin (Application specific).

VSS backup application  will explicitly select the databases from the metadata that is returned from the "SQL writer". The snapshot set should include all the volumes necessary to back up those databases.

 

VSS Writer = The SQL writer is a VSS writer provided by the SQL Server. The VSS writer that owns and manages the data to be captured in the Snapshot copy, i.e SQL_Writer for MSSQL.

 

VSS provider = The VSS provider is responsible for creating and managing the Snapshot copy, in this case Data_ONTAP_VSS_Hardware_Provider. (To ensure that the Data ONTAP VSS Hardware Provider works properly, do not use the VSS software provider on Data ONTAP LUNs.)

 

In order for Application-consistency: VSS Requestor coordinates with VSS, which in turn coordinates with Writer (SQL Writer) to ensure write requests are frozen and write caches are flushed before you make backup copies. If there is no plug-in, this will not be coordinated and you may end up taking VSS file-system snapshot, which could mean the backup is not 100% application consistent and may not be able to restore to the same state (Especially for the DB files hosted on the NetApp LUN.)

JS75
2,180 Views

I agree with all above, apart of the requirement for SnapCenter SQL Plugin as VSS Requestor. VMware Tools serve the role of VSS Requestor since Windows 2003 (actually usable since Windows 2008), provide 'VSS Support' component is installed with VMware Tools. Working with Microsoft Shadow Copy (vmware.com)

elementx
2,173 Views

If OS and SQL-specific snapshot behave exactly the same, neither should have to roll back after a snapshot restore and OS restart.

Public