| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2006-04-20 : 04:03:04
|
| I am looking to buy a new server. The ones we have to buy can only take 6 discs so as I see it I have two options and wondered what people thought or if anyone had any other suggestionsOption 12 Mirrored for OS2 Mirrored for T logs2 Mirrored for DataOption 22 mirrored OS (and possibly T Logs)4 Raid 5 for data (and T Logs if they aren't on the mirrored discs)With a bit of luck and a following wind I may be able to buy two of these servers. If that's the case we may also take the leap to SQL server 2005. If we want the second server to provide failover capability what are our best options. Would the servers be better in a cluster or would it be better to do something like log shipping/database mirroringthanks in advancesteve-----------Oh, so they have internet on computers now! |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-04-20 : 04:19:11
|
| It looks like you are going for security over performance. I'd probably consider striping the tran log discs instead of mirroring them, same with the OS. Depends what you are looking for though.-------Moo. :) |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-04-20 : 04:56:22
|
quote:
It looks like you are going for security over performance.
That's a fair assessment as the database is pretty small (approx 2Gb). The performance issues we have are largely related to reporting and that is going to be offloaded to another server. Also the spec for these machines is very high for our requirement. This means that other things than performance can take on greater significance than may otherwise be the case.steve-----------Oh, so they have internet on computers now! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-20 : 08:47:03
|
| The greatest improvement that we have made to our clients machines is to:a) dedicate SQL on a single box (previously we shared IIS and SQL)b) have a shed load of drives. I forget how many, but from memory the O/S is a pair (mirrored), the DB Logs are a mirrored pair, the backups are on a mirrored pair, and the data is on RAID10 using about 6 or 7 drives.The performance is like night & day compared to what we did before (everything on a single RAID5 "low-end" server).Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-04-21 : 03:08:40
|
| Thanks guys-----------Oh, so they have internet on computers now! |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-21 : 14:14:41
|
quote:
The performance is like night & day compared to what we did before (everything on a single RAID5 "low-end" server).
Which one is night and which one is day? Things run much faster on our server at night, and yet I'm assuming the inference is that "NIGHT" is the bad one. (just kidding.)I think your first suggestion 2, 2, 2 would probably give you the best blend of security and performance. And you will see great performance once you off load reporting to another server. Once you do that you might consider indexes that could be removed on the master server because they were only needed for reporting queries, and then re-added on the report server each night after data is restored to it.Hope it helps,Dalton |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-04-21 : 14:26:45
|
| I'd probably go for option #2. Most OLTP databases are read heavy, so the RAID 5 will really help performance. In theory, you'll not be swapping to the page file, so you can probably get away with the TXLogs on the same RAID 1 array as the OS.You've not mentioned how much RAM will be in the machine. If the database is 2GB, I'd say get at least 3-4GB's of memory so you can put the entire thing in memory and have room to grow a bit. With that sort of config, you could get away with the 2-2-2 solution. For performance, I'd for 2-4. Really, the 2-2-2 doesn't really give you any more protection than the 2-4 solution, but you get more performance.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-21 : 14:50:11
|
"Which one is night and which one is day"That requires at least a days consultancy fees for me to tell you!!"I'd say get at least 3-4GB's of memory"You think his stingy employer is going to give him a suitable a) SQL licence or b) machine that allows more than 2GB of RAM? I'd prefer to spend more of my taxes on a right-size solution, rather than an economy-solution and lots of overtime/man-time/pissing-around time / etc  Kristen |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-04-21 : 15:00:49
|
| Oh yeah, I forgot about the 2GB limit for the "inexpensive" SQL server version. I'm so used to running Enterprise I forgot about the 2GB limit. My bad! That's what ya get for listening to a crazy Yank!2GB's of RAM and 6 disk in a (2) RAID 1 (4) RAID 5 is probably the way to go. Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-21 : 16:36:18
|
quote:
Originally posted by MichaelP
Oh yeah, I forgot about the 2GB limit for the "inexpensive" SQL server version...
Since he mentioned SQL Server 2005, on the x64 version, even the standard version allows you to go to the OS limit.Get one of those dual-core, dual slot x64 systems, load it with enough memory to keep the DB in cache and leave the OS with plenty of memory, and it should be smoking fast. The only IO to worry about will be writes to the DB and transaction logs, and backups.If the application is not extremely write heavy, disk IO should not be a bottleneck.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-22 : 05:56:58
|
| "dual slot x64 systems"Now my sides are splitting - imaging elwoos asking his IT Department for one of those!Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-04-24 : 04:06:11
|
quote:
You think his stingy employer is going to give him a suitable a) SQL licence or b) machine that allows more than 2GB of RAM? I'd prefer to spend more of my taxes on a right-size solution,
I have good news for you Kristen - as this is tax payers money it will be Enterprise Edition and 6GB RAM for me  - having said that I doubt they will go for Dual Core I think that it's this one but with an extra RAM that I have been quoted for [url]http://h10010.www1.hp.com/wwpc/uk/en/sm/WF06b/521-525-358263-358263-12083449-11301713-39121321.html[/url]steve-----------Oh, so they have internet on computers now! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-24 : 04:51:33
|
| Thanks goodness for that. Perhaps I'll give up my private health care and join the now-short queue for the NHS! |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-04-24 : 08:49:32
|
| There may be short waiting lists but will there be anyone there to see you? [url]http://news.bbc.co.uk/1/hi/health/4931648.stm[/url]steve-----------Oh, so they have internet on computers now! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-24 : 09:02:02
|
| With that model, I thought that the max number of internal drive arrays was two. That would eliminate your option 1 drive layout. If that is the case, I think that 2 mirrored for the OS, and 4 in a RAID 10 array would be the best performance option, since it looks like drive space will not be a problem.I could be wrong about the limitation, but that seems to be the industry standard for that class of system.I would suggest checking with your server guys, but...CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-24 : 15:29:12
|
| If you went down that route would you be tempted to share the [single] mirror for O/S and SQL Logs? (i.e. leaving the RAID 10 for the MDF data files only)If not, why not? And no, I have not been set this as a homework question!Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-24 : 16:37:19
|
quote:
Originally posted by Kristen
If you went down that route would you be tempted to share the [single] mirror for O/S and SQL Logs? (i.e. leaving the RAID 10 for the MDF data files only)If not, why not? And no, I have not been set this as a homework question!Kristen
The correct answer is: it depends.You have to consider where the DB backups and transaction log backups will go. I don't like having the backups on the same array where the database is located.In this case, I would be tempted to put the DB tran logs and datafiles on the RAID 10 array, and keep the backups on the OS mirror. That way if either array fails, you can still recover the database. Of course, if the single array controller fails, you are still out of luck.On the other hand, if there is a lot of write activity, having the database transaction logs on the OS mirror may give better performance. Since the database is fairly small, backing up to a network file share may be a good option.Maybe tempdb will be the real bottleneck, and it might be good to put it on the OS mirror.As you pointed out earlier, it is not an ideal arrangement. I would prefer to install it on SAN or use an external drive array chassis with multiple channels, but that doesn't sound like an option.Since the database is fairly small, plenty of memory and processor speed should still give good performance even without an ideal disk arrangement.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-24 : 17:06:38
|
| Great! So now I've got 10 hypothetical options to balance and consider, instead of the two I started out with!Me, I'd put the LOGs and Backups on the O/S Mirror, and the MDFs on the RAID 10.Actually, no I wouldn't, I'd demand a server with more channels!Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-04-25 : 03:57:28
|
| Thanks for your comments guys. Michael I'll check it out whether it is definitely that model. It may mean that the decision is made for me. With regard to where to save the backups - IF we get two of these then the backups could be put onto the other one. If we only get one then maybe the backups could be stored on the current server which will probably be kept in use for other things.Also, if we get two then one will probably be used for reporting/BI type stuff as I can dump the data over night onto it. which will mean that the main one will be relatively write intensive and the reporting one will be read intensive obviously. Would a second one be better in a different disc configuration?To be honest putting the database(s) onto a SAN is an option but at the moment I am reluctant to go down that road at the moment for a number of reasons (some political!).As cost is an issue (as always) a server with more channels is not likely to be a possibility - though Kristen you could always buy it for us and write it off as a tax losssteve-----------Oh, so they have internet on computers now! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-25 : 04:40:41
|
| "IF we get two of these then the backups could be put onto the other one"I recommend that you avoid backing up directly to another box - backup locally then COPY/MOVE to the other box. And then you are left with the same performance problem of the backup files being on the sample spindle as TLogs or Data files, OR having the luxury of them being on a different spindle.Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-25 : 11:14:58
|
| The thing that is nice about the SAN option is that you can get a really compact 1U server server, because you don't need space or power for internal drive arrays.It's also a lot easier to setup the drive LUNS the way you need them, instead of what is available on the model they will let you buy. It's nice in an emergency too, because you can have the LUNS moved to another system, even if your server is completely dead.Can't help you with the politics. CODO ERGO SUM |
|
|
|
Next Page
|
New server advice please