|
Kristen
Test
United Kingdom
22191 Posts |
 Posted - 04/25/2006 : 14:18:50
|
"I am used to creating and coding for SQL databases that reside on the same box as the web server"
If you are after a performance improvement I wouldn't go for something complicated like hosting different parts of the database on different servers, just sort out the fact that you don't currently have a dedicated database box and IME you'll be amazed at the difference.
1) Move SQL off the IIS box onto its own box.
2) Arrange separate disk channels for O/S, Logs and Data files (mirrored for the first two, RAID10 for the data).
3) If funds permit allocate one more mirrored channel for the backups.
4) Stick as many drives as you can on the RAID10 for data - 3 is the minimum and its not enough. We use at least 6.
5) If you can afford the Enterprise License then fill up your SQL box with RAM. (SQL2k5 allows more RAM in the Standard SQL version than SQL 2000 did, SQL 2000 limit was 2GB before you needed to buy Enterprise License - which for a web application was more than $10,000 per CPU as I recollect it. So depends a bit on whether you plan to stay with SQL2K for now, or immediately move to SQL2k5).
"Do I need to make changes to my established coding practices"
If you have not already optimised your queries then you really ought to do that first. "It all depends" but I've done consultancy work for folk that had pretty good code quality (i.e. not a "bunch of rubbish") where we got 10-fold improvements just by optimising their queries etc. So that may give you all the improvement you need without any extra expenditure on hardware ...
If you aren't already using Stored Procedures, or parameterized SQL calls - i.e. probably using sp_ExecuteSQL - and you don't have carefully considered indexes and a decent DBA who has carefully crafted an Index Defragging and Statistics Updating policy then there is almost certainly loads of gain that you can get before you need to resort to hardware.
But that's just my $0.02!
Kristen |
 |
|
Data Files and Log Files on Seperate Servers
