Should we get a new server box???

[echowave]

Hey,
I recently was asked to review the status of our current SQL Server and whether it was going to be sufficent to accomadate our companies current growth. I am a developer by trade so any advice would be greatly appreciated.
The current server is a Windows 2000 box with SQL Server 2000. It has 2GB RAM, dual processor, 250 GB hard drive (RAID 1 I think...so they tell me). The drive is partioned with OS and SQL files on c:\ ;while backups are on d:\ in addition to a huge amount of software installation files, therefore this is not a dedicated SQL Server, this really disturbs me!! Concurrent users should not be over 50. I think that is all the info you should need, if not, let me know...
I personally do not like how this server is set up, considering how critical the data is. I want to recommend buying a new dedicated server but I need some firepower to bring to the IT department. Any good articles or advice on the best practices for a SQL Server would be greatly appreciated.

Thanks in advance...

 

[mrdenny]

At the very least you should have your SQL Files on a drive that has nothing else. Having them on the C drive is very dangerous. If the database grows in size and fills the drive the OS will have no free space to use.

At the minimum you need to be on a RAID 1. RAID 5 is much better as it gives you better performance along with the redundancy. Never use RAID 1.

I've got an FAQ that goes over selecting your drive config for your SQL Server. faq962-5747

I'd recommend a seperate box for the SQL Server. A seperate SQL Server is always best. Dual Chip 2.5 GB of ram. SQL Server standard edition can use up to 2 GB of ram. If you get 2.5 GB that leave 512 for the OS. If you can't get that much be sure to configure SQL Server to leave some ram for the OS.

I can't really comment on the drive space since I don't know how big your database is, and how much it will grow over the next couple of years.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[LawnBoy]

Never use RAID 1.

Care to explain that? I use RAID1 all the time, never seen a problem. True, RAID5 is somewhat faster, but RAID1 still has it's place.

 

[MDXer]

The RAID Type you choose should be decided using a number of factors such as:

Acceptable Down Time - If you can afford for the system to be unavailble and have a proven backup strategy then a few hours to a couple days may be acceptable.

Backup - If you have a solid and proven backup strategy in place with complete db backups and tran log backups then again down time may be acceptable.

Performance - What type of performance is required. RAID 5, RAID 1 and RAID 10 all have their own pros and cons. The good and bad of each needs to weighed against desired performance.

Available budget - THis quite often will determine what you can get which in turn determines what you can do with it. In a Raid5 you can plan on losing 1 drives worth of capacity on the Drive. Also Raid 5 sets should be done is odd Numbers and requires a min of 3 drives.

If your manager says I can get 2X142GB drives rather than 4X72GB or 8X36GB and have room to expand later and triple the internal storage then there isn't much you can do after making your concerns and possible perfomance problems known.

Spec a new machine as an ideal (within reason) server, but know that it isn't the min required this will give you room to wiggle if needed.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

Sorry I meant to say never use RAID 0. I also use RAID 1 all the time.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[DrSql]

Here is a hardware doc from DELL. Now they got into selling SQL Server installed and configured servers..

http://www1.us.dell.com/content/topics/global.aspx/alliances/en/ebook_landing?c=us&cs=19&l=en&s=dhs

Dr. Sql

 

[echowave]

Thanks to all that posted, the information was awesome and we are well on our way to a new server, YEAH!

So here is what I had in mind for my server setup, please feel free to make any comments or suggestions.

- RAID 1 (30 - 60 GB Drives) for the OS and SQL installation.
- RAID 5 (4 drives which will be > 40GB) for the databases and log
- RAID 0 for backups ( drives > 100 GB)

These are pretty general stats, but they are a start. I have not decided on actual drive sizes so that is why I included a range

Is there something that I am missing with this setup? As a side note I have decided that I will not be seperating logs and database, is this a huge performance degrader?

Thanks again all.

 

[SQLBill]

I would consider moving one of the drives from the raid 5 array and using it for the TEMPDB. Also consider moving one drive and using it for the logs.

1 drive - OS
2 drives - databases
1 drive - TEMPDB
1 drive - logs
? drives backups

-SQLBill

Posting advice: FAQ481-4875

 

[echowave]

Another question for you guys....

We have 2 different applications here that both utilize SQL Server. My plan is to have both of these apps to use this new server that we have been discussing. The only problem is that both applications are job # based therefore it is highly possible that we could have the same name for a database for different apps. I can make the db's distinguishable by adding a prefix or should I create a second instance on the server?? This means I would have an instance for each application. Another possiblility is that I could try to get another server to keep boths apps totally seperate.

Any suggestions??

 

[DrSql]

Depending on your resources you can goto the route of getting two new servers. But If yo want to do it on the same server, you can create multiple instances and host.
But what type of application needs same database names. Normally Apps needs ODBC, OLEDB, ini files to connect to database. So see what type of connection is used by the APP and, you can name two names for DBS on the server, but you can name the data source on APP server anything you want, it can same as long as on diff app has 2 servers.

Dr.Sql
Good Luck.

 

[Corran007]

it depeneds on the app. most let you chose your own database name. some do not. if it does just use 2 different names. if both do not let you change them, then we can talk about instances vs seperate hardware.

also how much resources they take up could influance these dicisions.

 

[Catadmin]

FYI, a piece of ammo you can use (if you need to) to get SQL on its own server is to verify whether or not you have Exchange running on the same machine as SQL. A lot of companies do this, thinking since they are both MS programs, they can get by cheaply by having both apps running on the same server. Unfortunately, Exchange and SQL both try and take "command" of the server resources, so eventually they will get into a fight over those resources and one will severely retard the other app's ability to perform correctly. The bigger the company, the bigger the databases and the worse the situation gets. They don't like each other very much. @=)

If you have this situation, simply tell your boss that it isn't recommended to have Exchange and SQL on the same machine because you'll start having problems (if you haven't been having problems already).

Hope that helps you out.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[echowave]

Once again, thanks for the posts...greatly appreciated. As I am pretty new to this side of SQL Server, any info is a great help.

So after much research here is my latest SQL Server config:
-SQL Server Standard, for the time being.
-Windows 2003 server
-Dual CPU ~3Ghz
-4GB RAM. I guess I will be allocating 2GB OS and 2GB for SQL Server since that is the max that you can use with Standard edition, is this correct??
-RAID 1 - 36GB (c: -> OS -> 8GB)
(d: ->APPS -> 28GB)
-RAID 1 - 73GB (e: -> Log files or ldf's)
-RAID 5 - 4 or 5 Drives 146GB (f: -> Data or mdf's)
-RAID 1 - 146GB (g: -> backup files, .bak,.trn)

Does this system seem ok or I am missing something, or going overboard on something, please let me know...

The biggest doubt that I have is the RAID 5, is this too big as we are not dealing with massive databases?

Thanks in advance....

 

[MDXer]

RAID 5 - 4 or 5 Drives 146GB (f: -> Data or mdf's)

For performance (actual reasons I never remember but my admins know) raid 5 should always be done with an odd number of drives.

if you Think 5X146 is to much for data then why not create 1 or 2 more drives for tempdb

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MDXer]

also forgot to mention remember with raid 5 you essentially lose 1 drive worth of capacity as usalble drive space Raid 5 with 3X146 will result in ~292GB of usable drive space.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

It looks good. Stick with an odd number of drives for the RAID 5. I've got no idea why there is a performance difference but there is. Odd number of drives is better.

Yep, 2 Gigs is the limit with SQL 2000 standard.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)