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 |
|
jhappy
Starting Member
3 Posts |
 Posted - 2006-04-25 : 14:04:40
|
| I read that putting your SQL Server 2005 data files on one Windows 2003 server and your SQL Server 2005 log files on another Windows 2003 can be beneficial under very heavy database useage situations. I am used to creating and coding for SQL databases that reside on the same box as the web server (IIS). Should I opt for the dividing-up-the-load scenario, will my VB and SQL stored procedures code have to change. My best guess is that I will use exactly the same code and the only difference will be to make sure that the path to the data and log files entered during database creation is correct. Do I need to make changes to my established coding practices in order to best take advantage of the two server scenario? |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-25 : 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 |
 |
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-04-25 : 15:46:26
|
quote:
Do I need to make changes to my established coding practices...
If your coding practices don't include going back and looking through everything you have done to make it compatible or efficient then yes! |
|
|
|
|
|
|
Data Files and Log Files on Seperate Servers