Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
afifimk
Starting Member
22 Posts |
 Posted - 2010-10-02 : 10:35:15
|
| I am building a SharePoint 2010 farm with MS SQL 2008 R2 (STD) for the backend. The H/W is PowerEdge R710, 32 MB RAM, 4 X146 GB Hard Drives plus 1 additional 146 GB Hard Drive (hot Swap). I have been doing a lot of reading about database installations/configurations and I understand the value of placing the .mdf and .ldf on separate spindles. So how best to do the RAID configuration with this system? Any insights on this will be much appreciated. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-02 : 11:24:52
|
| With exactly 4 disks to work with, I'd create two mirrored sets, put OS/Binaries/MDF's on one, LDFs on the other. If you can use all 5, I'd mirror the logs (RAID 1) and make a RAID 5 with the other three.Can you get more disks? |
 |
|
|
afifimk
Starting Member
22 Posts |
Posted - 2010-10-02 : 11:55:14
|
quote:
Originally posted by russell
With exactly 4 disks to work with, I'd create two mirrored sets, put OS/Binaries/MDF's on one, LDFs on the other. If you can use all 5, I'd mirror the logs (RAID 1) and make a RAID 5 with the other three.Can you get more disks?
Yes, I can purchase another Disk. By the way, I will purchase two identical servers so I can mirror the servers and I might even go crazy and purchase a low-end server with SQL Exprees installed to act a witness. So does this change your recommendations? Thank you so much for the assistance. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-02 : 20:15:09
|
| Remember that mirroring is a database level solution, not a server level solution.So if you have multiple production daatabases, and you mirror, you'll need to write your own script to cause them all to failover.Transactional replication may be a better solution for you...depends on your situation. If your production system is a single database then I'm convinced that mirroring is your best solution for HA.Mirroring doesn't really change my recommendation as far as disks. You want fault tolerance AND high performance. More disks should mean better performance.As is all too common, proper configuration depends on your needs and your situation. Is it better to have 2 raid 5s than 3 mirrored sets? Depends. Of course 2 RAID 10s and a mirrored set would be nice.When you can, obviously you want to seperate the logs from the data files. However, much of the gain is given back if there are multiple (busy) ldfs on the log drive. It would be nice to put tempdb on its own array as well, but when I can't, I'll put tempdb ldf with the others mdfs and the log with the other logs. On limited arrays, I'll always choose to put the other system dbs with the OS and binaries (except for a dedicated job server where msdb sees a lot of activity).Hope this helps a bit. I'm sure others will have some thoughts too. Please feel free to post back with more questions or if you need further details. |
|
|
|
afifimk
Starting Member
22 Posts |
Posted - 2010-10-03 : 11:08:13
|
quote:
Originally posted by russell
Remember that mirroring is a database level solution, not a server level solution.
Understood.quote:
So if you have multiple production daatabases, and you mirror, you'll need to write your own script to cause them all to failover.
I have about 20 prodution databases(all on the same node and on the same physical server) and I was planning on using synchronous mirroring in High-safety mode with automatic failover [url]http://msdn.microsoft.com/en-us/library/ms189852.aspx[/url]. The reason behind all of this is the H/A offered by SQL mirroring.quote:
Transactional replication may be a better solution for you...depends on your situation. If your production system is a single database then I'm convinced that mirroring is your best solution for HA.
I started reading about the subject since your response to my question. It is very interesting and I may go that route. My follow up question is: with number of databases that I have, do you still think it will be best to Mirror or transactional replication?quote:
Mirroring doesn't really change my recommendation as far as disks. You want fault tolerance AND high performance. More disks should mean better performance.As is all too common, proper configuration depends on your needs and your situation. Is it better to have 2 raid 5s than 3 mirrored sets? Depends. Of course 2 RAID 10s and a mirrored set would be nice.When you can, obviously you want to seperate the logs from the data files. However, much of the gain is given back if there are multiple (busy) ldfs on the log drive. It would be nice to put tempdb on its own array as well, but when I can't, I'll put tempdb ldf with the others mdfs and the log with the other logs. On limited arrays, I'll always choose to put the other system dbs with the OS and binaries (except for a dedicated job server where msdb sees a lot of activity).
Here's my plan: I'll purchase an additional disk to a total of 6. Go with your recommendations of 2 RAID 10s and a mirrored set(RAID 0).- I will place the System files, binaries, page file, tempdb on the mirrored set (RAID 0).
- Place the MDF on the first RAID 10 set.
- and the LDF on the seconed RAID 10 set.
- Backup files will go directly to a shared drive or SAN then to a tape daily.
Thank you very much for your assistance. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-03 : 23:16:11
|
| I love your plan, but I think you need to study up a bit on RAID levels.RAID 0 means striping but no redundancy. This is unacceptable for any mission critical application.RAID 10 requires at minimum 4 drives.A mirrored set is RAID 1. RAID 10 is a combination of striping and mirroring. It provides, in most cases, the best combination of performance and fault tolerance for mission critical apps.Please review this link:http://msdn.microsoft.com/en-us/library/ms190764.aspxI must say that while RAID 5 is not optimal from a performance standpoint, it is a common choice on commodity hardware due to cost considerations.If you have a SAN on your network, what is it doing more important than storing SQL Server data (please don't say emails, unless you're an email provider or marketing company :) ) |
|
|
|
|
|
|
|
|
RAID Configurations