We have recently run into a challenge with DFM. We have the following setups:
A SQL Server with typical 2 to 6 instances installed on the server, we have SD and SMSQL with DFM integration, and we would like to use the Backup (SnapVault) option.
Because we want to limit the number of volumes on the primary Filer we typically have two volumes for each server, one for the databases and one for the logs.
The databases volume has several qtrees with a LUN in each, much the same with the logs where we have both SQL Logs and the Snapinfo LUN on the same volume.
The is all supported and works well, one might argue that having 6 instances on one volume might not be wise, but this is our design so far 🙂
The problem is not on the primary side, but much more on the secondary Filer where we reach the maximum number of snapshots for a volume.
When we add the provisioning policy to the dataset, DFM creates the destination volume just fine, and also creates this as on the primary, that is a qtree for each LUN.
Sadly SMSQL is not able to do a snapshot of all the instances at once, and therefore it creates one per instance, this is also the case when it creates the secondary snapshots.
So for every backup cycle we get from 2 to 6 snapshots dependent on the number of instances on the SQL Server.
So there are two questions I guess...
1. Is it possible to limit the number of snapvault snapshots SMSQL creates ? (I don't think so)
2. Is it possible to make DFM create one volume per LUN on the secondary Filer ?
Well in theory is should be possible to do this manually, and then import the relations into the dataset, but it's kinda a hassel and I was thinking if there was an easier way.
I know that dfm options list has an option called dpMaxFanInRation which I am not sure has any function in this example. It is set to 1 now, and it still creates several qtrees on the secondary volume.
One option might be to call a script when creating the secondary volume, there is an option called dpSecVolNameScriptPath... and dpSnapNameScriptPath.. but I'm not at all sure how they work and if it can help me at all in this case?
Started to search for the dfm options, and ran into this one: pmMaxSvRelsPerSecondaryVol which should "fix" this.
Only problem now, is that this is a global option (I guess?) and we have many other relations where is it OK to have several relations.. (like CIFS volumes).
But I guess for now we can live with the option to set this to 1, add this provisioning policy to the dataset, and then clear it again.
Another issue we have now is that we only found out about this issue after running full of snapshots on the secondary... I guess there is no easy way to "migrate" the qtrees to new volumes... I guess I'm looking at a baseline here... (or several baselines) ?
I found a solution to my problem, in fact SMSQL does not do a snapshot per instance, it is infact able to do one snapshot of several instances.
SMSQL has an backup option (available from the SMSQL Backup Options menu) which by default is set to 35.
So if you have over 35 databases in your backup (in one instance or in several instances) SMSQL will do more than one snapshot per backup cycle.
After setting this setting to 255, SMSQL only do one snapshot per backup.
Please be aware that there may be some performance issues with many SQL databases and Snapshotting, mainly because Windows has do its VSS thing of all the databases, which may cause some performance issues.
We have chosen 255 and will keep an eye on the SQL servers for performance issues during backup, until now there is no problems, but we are also no where near 255 snapshots yet 🙂
The backup option is number of databases in a backup group. SMSQL sends the VDI commands to freeze the databases at the SQL level, and as I'm sure you are aware each action requires additional SQL threads. On a lightly loaded server, or a very powerful one, using a large number of threads may not be an issue, but on a heavily loaded or low-resource server this can be a problem so that must be considered when using this approach.
Also, VSS is never used in an SMSQL backup. We use SQL VDI to quiese the databases for snapshots, then call Snapdrive to create the actual snapshot. Snapdrive uses Windows API calls to flush the write buffer to ensure a consistent snapshot. However, you are right that selecting too many databases with that setting can cause performance issues, because SQL has to freeze IO to every database included in the backup group (backup group as defined by that setting). If you are freezing I/O on 255 databases at the same time, that can take some time if the server is busy. While the databases are frozen all writes to the databases are held, so if this part of the backup takes too long, you can see application-level timeouts.
The bottom line is this is not necessarily a bad thing, but as you stated, you do need to keep an eye on server performance when increasing the number of databases in a backup set.