SQL Server Performance Forum – Threads Archive
Give me advice about hardware for 15 SQL Server DB
We have 15 different locations with 15 SQL Server DBs. These 15 servers communicate together by leased line internet. We want there is a replication about data among them.With each DB server:
Average total of transactions: 38,500 transactions per day
Number of transactions in peak hours: 7,700 transactions per hour
Number of transactions in peak minutes: 140 transactions per minute
Yearly growth: 25% per year
Stored data in DB is 5 years at least Please suggest me the harware configuration for 15 DB servers.
Thanks so much Phuoc Tu
Hi, Fore Hardware Configuration i suggest to read :
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12015 http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11875 http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10644 http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9056 http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8235 http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6281 http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5681
Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
And wait for Joe Chang suggestion.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
what is your current system in terms of processor, memory & number of disks
for the work load above,
what is the CPU loading on the current system?
what if the memory & buffer cache hit rate.
what is the disk activity to data file (reads/sec & write/sec) are you saying you want to buy servers that will last 5 years, or that you want to retain 5 years data. generally i do not recommend buying an expensive system with growth headroom for 5 years at 25% annual growth.
example, if a moderate cost 2 socket system (single or dual core) is expected to support 5 years growth, then fine. but if it will take a 4 or 8 socket system today to support expected growth for 5 years,
my advice is to buy a 2 socket system good for 2 years, then replacing it with a new 2 socket system then, which should be as good as a 4+ socket system today, at the same price as a 2 socket of today, not to mention the reduced software licensing costs also don’t bother buying a 4/8 socket system only populated with 2 sockets for future growth, this is generally a waste.
I would like to clarify issue raised by Phuoc Tu as below. Our system consist of 14 marts and 1 (one) head office. – Some of marts connected together by Leased line, some of them connected by ADSL in WAN (internet). – Total transactions per day: 38.500 – No. transactions in peak hours: 7.700 – No. transactions in peak minutes: 140 – Total customer is 300.000 and yearly growth is about 25% – Make sure data stored at least 5 years. The data changed (customer buy goods) at marts will be synchronized each other and with the head office by using SQL Replication mechanism periodically. Please help me achieve a hardware solution (for each mart, for head office and speed of leased line or ADSL) meet above requirements and ensure unexpected cases counted as well. Thank you very much,
Hiep Tran
ok, i will assume that you do not currently have a working prototype from which to get CPU, memory and disk usage. next, my answer will differ for 1) US & Western European countries and 2) other countries.
the reason is that relative to the cost of personnel, the cost of a 2 CPU(socket) system is pretty much irrelevent for US, but can be very significant for other countries.
basically in the US , businesses like to buy the 2P system regardless of whether a 1P might work just fine. Processor: your choice of a single socket dual core:
1) AMD Opteron 165, 170 or 175
2) Intel Pentium D 920 or 930
3) the new Intel Duo & matching motherboard that requires ECC memory (do not think of running a business on a motherboard without ECC memory).
I would recommend a Supermicro motherboard for Intel processors, some one else should make a recommendation for the Opteron.
The Supermicro PDSM4 or PDSG4 motherboards and SC743i-650 chassis would be a good combination
2-4GB of ECC memory 4 73GB 10K SCSI drives (Fujitsu MAT3073NP for example)
2 300-400 SATA drives Notes
use the 4 SCSI drives in a) 2+2 RAID 10, 3+1 RAID 5, b) 2 arrays of RAID 1 for hot data and logs
use the 2 SATA drives for backups, and for a separate Filegroup for archival data that might not fit on the SCSI drives.
SATA drives alone will not be to handle transaction processing random IO. I am recommending a white box (unbranded, ie, not a major brand) because that is the easiest way to mix SCSI and SATA drives.
For US customers, just buy 8 SCSI drives and don’t complain about the cost.
(or if you have lots of money and light on intelligence, buy an expensive SAN that will performance worse than the SCSI drives) Given that your peak work load is 140 tx/ min, assuming there are not lots of uncounted read queries on top of this,
this means you have 0.85 CPU-sec per transaction, (2 cores, so you have 120 CPU-sec per min, to spend on 140 tx) A very basic transaction (12 updates & 11 inserts) consumes only 3 CPU-milli-sec, so you can do alot in 0.85 sec. but you cannot do stupid things like compiles and recompiles, or multiple network round-trips per tx (2-3 OK), or unnecessary XML parsing etc.
if you like doing stupid things, buy a 4 or 8 socket system. I recommend you do this on SQL 2005 Std Ed, because 2005 Std supports more memory than SQL 2000 Std. use the 64-bit version if this is new project.
Now i am saying all of this without knowing anything about your application.
that’s ok because if it turns out you need much more CPU power, you can always use these boxes as web/app servers, you just might want to switch to a 1U chassis. for the home office, just start with a 2 or 4 socket system, 4-8GB memory and 8-15 disk drives
In addition to t/sec, you are going to need the metrics on actual packet/data size that would be transferred between all these locations. Remember to plan for peaks when determining if your lines are going to be sufficient. The speed of your lines and the maximum capacity and optimal capacity is really going to determine how, or even if, you can pull this whole data replication thing off. Transactions don’t mean anything if they are all extremely small. If they are extremely small though, you will be in trouble without spending a LOT of money on your WAN. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>