SnapVault/PM SQL backups

[marcconeley]

Forums to the rescue!

I am looking for some guidance on the best way to backup my critical SQL server.

Currently I am backing it up as follows:

- Daily SMSQL full backups (every evening) - which initiate SnapMirror to secondary site (for DR).

- And 30min SMSQL log file backups which also initiate SM update to secondary site.

So every 30mins, data gets mirrored offsite. Good.

The problem with this setup is that DR is very good, but it's actually backup that I need (and in the local snapshots, due to space limitations on the primary SAN, I don't wan't to keep 30 days of snapshots for example.

So now I have thrown Protection Manager/SnapVault into the mix with a backup site SAN, but I can see that it's not possible to run a log-file only SMSQL backup and archive it to my backup SAN!?

What I would like to do is keep only 2-3 most recent snapshots locally, but then keep around 30 days archived on my backup SAN.

And continue with 30min tlog backups, which also get archived to my backup SAN.

SMSQL only gives the option to "archive" full backups though and not tlog only backups (unlike the SnapMirror option, which allows you to initiate a snapmirror relationship update after a tlog backup also). So I can run my daily full backup and archive it, and keep 30 days worth on my backup SAN, but what do I do about the 30min tlog backups? These will only be on my primary SAN, and not mirrored or backed-up!? 😕

I am guessing the only way around this is to run SnapMirror backups, and SnapVault/PM backups also? So I continue performing full daily backups which get mirrored, and continue running 30min tlog backups that get mirrored, but then in addition run a daily snapvault/PM backup of the databases (which I can then keep for my desired 30 days on my backup SAN).

Or should I simply ditch the SnapMirror backups if I don't need them, and run a full SQL backup/archive every 30mins using PM?

(I have no idea what impact this would have if any on the primary SAN).

As long as I can't lose more than 30mins of data, I am good. And as long as I have 30 days of backups, I am also good.

The DR of snapmirror is nice, but not essential.

If anyone has any suggestions or recommendations on the best way to do this I would be happy to hear them!

Thanks,

M

[andrc]

It all comes down to your rquirements for each of the following:

Local snapshots

DR requirements

Archive requirements

A backup of the DB itself is a point in time backup and frequent transaction log backups are required for up to the minute restores; the lower your RPO, the more frequent the tlog backups.

SMSQL can restore a databse by replaying backed up transaction logs but how about a DB backup (no logs) on the hour with SM update and a tlog backup at 30 minutes past the hour with SM update. That way in a DR scenario you have your 30 minute RPO by restoring the previous DB backup directly or by restoring the DB backup and then replaying the logs from the recent tlog backup.

For archiving purposes you then run a full daily backup with verification which is SnapVaulted with the 30 day retention on the secondary.

[marcconeley]

Question regarding this:

So basically, if I have Protection Manager (which is basically SnapVault), SnapMirror and SMSQL and I want to achieve a max of 30mins data loss on my SQL databases (i.e. move data off the primary SAN), then I have 2 options:

- Run a full database backup every 30mins and archive it to my secondary storage.

- Or  run a full database backup (daily or every 4hrs depending on my needs), with 30min tlog backups - and both jobs initiate a snapmirror update.

- Or as you suggested, run a mixture of both (which is what I am going to do).

It's a shame I can't archive the 30min tlog backups - although I guess this makes sense technically. Instead you need to snapmirror them.

And transaction log shipping is too complicated when I have snapmirror licenses.

Is my understanding correct?

Regarding my requirements, I have to provide a max of 30min data loss in the event that the primary SAN gets canned. So my current daily backup with archive and 30mins tlog backups isn't sufficient (as the tlog backups are stored on the primary SAN).

Thanks!

[andrc]

I've seen some customers that use DB log shipping instead of SnapMirror in order to meet RPOs. One restriction of SMSQL is that only one backup job can run at a time and in order to meet a strict RPO you need frequent backup jobs so the scheduling can get pretty tight. If you use some form of DB replication for DR purposes then that frees up SMSQL to run full backup jobs with verification and archiving, but if you have jobs running every 30 minutes for DR then it's hard to squeeze in a full verified daily backup as well.

[abhisek]

We do not have the option to archive only log backup right now. Also keep in mind that we do not support log backup if log-shipping is set on the db.

A SnapManager backup set consists of the Snapshot copies of the LUNs that store the SQL Server databases and the SnapInfo directory that is created as part of the backup operation:

SQL Server database Snapshot copies

SQL Server transaction log Snapshot copies

SnapInfo directory Snapshot copy

All of the above components must be archived for you to successfully recover and implement a point-in-time restore.

Regards,

Abhishek

[marcconeley]

OK guys.

So I think I've now nailed this.

I run a daily full backup which I archive to my backup filer and also initiates a snapmirror update to my DR site filer.

I run a 30mins tlog only backup which initiates a SM update to my DR site filer.

So in summary:

- I have 3 days of local snapshots on the primary SAN, and therefore on my snapmirror destination also (with only 30mins of lag).

- I have 30 days of archived full backup snapshots on my backup SAN.

In the event my primary filer is offline, I have a SM of the logs, databases and snapinfo at my DR site.

In the event of database corruption, I can restore data from my local snapshots going back to any point in the last 3 days, or from my backup SAN going back 30 days.

Thanks for the help guys.

[andrc]

What you can then do with Protection Manager is create Datasets and Policies for the SQL data and set thresholds for lag, that way if any jobs or replications fail you can have Ops Manager send out notifications.You don't need to apply schedules as SMSQL jobs are already scheduled on the SMSQL server.

With regard to your current setup, do you notice the 30 minute tlog backup jobs failing during the full daily backup job? Unfortunately SMSQL can only handle one active backup job at a time so it's something you'll probably have to live with for the time being.

[marcconeley]

Exactly. I am using Protection Manager to monitor both the archiving jobs, and the snapmirror jobs.

And you've actually hit on a few of my annoyances with Protection Manager and NetApp tools in general here! The fact that I have PM but still need to configure schedules in multiple different places, through different interfaces is really annoying. So yes, we schedule some SnapManager schedules using SnapManager, and some SnapMirrors using SnapManager, other SnapMirror schedules on the filers. And then SnapMirror settings aren't yet available in System Manager interface - so I use the command line or Filerview, but then you also have regular need to edit the "snapmirror.conf" file (especially if you wish to disable a snapmirror schedule!). They really don't make it easy for anyone trying to get away from the command line!

And yes, across all our SnapManager products we have the problem that you mention regarding only 1 active backup can run at the same time. So for example, we don't run our 30min tlog backups during the full snapshot backup window (which can be quite long if you're running a verify too). Again, it would be nice if this "intelligence" was built in to the tools 😕