473,388 Members | 1,988 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

SQL Server Sizing - Hardware requirements for SQL Server 2005

Hi all

I am having a SQL Server database of arround 110GB and 250 Users. What
is best suited hardware requirements for handling of such a database. I
am having following requirements from the server:

1. Feeding purpose : On which continously feeding is done by average
200 users.
2. Reporting purpose : on which average hundred of repords are running
at a time.

Right now i am having two servers for both of the above said purposes
with the following configuration.

Feeding Server

HPSERVER 370G4
3.2 Xeon Single processor
3GB RAM
1 SCSI 73GB 15000 RPM(OS & Transaction Log), 4 SCSI * 73GB with RAID 0
(Data Files) and 2 IDE * 300GB (Backups)

Reporting Server

Board SE7520BD2V
2 x XEON 3.2 GHz CPU 800 FSB with 2MB Cache
4GB RAM
3 SCSI * 73GB with RAID 0 (Data Files and Transaction Log), 2 SATA *
300GB (Backups) , 1 80GB IDE(OS)

I want to fullfill both of my requirements from a single . I want to
know what upgrades or changes I need to do in the configuration
according to the load and performence on existing servers. Here is the
table of performence counters along with their values to show the load
and performence for both of the servers.

ReportServer Value
FeedingServer Value
Counter Instance Avg. Min. Max. Avg.
Min. Max.
Pages/sec 0.081 0 8.001 0.02
0 1.993
Avg. Disc Queue length _Total 18.658 13.592 51.896
34.421 0 175.783
% Processor Time _Total 36.841 5.469 64.453
79.837 54.688 100
Buffer Cache hit ratio 97.702 93.595 99.463
97.41 77.261 99.989
Checkpoint pages/sec 0 0 0
0 0 0
Lazy writes/sec 1.243 0 30.003 14.263
0 97.194
Page life expectancy 17 4 81
10 6 14
Avg. Disk
Bytes/transfer _Total 37934.133 7903.004
128427.747 53806.538 8025.801 141750.562
Avg. Disk
Bytes/Read _Total 16737.419 8192 42895.862
54228.822 8170.499 143489.965
Avg. Disk Bytes/Write _Total 12298.494 0 65536
11708.014 0 92842.667
% Disk Time _Total 77.555 49.976 261.966 760.356
12.534 4890.383
Users Connections 17 17 18
195 185 212
Batch Requests/sec 0.859 0 23
73.215 0 205.009
Transactions/sec _Total 1.768 0 18.001
59.314 3 241.814
Page splits/sec 0 0 0 1.03
0 5.007

Please suggest what should be the configuration for my new server.
Should I consider buying an Dual Itanium Processor based system with 16
Gigs of Ram and 8 Hard Disks in Raid 0. Or Should I consider buying a
Quad Xeon Dual Core Processor based system. Or may be my existing HP
server can be upgraded to dual processor and 8 Gigs of Ram and 2-3 more
hard disks? Pls recommend what should be the factors that sould be
considered, guiding priciples and my approact to reach a decision.

With Best Regards
Parveen Beniwal

Oct 24 '06 #1
2 2636
I am having a SQL Server database of arround 110GB and 250 Users. What
is best suited hardware requirements for handling of such a database. I
am having following requirements from the server:

1. Feeding purpose : On which continously feeding is done by average
200 users.
2. Reporting purpose : on which average hundred of repords are running
at a time.

Right now i am having two servers for both of the above said purposes
with the following configuration.

Feeding Server

HPSERVER 370G4
3.2 Xeon Single processor
3GB RAM
1 SCSI 73GB 15000 RPM(OS & Transaction Log), 4 SCSI * 73GB with RAID 0
(Data Files) and 2 IDE * 300GB (Backups)

Reporting Server

Board SE7520BD2V
2 x XEON 3.2 GHz CPU 800 FSB with 2MB Cache
4GB RAM
3 SCSI * 73GB with RAID 0 (Data Files and Transaction Log), 2 SATA *
300GB (Backups) , 1 80GB IDE(OS)
Please suggest what should be the configuration for my new server.
Should I consider buying an Dual Itanium Processor based system with 16
Gigs of Ram and 8 Hard Disks in Raid 0. Or Should I consider buying a
Quad Xeon Dual Core Processor based system. Or may be my existing HP
server can be upgraded to dual processor and 8 Gigs of Ram and 2-3 more
hard disks? Pls recommend what should be the factors that sould be
considered, guiding priciples and my approact to reach a decision.
The most critical part in your setup is the I/O. This is where you must
put your money.

Put in as many disks as you can so your RAID arrays will have a larger
span; individual disks will work less, which leads to a better
performance. If you can afford it, get a SAN, which would be very
valuable in your situation. Otherwise you could put two RAID
controllers in your server (one for reporting, one for OLTP) to ensure
that there is as little contention as possible between the two groups
of users.

If budget is an issue, throw as much money as you can in good SCSI
disks for the OLTP then use cheaper SATA disks for the reporting, which
has less frequent disk access and requires more storage. However, keep
a few spare SATA disks as they will fail more often than the SCSI.

As for the CPUs, I would advise you to go with 64-bits Xeon DC+HT, not
Itanium.

