SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Data Files and Log Files on Seperate Servers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jhappy
Starting Member

3 Posts

Posted - 04/25/2006 :  14:04:40  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 04/25/2006 :  14:18:50  Show Profile  Reply with Quote
"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
Go to Top of Page

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 04/25/2006 :  15:46:26  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000