Backing Up SQL VM Server Without Client Agent

[AndrewPilgrim]

Hi,

Just looking at an issue that is confusing me, again. A project has just built a new Windows (Windows 2008 Std R2) VM and it is running SQL (SQL 2008 R2). The client does not want to pay for the SQL client agent so they are running nightly jobs to backup the SQL d/bases locally to disk as a flatfile/export.

This is on a 7.5.0.5 environment.

With this in mind i added the VM server to an existing policy that uses Flashbackup-Windows for its policy type, backups ALL_LOCAL_DRIVES and it seemed to work okay to start with but now we have noticed when a backup is done it actually triggers a SQL database backup locally on the server (See logs example)

These start times are bang in line with the start time of the backup on NBU. The actual job in NBU always finishes with Status 0 and i see no errorrs in the detailed status for the job. I have even tried a test using VMware as the policy type to see if it makes any difference.

database_name	backup_start_date	backup_finish_date	mins	type	Size in GB	physical_device_name
master	2013-07-01 01:30:55.000	2013-07-01 01:31:09.000	1	D	0.00287	{BC90CF83-E0EE-4669-9631-A43F8DB76A08}5
model	2013-07-01 01:30:55.000	2013-07-01 01:31:09.000	1	D	0.001411	{BC90CF83-E0EE-4669-9631-A43F8DB76A08}1
msdb	2013-07-01 01:30:55.000	2013-07-01 01:31:09.000	1	D	0.023324	{BC90CF83-E0EE-4669-9631-A43F8DB76A08}2
WebSeries	2013-07-01 01:30:55.000	2013-07-01 01:31:09.000	1	D	0.077227	{BC90CF83-E0EE-4669-9631-A43F8DB76A08}3
sysdb	2013-07-01 01:30:55.000	2013-07-01 01:31:09.000	1	D	0.00409	{BC90CF83-E0EE-4669-9631-A43F8DB76A08}4
master	2013-07-02 08:09:47.000	2013-07-02 08:09:59.000	0	D	0.002876	{FD632596-EF30-4DE1-AEE0-3495CCDE5E64}5
model	2013-07-02 08:09:47.000	2013-07-02 08:09:59.000	0	D	0.001411	{FD632596-EF30-4DE1-AEE0-3495CCDE5E64}1
msdb	2013-07-02 08:09:47.000	2013-07-02 08:09:59.000	0	D	0.024099	{FD632596-EF30-4DE1-AEE0-3495CCDE5E64}2
WebSeries	2013-07-02 08:09:47.000	2013-07-02 08:09:59.000	0	D	0.078264	{FD632596-EF30-4DE1-AEE0-3495CCDE5E64}3
sysdb	2013-07-02 08:09:47.000	2013-07-02 08:09:59.000	0	D	0.00409	{FD632596-EF30-4DE1-AEE0-3495CCDE5E64}4

I am trying to get the server team to advise if the server was built from a clone or a golden image and what has been done recently incase there is something on the server causing the problem. They have told me there is definately no agent installed on it though.

In the meantime i wondered if anyone has any thoughts on what could be causing this?

[Yasuhisa_Ishika]

It seems exactly same with this blog. VMware snapshot kicks VSS SQL writer to quiesce SQL, and this is logged in SQL Server as backup.

http://www.stuartclowes.com/2011/10/when-is-sql-backup-not-sql-backup.html?m=1

[AndrewPilgrim]

Yeah - Exactly the same as that blog. I have forwarded that blog and links to the server/dba team to look at and review as well as compare things like the SQL Writer Service with other machines that are the same spec and backed up the same.

Shall wait to see what happens now.

[RLeon]

Are you saying that you do not want MS SQL to know that a backup has taken place during VMware .vmdk level snapshot backups? There is normally no harm in MS SQL knowing.

A workaround is available from VMware kb that allows you to stop the SQL VSS Writer from being called during VMware .vmdk level snapshot backups. This will prevent MS SQL from knowing that a backup has taken place.

However, by doing this, your VMware level backups would contain SQL databases in inconsistent states.
(But then, since your client is already doing manual SQL backup to flat files, this issue should not really be a concern unless the VSS triggered backup records are somehow interfering with their incremental backups.)

Here is the link to the VMware kb article:
http://kb.vmware.com/kb/1031200

A similar discussion:
https://www.symantec.com/connect/forums/sqlvmware-problem

[AndrewPilgrim]

Hi RLeaon

Like you, i dont see the harm in SQL knowing a backup has happened. But i shall forward that VMware kb article to the server guys and let them decide what they want to do.

On think you mentioned though i am not sure about. When talking about inconsistent state backups -  "unless the VSS triggered backup records are somehow interfering with their incremental backups.". What did you mean when talking about interfering with incremental backups?

[RLeon · Accepted solution]

On think you mentioned though i am not sure about. When talking about inconsistent state backups -  "unless the VSS triggered backup records are somehow interfering with their incremental backups.". What did you mean when talking about interfering with incremental backups?

Without having the SQL VSS writer invoked during a VM level backup, the databases captured along will not have been properly quiesced. They will be inconsistent, or some would call it crash consistent, or simply "corrupted".

By allowing the SQL VSS writer to be invoked, the databases in this vm level backup will be consistent and fit for recovery. However, SQL will know that a full backup has taken place.
Depending on how they are doing SQL flat file dumps, some people prefer to have their SQL differential backups reference the full backup that they know they have manually created previously, and not the one created by the sneaky VSS writer sometime after the last manual-full and before the next manual-differential, when nobody was watching. If all their manual backups are fulls, then they will not have this issue.