New SQL volume and autogrow questions

paulfotner · ‎2011-06-10

We are new to NetApp so appologies if any of these queries are obvious. We are creating a new 2 node win/sql 2008 cluster and are using SMSQL 5.1

1. What are the trade-offs between creating a separate vol for each DB and Log LUN vs combining all the DB LUNs in one vol and all of the log LUNs in a separate VOL? We already know to separate the SystemDBs, TempDB and SnapInfo LUNs into separate vols. We are planning to use snapmirror for offsite replication over the next year if that is a factor. We will also want to mount DB snapshots to separate hosts regularly for QA/Dev purposes.

2. Is there a way to turn off the autogrow limit on volumes? I know you can use the CLI to set a different size but can the '120% of the Initial Size' default be changed? I just don't like having this hidden limit that could potentially take LUNs offline down the road. If it can't be overwritten, is there any problem with creating volumes very large (to set this maximum to verylarge+20%) and then shrinking them to our best guess on an appropriate size?

3. If for #1 we go with one vol per DB LUN and Log LUN then volumes seem like they would become cumbersome to size properly and manage. Is removing the reservation on volumes a viable option or playing with fire?

shaunjurr · ‎2011-06-10

Hi,

Being new to NetApp, I would suggest you take a good deal of time and research the Best Practices documentation and the system documentation. Some of these things are just too time-consuming and too long to explain in forums. Having a well-running storage operation, like most things in life, requires a certain amount of knowledge and experience. While I can appreciate the desire to simplify by using automagical GUI's and minimal installs, understanding a NetApp storage system really can't be achieved this way. Reading and using the cli are going to give you more information.

1)A short summary of the advantages of one volume per lun and one lun per volume might be:

a) Control over growth/encroachment over other datasets (databases, NAS shares, etc)

b) Control over data integrity. Errors will affect smaller amounts of your services (deletions, software corruption, user errors)

c) More fine-grained snapshot and snapmirror/snapvault policies are possible

d) Better performance control with reallocate and priority settings

2) Read up on thin-provisioning and auto-grow and perhaps snap autodelete. Not having the system adjust volume sizes when you use snapshots is a sure fire method to get burned. Unless you have 100% control over block changes, snapshots will at some point grow unexpectedly and fill up your volume and take your lun offline. +20% is a very conservative estimate depending on how long you are going to keep your snapshots. A single decision to increase logging one day to monitor some little problem could easily fill up a log lun in just a few hours, ending with the same volume full and offline lun situation

3) This is basically part of thin-provisioning. There is a TR on this as well. Removing all guarantees works if you monitor your systems and use volume autosizing.

That is the short version.

paulfotner · ‎2011-06-11

Thank you for the reply. I have been pouring over a dozen or more documents and the problem I run into is that many of the most current ones look to be 1-4 years old so I am never sure if what I am reading is still relevant. My understanding is that there have been major improvements in snapshots at the LUN level over that period of time that these documents wouldn't capture? Are there any specific and up to date documents that anyone can point me towards to help address these questions?

shaunjurr · ‎2011-06-11

I sympathize with your confusing research. The "Library" is incredibly hard to search through and the results of at least some of the documents results in confusing or at times, conflicting information. Levels of detail are different. Some documents will lead you to a setup that you later can't even use well with snapshots or SnapManager products (my experience with Exchange "Best Practices").

Basically, the volume is the unit you need to remember when dealing with snapshots. One LUN per volume is a good idea unless you are going to exceed the platform limit in doing so. There are only a few small disadvantages in doing so. You need a little more time for allocation, and boot times for the filer get just a bit longer, but you win in flexibility. Like most such situations, there is a trade-off between complexity and flexibilty. Getting it done quickly at the start might mean a lot more work later.

Snapshot functionality is basically the same as it has been the last 20+ years. There have been many improvements in many areas over the years, but as far as provisioning LUN's on NetApp storage, I don't think there is anything specific.

The whole development clone discussion is more or less a matter of available space. You can use consistant snapshots, but you can't keep them lying around very long if you have lots of block changes in your volumes. You will have to "split" the clones off to separate volumes (an internal ONTap function) if you want to keep them longer than you keep your normal snapshots if you don't want to fill up your volumes.

Hope this helps.