Base hardware/software req list for Datawarehouse using sqlserver 2000

[sqlserverbest]

Hi,

I am looking for base hardware/software list for a datawarehouseing project using Sqlserver 2000.

Thanks
Anand

 

[SQLBill]

I don't do datawarehousing, but I think you are going to have a problem with getting a good answer. You just don't provide enough information.

The "base" requirements are going to be different based on a lot of factors.

You'll need 'bigger' hardware for a dw that combines the input from 20 db's than you will need for one that combines 3 dbs.

You'll need 'bigger' hardware for a dw that will maintain 20 years of data than one that will maintain 6 months of data.

You'll need 'bigger' hardware for a dw that will maintain 6 terabytes of data than you will need for one that maintains 60 GB of data.

To provide a 'base' configuration, anyone who does datawarehouseing would need to know more details about what you are warehousing (amount of data, length of time, how many original sources there are, etc.)

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Check out this Microsoft web site:

http://www.microsoft.com/sql/evaluation/bi/default.asp

Specifically, check the Implementation Considerations section with subtopic Data Warehouse Design Considerations.

-SQLBill

Posting advice: FAQ481-4875

 

[sqlserverbest]

Thanks,

The datasource is going to be just one. It is going to be a incremental load of approx 40MB of data everyday. It is planned to keep the data for not more than 1 year. Users may be between 20-30 concurrent users.

Please understand it may not be accurate but can I get some hardware/software list based on the above.

Thanks

 

[SQLBill]

How is that 'data warehousing'?

That just databasing. Normally a data warehouse is a storage for the data from several different databases. It's combined so everyone has access to all the data vice just the stuff from themselves. For example: HR, payroll, and finance have separate databases, their data is copied over to a common database - that's data warehousing. Same thing if your company is at different locations and has dbs at all those locations. The main office might have the data warehouse holding data from all of the remote locations.

Anyways.....You are only going to have 14.6 GB of data a year (40MB * 365 days = 14600MB = 14.6 GB). That's a small server. I would get a two processor server with at least an 80 GB hard drive. 80 GB allows you room for the SQL Server application, OS, data, log files, and some backups.

But that's my opinion.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Agreed, that's a fairly small server. I'd recommend more than just an 80 GB drive though.

I'd pickup 2 18-36 Gig drives for a RAID 1 for the OS.
Say 5 or 6 36 Gig drives for a RAID 5 for the database files. That should give you some nice speed.

Dual chip should be fine.
3 Gigs of RAM (2 for SQL and 1 for Windows).
Windows 2003 Server (Standard)
SQL 2000 Server (Standard)

At that point, you should be good to go.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLBill]

Denny,

I agree 'bigger' would be better and you did go more in detail (thank you). I was just trying to provide the 'base' not the best.

-SQLBill

Posting advice: FAQ481-4875

 

[sqlserverbest]

Thanks,

I was looking for more like what mrdenny had replied - base but little detail. What about the failover server etc...

Any chances I can get an architecture diagram for the whole configuration.

 

[SQLBill]

A failover server isn't 'base'. Not everyone has one of those.

If you are looking 'beyond base', we could provide you data for days to come....for example, have separate hard drives for the data files and the log files. Better yet have a third set for the TEMPDB. And another set for backups.

See? We can really add on stuff, but Denny gave you the base system. From there you should be able figure out what you really need and design it.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

And if you really want to add a failover server, double what you're planning so you can get a server with similar hardware and identical software. That means you'll have to invest in extra software licenses too.

With the above, you can do log shipping, assuming you're using SQL Server Enterprise version. Log Shipping doesn't work on other versions. But it will allow you to "flip a switch" so to speak to bring it on as the primary server should your primary fail.

The other alternatives are Clustering (Enterprise needed for that too) and Replication (not sure if Enterprise is needed or if it works for Standard). Still, you're talking additional hardware and software investments. This is, as SQLBill mentioned, way above base system requirements.

The cheap method of data recovery is to get yourself a tape backup drive and do daily backups. Note that this is not necessarily the best method of recovery setup options. Simply the cheapest.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

I'd recommend writting your own procedures for log shipping. The builtin log shipping doesn't always work correctly when it comes to failing over from one server to another. A good SQL admin/programmer can write there own log shipping setup in about an hour or so. This way you can get away with not purchasing Enterprise just for the log shipping.

Replication doesn't need enterprise Edition. All editions of SQL Server can be used for replicaiton including MSDE, Personal, Dev, Standard and Enterprise. The only limitation on Replciation is that MSDE can't be a merge replication publisher.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

Denny,

I'm working on a log shipping solution for my current employer. I've been playing with the Log Shipping in Ent. Manager, but have noticed problems with it restoring occasionally. Where would I go looking for information on scripting my own version of it?

Do you have a FAQ on this or a recommendation of a book/website?

Thanks,



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

Apparently I've never written an FAQ on this before. I'll work on one. It'll probably take a while though. There's a bunch of code behind it.

I couldn't refference any books with the code. I've never actually read any books on SQL (except the onces for the MCDBA exams).

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

Thanks, Denny!

We greatly appreciate your hard work in advance. @=)





Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!