SnapManager for SQL SnapInfo sizing

[ianaforbes]

Does anyone have any solid SMSQL snapinfo volume sizing guidelines? The SMSQL admin guide does a good job explaining sizing for the databases, logs but not good at all for sizing the snapinfo directory.

http://now.netapp.com/NOW/knowledge/docs/SnapManager/relsmsql211/html/software/admin/plandat4.htm

Is there a formula that I can use for snapinfo? There's one for database and logs volumes but not for snapinfo. I find myself totally guessing what this number should be when doing snapmanager work for a client. How are we supposed to come up with an accurate snapinfo lun and volume size?

Any suggestions?

Cheers

Ian

Message was edited by: Ian Forbes

[mikepapalia]

Ian,

SnapInfo is the streaming log repository used for the SnapManager function that is equivalent to SQL's BACKUP Log statements. It should hold the current tlog and all of the tlogs necessary to restore any point in time, going back to your oldest backup that was indicated with "retain up to the minute." This means that if you took hourly full backups A, B, and C--and tlog backups every 15 minutes, it would look like this: A 1 2 3 4, then B 5 6 7 8, then C 9 10 11 12. With this, if backup B was removed, you would still have the incrementals 1 through 12 in the SnapInfo folder to restore all the way back to A.

If I am just complicating the matter : ) please check out TR3431. Good formulas and examples.

I hope this helps.

[ianaforbes]

Hi Mike. Thanks for the reply. What do A, B, C represent? Different backup sets? I plan on doing probably one full backup daily, replicating that to a secondary site and also doing hourly rolling log snapshots to secondary site (using sdscli scripting). I guess my question is more how do I walk into a customer site and accurately size the snapinfo lun and volume. There are formulas for database and log sizing but not for snapinfo. Even in that TR you gave me section 3.4.5 outlines volume sizing for everything other then snapinfo. Can you specifically point me to snapinfo quantitative sizing?

[mikepapalia]

Ian,

The A, B, and C would be your full backups. In your scenario: if you did backup "C" last night at 12a, then 23 tlogs all day today--and your retention (this is the key) was set for 23 logs--then your tlog LUN would need to be sized for (23 x The Size of the Logs), with some headroom (as you know).

The SnapInfo sizing would be similar, except the SnapInfo LUN will keep this *and retain all logs* going back to your oldest full backup (in this case, "A" and in this case, therefore, about 75 logs). All these logs get retained when you choose "Retain Up to the Minute" in the backup configuration in SMSQL.

[ianaforbes]

Mike - One last question about restore up to the minute capability. Let's say I decide to schedule fill sql backups nightly at 11pm only. If my database has been corrupted at 3PM and I wanted to restore it up to the minute I'd have to restore my last backup (which was 11pm the night before). At this point all of the transaction logs are still in the log lun/volume. They haven't been truncated because I haven't run a transaction log backup. Wouldn't snapmanager for sql be able to take those logs and play the restored database forward up to the minute?

When I backup up the database I didn't select 'retain up to the minute restore capability....', so, I wouldn't have backed up the logs - but according to my example above that shouldn't matter. The logs are still in the volume and should be available for replay. Is that correct?

Now, if I were to run a full backup followed by a transaction log backup that would truncate any committed logs, right? At that point newly uncommitted logs would start to accumulate in the logs volume. If I follow my example again and corruption occured at 3PM, I'd restore the 11pm full backup from the night before. Would I also have to rstore the transaction log backup? Lastly, there would be a bunch of logs that have accumulated since the last transaction log backup. Are they available for an up to the minute restore?

Hope that wasn't too confuisng 🙂

[ianaforbes]

I think I found my answer. It seems that when you perform a restore, a dialog box pops up and states that a transaction log backup before restore will take place. If snapmanager fails to perform a successful transaction log backup before restore the transactions that were logged after the most recent full database or transaction log backup will not be restored to the database, if an up to the minute restore was selected. Some data may be lost after the restore is completed.

So, a transaction log backup is attempted before a restore can happen (for up to the minute capability). I'm still confused as to why we're supposed to retain the logs if the restore process will back them up for us anyways.

[mikepapalia · Accepted solution]

Ian,

Yes you would be covered. But for clarification, "Up to the Minute" would not affect your current logs and your ability to use them for restore from the most recent backup, either way.

