SQL Server Disk configuration
-
Say you were a consultant with a brand new SQL Server that is going to replace an old, tired SQL Server. The server is sitting 1000 miles away in a colocation. The hardware specs look like this:
1. dual 3Ghz procs
2. 3 GB of Ram
3. 2x72GB captive disk, configured in a RAID1 setup with a 72GB C:\ drive.
4. Windows Server 2003
5. SQL Server 2000
6. Server will house a 2GB database with the potential to grow to a 10GB database in 3-4 years.
Would you be concerned with only a C:\ drive? Even if it were 72 GB? The server is not in production yet. Say the company doesn't want to spend the money get more drives and another RAID controller.
My stance is this. I don't like having only a C:\ drive, even if it is 72GB.
1. I like my C:\ drive clean and close to "pristine" as possible. If I only have a C:\ drive, I'm downloading and extracting everything to this drive. The owner likes to run lots of 3rd party tools, etc. I'm thinking about all the SQL backups, autogrow of MDF/LDF files, software, service packs, patches all sitting on the C:\ drive... filling it up. "Whoops, did I just delete the ntldr and ntdetect.com files?" "I was just cleaning up the C:\ drive so it wasn't so messy." AAAAAAAAaah... makes me cringe. Am I alone in this mindset?
2. Say I have at least two partitions. A C:\ for windows and an E:\ for the SQL data files. One day we find a corrupt OS, the server won't boot. My recommendation after 2 hours of downtime: Format the C:\ drive, install Windows 2003, Install SQL Server, restore master, DONE. If we're dealing with only a C:\ drive, we're either doing a parallel install of Windows (YUCK!) to get at the data files or formatting, installing Windows 2003/SQL Server and going to tape for the most recent backup files. Sure... a corrupt OS doesn't happen every day, but with everything on the C:\ drive, chances are more likely that one of those files may get messed with. Accidents happen... what can we do to mitigate Murphy's Law where possible?
3. "The C:\ drive is full!" The terminal server session is running slower than slow.
I thought I set my SQL Server backups to retain the files for 4 days, not 4 weeks! Now, I can't even get on to the system to fix my mistake. Say I fill up the C:\ drive with SQL stuff... or the antivirus program or backup software fills it up... doesn't matter, C:\ drive full... things are going be slow and ugly. It's only a matter of time before this happens. 4. Defragmentation on a C:\ drive only situation. Autogrow MDF/LDF, t-log backups, full backups, diff backups, Windows patches, software installation, log file growth... We could save some defrag issues if we separated the OS from the SQL install.
Now, in a perfect world, we'd have a ton of I/O channels with plenty of disk space. We'd be running RAID10's everywhere. We'd have many Gigs of RAM. Life would be good. But, we're dealing with a 2GB database folks... it could go to 10GB in 3-4 years. Maybe 25 concurrent connections with 1000 unique users every day. Productions system, dealing with important data.
Is my request to have the server rebuilt with a 10GB C:\ drive with the remaining space open for me to partition reasonable? Am I out of line here?
Granted, it's not the end of the world if they leave it with only a C:\ drive.
-
Logically partitioning the mirrored pair will not get you the benefits you are looking for. The only way to better the performance of SQL Server through the disk subsystem is to separate the OS / SQL Server database MDF files / LDF files / TempDB off onto their own physical drives. The reasoning behind this approach is to reduce the I/O contention generated by all of the above stated objects residing on the same physical drive. Logically partitioning the disk means that the physical separation that I've mentioned will not actually happen. While it may appear to be separate from a Windows Explorer perspective, the real benefits of physical separation will not be achieved.
-
Right, I understand that. Ideally, you'd throw more money at it with controllers and disk. Performance isn't my concern. My concern is the fact that everything is on one partition... the C:\ partition.
-
It sounds like your primary concern is preventing users from writing to OS files, and secondarily preventing the system disk from becoming full.
In that case, I'd suggest some obvious things you've probably already planned:
- granting limited authority to users (dbas do not need to be local administrators)
- disk quotas
As far as hiding the O/S by putting all apps on a separate drive, that makes sense to me, but as your primary concern appears to be making it hard for users to reach the O/S disk, you might consider installing your entire O/S to another drive letter, as that also helps save you in case they run scripts with hardcoded C: paths in them.
ObDisclaimer: I'm no expert, just another guy throwing ideas out.
-
Maybe I'm just looking for affirmation here... but these snippets below seem to back my argument for 2 partitions.
http://hosteddocs.ittoolbox.com/WindowsDiskPartitionDesign2.pdf
"It is important to separate all logs from the system/boot partition. In case of a serious blue screen condition
or corruption on the system volume that requires a rebuild or restore of that partition, you will still have a
preserved set of logs on the other volume. These can then be used for forensic purposes to aid in problem
determination from the previous build."
http://www.computerworld.com/blogs/node/360#comment-170
"The debate about partitioning has been going on for years, about a score. Might as well comment on whether pants (trousers) are OK for women. Quaint and boring. "De gustibus non est disputandum"
My side says that your side is completely in error for several reasons, if you put all the Windows C.R.A.P.* in the same partition as data and other, better designed applicaitions and utilities, then when Windows is sufficiently incontinent you often need to reformat the partition to remove the confused Windows and other malware installed.
Keeping the Windows partition to a minimum vastly reduces the pain of recovery after Windows has yet again peed itself.
There are other reasons such as ease of partial backups, but these only are for people who can organize themselves (and their data).
How's your desk? Time to straighten it?
* C.R.A.P. Content Reducing Awful Programming
By James Nickson (not verified) at Wed, 06/15/2005 - 4:23pm "
Same struggle I'm dealing with in the above link.
Looks like this just comes down to methodology...
-
I agree that I would rather have separate logical partitions of the 72 GB drive. That way you are only concerned with SQL Server on the other partition and can administer the disk your way, letting the databases grow, as you see fit. I would suggest a 10 GB C Drive and the rest for your drive letter.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply