July 21, 2008 at 8:59 am
We are currently attempting to standardize many aspects of our servers in our data center. We have a lot of variety and it is difficult to manage everything with any ease. I was wondering what people thought would be a good practice for installation of sql and any particular windows components. Should we have a separate drive for backups than the drive where we have the data files? What are some of the standards you have for your servers? It isn't just SQL that I am concerned with, but it is the one part that I am responsible for coming up with the procedures on. We currently do not have any VMware or blades, but it is in our very near future. We use a SAN solution for our storage with a Shark and FastT.
I was thinking that it would be a good idea to install SQL on the C: and then have the DATA files on, say, the Y: and Backup files on the Z:. Should we be sure to install any particular windows components or other management applications? Any direction would be helpful.
Thanks, in advance, for your responses.
July 21, 2008 at 9:30 am
SQL Servers will vary depending on the purpose of the database(s) on them. It is a good start to make sure the OS, Data Files, Log Files, and backups are all on different drive arrays. It would also be good for you to consider having an additional default drive specifically for TempDB depending on your installation, but it is pretty common to want it. In some cases, you may need to optomize disk access by having additional drives for particular file groups.
Don't get yourself into standards that are too tight for any of your servers. The optimal configuration is going to be different depending on server purpose.
July 21, 2008 at 9:36 am
As far as separating databases and database backups, yes they should be on separate drives. Having backups doesn't to much good if the data drive fails and that is where the backups are too!
As Michael says your needs will vary based on size and usage of the database. Also are you using it as a catch all database server or just for one application? If a general database server with multiple applications on it, you don't necessarily get as much benefit of separating log and data files as there are multiple logs being written to.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 25, 2008 at 9:52 am
Michael Earl (7/21/2008)
SQL Servers will vary depending on the purpose of the database(s) on them. It is a good start to make sure the OS, Data Files, Log Files, and backups are all on different drive arrays. It would also be good for you to consider having an additional default drive specifically for TempDB depending on your installation, but it is pretty common to want it. In some cases, you may need to optomize disk access by having additional drives for particular file groups.Don't get yourself into standards that are too tight for any of your servers. The optimal configuration is going to be different depending on server purpose.
Would you install SQL 2005 on the C: with the OS in your example or is that also something that should go on its own drive?
As it stands, this is what I would think is needed:
C: = OS, SQL Application
X:\SQL Data Files = Data Files
Y:\SQL Log Files = Log Files
Z:\SQL Backups\System Databases = master, model, and msdb databases
Z:\SQL Backups\User Databases\
Z:\SQL Backups\User Databases\
The reason for the folder distribution on the Z: drive is so I can have all of the system databases in one maintenance plan and still select subfolders for the backups. If I didn't do this, I couldn't have their cleanup in one step. This part is from another thread
July 25, 2008 at 10:04 am
That's a reasonable start. I usually put the OS and SQL Install on the same drive. Depending on the SQL logs (not the log files) you may want to put them somewhere other than the OS drive, but they are usually fine in the default location.
If you have the option, put TempDB on it's own drive array - preferably a good, fast set of drives. That can be a big boost as SQL 2005 spills a lot into TempDB under memory pressure. Leave enough space on the OS drive for your page file.
July 25, 2008 at 10:42 am
Michael Earl (7/25/2008)
That's a reasonable start. I usually put the OS and SQL Install on the same drive. Depending on the SQL logs (not the log files) you may want to put them somewhere other than the OS drive, but they are usually fine in the default location.If you have the option, put TempDB on it's own drive array - preferably a good, fast set of drives. That can be a big boost as SQL 2005 spills a lot into TempDB under memory pressure. Leave enough space on the OS drive for your page file.
Doh...forgot the TempDB
C: = OS, SQL Application
W:\SQL Data Files = Data Files
X:\SQL Log Files = Log Files
Y:\SQL TEMPDB = TempDB
Z:\SQL Backups\System Databases = master, model, and msdb databases
Z:\SQL Backups\User Databases\
Z:\SQL Backups\User Databases\
Put the SQL logs on another drive? That would be 5 drives. If these drives are all on a SAN anyway, how much of this matters?
July 25, 2008 at 10:47 am
I would not do anything with the logs by default, but it is a consideration.
As far as if it makes a difference on the SAN - make sure these are ending up configured correctly. They should be logically and physically separate. That is why a configuration like this gets expensive - quick. Most SAN's also support a couple levels of speed. You may have a LUN that is a group of slower, less-expensive drives for backup files and high-speed LUN's for the database and log files.
Every SAN is different. Talk to the vendor you purchased it from. They will be able to give you a lot of tips.
July 25, 2008 at 10:53 am
I've done something similar to Jason in the past. having standard drive letters makes a huge difference since everyone knows backups are on "z"
Be sure that your arrays are physically separate. Otherwise you're not really separating things. This means they should be separate on the SAN as well if you have one.
One array, worst case.
Two arrays, to me, first thing is separate out backup files. you need these separate for DR purposes.
Three arrays, second is either data or log files, depending on which is loaded more.
Four arrays, log or data, depending on what's left
fifth, move tempdb data away (could move logs with it.)
sixth, pagefile.
This is a general guideline. SQL OS and OS don't really get used much, so not sure there's much point in separating.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply