Capacity Planning for Sql Server 2005 database of 200GB size.

Question

Post reply

Capacity Planning for Sql Server 2005 database of 200GB size.

sunil.singh-663153

Valued Member

Points: 51
More actions
September 27, 2010 at 4:23 am

#148902

Hi
i am asked to provide hardware and software configuration for deploying 200GB of Sql Server 2005 database.
Being very new to capacity planning i am struglling to gather data.
There is existing database which is already running into production but for deploying revamped application which has significant changes in db also needs to redeploy on new servers.
Points to be considered.
1) Replicaitons are implemented on servers
2) Its not going to grow in size rapidly. Its slowly growing DB.
Any help or article in this regard will be appreciated
Sunil

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Oliiii SSCertifiable Points: 5328 More actions · Answer 1

You need a bit more info to get an answer:

- How heavily will the DB be used (how many concurent users, heavy queries or small queries, etc)

- The % of read vs write

- The amount of data that will need replication (insert, update, delete) per day.

The size of the DB alone doesn't always dictate on which hardware it should run, you can have 2TB DBs that can run on 4GB Ram 1x dual core server while a 50GB DB can kill a 16Cores 64GB Ram server.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Try to keep almost similar config as you have on prod ...you can compromise with disk , CPU etc.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sunil.singh-663153 Valued Member Points: 51 More actions · Answer 3

Hi Oliii thanks for your reply...

-There will be atmost 800-1000 concurrent users, Queries will not be too heavy it will be of medium size.

-% ratio between Read and arite will be 40:60 respectively.

-Replicated data will not exceed more than 1-2 MB per day

Do let me know if i need to provide some other info to come up with some data

Thanks

Oliiii SSCertifiable Points: 5328 More actions · Answer 4

800-1000 concurent users is kinda a lot, so you'll need a better than average machine to keep up with the load spike (i guess in the morning and after lunch).

You might want to aim for 8 - 16 core total (2x4 or 4x4 cpu) and 32-64GB RAM (the more the better of course, but it does cost a lot).

1-2MB of data replicated is tiny, so the server wont even notice it.

You'll also need to have your data on a SAN (Tier 1 if possible), data, log and tempdb on separate LUN. You can also try to split the DB in several datafile and spread on multiple LUN if you have too many IO_LATCH wait time.

If you are tight on budget reduce the number of cpu and RAM to 2x4 and 16GB but try not to use a lower tier SAN (since you have a lot of write, your disks will be the most used).

This is a broad guess, you have to run some load test to make sure you don't have any specific bottleneck (you might even end up ordering a smaller server).

sunil.singh-663153 Valued Member Points: 51 More actions · Answer 5

Thanks Oliii....Will consider your suggestion and do some load testing.

Thanks