Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Base hardware/software req list for Datawarehouse using sqlserver 2000

Status
Not open for further replies.
sqlserverbest

sqlserverbest

Programmer
Joined
Jan 16, 2003
Messages
8
Location
US
Hi,

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

Thanks
Anand
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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)

[noevil]
(My very old site)
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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)

[noevil]
(My very old site)
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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)

[noevil]
(My very old site)
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

RRinTetons
  • Locked
  • Question Question
Tool for job scheduling
Replies
0
Views
579
RRinTetons
RRinTetons
RRinTetons
  • Locked
  • Question Question
Logging into a database using SSMS from non-domain machine
Replies
1
Views
755
fredericofonseca
fredericofonseca
tekpr00
  • Locked
  • Question Question
Backups in a multiserver environment while using Central Management Server and Server Groups
Replies
0
Views
424
tekpr00
tekpr00
mrmovie
  • Locked
  • Question Question
ODBC connection, or other from Linus system using AD account details
Replies
2
Views
442
mrmovie
mrmovie
Richard Parry
  • Locked
  • Question Question
Server to Server VPN for SQL Traffic
Replies
2
Views
351
Richard Parry
Richard Parry

Part and Inventory Search

Sponsor

Back
Top