March 10, 2009 at 2:33 pm
the powers that be have let us know that we will be getting the go ahead to purchase a second sql server in the next 2-3 months so we can have a dedicated reporting db.
This new server will be more powerful. 2 x quad core xeon 3.xxghz, 32+gb ram vs our current db that is a 2 x dual core xeon 3.00ghz, 16gb ram.
we plan to setup the reporting box to have data flattened, de-normalized, etc for historical tracking and statistical processing.
so the question is which one do we make as the transactional and which the reporting.
i keep bouncing back and forth on which one to give the more power too.
my personal view was the transactional should get the horsepower so it has plenty of growth for the future but i also see the viewpoint that since the reporting is running a lot of complex queries too, along with doing all the flattening, it should probably get the extra horsepower.
any help on which way is the best way would be greatly appreciated.
March 10, 2009 at 2:41 pm
You could use the new box for transactional processing but the SSRS box must come with good design and flexibility to use Views as datasource as needed so your developers don't use many temp tables which create issues.
SSRS generally rejects bad code but some run and make tempdb bloat so you need to work with your developers and Business units.
Kind regards,
Gift Peddie
March 11, 2009 at 6:57 pm
i'd say it depends on the queries. if the reporting code is a lot of data manipulation than you'll probably want the powerful CPU's there. most of our transactional stuff is a lot of queries that modify very few rows at a time so the faster CPU isn't that big a deal
we use HP Proliants and I rarely buy the fastest CPU since the speed difference is very minor. and these days GHz isn't a big indicator of speed like it was in the P4 days. i'd buy something around 2.3GHz. something like an E5420 CPU and the 32GB ram make sure you get it as 4GB chips if you are buying a 2U server like a Proliant DL380. It will cost you $1400 or so vs $3000 for the 8GB chips.
I finalized a quote today for 5 new SQL servers with 3 of them to have 32GB of RAM. all will be 4GB chips. in a year or so when the 8GB chips will drop in price we'll talk about upgrading to 64GB chips and we'll have plenty left over for other servers with less than 32GB. For CPU's i'm using E5440's and E5420's.
4 of these will be for a reporting services project and 1 for QA. 2 will be clustered db servers and 2 will be a reporting services web farm and we may put small db's on them later on to clear up some older servers
March 11, 2009 at 9:04 pm
As a very broad generalisation I would expect a reporting system to access more data and so to benefit more from additional memory than a (well-written) transactional system, where the queries are more likely to be inserts and single-row updates.
If you're getting adequate performance for your current transactional system on the current server then I'd be inclined to leave it there, but if you've got performance issues with it that aren't due to running the reporting database on the same server then move the transactional system to the new server, as the response time of reports isn't (generally) as critical as it is for a transactional system.
March 12, 2009 at 6:56 am
Thanks for all the advice guys!
We had a meeting yesterday to go over all this. looks like we're going to virtualize our dev server (which is an identical spec match to the current production db).
lets us save 10 grand on hardware so we can push for getting sql 2008 enterprise.
March 12, 2009 at 7:35 am
how is it going to save $10,000? we run VMWare here but don't run exchange or SQL on it. we're about to order a new QA SQL server as well. Proliant DL380 G5 with 32GB RAM and 10TB of storage. for dev we don't buy 32GB of RAM, but they still need the storage for all the databases which is over 1TB. memory and storage are most of the cost of a server and you'll still have to buy them if putting it on vmware as well as a license
we could put it on vmware but it will cost us more money
a base HP server is dirt cheap, less than $2000 for a decent one. RAM is cheap, 32GB can be bought for less than $1500. storage is pretty cheap as well. our 10TB of storage is going to cost us $5400 including the jbod and hard drives
March 12, 2009 at 8:06 am
we have started virtualizing some stuff using the free vmware server ontop of one of our file servers so no big deal to just setup anothing virtual machine to run the dev stuff.
anyhoo, we are a dell shop so the speced out new box was priced at around $10,000 (includes 24x7x365 w/ 4 hour gold service guarantee).
we always go with the gold support level as it's kicks ***! had more than a few HDs die and even a cpu fan or two. every time we call up dell and in just over 2 hours we have replacement parts hand delivered to our datacenter and installed.
March 13, 2009 at 5:51 pm
we usually stick with the 3 years NBD, but i'm buying some servers now and going to try the 5 year NBD. Almost every time HP's has had a new hard drive onsite the next day and we usually have a few extras lying around. one time i had 2 hard drives die in one server within 3 days of each other. the second one died literally hours after the RAID finished rebuilding from the first one. the database is over 1TB and it took almost 2 days to rebuild.
last few months we were pricing out a new cluster and priced out 4 hour support for the HP storage piece, but we ended up buying more storage on our EMC DMX-3 for this. anything important we cluster since 4 hours is considered too long for it to be down.
if you're intersted, VMWare ESX 3.5 is now free. it runs on the bare metal hardware unlike the previous free version
March 15, 2009 at 1:09 pm
If reporting includes OLAP, it will absolutely need the new server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply