SMSQL and snapinfo directory

[yjahangiri]

I am trying to setup SMSQL on a standalone sql box. The Storage lay out i have is as follow

1. Lun for user databases (Volume is sized to hold 14days of snapshot with 10% ROC, created with snap drive)

2. Lun for Transactional logs (Volume is sized to hold 14days of snapshot with 10% ROC, created with snap drive)

3. VMDK for OS

4. VMDK for System databases

When i run the configuration wizard (do not need to migrate databases etc) and get to snapinfo directory screen, i get following message

[abhisek]

Hi,

The configuration wizard is used mainly to migrate the db’s to NetApp storage from local disk. From the screenshot it seems that you do not have any existing LUN for the snapinfo directory.

For snapinfo directory you need a dedicated LUN.

Regards,

Abhishek

[yjahangiri]

Thanks for your response. I will appreciate it if you can clarify this for me. I created the volumes for both user databases and Tlogs and made them big enough to hold the snapshots etc. Should i be creating a separate LUNs in both the volumes to hold the snapinfo directory? I did not create any LUN for snapinfo directory.

You mentioned that config wizard is ran only to migrate the data, but all the documentation says that you need to run config wizard if you add new databases. It also helps put the snapinfo directory at the right place.

[abhisek]

Config wizard would definitely be used to add new databases and getting your snap info directory in place apart from migration.

I can understand that you have created separate volumes for both user db’s and tlogs. Having separate LUN’s for userdb’s and logs is the correct way.

However SnapInfo cannot share a LUN or VMDK with any database files.So you need to create a separate LUN for SnapInfo directory.

Regards,

Abhishek

[yjahangiri]

Thanks for your response, my question is

Where should I craete snapinfo LUN. I can put it on TLogs LUN (Not databases LUN) as well. But how about sizing the TLogs LUN or a new LUN for snapinfo. Is there any documentation I can find how to setup snapinfo directory like sizing the volume LUN etc for snapinfo.

[Darkstar]

This is described in detail in the admin guide, and there's also a few TRs on SMSQL best practices.

Sizing basically involves calculating the size of the logs per day (for example, 100mb of logs per day) multiplied by the number of days you want to keep your snapshots (for example, 21 days). In that case the snapinfo lun would have to be at least 2.1gb, although i'd rather make it at least 50% bigger (you can increase its size on demand later though) since there have been some cases of SMSQL not correctly deleting old SnapInfo entries when it deletes the corresponding snapshots, so if you don't watch out it could fill up your disk pretty fast.

Oh and you can of course share the SnapInfo LUN between multiple databases on the same server (even in different instances) which is great because it saves you from creating 20 new LUNs just for SnapInfo

-Michael

[yjahangiri]

Micahel,

Thanks for your response. I saw the admin guide and few best practises papers. I still can not understand the snapinfo directory. The volumes that have the LUNs for user databases and transactional logs have been resized so they can hold the snapshots in them, which means I am already setting up the fractional reserves on those volumes. Now I need to create another volume for snapinfo directory that also needs to be big enough to hold everything. This way I will be using so much space for snapshots. Maybe it is an easy thing to understand and I am missing a point here.

[radek_kubka]

Now I need to create another volume for snapinfo directory that also needs to be big enough to hold everything. This way I will be using so much space for snapshots.

Nope - snapinfo does not keep your actual snapshots (SQL backups). It keeps SMSQL backup catalogue, hence normally requires much less space.

Regards,
Radek

[Darkstar]

Not quite right. Snapinfo stores copies of your SQL logs for up-to-the-minute restorability. For this to work, you have to keep the truncated logs somwehere, otherwise you can only restore from the latest full backup.

If you don't want/don't need up-to-the-minute restorability, you can make your snapinfo LUN quite small (a gig or five).

However, you can then only perform point in time restores (or up-to-the-minute restores from your most recent full backup if logs haven't been truncated yet)

A correct SMSQL sizing should have taken the additional space for snapinfo lun into account IMHO (at least, our sizings always do take it into account)

-Michael

[yjahangiri]

Thanks for your responses, it is making sense now. I want to make sure i really understand and size my snapinfo lun correctly can you please double check for me. I found the formula for sizing snapinfo volume from TR 3821 page 8 and it is as attached file

The values i calculated on my text box are as follow

Total users database size = 40GB

Total System DB Size = 4GB

Daily Rate of change = 10%

Number of online snap shots = 14

Putting all these values in this formula i get really huge size for my snap info directory or may be i am missing some thing here. I am trying to understand this so I can size the snapinfo for all my production SQL and exchange boxes. Thanks for every one's help

[abhisek]

You should create a new LUN for SnapInfo . The main data items present in SnapInfo are

-. Metadata related to SMSQL configuration and backup sets.

-. Streamed full backup of system databases

-. Streamed full backup of user-defined databases that share a virtual disk with a system database

- All backed up transaction logs (through SMSQL)

The following items are easy to size and thus you get a good idea of the amount of space that would be consumed: -

- Metadata - Metadata per backup set is less than 10kb.

-System databases – Total size of all system databases (that can be backed up with SMSQL) except TempDB which are MASTER, MODEL & MSDB = 15MB (at the maximum).

For documentation on sizing of SnapInfo Directory I would strongly recommend TR-3431. http://media.netapp.com/documents/tr-3431.pdf <http://media.netapp.com/documents/tr-3431.pdf>

This will answer most of the doubts that you are facing.

Regards,

Abhishek

[HendersonD]

Do you have NFS licenses for your filer? If so, my suggestion would be to use separate NFS mounts for:

System Databases

User Databases

Logs

Snapinfo

Shrinking or growing them is easy