June 20, 2003 at 5:46 am
We are looking to upgrade our sql Servers and my boss is worried about the cost.
We have a production server wich runs press management software for 12 presses. it is constant communication getting copy counts etc, as well as scheduling new jobs and generating presets(quite a lot of work for each Publication). Then we have Mis servers wich capture data from presses as well as managemnt and distribution input. Input is not that big but reporting and selecting infomation is biggest load.All these servers have a backup of same spec and are mirrored. as well as the same set of servers being on other sites and replication tasks running between sites to create a common report DB.
The servers that were recommended were from compaq and the configuration was 1 disk on server for Operating system and SQL program. Database on seperate raid 5 and Tranasaction logs on a 2 disk striped set mirrored with other server.
My question is what is the advantage of putting the transaction logs on a seperate set of disks, to justify this cost how many transactions/sec warrant this configuration?
I feel that this is a bit of overkill and if we can get the cost down the much needed upgrade might happen sooner. At the moment our production servers are running with NT 4.0 and sql 2000 because the service providers of the production software say the hardware is not good enough, but i have argued that this configuration is worse than running 6.5 or using Windows server 2000 on exsisting hardware.
Edited by - MikeTomkies on 06/20/2003 05:51:21 AM
June 20, 2003 at 7:49 am
quote:
My question is what is the advantage of putting the transaction logs on a seperate set of disks, to justify this cost how many transactions/sec warrant this configuration?
From BOL.
"General recommendations for creating transaction log files include:
Create the transaction log on a physically separate disk or RAID (redundant array of independent disks) device. The transaction log file is written serially; therefore, using a separate, dedicated disk allows the disk heads to stay in place for the next write operation."
Use performance Monitor to monitor how busy the disks the transaction logs reside to decide whether your configuration can handle the workload.
June 20, 2003 at 8:18 am
Thanks for the info. I do plan to run performance monitor to find out how busy the servers are, Just a bit worried about our production server it regulary peaks at 100% processor usage, i read that this is a known problem with sql 2000 quey analyzer and is sorted out in a service pack wich our suppliers have not put on(Shocking considering how important this server is).So when i evntually get a figure of transactions per sec, what benchmark figure should i use to determine wether i should implement this configuration?
June 20, 2003 at 8:32 am
You should monitor CPUs, Memory, I/O, Network and SQL Serevr's counters.
http://www.sql-server-performance.com/performance_monitor_counters.asp
http://www.sql-server-performance.com/performance_monitor_counters_io.asp
http://www.sql-server-performance.com/performance_monitor_counters_memory.asp
http://www.sql-server-performance.com/performance_monitor_counters_network.asp
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
June 20, 2003 at 10:51 am
Putting the transactions logs on seperate set of disks is to gain disk I/O speed. Though the transaction log file is written serially, if you put the log file on a stripe set, the data will be written accross the disks, therefore gaining I/O speed.
I recommend putting the log on stripe set with parity instead of mirroring, because mirroring brings a lot more CPU and I/O overhead.
WM
June 20, 2003 at 4:59 pm
Any links to the documentation of the 'known sql2k cpu peak' problem?
June 20, 2003 at 5:20 pm
Is this documented in Microsoft Knowledge Base?
June 22, 2003 at 11:57 pm
If any one have got references to problems with SQLSrver upgrading please share with me,In our case it seems to be a never ending process ,correcting errors one bye one ,then proceed step by step …,
We are upgrading it from SQL 6.5.
Which will eassy 6.7 to 7 to 2000 or direct to 2000
June 23, 2003 at 4:23 am
Direct to 2000.
Andy
June 23, 2003 at 7:41 am
quote:
Putting the transactions logs on seperate set of disks is to gain disk I/O speed. Though the transaction log file is written serially, if you put the log file on a stripe set, the data will be written accross the disks, therefore gaining I/O speed.I recommend putting the log on stripe set with parity instead of mirroring, because mirroring brings a lot more CPU and I/O overhead.
WM
Actually, you would want the log on a mirror (Raid 1) for redundancy - or preferably a striped mirror Raid 0+1 for speed because putting it on a Stripe with parity (Raid 5) will double your I/Os (the extra I/O is needed to calc and write the parity). Hardware mirroring typically puts NO overhead on the CPU and you could even gain additional I/O speed due to the hardware's ability to split reads.
Mike - Compaq's recommendation in your original post is the best way to go. Of course if you have some additional $$$, you could also go with Raid 0+1 on the data side as well (instead of raid 5). Remember to configure your drive arrays for the I/Os that you need to get, not just space.
Edited by - fapel on 06/23/2003 07:44:53 AM
Francis
-----------------
SQLRanger.com
June 23, 2003 at 9:11 am
quote:
Any links to the documentation of the 'known sql2k cpu peak' problem?Is this documented in Microsoft Knowledge Base?
If you look at the fix lists for microsoft sql server 2000 SP1 to SP3 you will see quite a lot of problems related to CPU Spin this is where i saw problems wich are related on our server.
June 26, 2003 at 11:39 pm
MikeTomkies,
What is the total size of the DBs on this Server?! Also how many transactions & users connect to this server?!
Your decision of the config will depend on this. Because one our server is not very very busy compared to other servers. So the is the config use had till recently...
We had 4 DISK RAID-5 (out of them 1 for Parity). We used to store the databases and Logs on the same drive. We used to dump the logs every 1 hr, nightly full backup, then to tape.
Quite recently since this server has become busy, we are upgrading with 2 DISK RAID-1 (with DISK MIRRORING). Still in the process.
.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply