Re: SQL Server LUNs, Volumes, best practices

CASCADIAN · ‎2011-06-06

Hi, we are new to Netapp. Trying to figure out the optimal setup for our volumes, luns, drives, etc.

We are using SQL Server 2008 on Windows Server 2008 R2.

SnapManager 5.1.

What is the best setup for where to place .MDFs and .LDFs?

We are planning on putting System databases on their own LUN/Volume.

SnapInfo gets it's own private LUN/Volume.

All databases that are on a SIMPLE backup plan will be on the same LUN/Volume.

Question 1: Is that all correct? If not, what should we be doing differently?

Question 2: How do you layout the LUNs / Volumes for FULL databases?

Does each database get a diffferent LUN for the .MDF and .LDF? (2 for each database?)

Or do all of the .MDFs go together on the same LUN / Volume?

Then all of the .LDFs go together on a common LUN / Volume?

What is the best setup for eventually being able to provide and individual copy of the database to developers or QA?

What is the best setup for effecient backups?

Thanks in advance for any tips you have learned from actually using Netapp.

curtish · ‎2011-06-06

If you are using SIMPLE recovery model, then having mdf and ldf in the same volume/lun is the correct approach.

For FULL recovery model, the best thing is to place ldf in separate volume/lun than mdf for recovery.

If you have mdf and ldf in the same lun, then if you lose the lun, you cannot do point-in-time recovery. If mdf is in one lun and ldf is in another lun but they are both in the same volume and you lose the volume, you cannot do point-in-time restore.

You can place multiple mdf for different databases in the same lun, but the restore process is copy out to the active file system. If mdfs are small, then this is not a problem. If mdf is big, then you will want to place that database in its own lun so SMSQL can do a lun clone split to do a fast restore. The reason for placing multiple databases in the same volume - even in they are in different LUNs, is if the have the same backup or snapmirror schedules, otherwise you might want to place some database(s) in their own volume. The ldfs for different databases can be in their own luns in the same or different volume - just consider the possiblity of losing a volume for better protection to use different volumes.

If using SMSQL, then place the SnapInfo LUN in its own volume. You can manage multiple instances from one server with SMSQL and still use one SnapInfo lun.

Hope this helps,

Tom

shaunjurr · ‎2011-06-06

You probably should dig into TR-3431...

http://media.netapp.com/documents/tr-3431.pdf

abhisek · ‎2011-06-07

Hi ,

I am trying to answer some of your questions.

You need have separate luns for system db’s(master,model,msdb) , temp db, User db’s primary (mdf),User db’s log file (ldf) and for Snapinfo directory.

You can also try to keep system db’s on separate volume to prevent I/O bottleneck.

You can Netapp’s flexclone technology to clone databases for use of QA/Dev. This can be performed by using NetApp’s SMSQL 5.1 Clone Wizard.

Also download and refer to TR-3821 for best practice guide. We are currently working on refreshing the BPG for SMSQL 5.1.

Regards,

Abhishek

brendan80 · ‎2011-06-09

The model we use to backup our SQL DBs looks like this and works well for us:

Software specific MDF DB's in the 😧 LUN

Software specific LDF Logs in the E: LUN

Snapinfo on the F: LUN

Master / Model / MSDB on the M: LUN

Lately we have gone the route of putting TempDB / TempLog on a LUN thats part of a separate volume. Reason being is that we want to SnapMirror the volume above with those LUNs to our destination filers. However the TempDB / TempLog LUN can grow to be large in some environments, hence the reason why we leave TempDB / TempLog files in a separate volume since we don't need to mirror that.

shaunjurr · ‎2011-06-09

Hi,

This is a little beside the point perhaps, but I always cringe a bit (mainly because I have been unix administrator for a lot of years) when I see so many "drive letters" in use. One of the really useful abstractions that Microsoft came up with already in w2k3 was the concept of "volume mount points", that means the submounting of resources below the drive letter level. If you ever want to run a lot of instances on a server (the servers are generally powerful enough to run a number of instances simultaneously) you will run out of "drive letters" before you are actually utilizing your hardware.

We run all of our MSSQL implementations the last couple of years with one drive letter per instance. The setup sort of looks like this:

