Where to Install SQL Server for best performance 2

[dlima]

Install SQL Server for best performance

I currently have a .net application that uses SQL server 2000 svcpk3a as the back end to a custom .net application.

My question is where should I install the SQL Server, SQL Data, and the .net web app to get the best performance.

Meanwhile server 2003 Enterprise, iis, and the .net frame work is installed on the crive.

My server hardware is configured as follows:

HP Proliant 360

(2) Dual Core Xeon processors
8Gb of memory
(6) Hard drives total-
(2) setup as mirrored drives on separate controller that makeup the C:drive
(4) setup as RAID drives on separate controller that makeup the drive


So with this configuration should I install my sql server application on the C:drive and place the sql data on the drive.

Also where should I place the actual web app. On the C:drive with the ISS and .net installation or on the drive

***Install option 1***
C:drive (2) mirrored drives
iis and .net frame work
.net application

D:drive (4) RAID drives
SQL Server application
SQL Data

***Install option 2***
C:drive (2) mirrored drives
iis and .net frame work
SQL Server application

D:drive (4) RAID drives
.net application
SQL Data

Respectfully,

David

 

[ptheriault]

Here is a great FAQ that Denny wrote.
faq962-5747

I would also recomend that you never install IIS and SQL Server on the same box. There are significant security risks in doing this. Also keep in mind that SQL is a resource hog. What version of SQL is this? Standard or Enterprise? How much memory is in the server?

What is the RAID level of the D: drive?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

always put the log and the data files on separate drives, if you can afford put tempdb on a third drive

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[dlima]

So I understand to achieve best performance when using SQL in a .net frame work the following approach is recommended:

1)Install SQL and IIS on two different servers.
2)Put the log and the data files on two separate drives.
3)Put the tempDB on a third drive.

My question is how do I move them over to different locations?

a- Is it done during the installation?
b- Do I copy and past them to different locations?
c- Is it done by exporting and importing?
d- None of the above. Some other method that I am missing

 

[dlima]

Hi Paul/ptheriault's

To answer your question about sql version and memory.

I am using SQL Server 2000 standard.

As for memory I have 8GB.

Thanks for your time

 

[ptheriault]

dlima,
Denis is correct. For best performance split up ldf, mdf and your temdb to different drives. For VLDB's (very large database) that have a high level of transactions you should also try and split your index i/o from you data i/o.

You move you system databases after install. Move the master with startup parameters -d for data and -l for the log. Use the Alter database command to move the other system databases. Here is an example for tempdb.

[COLOR=blue]use[/color] master
[COLOR=blue]go[/color]
[COLOR=blue]Alter[/color] [COLOR=blue]database[/color] tempdb [COLOR=blue]modify[/color] [COLOR=blue]file[/color] ([COLOR=blue]name[/color] = tempdev, filename = [COLOR=red]'D:\programs\mssql\data\tempdb.mdf'[/color])
[COLOR=blue]go[/color]
[COLOR=blue]Alter[/color] [COLOR=blue]database[/color] tempdb [COLOR=blue]modify[/color] [COLOR=blue]file[/color] ([COLOR=blue]name[/color] = templog, filename = [COLOR=red]'E:\programs\mssql\data\templog.ldf'[/color])
[COLOR=blue]Go[/color]

For your user database I would just use the detach and attach method to move it.

now for the bad news...

SQL 2000 Std can only use 2GB max. Even if you enable 3gb /PAE and AWE. If you want to take advantage of the 8 gb you will need Enterprise edition. (or upgrade to 2005)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[dlima]

Regarding the memory limitation issue;

Does that limitation apply if I am running SQL Server 2000 Standare Edition on a server running Windows 2003 Enterprise Server?

If the memory limitation still applies will it still exsist if I go to SQL 2000 Enterprise or SQL 2003 Enterprise and run it on my Windows 2003 Enterprise Server?

Thanks

David

 

[mrdenny]

Yes the 2 Gig memory limitation applies to SQL 2000 Standard no matter what OS you run it on.

The memory limitation goes away with SQL 2000 Enterprise.

There is no piece of software called SQL Server 2003.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dlima]

Hi mrdenny & ptheriault's

I will try to make this the last question on this particular thread.

Does the memory limitation go away if I install MS SQL Server 2005 Standard Edition or is just when you move to any Enterprise version?

Respectfully,

David

 

[ptheriault]

Yes it goes away with 2005 STD.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[dlima]

Does the memory limitation go away with
SQL Server 2000 Enterprise.

I am trying to do this correctly and keep the cost down for my company. We are not for profit. Check us out.

http://www.ocmsinc.org

David

 

[ptheriault]

You know, microsoft has special prices for non-profits. you should contact them.
I would bet 2005 STD is cheaper than 2000 Enterprise. Also if this isn't a production server then buy the developer version. Either way I think you are better off with 2005.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[dlima]

Thanks for all your help.

Regarding MS not for profit pricing:

We had a charity pricing for years, but then MS changed their rules and because we work with people that have mental illnesses we are no longer eligible.


David