NetBackup 7.5 V-Ray Technology on SQL VMs and Transaction Logs

[savas_irez]

Hi,

One of our customers is using NetBackup 7.5.0.4 V-Ray technology to backup their Virtual SQL servers. The backups are running fine, but there is a question, consider the following situation:

1. The customer takes V-Ray backup during the night (and truncate the logs)

2. When the day starts, they start taking backups of transaction logs hourly, then truncate the logs

3. After the end of the day they take another V-Ray backup and so on...

Is this setup correct and can it provide us an RPO of 1 hour during the day?

Thanks.

[RLeon · Accepted solution]

The following is from a non-public document.
As a Symantec Partner, you should have access to it.
I tried but I couldn't find any public references to the points made by the document.
Hopefully they will put this important information in the publicly available Nbu VMware / SQL admin guides. But for now...

NetBackup 7.5 Feature Briefing - Application Protection for VMware Virtual Machines:
Under the VM MS SQL Server section, regarding the use of the VMware policy to backup entire VMs together with the SQL server inside:

  ...
SQL logs may optionally be truncated using a policy setting. This requires installation
of the Symantec VSS provider as noted above.
If this is selected, logs are backed up to the OS temp directory and deleted. The process occurs for each eligible database.
  ...
Backup restrictions:
- No differential (BLIB) backup support
- No clustering support (I.e., if your sql servers are VMs, they must run as standalone servers)
- No mirroring support (Same as above)

Restore Restrictions:
- Full database restore/recovery only
- Consistency check is disabled
- Page verification is disabled
- Mandatory restore with replace

VM Database backups cannot be mixed with legacy agent backups:
 - Transaction Logs
 - Differentials
 - Filegroups
 - Filegroup differentials

If your VM SQL servers are not running in cluster/mirror configurations, then you can back them up the way you are suggesting, and have the Sym VSS provider truncate the T.logs. However, do not backup the T.logs using a separate MS-SQL-Server policy-type policy because it is not supported.
This makes sense because the policies will break each other's T.log chain, which breaks the restore when T.Logs are used in the process.
Since the above would imply that you'd have to backup the entire VM hourly (according to your T.log backup requirements), I would suggest to instead do SQL backups separately using a MS-SQL-Server policy type policy.

If your VM SQL servers are running in cluster/mirror configurations, then you have to use the MS-SQL-Server policy type to back them up, which does not support having the backup traffic going through the SAN in anyway, and must go through the LAN. (I.e., no SAN transfer mode, and no SAN client because SQL is in VM)
You can still separately backup the individual SQL cluster/mirror nodes using the VMware policy type, but you have to uncheck the check box that says to also backup the SQL app.
In this case, for entire cluster node restores you can first restore the entire VM of the node, then either let the application's own cluster mechanisms handle the rest, or proceed further and restore the SQL DB backup on top of it, which is a two-step restore: OS first, then application.

1. The customer takes V-Ray backup during the night (and truncate the logs)

Only supported if the SQL server is not running in clustered or mirrored configurations.

2. When the day starts, they start taking backups of transaction logs hourly, then truncate the logs

Not supported. The backup will complete successfully, but the two policies will break each other's transaction log chain. This will cause problems during restores.

3. After the end of the day they take another V-Ray backup and so on...

Same problem as the above.

Is this setup correct and can it provide us an RPO of 1 hour during the day?

You'd have to backup the entire VM hourly to provide a SQL RPO of 1.
That is why I recommend treating the OS and the SQL server as unrelate entities and backing them up separately. That way, you will have an OS backup every night, and a SQL RPO of 1 hour.
There are currently too many limitations when you try to protect both the OS and the SQL app in one swift VM level backup.

[V4]

@Rleon: I suspect following could be pointing to NBU legacy methods

VM Database backups cannot be mixed with legacy agent backups:
 - Transaction Logs
 - Differentials
 - Filegroups
 - Filegroup differentials

We had previously one of talks with MS team on best practises where in there would not be any harm if NBU backs up Full DB and trucates the logs. and customer relies with incremental log backups for short RTO

T.Chain with NBU would break if NBU has Full + Diff or T.log backup scheduled and there is intervention of SQL T.Log backup interval which causes NBU to lost it's hold over chain resulting diff/t.log backups are not 

restored as expected.

Only Full backups can be restored.

However savaz , it is best practise to rely on 1 vendor for backup purpose. Do not indulge in cocktail .

NBU is best of breed. It protect SQL the way SQL DB wants it. Full, Diff, File group,  Trnx. Log etc.

DB often does not trust method they are not familiar with and they rely on their traditional method of creating sql jobs to schedule backup jobs.

Your method should not have any issues as long as you are protecting FULL backup through NBU.

[savas_irez]

@RLeon: What about differential V-Ray backups using "Changed Block Tracking" ? I see that setting is significantly reducing the diff backup time of vm backups. What if I backup only with V-Ray every hour (diff)?

@Captain: I'm trying to test my consideration to make sure it works... Everything is backed up under NBU so I'm feeling lucky...

Thanks.

[RLeon]

Netbackup's Block Level Incremental Backup (BLIB) uses VMware's Changed Block Tracking (CBT).

If you reread my reply, the document has the following to say about BLIB with SQL:

Backup restrictions:
- No differential (BLIB) backup support

That means if you enable the "Enable SQL Server Recovery" checkbox, the backup will not use BLIB/CBT.
If you backup this entire VM hourly, the SQL DBs will always be a full backup. Deduplication might help.

To Captain:
Yes, we are referring to alternating between using Netbackup's VMware-policy and SQL-policy to protect the same SQL server, which is not supported.

[savas_irez]

I think still the best way to achieve the RPO of 1 hour is:

1. Have traditional full vm backups weekly

2. Have BLIB vm backups daily

3. Have traditional full sql agent backups daily (or weekly according to the customer needs)

4. Have traditional TX log agent backups hourly.

I can not use V-Ray because I need to have full backups daily (Cant use BLIB). Although deduplication seems to help with space needs, all of my customers demand to have a copy of vm backups on tapes and all copies are un-deduped while being copied to tapes. Which means more tapes than BLIB backups. Addition: Also, BLIB's are faster than full backups.

This way, when a disaster occurs on any virtual db:

1. I will restore the vm (full + BLIB)

2. I will restore the full db backup

3. I will restore the TX log backups

So  at worst case, I will loose 1 hour of data.

What do you think?

Thanks.

Edit: Looking at the previous posts, it seems like I was repeating the same things that was mentioned by RLeon and captain. But writing the steps altogether looked better, so I'm not removing.

Edit2: I still intend to test full V-Ray backup (+truncate) + TX backups, as soon as I find an ESX system to work on...