Looking for sugguestions for a new server
-
Hello all.
I've been green-lighted to buy a new box for SQL Server, and before I call a salesperson, I want to have a general idea of what I really need. Here's what I know:
The current box is a dual-P4 tower, 2003 Server, SQL Server 2000, 4G RAM, RAID 5. The primary business sytem uses one database that is about 3GB in size. There are a few additional databases; all much smaller. Our company has approximately 25 users hitting the primary database. Of those, about 15 are heavy users, and one or two users slam it pretty hard. The current box is pretty well hosed up at the moment, so I don't have any reliable gauge of performance.
Due to limitations of our primary business system, we will have to stick with SQL Server 2000 for now. Here are some general questions:
- What are the advantages and pitfalls of using the 64-bit versions of Windows Server 2003, and SQLServer 2000?
- Do 32-bit systems run stable on a 64-bit OS? We already have a dual-processor license for SQL Server 2000, and may choose to just use it instead of getting another license. (We'll retire the existing box)
- Do we really need SCSI drives, or do SATA drives work as well? What would be the best RAID configuration for performance and fail-safe?
- Backup system?
- What things that the salesperson is going to offer me should I be leery of, and what things that he doesn’t offer me should I ask for?
Thanks for any advice!
Mike
- What are the advantages and pitfalls of using the 64-bit versions of Windows Server 2003, and SQLServer 2000?
-
My SQL Servers DBs with 100+ users have done pretty well with 32bit .The one system that had more users tried 64bit but because of prevalent locking went to 64bit 2005
Got one proposition .Your trans log should not be on raid 5.
Will let some more knowledgable person talk about hardware but as a sales man on commision
I will try to sell the most expensive bit of hardware
Good luck
-
1)What are the advantages and pitfalls of using the 64-bit versions of Windows Server 2003, and SQLServer 2000?
64-bit offers better memory managemnt especially if you have more than 4Gb of RAM. But if you're using SQL 2000 32-bit, SQL server cannot take advantage of this. I don't know of any real pitfalls and I've seen it running stable on several large environments. On the other hand why not go for SQL 2005 64-bit.
2) Do 32-bit systems run stable on a 64-bit OS? We already have a dual-processor license for SQL Server 2000, and may choose to just use it instead of getting another license. (We'll retire the existing box)
See above
3) Do we really need SCSI drives, or do SATA drives work as well? What would be the best RAID configuration for performance and fail-safe?
I've never seen a server using SATA, but your decision basically depends on your IO needs. How many concurrent users do you have, how much data, what kind of system is it (Olap or OLTP).
One thing though. Definetely don't use RAID 5 for the transaction log. Raid 1 or 1+0 offers the best write perfomance. For the datafiles RAID 5 should be the best option. See also:
http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=64
4) Backup system?
What about it ? Yes, you should have one.
5) What things that the salesperson is going to offer me should I be leery of, and what things that he doesn’t offer me should I ask for?
Just make sure your system is scalable enough for the foreseeable future (at least 3 years), without going for the overkill.
Hope this helps
Markus
[font="Verdana"]Markus Bohse[/font]
-
Mike
Have you done any performance monitoring to find out what the bottleneck is on your current server? You could save thousands if it turns out that all you need to do is buy some more disks and improve your RAID configuration, or add more memory (and upgrade to Enterprise Edition if you don't already have it).
John
-
Yes, maybe what you really need to do is add some indexes to some tables, or optimize some stored procedures. You need to be sure that you are actually doing something to fix the problem.
There's nothing worse that spending a lot of money to buy a new server, and finding out it does nothing to improve performance. It's a good way to get your boss thinking that the money they spend to keep you around isn't a good investment either.
-
I agree with John and Michael. For 3GB database with 25 users, your system should be good enough... I would suggest to start with these things:
- get rid of RAID 5; we had that in the beginning and it was absolutely terrible. We are using RAID 10 for both data and log file. (BTW, for some time we were running a 100+GB database with 50+ users on a configuration only slightly better than you described /except that RAID/, without any serious problems). SCSI yes.
- start optimizing the SQL, get rid of cursors and other row-by-row processing, avoid blocking and deadlocks, add missing indexes
- be sure you regularly defragment (DBCC INDEXDEFRAG) or rebuild (DBCC DBREINDEX) indexes
- put LOG file and TEMPDB on a separate disk (not on the same with data file)
If the problem is in bad SQL or missing indexes, new server won't help.
Oh, about backup, do the backups from SQL Server on disk and then you can copy the files somewhere for safe storage, burn them on CD or whatever you wish to do with them.
We still have 32-bit system for 300GB database and about the same number of users as you have (slightly more), no problems.
-
This is a great discussion. Thanks for the info.
I agree with the premise that putting all of my problems into a bigger, faster problem box isn't going to help that much.
Unfortunately, there's not a lot I can do with the majority of queries/stored procedures/jobs/etc that run on the box. The large database is a semi-out-of-the-box program known as Ecometry that was originally developed for MPE/HP 3000. When they ported the database to SQL, they did not fundamentally alter the structure. Packed fields, money stored as CHAR...just to name a few. I can't change anything under the hood of that database, sadly. We defrag regularly. The custom databases that I have created are indexed, and I don't do cursors. About the only thing I can do is throw more horsepower at it.
There are also problems at the O/S level that neither myself nor hired guns have been able to fix. I believe that rebuilding the server would help a lot, but that isn't a real practical solution for us right now. That may be the solution if the owners get sticker shock when I show them a price for the new server. Hopefully not, though, as I have plans to turn the existing server into a development box.
Thanks again for the thoughtful discussion.
-
One thing you might try, if you haven't already, is using Profiler to capture the query workload to a table, and then use the Index Tuning Wizard to see if it suggests additional indexes.
I would suggest capturing at least a couple hours of workload on three different days. Run the Index Tuning Wizard against each workload, and look at the indexes that it suggests creating. If it suggests the same index each time, create that index. Then repeat the process of capturing workloads for a few days, running the Index Tuning Wizard, and creating new indexes until it can suggest no additional indexes.
You may be surprised at the improvement you can get with just a few indexes. Many times, just a few queries represent the majority of the workload on a server. I have seen a case where just one index reduced the workload on a server by over 90%.
Even if you get new hardware, any tuning that you do will still benefit performance.
-
This is usually pretty obvious but could be overlooked. I was on a friends SQL machine and noticed SQL was not configured to take advantage of a memory upgrade.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply