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!!
Showing posts with label amounts. Show all posts
Showing posts with label amounts. Show all posts
Wednesday, March 7, 2012
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!!
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!!
Monday, February 13, 2012
beginner question
I have just discovered that tables designated for publication cannot be
dropped.
I use snapshot replication for large amounts of data but infrequently.
Often need to refresh the entire table with new values. I do this by
dropping a table and writing a whole new one.
Is there a way to disable this property do that tables can be dropped and
recreated?
Or is there perhaps some other way?
Any hints will be appreciated.
Ernesto,
you could use Truncate Table' instead. This'll remove the rows and leave the
table intact (also resetting the identity column if you have one).You'll
have to drop any FK references before running this against a table, but
presumably you're doing that anyway.
HTH,
Paul Ibison
|||Thanks, that may be what I need.
However, two more questions:
1. What is an FK reference?
It sounds like Truncate will leave the structure intact.
Alas, the routine changes sometimes mean that a new column is added or
dropped from the table.
2. Do you have any suggestions if the structure of the table actually
changes?
Much appreciate your help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#agkcbLgEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Ernesto,
> you could use Truncate Table' instead. This'll remove the rows and leave
the
> table intact (also resetting the identity column if you have one).You'll
> have to drop any FK references before running this against a table, but
> presumably you're doing that anyway.
> HTH,
> Paul Ibison
>
|||Ernesto,
FK references are foreign key references to a primary key on the table you
are truncating.
sp_repladdcolumn can be used to add another column to the table and
subsequently have it replicated (sp_repldropcolumn for the converse).
HTH,
Paul Ibison
dropped.
I use snapshot replication for large amounts of data but infrequently.
Often need to refresh the entire table with new values. I do this by
dropping a table and writing a whole new one.
Is there a way to disable this property do that tables can be dropped and
recreated?
Or is there perhaps some other way?
Any hints will be appreciated.
Ernesto,
you could use Truncate Table' instead. This'll remove the rows and leave the
table intact (also resetting the identity column if you have one).You'll
have to drop any FK references before running this against a table, but
presumably you're doing that anyway.
HTH,
Paul Ibison
|||Thanks, that may be what I need.
However, two more questions:
1. What is an FK reference?
It sounds like Truncate will leave the structure intact.
Alas, the routine changes sometimes mean that a new column is added or
dropped from the table.
2. Do you have any suggestions if the structure of the table actually
changes?
Much appreciate your help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#agkcbLgEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Ernesto,
> you could use Truncate Table' instead. This'll remove the rows and leave
the
> table intact (also resetting the identity column if you have one).You'll
> have to drop any FK references before running this against a table, but
> presumably you're doing that anyway.
> HTH,
> Paul Ibison
>
|||Ernesto,
FK references are foreign key references to a primary key on the table you
are truncating.
sp_repladdcolumn can be used to add another column to the table and
subsequently have it replicated (sp_repldropcolumn for the converse).
HTH,
Paul Ibison
Subscribe to:
Posts (Atom)