When you talk about reporting, do you mean Reporting Services? If so,
make sure to have as much RAM as you can afford, since the reports are
built in-memory (which can be an issue when there is a lot of
concurrent users).

Regards,
lucm

Oct 24 '06 #2

"Parv" <Pa**********************@gmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...
Hi all
HPSERVER 370G4
3.2 Xeon Single processor
3GB RAM
1 SCSI 73GB 15000 RPM(OS & Transaction Log), 4 SCSI * 73GB with RAID 0
(Data Files) and 2 IDE * 300GB (Backups)
Don't go down this road. Do NOT use RAID 0. This will quadruple your
chances of a failed drive giving data loss.

Put the transaction log on a RAID 1 or even RAID 10 setup.

But the data on RAID5 if you want to minimize disks. Otherwise you can use
RAID 1 or 10 if yo can spare the disks.

Move the backup drives to a different machine. If this one fails (say bad
fan, it overheats destroying memory or CPU) those backups are of limited use
untli you can build another srever to put them in.

>
Reporting Server

Board SE7520BD2V
2 x XEON 3.2 GHz CPU 800 FSB with 2MB Cache
4GB RAM
3 SCSI * 73GB with RAID 0 (Data Files and Transaction Log), 2 SATA *
300GB (Backups) , 1 80GB IDE(OS)

I want to fullfill both of my requirements from a single . I want to
know what upgrades or changes I need to do in the configuration
according to the load and performence on existing servers. Here is the
table of performence counters along with their values to show the load
and performence for both of the servers.

ReportServer Value
FeedingServer Value
Counter Instance Avg. Min. Max. Avg.
Min. Max.
Pages/sec 0.081 0 8.001 0.02
0 1.993
Avg. Disc Queue length _Total 18.658 13.592 51.896
34.421 0 175.783
% Processor Time _Total 36.841 5.469 64.453
79.837 54.688 100
Buffer Cache hit ratio 97.702 93.595 99.463
97.41 77.261 99.989
Checkpoint pages/sec 0 0 0
0 0 0
Lazy writes/sec 1.243 0 30.003 14.263
0 97.194
Page life expectancy 17 4 81
10 6 14
Avg. Disk
Bytes/transfer _Total 37934.133 7903.004
128427.747 53806.538 8025.801 141750.562
Avg. Disk
Bytes/Read _Total 16737.419 8192 42895.862
54228.822 8170.499 143489.965
Avg. Disk Bytes/Write _Total 12298.494 0 65536
11708.014 0 92842.667
% Disk Time _Total 77.555 49.976 261.966 760.356
12.534 4890.383
Users Connections 17 17 18
195 185 212
Batch Requests/sec 0.859 0 23
73.215 0 205.009
Transactions/sec _Total 1.768 0 18.001
59.314 3 241.814
Page splits/sec 0 0 0 1.03
0 5.007

Please suggest what should be the configuration for my new server.
Should I consider buying an Dual Itanium Processor based system with 16
Gigs of Ram and 8 Hard Disks in Raid 0. Or Should I consider buying a
Quad Xeon Dual Core Processor based system. Or may be my existing HP
server can be upgraded to dual processor and 8 Gigs of Ram and 2-3 more
hard disks? Pls recommend what should be the factors that sould be
considered, guiding priciples and my approact to reach a decision.

With Best Regards
Parveen Beniwal

Oct 25 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

35
by: English Teacher | last post by:
Which would be a more useful relational database server to learn nowadays: MS SQL SERVER or ORACLE? Thanks!
2
by: ramankalia | last post by:
Hi, I need to provide SQL server for 100 or 50 VB clients. Please suggest the configuration for the above. thanks
28
by: prunoki | last post by:
Hello, I am an SQL server newbie. Our company has a massive application written in PL/SQL. I need to port parts of it to SQL Server. - Which SQL server version should I choose, to have a...
6
by: John | last post by:
Hi, I'm ussually just told what I will be working with, but today I was asked to choose the hardware myself. First off, it's a small company, so a $10,000 server is not an option. But also,...
8
by: cjshea | last post by:
Hi, no point in trying to be eloquent with a question like this. If a client met with me and told me of an application he wants to have changed over to DB2 as a database backend, or perhaps it...
3
by: danofweaver | last post by:
How can I practice the use of SQL server without changing my girlfriends laptop to XP Pro (which anyway I dont have)? Using (unfortunately) Windows XP Home not Pro, Visual Studio 2005. Trying to...
3
by: Steve | last post by:
Hi All I downloaded Sql server 2005 express SP2 and attempted to modify the Bootstrapper package files as I did with SP1 When i try to install SQL server as part of my VS 2005 deployment app I...
3
by: =?Utf-8?B?c2F2dmFz?= | last post by:
Do you know if microsoft has any tools or guidelines to calculate the hardware requirements for a server with windows server 2003 that will have a couple of .Net webservices and a couple of aspx...
1
by: kkshansid | last post by:
what is minimum hardware requirement of sqlserver 2005 my system has 9.24gb space in c drive 13.9gb space in d drive why its still showing The current system does not meet the minimum hardware...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.