Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1757
  • Last Modified:

Sql Server 2005 & Sql Server 2008 Raid 1 OPTIMAL Stripe Size and NTFS Allocation Unit Size

HP DL370 G6, Quad Core Xeon E5530 2.4 Ghz, 12 Gigs Ram,  410i 256 Embedded Controller.
3 Raid 1 Mirrors:  2x 146Gb 15k (OS), 2x 600Gb 15k (Sql Database(s)/Unstructured Data), 2x600Gb 15k (Sql Logfiles and unrelated Files).

Server 2008 R2 Standard 64 Bit,  Initial Install of Sql Server 2005 and Upgrade to Sql Server 2008 when Accounting Application/Database is vendor certified for MS Sql Server 2008.

I have seen many different and conflicting expert recommendations on the net for Sql Server 2005/08 Optimal Raid 1 Stripe size and NTFS Allocation Unit Size for the Database and Logfiles.  Would appreciate any expert technical advice here on the above.  I am not soliciting information on the Raid 1 Selection.  I am aware of the varying benefits of Raid 1, Raid 5 and Raid 10, etc in regards to read/write Database performance.  

This server will be servicing a mission critical 4 Gigabyte and growing database for a 40 user environment with a maximum growth of ten employees in the next 4 years so I am not concerned with performance hits via the Raid Arrays for this server.  I am most comfortable with 3 Raid 1 mirrors.

Any comments on the above as well as MBR and GPT Partition Style selection and Basic/Dynamic disks in Server 2008 from those who have built and deployed production database servers is most appreciated.  More interested in practical experience rather than higher level theory.

Thanks,
Rich



0
rjearley1966
Asked:
rjearley1966
  • 2
  • 2
1 Solution
 
MikeWalshCommented:
For partition alignment --> The default in Windows Server 2008 is good. 128KB, I believe? In my Windows Server 2003 environments I use 64k alignments.

For the cluster size/NTFS stripe size I have had good luck with 64k there as well. Your mileage my vary but this has worked out well on warehouse type workloads as well as OLTP workloads in my environments.

Good article: http://msdn.microsoft.com/en-us/library/dd758814.aspx
0
 
rjearley1966Author Commented:
Thanks Mike. Surprised there are no opinions out there on MBR and GPT partition styles.  Tells me folks are playing it safe by going with what is known and familiar: MBR.
0
 
rjearley1966Author Commented:
Closed.
0
 
MikeWalshCommented:
I guess for smaller data volumes, I see no performance benefit to do the MBR style which is the "tried and tested" (and known and familiar). I have seen plenty of high volume low throughput (OLTP) workloads and high throughput in warehouse applications workloads work out fine with the MBR partition style.

I think we will see more performance tests start to happen with GPT and we'll see suggestions and blogs but right now there is no official Microsoft best practice to move to a GPT and I personally haven't seen a reason to move that way yet, either.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now