Best method to migrate data?

Hello all:
The task: migrate large amounts of data from multiple machines to
corresponding databases on one machine, which will serve as a data
warehouse. The warehouse machine will have no transactions, so I plan
to have logging turned off for it.
I understand that, although tables on the warehouse will be heavily
indexed, that I will want to disable the indices and constraints prior
to the bulk loads.
My question: what is the best methodology to actually move the data?
Should I use DTS packages, or export to files and use BCP, or ?
Also, should I be content to clear out and reload the warehouse tables
to be sure to catch any changes to existing records from the production
database, or is it more feasible from a performance standpoint to
update existing records and only insert new records?
Many thanks,
zdrakec"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147096374.882929.83610@.i39g2000cwa.googlegroups.com...
> Hello all:
> The task: migrate large amounts of data from multiple machines to
> corresponding databases on one machine, which will serve as a data
> warehouse. The warehouse machine will have no transactions, so I plan
> to have logging turned off for it.
You can't turn off logging.
> I understand that, although tables on the warehouse will be heavily
> indexed, that I will want to disable the indices and constraints prior
> to the bulk loads.
> My question: what is the best methodology to actually move the data?
> Should I use DTS packages, or export to files and use BCP, or ?
Use SSIS. No question. It doesn't matter what versions of SQL Server you
are using. SSIS is the right tool and it can load whatever you have.
> Also, should I be content to clear out and reload the warehouse tables
> to be sure to catch any changes to existing records from the production
> database, or is it more feasible from a performance standpoint to
> update existing records and only insert new records?
>
It depends. Consider loading a staging table. Then you can mix and match
INSERT, UPDATE, DELETE to fit your needs.
David|||Hello David:
Thank you for your remarks.
I was under the impression that the database could be started in a
no-logging mode. Am I mistaken, then, in this impression?
Also, I am unfamiliar with SSIS. Can you point me towards information
about it?
Thanks much,
zdrakec|||"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147100405.620218.169540@.j33g2000cwa.googlegroups.com...
> Hello David:
> Thank you for your remarks.
> I was under the impression that the database could be started in a
> no-logging mode. Am I mistaken, then, in this impression?
Yes. In the Simple recovery model the log is still written. It's just
truncated occasionally so it doesn't grow.
This doc is 2005, but the recovery models are the same in 2000.
Overview of the Recovery Models
http://msdn2.microsoft.com/en-us/library/ms189275.aspx
> Also, I am unfamiliar with SSIS. Can you point me towards information
> about it?
>
http://www.microsoft.com/sql/technologies/integration/default.mspx
http://msdn2.microsoft.com/en-us/library/ms141263.aspx
http://www.sqlis.com/
David|||Thank you sir!!