Info on Data Warehouse Hardware requirements needed

[Catadmin]

All,

Any references you can give me (books, URLs, or just comments on your own experiences) would be greatly appreciated...

My boss has me searching the internet for the recommended hardware (assuming all Microsoft tools) for a data warehouse we're developing. I know the approximate size of the data warehouse will be approximately 342 GB (we added the size of the 2 biggest DBs and multiplied by 3). And while traffic will be light at first, about mid-way through the project, we anticipate approximately 20 users doing continuous data mining / ad hoc reporting and about another 15-30 using canned reports on a daily basis.

We're looking at putting this on the passive node of a current Active / Passive cluster. 4 processors (hyper-threaded to 8) of the AMD Opteron 875 type (2.20 GHz) and 11.8 GB of RAM. But the boss wants to make sure this is enough. We already have the SAN / RAID setup at Corporate. All we have to do is "lease" another logical drive from them for this.

Any thoughts on where I can go to look at CPU / RAM requirements? Any thoughts on hardware I should look at that I'm not thinking of?

Thanks!



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Cat,
How can you put this on the passive node of a cluster? The SQL Services are stopped until the server fails over. Are you going to break the cluster? I didn't know you could use the passive node.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

We're going to turn it into a Active/Active cluster. I believe we can do this through the Cluster Administrator and by adding the new drives as new resources pointing to a 2nd instance of SQL whose home will be on the currently "passive" node of SQL.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Cat,
If your moving to an A/A cluster aren't you going to have to partition your production db to a new named instance? Then you'll need another named instance for the Datawarehouse? Mr Denny had provided me some great information about A/A clusters. I will try and find that post for you.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

The production DB is already on a named instance. We'll be installing a second named instance when we're ready to do the Active/Active thing.

But I'm looking for hardware information, Paul, not cluster information. I'm trying to verify that we don't have to buy a new server entirely.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Ok, I found the thread I was looking for. Here is Denny's quote.

In Windows clustering there isn't a way to have a true active/active cluster. When you end up with is two seperate instances, one running active on each node (default instance on one node, and a named instance on the second node). There isn't any way with Windows 2000 or Windows 2003 to run a single SQL Server instance active on both nodes at the same time. The SQL Service runs on either one node or the other.

There are some third party cluster services, but they are not supported by Microsoft.

Here is the thread.
thread183-1296200

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

Paul,

Thanks, but I already know that information. We have that setup on our Development/Test/QC cluster.

All I am looking for right now is hardware references. Can you help me with that?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Cat,
I'm just trying to understand the entire load on your server. from both the cluster and the datawarehouse. From personal experience your hardware should support a datawarehouse. I would caution you on the following. We were allowing users to run ad hoc queries. What I found was that some user don't know how to write queries. (surprise!) We constently had users writting bad queries that crashed the system. I ended up setting a limit to the tempdb to stop run away queries.

- Paul
- If at first you don't succeed, find out if the loser gets anything.