E:\ -> lun for binaries for entire server (not shared in a cluster environment)

F:\ (for example) --> 5GB lun for additional mountpoints and system DB

F:\instance_data --> lun for database files, sized as needed

F:\instance_log --> lun for log files, sized as needed

F:\instance_tempdb --> lun for tempdb

F:\instance_snapinfo --> generally 4GB or so for snapinfo

You need 5 luns (one lun per volume) and some idea of how to use mountpoint disks and this works very well, even for MSCS clusters (you'll need a shared lun for the quorum disk, of course). You can then run up to like 20 instances per server/cluster with the available drive letters. It also lends some structure to what belongs to an individual instance. This way you can also split your log lun, for example, and put it on a different aggregate or controller to balance the load. Reallocation really only needs to be run on the database lun, for example, as well. This saves you some cycles too. You can use 'priority' to prioritize I/O to each volume (and lun if you use one lun per volume) to tweak I/O for individual databases too.

Hope this helps.

CASCADIAN · ‎2011-06-14

Followup question. I have everything configured with SnapManager for SQL Server 5.1

We have created different LUN/Volumes based on the type of backup (FULL vs SIMPLE) that is happening and the size of certain databases.

Our largest FULL databases on the server have their own LUN/Volume for the .MDF, then a different LUN/Volume for the .LDF.

Then we have another group of small databases on the same server that share a LUN/Volume for their .MDFs, etc.

As a result, there are a few different backup jobs scheduled for the transaction logs.

Each of the large databses had a serperate job scheduled (via SnapManager).

Snapmanager then creates a job on SQL Server 2008 for the associated Transaction log backup.

Then in SQL Server I scheduled the jobs to run on a 5 minute schedule.

There appears to be a conflict. All of the transaction log backup schedules are running at the same time every 5 minutes, but it seems that only one at a time is allowed to be successful.

When one suceeds, the other fails. It seems almost random as to which one suceeds and which one fails. The jobs alternate for success and failure message history.

Is this expected? or have I messed it up?

I figure I could probably set these things up for 15 minute schedules and do it so that they each take turns every 5 minutes for a different job.

That way there would be no contention. They would be on a rotation within every 15 minute block.

Three different jobs would take a turn once every 15 minutes.

Is my Netapp just messed up? or is that normal and my 15 minute rotation is the solution?

shaunjurr · ‎2011-06-15

CASCADIAN wrote:

...

Then we have another group of small databases on the same server that share a LUN/Volume for their .MDFs, etc.

...

Don't do this. Each database should have a LUN for database files and a LUN for logs. Remember, snapshots are volume based, so putting a number of databases in the same LUN (and volume) is just going to cause immense problems. Use volume mountpoints to get passed the overuse of "drive letters".

jason_fay · ‎2012-04-30

shaunjurr wrote:

CASCADIAN wrote:
...
Then we have another group of small databases on the same server that share a LUN/Volume for their .MDFs, etc.
...

Don't do this. Each database should have a LUN for database files and a LUN for logs. Remember, snapshots are volume based, so putting a number of databases in the same LUN (and volume) is just going to cause immense problems. Use volume mountpoints to get passed the overuse of "drive letters".

Really? I've got a sharepoint instance with 120 databases from Nintex forms, to Sharepoint Config, to MySites, to Content Databases... should I have 240 LUNs just for that Sharepoint instance? Having a large number of databases in a single LUN on a single Volume does not cause immense problems. I just have to keep in mind that if I want to restore a single database, the only way is to restore the backup via a stream based backup. I would say that if you only have a couple of VLDB's on an instance, then your suggestion is sound, but that's probably the only time I would segregate databases into their own LUN and Volume.

radek_kubka · ‎2012-05-01

TR-3696 (http://media.netapp.com/documents/tr-3696.pdf) gives a very sensible recommendation on p. 24:

If each database has a unique backup requirement, do one of the following:

o Create separate FlexVol volumes for each database and transaction log.

o Place databases with similar backup and recovery requirements on the same FlexVol volume. This can be a good option in cases of many small databases in a SQL Server instance.

o Storage administration overhead can increase as more volumes are used.