New datawarehouse

What is the best way to set up a datwarehouse? Do I use replication or dts
packages or what? What I need to do is archive production data every two
weeks to this datawarehouse. Then I will need to be able to query the
archive data. What is the best way to do this? And how do I go about doing
it?
Thanks in advance for the help.
DaveDave,
Surprising that you didn't seem to spark a lively conversation on this
question.
If you really need a DW then the right answer is to take a long term
approach and design an enterprise level data warehouse where the
organization can go to analyze data from a single perspective that has been
defined, researched, tested, and authorized by the business as the single
point of truth. Sounds like a lot of work? Well perhaps what you are
really needing is a reporting operational data store, ODS. This is a common
starting point of a DW. There are many reasons for needing a reporting ODS.
Adhoc queries are contending with transactional inserts/updates
The OLTP is slowly down due to volume of data and the OLTP doesn't need the
old data
Queries are too slow against the OLTP normalized schema
Queries are to hard for users to create against the complex OLTP schema.
An assumption here is that archive means copy and remove. If you are
intending to remove the data from the OLTP then replication is out as a
solution.
Based on the limited information provided and not knowing budget or skill
levels, I would recommend:
Buying a separate SQL server
Defining an easier/faster schema for the data your users need (Star or just
denormalized and use surrogate keys)
Depending on skill level, budget, and transformation complexity use TSQL,
DTS, or a third party ETL tool to extract, transform, load, and purge the
incremental data
Be careful of updates and deletes as the source system may not clearly
indicate them in the schema
The usual system development life cycle and operational techniques and
should be applied.
Good luck,
Danny
"Dave Mortenson" <dmortenson@.ident.com> wrote in message
news:OXrlm7ByEHA.1396@.tk2msftngp13.phx.gbl...
> What is the best way to set up a datwarehouse? Do I use replication or dts
> packages or what? What I need to do is archive production data every two
> weeks to this datawarehouse. Then I will need to be able to query the
> archive data. What is the best way to do this? And how do I go about doing
> it?
> Thanks in advance for the help.
>
> Dave
>