August 20, 2010 at 12:20 am
Hi,
We have build a 2 node a/p windows 2008 R2 cluster and now we are ready to install SQL Server 2008 R2 which is going to host MOSS 2010 databases. We have 32 GB RAM and 8 processors and all the storage is from SAN.
We want to implement the below disk configuration. Please provide your inputs
C - OS local drive
D - SQL Binaries local drive
Is this good practice to keep SQL binaries on separate local node instead of OS drive C?
E - Primary Data Files
F - Secondary Data Files
We want to create only one secondary data file instead of 1 mdf & 7 ndf files. Is this OK or need to create 7 ndf file and keep on separate drives?
G - Log files
T - TempDB
We have 8 processors but we want to keep both tempdb data files & tempdb log file on same drive T. Is this good or need to create 7 more Disks to create 8 data files for Tempdb and keep tempdb log on separate drive??
Thanks
August 20, 2010 at 10:05 am
Could you please advice me..thanks
August 20, 2010 at 10:10 am
The one data file per cpu rule of thumb mostly applies to TempDB.
I'll address that first....It is typically not a good idea to have the tempdb data files and log on the same drive, but realistically when you're dealing with SAN storage, even if you separate them they'd end up in the same raid group. I do keep my log on a separate logical drive, and all TempDB data files (1 per CPU) on the same drive.
As far as where you install SQL - yes, I do exactly what you do. OS on C:, SQL on D: (usually c/d are partitions on a local raid 5 array if this is a server).
For data files for the database, start off with 1 mdf and the ldf, and monitor performance. If you get disk queues, then you can add another drive (preferably in a different raid group) and add another file. Just make sure the mdf and log are on separate drives. Honestly you need to hit the server with a TON of traffic to get to the point where you need multiple data files on multiple drives, and it's heavily dependent on the type of traffic.
edit: also, please don't bump your threads. Trust me, people are seeing it and will reply when/if they have input for you (as I was already in the process of doing).
August 20, 2010 at 10:16 am
Is this good practice to keep SQL binaries on separate local node instead of OS drive C?
It is common for people to do this, but the binaries are not really the focus when I/O issues come up. So it is more of preference/policy than best practice.
We want to create only one secondary data file instead of 1 mdf & 7 ndf files. Is this OK or need to create 7 ndf file and keep on separate drives?
This is going to depend on what your main goal is in using SharePoint 2010. With the ability to keep documents and such in a SharePoint library it will cause the data files to grow if you are implementing a document management solution. You might want to look at the FILESTREAM features in SQL Server 2008 that work well when implementing SharePoint 2010. This might even be mentioned in the SharePoint Server 2010 best practices documents (if they have been published).
We have 8 processors but we want to keep both tempdb data files & tempdb log file on same drive T. Is this good or need to create 7 more Disks to create 8 data files for Tempdb and keep tempdb log on separate drive??
You can start out with them on the same drive and then just monitor the performance. If the performance issues point toward tempdb then you can start looking at how to improve them. I don't believe creating a tempdb data file per processor is going to help you starting out. I would monitor for performance and then test out adding additional data files. In some circumstances it can be a issue instead of a benefit. (Others may have more knowledgable answers than I do on this subject:-D)
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 21, 2010 at 2:54 am
Thanks,
Is this good practice to keep SQL binaries on separate local node instead of OS drive C?
My only concern in keep SQL binaries on D drive( local). Previously we used to use C drive. But I heard that many organizations keep their SQL binaries on separate Local drive in a cluster environment rather than on C drive
Thank you
August 21, 2010 at 7:03 pm
pshaship (8/21/2010)
Thanks,Is this good practice to keep SQL binaries on separate local node instead of OS drive C?
My only concern in keep SQL binaries on D drive( local). Previously we used to use C drive. But I heard that many organizations keep their SQL binaries on separate Local drive in a cluster environment rather than on C drive
Thank you
It is required in a cluster environment to have the SQL binaries on a shared drive. The only thing that resides on the local drives is the Binaries for the management tools (SSMS, etc).
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 21, 2010 at 11:18 pm
It is required in a cluster environment to have the SQL binaries on a shared drive. The only thing that resides on the local drives is the Binaries for the management tools (SSMS, etc).
What exactly SQL BINARIES are? I would appreciate, if you provide the list of SQL BIANRIES. I want to have a clear idea about SQL BINARIES and what they do.
Thanks
August 21, 2010 at 11:33 pm
The files under the BINN directory in the SQL Server program files directory. It is the SQL "OS" files, that SQL Server function with.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 23, 2010 at 8:33 am
Your SQL BINN should be a shared SAN Drive and you forgot your Quorum Drive too.. Work with your SAN Engineer to seperate your volumes across different LUNS!
August 23, 2010 at 11:09 am
Your SQL BINN should be a shared SAN Drive and you forgot your Quorum Drive too.. Work with your SAN Engineer to seperate your volumes across different LUNS!
NO, SQL BINN should be on local Drive.
On our cluster below is the path for BINN, which is local disk:-)
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply