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!!

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/techno...on/default.mspx
http://msdn2.microsoft.com/en-us/library/ms141263.aspx
http://www.sqlis.com/
David|||Thank you sir!!

Benchmark workload

I`m looking for programs, that could test response from SQL serve db.
I have DB, but it is operational, and I do not like to make any changes on
that DB. But I will have to test server (SQL) on that hardware.
My be program that makes it own generic DB, that can be use on different
hardware
Two recent blog posts about this.
SQLIOSIM
http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx
SQLQueryStress
http://sqlblog.com/blogs/adam_machanic/archive/2007/06/28/new-version-of-sqlquerystress-released.aspx
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Mr.B" <MrB@.discussions.microsoft.com> wrote in message
news:136AA429-726F-4FAB-91E6-05FC4B0C9A66@.microsoft.com...
> I`m looking for programs, that could test response from SQL serve db.
> I have DB, but it is operational, and I do not like to make any changes on
> that DB. But I will have to test server (SQL) on that hardware.
> My be program that makes it own generic DB, that can be use on different
> hardware
|||You are asking a very broad question w.r.t. benchmarks. The answer depends on
what exactly you want to accomplish or want to test. Is there a specific
question you'd like the benchmark to answer?
Linchi
"Mr.B" wrote:

> I`m looking for programs, that could test response from SQL serve db.
> I have DB, but it is operational, and I do not like to make any changes on
> that DB. But I will have to test server (SQL) on that hardware.
> My be program that makes it own generic DB, that can be use on different
> hardware