June 20, 2011 at 9:12 am
Hello everyone,
My question deals with best practices. When installing SQL Server 2008, should the OS and SQL Server binaries be installed on their own separate drives even when the System Databases and TempDB will reside on their own disk drives? For example:
C - OS
D - SQL Server binaries
E - mdf + ndf files
F - ldf files
G - TempDB
If I shouldn't install the OS and SQL Server binaries on the same drive, what are the reasons?
Thank you in advance for any help or direction given.
June 20, 2011 at 9:32 am
I've always put SQL Server's binaries in the default location in Program Files on C drive. Can't think of any reason to do otherwise.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2011 at 9:46 am
I'm with GSquared. I've never seen a reason to move SQL Server binaries anywhere. It's the data, log, and backups that are important.
June 20, 2011 at 9:49 am
i seem to remember that the biniaries that are the core srvice and stuff must be installed on the %systemdrive%, with no choice in the matter...you can move the databases, i thought, but the binaries go on that drive(C: on most of my machines)
I think the client tools can be installed/re-installed elsewhere, but
am i wrong or did that change?
Lowell
June 20, 2011 at 10:02 am
balkoth (6/20/2011)
Hello everyone,My question deals with best practices. When installing SQL Server 2008, should the OS and SQL Server binaries be installed on their own separate drives even when the System Databases and TempDB will reside on their own disk drives? For example:
C - OS
D - SQL Server binaries
E - mdf + ndf files
F - ldf files
G - TempDB
If I shouldn't install the OS and SQL Server binaries on the same drive, what are the reasons?
Thank you in advance for any help or direction given.
Assuming that the split between MDF's and NDF's was well thought out, I would split E as mdf on one LUN, NDF on another to benefit from potential parallel reads.
June 20, 2011 at 12:07 pm
Thank you everyone for the quick responses! I received my answer and I was given more food for thought, thanks again.
June 21, 2011 at 2:05 am
If you have multiple drives not partition then separate drives gives you performance advantage,when you go for the default drive then there are several other processes are running on this default windows partition or drive we can say like OS level logs,Master File table,log and etc .
If you have multiple drives then go for separate drives for
tempdb
SQL Server Logs
Binaries
and offcourse database files should have different drives
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 21, 2011 at 4:11 am
From past experience I have seen the below issue , as a result i think it would be better to install SQL on its own drive.
The OS drive becomes full because users keep connecting to the system and copying files to their desktop , pretty soon the drive runs out of space. i.e 0 mb free on c drive and user profile stop getting loaded.
June 21, 2011 at 4:29 am
Jayanth_Kurup (6/21/2011)
From past experience I have seen the below issue , as a result i think it would be better to install SQL on its own drive.The OS drive becomes full because users keep connecting to the system and copying files to their desktop , pretty soon the drive runs out of space. i.e 0 mb free on c drive and user profile stop getting loaded.
Best practice recommendation: never, ever, under any circumstances, allow users to have console or share access to the server running sql server.
June 21, 2011 at 4:31 am
June 21, 2011 at 5:12 am
Jayanth_Kurup (6/21/2011)
Am sorry i should have mentioned this is a development environment.
Understood, provided your UAT environment matches production. It's the only way to do valid UAT testing.
Also, for the record, when I refer to a LUN, I am referring to a RAID set of physical disks, not some logical grouping. And I agree with one of the previous posters, in that splitting the OS from the sql binaries by physical LUN is a good practice. One needs to leave a fair amount of free space for the OS. Microsoft, in the infinite wisdom, insists on dumping a lot of transient garbage on the the OS drive during OS and/or SQL patch / SP installation.
In addition, the OS swap file should go on its own, separate, physical LUN. Something along the lines of:
C$ OS
E$ OS Swap File
F$ SQL binaries
G$ Program files
H$ SQL mdf files
I$ SQL ndf files
J$ SQL log files
K$ TempDB
L$ SQLIO files (IO files from ETL processes, scripts, etc.)
M$ SQL Backup (*.bak files, which should then be copied to another server
restore testing).
June 21, 2011 at 6:37 am
KerberosLLC (6/21/2011)
Jayanth_Kurup (6/21/2011)
Am sorry i should have mentioned this is a development environment.Understood, provided your UAT environment matches production. It's the only way to do valid UAT testing.
Also, for the record, when I refer to a LUN, I am referring to a RAID set of physical disks, not some logical grouping. And I agree with one of the previous posters, in that splitting the OS from the sql binaries by physical LUN is a good practice. One needs to leave a fair amount of free space for the OS. Microsoft, in the infinite wisdom, insists on dumping a lot of transient garbage on the the OS drive during OS and/or SQL patch / SP installation.
In addition, the OS swap file should go on its own, separate, physical LUN. Something along the lines of:
C$ OS
E$ OS Swap File
F$ SQL binaries
G$ Program files
H$ SQL mdf files
I$ SQL ndf files
J$ SQL log files
K$ TempDB
L$ SQLIO files (IO files from ETL processes, scripts, etc.)
M$ SQL Backup (*.bak files, which should then be copied to another server
restore testing).
You need to add three or four more to that for separate tempdb data files. And the tempdb log should really be on its own I/O system, too, of course. And really, each OLTP database should have its own I/O channels, to avoid contention. And ...
... But really, that's almost certainly just going to make administration more difficult. Separate the O/S and binaries from the data files, and separate those from the log files, and tempdb if possible. Anything beyond that is probably premature optimization for most companies. Even the separation of tempdb from the rest is overkill unless you have the traffic to warrant it.
And if the reason to separate the binaries from the O/S is that developers are filling up C-drive with junk, or because Windows patches are filling it up with temp files, then FIRE YOUR SYS ADMINS. Overfull C-drive on any system being used in a business environment is inexcusably incompetent administration. Fire them, and scan for malware on every system they ever touched, because if they're that incompetent, they almost certainly missed things like BonziBuddy on the web server or something equally moronic. If your devs are the problem, fire them too.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 21, 2011 at 10:14 am
I tend to agree with GSquared above. The only other separation I worry about is backups. Data backups should not be with data files, and log backups should not be with log files, if possible.
If I have 3 drives (LUNs, arrays, whatever, just 3 separate physical, not logical, locations), I would do
1: OS, SQL binary, backups (data + log)
2: data
3: log files (ldf)
That's usually the minimum. If I can get more, I might move tempdb separate, or heavy used databases to separate LUNs, but at that point, I'd be looking at wait stats and usage across the various files to determine what might benefit from separate drive heads.
June 21, 2011 at 11:16 pm
Do not split more drives on the same RAID group
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
November 29, 2018 at 6:41 am
When installing SQL server to the same drive as your OS, be very diligent in making sure that EVERY file that SQL server can cause to grow is moved off the system drive onto a drive other than the system. We recently had an issue where SQL Server / SSIS was installed to the system drive. We have a sizeable SSIS ETL that runs on this machine. The ETL process filed up the SSISDB data files (default System drive install) and crashed the machine. Had we been able to get to a command prompt, we could've resuscitated SQL server in under an hour. As it stood, it took much longer to get the machine back.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply