January 16, 2007 at 10:38 am
If this has been asked before, I apologize.
My boss has me looking around to see if we need to get a new box for the data warehouse we're designing. Currently, we have an Active/Passive cluster with our Production DB on the Active partition. The plan is to add a new named instance to the Passive side and put the Data Warehouse there, making the whole thing an "Active/Active" cluster. Current hardware is 11.8 GB of RAM and 4 hyper-threaded AMD Opteron 875 processors (2.2 GHz).
We already know we're going to need more disk space. We're planning for approximately 342 GB (not a gigantic DW) and Corporate already has a SAN / RAID set up available. We just need to give them the PO for the drives.
Usage plans are small to start with (we're doing this in small "deliverables"), but about at the mid-way point of implementation, we forsee 20 concurrently connected users running Ad hoc queries and between 15-20 more users running canned reports.
I'm a little worried that with our current config, we may not have enough RAM to support both the production DB and a data warehouse should a fail-over occur. Does anyone have any thoughts or experience on this issue? The little I've found through Google seems to be about DWs which are Terrabyte size, not Gigabyte size.
Thanks in advance!
January 16, 2007 at 11:19 am
What's the load on the production side v the DW side? If you are going to exceed the sum of the CPU usage, you might have an issue.
For memory it's harder. Trying to figure out how much memory you need v what SQL just grabs is hard, especially as most of the extra is caching data.
One thing you can try is to reduce the memory on the DW or production in SQL (using configuration) and see if it performs worse. If you can get a few days to test things and see about response and CPU performance v memory usge, you might make some guesses.
The other thing to think about is that a failure means that something's wrong. If you truly need both sides to perform as well in that event, buy the sum of the memory of both boxes for the new ones.
Keep in mind if you can do more disk separation, you can speed things up slightly as well by having separate threads handle indexes and data retrievals. Not a great substitue for more memory, but every little bit helps./
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 16, 2007 at 12:15 pm
Steve,
I appreciate the reply. My main problem in checking load is that I don't currently have a DW side to check the load against. I will, however, check the load against the Production side to see what it's doing (thank you for that recommendation).
We just got a whole bunch of new hardware for our SQL Server 2005 upgrade this year, so of course it has to last us at least 18 months before we can start asking for another box. Fortunately, I believe we asked for (and got) a scalable setup. Unfortunately, boss wants to know now, before we get into the project of course, what hardware requirements we need for the DW and it's hard to tell him since most of what I've been finding on the internet is "It depends on your setup".
January 17, 2007 at 9:48 am
OLTP aticvity is short, bursty transactions. Warehouse activity is large scanning, sorting, and aggregation. On the disk level the OLTP will do quick highly randomized lookups and small quick writes. Warehouse will do lengthy scans and with large readahead, and has potential for large write as well (reports). The warehouse will be much more demanding on CPU, tempdb, memory, and disk than your OLTP environment. You'll want to be deligent in planning the disk as these two environemts dont mix well. Since you dont have the WH in place, your best approach is to use the business drivers (number of concurrent users, volume of data, etc) to estimate CPU, memory, and disk resource intensity. It's a bit tricky to do, but given enough thought you should be able to come up with a solid forecast that will help you understand the requirements and help you communicate them to management. Also keep in mind that in a warehouse environemnt where people are free to run ad-hoc queries, users can, and will write poorly structured code. This will impact your OLTP system if the instance are on the same server.
The best design would include a standby server that would host one of the environments in the event of a server level failure. Ideally (but usuallu not realistic) each node would be configured to handle the load of both instances.
Bottom line is that as the DBA you are tasked with determining the best and most appropriate hardware configuration. In most cases management will push the requirements down in order to meet budget requirements, but it's important that they understand where corners are being cut and the related risk of doing so
January 18, 2007 at 4:53 am
Well said! And thank you for the perspective. I know I'm not going to get everything I want, but at least I can warn them. Then, if they don't listen to what I'm saying, at least I'll have a clean conscience.
The good thing about my workplace is that my boss & boss's boss are both ex-tech guys who are reasonable when something falls through. They don't tend to blame the little guy when they were warned in advance something was a possible point of failure. @=)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply