- Community Home
- >
- Storage
- >
- Entry Storage Systems
- >
- Disk Enclosures
- >
- Re: MSA500 G2 and SQL2005 Best Practices
Disk Enclosures
1753970
Members
7743
Online
108811
Solutions
Forums
Categories
Company
Local Language
back
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Discussions
back
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Blogs
Information
Community
Resources
Community Language
Language
Forums
Blogs
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-12-2007 01:53 AM
12-12-2007 01:53 AM
MSA500 G2 and SQL2005 Best Practices
Hi,
I am configuring a windows cluster with 2 x dl585s and a msa500 g2. My SQL2005 data files will be located on the msa.
My question is how best to configure the storage...
I know MS recommend we have a seperate disk for data files, transaction log, tempdb, etc.
So how do I configure my msa? Do I create one big volume using all the disks and then create logical segments for each drive required - so that each logical partition is shared across all drives? Or should I create a number of volumes and create a logical drive on each?
I want maximum performance (shared across all physical disks) but also want to minimise contention between the database files.
Any help appreciated.
Joe
I am configuring a windows cluster with 2 x dl585s and a msa500 g2. My SQL2005 data files will be located on the msa.
My question is how best to configure the storage...
I know MS recommend we have a seperate disk for data files, transaction log, tempdb, etc.
So how do I configure my msa? Do I create one big volume using all the disks and then create logical segments for each drive required - so that each logical partition is shared across all drives? Or should I create a number of volumes and create a logical drive on each?
I want maximum performance (shared across all physical disks) but also want to minimise contention between the database files.
Any help appreciated.
Joe
1 REPLY 1
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-14-2007 01:28 PM
12-14-2007 01:28 PM
Re: MSA500 G2 and SQL2005 Best Practices
Joe,
I'm not an expert on MSA's, but I am a SQL Server DBA, so I'll speak to that side of things.
Note that these recommendations don't change between SQL Server 2000 & 2005.
You do not want to create a single big volume with logical segments. That will negate the performance advantages of following Microsoft's best practices. You'll want to physically separate the data files, tlog, and tempdb. Different read/write heads for each. Therefore, different physical arrays for each.
When a write is made to the database, the transaction is written to the transaction log (tlog) *before* it is committed to the database (this is an oversimplification). That means that, until the tlog can be written to, any locks that transaction has will not be released. That creates contention. So, you want that tlog write to be finished as quickly as possible. The tlog is a purely sequential write (per database), so putting it on a RAID 1 by itself works well. Don't put anything else on that array (including another tlog).
With the db files, the more spindles you have, the better. It's like having more hands to accomplish more things at the same time. While the human brain might not be able to efficiently utilize many hands, you can bet a good RAID controller can! Btw, this is one reason why RAID 5 is not considered best practice for databases. That parity drive is taking up a perfectly good spindle! (The main reason is that RAID 5 setups have been shown to be much slower on writes than RAID 10s.)
Tempdb is kinda like that scratchpad you have sitting on your desk. The SQL Server engine uses it a lot. Putting it on it's own RAID can improve performance. However, keep in mind that if the tempdb goes down (bad drives, whatever), SQL Server goes down with it.
Here's how I configured a recently acquired, low-range server (DL360G5+MSA50):
- DB files (mdf) incl. tempdb: 10 x RAID 10 on drive E:
- Tlog file (ldf): 2 x RAID 1 on drive D:
I plan to move the tempdb to it's own RAID 1 as soon as I can get two more drives.
Here's some more info on physical storage configuration for SQL Servers:
http://sql-server-performance.com/tips/hardware_tuning_p5.aspx
http://www.sqljunkies.com/Tutorial/99EBD29D-8B59-4DF0-A2D2-EBB2DDF8A808.scuk
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
http://msdn2.microsoft.com/en-us/library/ms998577.aspx#scalenetchapt14%20_topic17
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
http://www.bolthole.com/uuala/RAID.html
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
I hope that helps..
..jeremy.
I'm not an expert on MSA's, but I am a SQL Server DBA, so I'll speak to that side of things.
Note that these recommendations don't change between SQL Server 2000 & 2005.
You do not want to create a single big volume with logical segments. That will negate the performance advantages of following Microsoft's best practices. You'll want to physically separate the data files, tlog, and tempdb. Different read/write heads for each. Therefore, different physical arrays for each.
When a write is made to the database, the transaction is written to the transaction log (tlog) *before* it is committed to the database (this is an oversimplification). That means that, until the tlog can be written to, any locks that transaction has will not be released. That creates contention. So, you want that tlog write to be finished as quickly as possible. The tlog is a purely sequential write (per database), so putting it on a RAID 1 by itself works well. Don't put anything else on that array (including another tlog).
With the db files, the more spindles you have, the better. It's like having more hands to accomplish more things at the same time. While the human brain might not be able to efficiently utilize many hands, you can bet a good RAID controller can! Btw, this is one reason why RAID 5 is not considered best practice for databases. That parity drive is taking up a perfectly good spindle! (The main reason is that RAID 5 setups have been shown to be much slower on writes than RAID 10s.)
Tempdb is kinda like that scratchpad you have sitting on your desk. The SQL Server engine uses it a lot. Putting it on it's own RAID can improve performance. However, keep in mind that if the tempdb goes down (bad drives, whatever), SQL Server goes down with it.
Here's how I configured a recently acquired, low-range server (DL360G5+MSA50):
- DB files (mdf) incl. tempdb: 10 x RAID 10 on drive E:
- Tlog file (ldf): 2 x RAID 1 on drive D:
I plan to move the tempdb to it's own RAID 1 as soon as I can get two more drives.
Here's some more info on physical storage configuration for SQL Servers:
http://sql-server-performance.com/tips/hardware_tuning_p5.aspx
http://www.sqljunkies.com/Tutorial/99EBD29D-8B59-4DF0-A2D2-EBB2DDF8A808.scuk
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
http://msdn2.microsoft.com/en-us/library/ms998577.aspx#scalenetchapt14%20_topic17
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
http://www.bolthole.com/uuala/RAID.html
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
I hope that helps..
..jeremy.
The opinions expressed above are the personal opinions of the authors, not of Hewlett Packard Enterprise. By using this site, you accept the Terms of Use and Rules of Participation.
News and Events
Support
© Copyright 2024 Hewlett Packard Enterprise Development LP