Consider our previous A, B, and C scenario: "Up to the minute" gives you the ability to have a retention (for example) of three nights' FULL backups and always have the transaction logs on hand to restore up to the minute for any time after A up to the current backup time--including any time in between, even if... (here's the difference) you *remove* the Full Backup "B". Because you will still have retained all of the logs that were written after A, and after B, and any that have happened since C, you will have all of the database transactions for the whole duration. If you removed Fulls B & C, and wanted to restore up to the minute, you would still have the baseline Full A to recover, then all of the logs to enable you to play back to the time of the most recent snapshot (full or incremental).

One more fyi, once Full A expires, all the logs between A and B would expire from the SnapInfo directory. Then you would have B, C, & D's logs. Once B expires, all logs between B and C drop off, and so on...

I hope this helping...

[ianaforbes]

That's the best explanation of heard with respect to up to the minute yet 🙂 Nicely explained Mike and very much apprecitaed.

Cheers

[ianaforbes]

Mike - Another question would be does snapinfo need to have fractional space reserve at anything other then 0%? The database and log volumes I've set fractional reserve to 100% to ensure writes to those luns (overwrite protection). I don't think that this protection needs to be in-place for snapinfo, does it? If I calculate snapinfo to be 150GB (lun), can I make my volume a little bigger to account for snapshots of that lun? If I mount my backup snapshot it should contain the lun for my databases, logs AND the lun for snapinfo - so I suppose that snapmanager for sql backup protects the snapinfo directory as well. *all quite confusing*

The question is can I set fractional reserve for the volume housing my snapinfo lun to 0%?

[mikepapalia]

Ian,

Allow me to gracefully bow out of having to think through your very logical question by just saying that, "it is NetApp best practices to leave it on" : )

I do know that if that volume runs out of space, bad things will happen.

[ianaforbes]

Fair enough 🙂 Thanks for your feedback.

[marcconeley]

Hi Ian,

What was the final outcome of this?

I have a bunch of SQL servers and I've left the FR at 100% for the SnapInfo volumes (funnily enough, for the db vols I've been experimenting with reducing the FR to say 50% and using autogrow and snap-delete).

Did you ever find out if it's recommended to set the FR on SQL SnapInfo volumes to anything less than 100%?

Thanks,

Marc

[tanmoy]

Hi Marc,

I am exactly not sure if this is the robust solution for SQL SnapInfo volume but NetApp best practice says to use FR=0 with volume autogrow and snap autodelete options. This has been tested and used by customers also.

This is the best thin provisioning configuration we can provide.

An example configuration could be:-

LUN size =500g;

Initial Snapshot size= 200g

And Max- autogrow size = 1TB.

Hence we can create a volume of size 700g ( volume guarantee, FR=0) and set the max-size set to 1TB; hence in this case if volume goes out of space when snapshots are being taken, it can grow till 1TB provided aggregate has space.

Hence I don't see any problem in setting the FR to any value other than 100.

Thanks

Tanmoy

[sharaf]

Hi

You can refer a TR which might give some idea

http://www.netapp.com/us/library/technical-reports/tr-3729.html

Regards

sharaf

[marcconeley]

OK - I've actually got another thread running regarding this issue: http://communities.netapp.com/message/19374#19374

My concern is how SnapMirror behaves if we do this - if we set the FR to 0% on a snapinfo volume, ,and enable auto-grow and snap auto-delete, then what happens when the vol auto-grows? Will the target snapmirror vol also autogrow, or will snapmirror fail because the source is now larger than the target?

Typically we have 2 types of snapmirrored vols: ones which we manually resize (and then we remember to also resize the snapmirror target), and vols which we allow to autogrow - and it's these vols which I am not sure about.

I took a look at the below provisioning TR, but I couldn't find anything useful in it.

Thanks for the help so far.

Marc

[tanmoy]

Protection Manager takes care of the snapmirror destination volumes which have autogrow enabled on the source volumes. This is taken care of when snapmirror initialize takes place. In case of manual snapmirror I think it will be wise to set the mirror vol size to autogrow size in the beginning itself and the vol_fs_fixed size as the source initial size.

Thanks

Tanmoy

[adaikkap]

When protection manager provisions a snapmirror destination volume it creates it to the size of the containing aggregate with volume guarantee set to none.

So you dont need to autogrow the volume.

Regards

adai

[marcconeley]

We are not using Protection Manager - I am simply using SnapMirror that I've licensed on my filers.

We have 2 FAS2020s which I am snapmiroring vols between - in this case, I am mirroring an SQL snapinfo vol and the snapmirror is being intiailised by the SMSQL application installed on my SQL server.