Archiving very slow, help needed please

Hey guys,
The vendor gave us an archiving tool for our huge tables. We gave them a
production copy of our tables. They tested their tool against their own
server and told us that it takes 2 seconds to insert each record. I know
this is already bad. But today, I tested it against our own test server.
They also gave us instruction on what to do first before running the
archiving tool. Well anyways, after running the archiving tool, it took 20
seconds to insert 1 record. That's totally bad!
I would like to know if you will be able to help me guys identify the
problem by just looking at this links.
http://restricted.dyndns.org/executionplan.txt
http://restricted.dyndns.org/execplan1.gif
http://restricted.dyndns.org/execplan2.gif
http://restricted.dyndns.org/execplan3.gif
The INSERT statement that you will see there consumed 10 seconds of CPU and
30 seconds of Duration.
Are there any other statements that I can execute against my captured
profiler table that can us troubleshoot?
Any help will be greatly appreciated.
Thanks.
V1rt>> told us that it takes 2 seconds to insert each record
well....
Go back to the vendor (I assume you haven't paid them yet) and tell them
this isn't acceptable.
How many records do you have to deal with - even at 2 secs per record?
"Neil" wrote:
> Hey guys,
> The vendor gave us an archiving tool for our huge tables. We gave them a
> production copy of our tables. They tested their tool against their own
> server and told us that it takes 2 seconds to insert each record. I know
> this is already bad. But today, I tested it against our own test server.
> They also gave us instruction on what to do first before running the
> archiving tool. Well anyways, after running the archiving tool, it took 20
> seconds to insert 1 record. That's totally bad!
> I would like to know if you will be able to help me guys identify the
> problem by just looking at this links.
> http://restricted.dyndns.org/executionplan.txt
> http://restricted.dyndns.org/execplan1.gif
> http://restricted.dyndns.org/execplan2.gif
> http://restricted.dyndns.org/execplan3.gif
> The INSERT statement that you will see there consumed 10 seconds of CPU and
> 30 seconds of Duration.
> Are there any other statements that I can execute against my captured
> profiler table that can us troubleshoot?
> Any help will be greatly appreciated.
> Thanks.
> V1rt
>
>|||It looks like you are filtering the source table, Enclosure. Nothing wrong
with that. However, then you are joining this to the destination table.
Why?
An archive table usually doesn't contain the data in it yet. Are you
joining to make sure that you don't attempt to archive data that's already
been copied?
If so, there are better ways to write this. If you are INSERTing into a
table, not an update, there is rarely a need to join the destination to the
source. This will only slow you down because an INSERT will usually involve
updating the joined columns.
Try something more like this:
BEGIN TRANSACTION
INSERT INTO TableDestination
(Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM TableSource
WHERE TableSource.ColumnX = ExpressionX
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM s
FROM TableDestination AS d
INNER JOIN TableSource AS s
ON d.Key1 = s.Key1
AND d.Key2 = s.Key2
AND...
AND d.KeyN = s.KeyN
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
> >> told us that it takes 2 seconds to insert each record
> well....
> Go back to the vendor (I assume you haven't paid them yet) and tell them
> this isn't acceptable.
> How many records do you have to deal with - even at 2 secs per record?
>
> "Neil" wrote:
> > Hey guys,
> >
> > The vendor gave us an archiving tool for our huge tables. We gave them a
> > production copy of our tables. They tested their tool against their own
> > server and told us that it takes 2 seconds to insert each record. I know
> > this is already bad. But today, I tested it against our own test server.
> > They also gave us instruction on what to do first before running the
> > archiving tool. Well anyways, after running the archiving tool, it took
20
> > seconds to insert 1 record. That's totally bad!
> >
> > I would like to know if you will be able to help me guys identify the
> > problem by just looking at this links.
> >
> > http://restricted.dyndns.org/executionplan.txt
> > http://restricted.dyndns.org/execplan1.gif
> > http://restricted.dyndns.org/execplan2.gif
> > http://restricted.dyndns.org/execplan3.gif
> >
> > The INSERT statement that you will see there consumed 10 seconds of CPU
and
> > 30 seconds of Duration.
> >
> > Are there any other statements that I can execute against my captured
> > profiler table that can us troubleshoot?
> >
> > Any help will be greatly appreciated.
> >
> > Thanks.
> >
> > V1rt
> >
> >
> >|||Hi Anthony,
Thanks for the awesome reply. Am I correct that the destination table that
is being joint is the tablefieldaudit? That's what I saw in the INSERT
statement.
Below is what I captured using Profiler. I saw tons of this running for 20+
seconds each. :(
INSERT INTO
TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,KEYVALUE,USERID,SUBKEY1,
SUBKEY2)
SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,E.RECORDID AS
KEYVALUE,USERID,SUBKEY1, SUBKEY2
FROM TABLEFIELDAUDIT
INNER JOIN ENCLOSURE E
ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND
TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER
WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'
Thanks again,
Neil
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OLmPhvtyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> It looks like you are filtering the source table, Enclosure. Nothing
> wrong
> with that. However, then you are joining this to the destination table.
> Why?
> An archive table usually doesn't contain the data in it yet. Are you
> joining to make sure that you don't attempt to archive data that's already
> been copied?
> If so, there are better ways to write this. If you are INSERTing into a
> table, not an update, there is rarely a need to join the destination to
> the
> source. This will only slow you down because an INSERT will usually
> involve
> updating the joined columns.
> Try something more like this:
> BEGIN TRANSACTION
> INSERT INTO TableDestination
> (Column1, Column2, ..., ColumnN)
> SELECT Column1, Column2, ..., ColumnN
> FROM TableSource
> WHERE TableSource.ColumnX = ExpressionX
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM s
> FROM TableDestination AS d
> INNER JOIN TableSource AS s
> ON d.Key1 = s.Key1
> AND d.Key2 = s.Key2
> AND...
> AND d.KeyN = s.KeyN
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> Sincerely,
>
> Anthony Thomas
>
> --
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
>> >> told us that it takes 2 seconds to insert each record
>> well....
>> Go back to the vendor (I assume you haven't paid them yet) and tell them
>> this isn't acceptable.
>> How many records do you have to deal with - even at 2 secs per record?
>>
>> "Neil" wrote:
>> > Hey guys,
>> >
>> > The vendor gave us an archiving tool for our huge tables. We gave them
>> > a
>> > production copy of our tables. They tested their tool against their own
>> > server and told us that it takes 2 seconds to insert each record. I
>> > know
>> > this is already bad. But today, I tested it against our own test
>> > server.
>> > They also gave us instruction on what to do first before running the
>> > archiving tool. Well anyways, after running the archiving tool, it took
> 20
>> > seconds to insert 1 record. That's totally bad!
>> >
>> > I would like to know if you will be able to help me guys identify the
>> > problem by just looking at this links.
>> >
>> > http://restricted.dyndns.org/executionplan.txt
>> > http://restricted.dyndns.org/execplan1.gif
>> > http://restricted.dyndns.org/execplan2.gif
>> > http://restricted.dyndns.org/execplan3.gif
>> >
>> > The INSERT statement that you will see there consumed 10 seconds of CPU
> and
>> > 30 seconds of Duration.
>> >
>> > Are there any other statements that I can execute against my captured
>> > profiler table that can us troubleshoot?
>> >
>> > Any help will be greatly appreciated.
>> >
>> > Thanks.
>> >
>> > V1rt
>> >
>> >
>> >
>

Archiving very slow, help needed please

Hey guys,
The vendor gave us an archiving tool for our huge tables. We gave them a
production copy of our tables. They tested their tool against their own
server and told us that it takes 2 seconds to insert each record. I know
this is already bad. But today, I tested it against our own test server.
They also gave us instruction on what to do first before running the
archiving tool. Well anyways, after running the archiving tool, it took 20
seconds to insert 1 record. That's totally bad!
I would like to know if you will be able to help me guys identify the
problem by just looking at this links.
http://restricted.dyndns.org/executionplan.txt
http://restricted.dyndns.org/execplan1.gif
http://restricted.dyndns.org/execplan2.gif
http://restricted.dyndns.org/execplan3.gif
The INSERT statement that you will see there consumed 10 seconds of CPU and
30 seconds of Duration.
Are there any other statements that I can execute against my captured
profiler table that can us troubleshoot?
Any help will be greatly appreciated.
Thanks.
V1rt
>> told us that it takes 2 seconds to insert each record
well....
Go back to the vendor (I assume you haven't paid them yet) and tell them
this isn't acceptable.
How many records do you have to deal with - even at 2 secs per record?
"Neil" wrote:

> Hey guys,
> The vendor gave us an archiving tool for our huge tables. We gave them a
> production copy of our tables. They tested their tool against their own
> server and told us that it takes 2 seconds to insert each record. I know
> this is already bad. But today, I tested it against our own test server.
> They also gave us instruction on what to do first before running the
> archiving tool. Well anyways, after running the archiving tool, it took 20
> seconds to insert 1 record. That's totally bad!
> I would like to know if you will be able to help me guys identify the
> problem by just looking at this links.
> http://restricted.dyndns.org/executionplan.txt
> http://restricted.dyndns.org/execplan1.gif
> http://restricted.dyndns.org/execplan2.gif
> http://restricted.dyndns.org/execplan3.gif
> The INSERT statement that you will see there consumed 10 seconds of CPU and
> 30 seconds of Duration.
> Are there any other statements that I can execute against my captured
> profiler table that can us troubleshoot?
> Any help will be greatly appreciated.
> Thanks.
> V1rt
>
>
|||It looks like you are filtering the source table, Enclosure. Nothing wrong
with that. However, then you are joining this to the destination table.
Why?
An archive table usually doesn't contain the data in it yet. Are you
joining to make sure that you don't attempt to archive data that's already
been copied?
If so, there are better ways to write this. If you are INSERTing into a
table, not an update, there is rarely a need to join the destination to the
source. This will only slow you down because an INSERT will usually involve
updating the joined columns.
Try something more like this:
BEGIN TRANSACTION
INSERT INTO TableDestination
(Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM TableSource
WHERE TableSource.ColumnX = ExpressionX
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM s
FROM TableDestination AS d
INNER JOIN TableSource AS s
ON d.Key1 = s.Key1
AND d.Key2 = s.Key2
AND...
AND d.KeyN = s.KeyN
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Sincerely,
Anthony Thomas

"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...[vbcol=seagreen]
> well....
> Go back to the vendor (I assume you haven't paid them yet) and tell them
> this isn't acceptable.
> How many records do you have to deal with - even at 2 secs per record?
>
> "Neil" wrote:
20[vbcol=seagreen]
and[vbcol=seagreen]
|||Hi Anthony,
Thanks for the awesome reply. Am I correct that the destination table that
is being joint is the tablefieldaudit? That's what I saw in the INSERT
statement.
Below is what I captured using Profiler. I saw tons of this running for 20+
seconds each.
INSERT INTO
TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIELDVALUE,CHA NGEDATE,KEYVALUE,USERID,SUBKEY1,
SUBKEY2)
SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,E.RECORD ID AS
KEYVALUE,USERID,SUBKEY1, SUBKEY2
FROM TABLEFIELDAUDIT
INNER JOIN ENCLOSURE E
ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND
TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER
WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'
Thanks again,
Neil
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OLmPhvtyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> It looks like you are filtering the source table, Enclosure. Nothing
> wrong
> with that. However, then you are joining this to the destination table.
> Why?
> An archive table usually doesn't contain the data in it yet. Are you
> joining to make sure that you don't attempt to archive data that's already
> been copied?
> If so, there are better ways to write this. If you are INSERTing into a
> table, not an update, there is rarely a need to join the destination to
> the
> source. This will only slow you down because an INSERT will usually
> involve
> updating the joined columns.
> Try something more like this:
> BEGIN TRANSACTION
> INSERT INTO TableDestination
> (Column1, Column2, ..., ColumnN)
> SELECT Column1, Column2, ..., ColumnN
> FROM TableSource
> WHERE TableSource.ColumnX = ExpressionX
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM s
> FROM TableDestination AS d
> INNER JOIN TableSource AS s
> ON d.Key1 = s.Key1
> AND d.Key2 = s.Key2
> AND...
> AND d.KeyN = s.KeyN
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> Sincerely,
>
> Anthony Thomas
>
> --
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
> 20
> and
>

Archiving very slow, help needed please

Hey guys,
The vendor gave us an archiving tool for our huge tables. We gave them a
production copy of our tables. They tested their tool against their own
server and told us that it takes 2 seconds to insert each record. I know
this is already bad. But today, I tested it against our own test server.
They also gave us instruction on what to do first before running the
archiving tool. Well anyways, after running the archiving tool, it took 20
seconds to insert 1 record. That's totally bad!
I would like to know if you will be able to help me guys identify the
problem by just looking at this links.
http://restricted.dyndns.org/executionplan.txt
http://restricted.dyndns.org/execplan1.gif
http://restricted.dyndns.org/execplan2.gif
http://restricted.dyndns.org/execplan3.gif
The INSERT statement that you will see there consumed 10 seconds of CPU and
30 seconds of Duration.
Are there any other statements that I can execute against my captured
profiler table that can us troubleshoot?
Any help will be greatly appreciated.
Thanks.
V1rt>> told us that it takes 2 seconds to insert each record
well....
Go back to the vendor (I assume you haven't paid them yet) and tell them
this isn't acceptable.
How many records do you have to deal with - even at 2 secs per record?
"Neil" wrote:

> Hey guys,
> The vendor gave us an archiving tool for our huge tables. We gave them a
> production copy of our tables. They tested their tool against their own
> server and told us that it takes 2 seconds to insert each record. I know
> this is already bad. But today, I tested it against our own test server.
> They also gave us instruction on what to do first before running the
> archiving tool. Well anyways, after running the archiving tool, it took 20
> seconds to insert 1 record. That's totally bad!
> I would like to know if you will be able to help me guys identify the
> problem by just looking at this links.
> http://restricted.dyndns.org/executionplan.txt
> http://restricted.dyndns.org/execplan1.gif
> http://restricted.dyndns.org/execplan2.gif
> http://restricted.dyndns.org/execplan3.gif
> The INSERT statement that you will see there consumed 10 seconds of CPU an
d
> 30 seconds of Duration.
> Are there any other statements that I can execute against my captured
> profiler table that can us troubleshoot?
> Any help will be greatly appreciated.
> Thanks.
> V1rt
>
>|||It looks like you are filtering the source table, Enclosure. Nothing wrong
with that. However, then you are joining this to the destination table.
Why?
An archive table usually doesn't contain the data in it yet. Are you
joining to make sure that you don't attempt to archive data that's already
been copied?
If so, there are better ways to write this. If you are INSERTing into a
table, not an update, there is rarely a need to join the destination to the
source. This will only slow you down because an INSERT will usually involve
updating the joined columns.
Try something more like this:
BEGIN TRANSACTION
INSERT INTO TableDestination
(Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM TableSource
WHERE TableSource.ColumnX = ExpressionX
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM s
FROM TableDestination AS d
INNER JOIN TableSource AS s
ON d.Key1 = s.Key1
AND d.Key2 = s.Key2
AND...
AND d.KeyN = s.KeyN
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...[vbcol=seagreen]
> well....
> Go back to the vendor (I assume you haven't paid them yet) and tell them
> this isn't acceptable.
> How many records do you have to deal with - even at 2 secs per record?
>
> "Neil" wrote:
>
20[vbcol=seagreen]
and[vbcol=seagreen]|||Hi Anthony,
Thanks for the awesome reply. Am I correct that the destination table that
is being joint is the tablefieldaudit? That's what I saw in the INSERT
statement.
Below is what I captured using Profiler. I saw tons of this running for 20+
seconds each.
INSERT INTO
TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIEL
DVALUE,CHANGEDATE,KEYVALUE,USERID,SU
BKEY1,
SUBKEY2)
SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDAT
E,E.RECORDID AS
KEYVALUE,USERID,SUBKEY1, SUBKEY2
FROM TABLEFIELDAUDIT
INNER JOIN ENCLOSURE E
ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND
TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER
WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'
Thanks again,
Neil
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OLmPhvtyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> It looks like you are filtering the source table, Enclosure. Nothing
> wrong
> with that. However, then you are joining this to the destination table.
> Why?
> An archive table usually doesn't contain the data in it yet. Are you
> joining to make sure that you don't attempt to archive data that's already
> been copied?
> If so, there are better ways to write this. If you are INSERTing into a
> table, not an update, there is rarely a need to join the destination to
> the
> source. This will only slow you down because an INSERT will usually
> involve
> updating the joined columns.
> Try something more like this:
> BEGIN TRANSACTION
> INSERT INTO TableDestination
> (Column1, Column2, ..., ColumnN)
> SELECT Column1, Column2, ..., ColumnN
> FROM TableSource
> WHERE TableSource.ColumnX = ExpressionX
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM s
> FROM TableDestination AS d
> INNER JOIN TableSource AS s
> ON d.Key1 = s.Key1
> AND d.Key2 = s.Key2
> AND...
> AND d.KeyN = s.KeyN
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> Sincerely,
>
> Anthony Thomas
>
> --
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
> 20
> and
>

archiving tables

We have some fast growing tables in our SQL Server 2000 db
which content 13 million records already. So, we going to
periodically archive them base on datetime field and allow
user to retrieve archive data via our application by
checking corresponding checkbox. The problem is that, we
do not know what to do with FK's. How to re-create FK's
after archiving to restore referal integrity.
Is there some standard solution?
Any comments will be appreciated.
Merry chistmas and happy new year to everybody.Vitalik
http://vyaskn.tripod.com/sql_archive_data.htm
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:08ee01c3c964$5faa1340$a401280a@.phx.gbl...
> We have some fast growing tables in our SQL Server 2000 db
> which content 13 million records already. So, we going to
> periodically archive them base on datetime field and allow
> user to retrieve archive data via our application by
> checking corresponding checkbox. The problem is that, we
> do not know what to do with FK's. How to re-create FK's
> after archiving to restore referal integrity.
> Is there some standard solution?
> Any comments will be appreciated.
> Merry chistmas and happy new year to everybody.

Archiving data to a seperate database.

Hi all,
Would appreciate some thoughts on the best way to do this scenario.
This is not a large database in the number of tables, but has about 100,000
new rows per day. Data is inserted only, and no queries / SPs are being run
against the data.
Database 1 (transactional database)
Holds all current data, and all data up to 100 days old.
Database 2 (historic database)
Holds all data and is max 15 mins older than Database 1.
So, what would be the best way to keep the historic database current?
Replication, triggers, agent running SPs, keeping it within 15mins sync to
the transactional database, and deleting records older than 100 days from th
e
transactional database...
Would appreciate anyones thoughts on this...
-MarkFor 100000 rows, in general, one could write a simple job & schedule it to
run every day. The job can have a script that copies the historical data to
the destination & purges it from the source.
Anith|||My choices...
Part 1: Removing from Active db.
Use a nightly job to delete all records over 100 days (You do have a Date
Added Column? Is it indexed?)
Part 2: Moving new data to 'Historic' db
Trigger that copies the INSERTED table to the 'Historic' db.
For this application, a trigger would be have the same or less
operational impact, and the maintenance would be significantly
less. This assumes, of course, that security and connection
issues allow the use of a Trigger.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"MarkieMark" <u20627@.uwe> wrote in message news:5e6ed66fd1456@.uwe...
> Hi all,
> Would appreciate some thoughts on the best way to do this scenario.
> This is not a large database in the number of tables, but has about
> 100,000
> new rows per day. Data is inserted only, and no queries / SPs are being
> run
> against the data.
> Database 1 (transactional database)
> Holds all current data, and all data up to 100 days old.
> Database 2 (historic database)
> Holds all data and is max 15 mins older than Database 1.
> So, what would be the best way to keep the historic database current?
> Replication, triggers, agent running SPs, keeping it within 15mins sync to
> the transactional database, and deleting records older than 100 days from
> the
> transactional database...
> Would appreciate anyones thoughts on this...
> -Mark|||Arnie,
My thoughts were with triggers to. The only issue I had was if the historic
database went off-line for some reason etc., that the databases would get ou
t
of sync. Haven't been able to get my head round that little problem yet.
-Marksql

Archiving data

You could possibly use transactional replication for this.
All the tables would be in the same publication and
relationships may also be registered there. Foreign keys
would be marked as Yes (Not for Replication). You'd have
to ensure that deletes are not replicated (using None for
the replication command).
Initially I mentioned 'possibly' - this is because there
may be some deletes that you really want to be replicated -
it all depends on how your system is set up. EG on one
database I worked on, there were no deletes allowed -
updates of a bit flag would effectively remove the record
from the user's vision. If your system is like this then
transactional would be ok. If not, then DTS, or snapshot
replication to append records could be used before the
deletes take place.
HTH,
Paul Ibison
I agree with Paul. I would incorporate the business logic for deletes in
custom stored procedures.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:17eb01c46f48$46da5a20$a301280a@.phx.gbl...
> You could possibly use transactional replication for this.
> All the tables would be in the same publication and
> relationships may also be registered there. Foreign keys
> would be marked as Yes (Not for Replication). You'd have
> to ensure that deletes are not replicated (using None for
> the replication command).
> Initially I mentioned 'possibly' - this is because there
> may be some deletes that you really want to be replicated -
> it all depends on how your system is set up. EG on one
> database I worked on, there were no deletes allowed -
> updates of a bit flag would effectively remove the record
> from the user's vision. If your system is like this then
> transactional would be ok. If not, then DTS, or snapshot
> replication to append records could be used before the
> deletes take place.
> HTH,
> Paul Ibison

Archiving and Pruning growing transaction tables

Hi,

I am using SQL Server 2005.

I would like to know best approaches for archiving and pruning couple of growing transaction tables in a database.

Possible approaches which I could think of
1) Take a backup of the database and delete records from tables based on date. (Issues - Deletion from existing tables takes a long time and the Indexes are disturbed requiring reindexing.)

2) Partition the table based on Date and possibly backup only the older partitions and remove them. (not sure if this can be done seemlessly).

Please let me know your thoughts.

Thanks,
Loonysan

I would suggest you look into partitioning, as you can drop a complete partition instead of doing a lot of deletes.

I worked on a system before where we had to delete a lot of records on date, and we had performance issues when we had to delete a lot of rows. We converted the system to partitioning, and could drop a partition at a time, which solved the performance problem.

Thanks,

Marcel van der Holst
[MSFT]

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVR
Why not just back up the database itself?
One thing you could do is put the tables w/ binary data on their own
filegroup -- then just back up that filegroup more regularly -- if that's
what you need?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:eMjAtpdKFHA.3552@.TK2MSFTNGP12.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVRVyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Narayanan,
I saw your Link, it looks good but i think it may or may not work if the
orders for 6 months is more than 100,000.
What happens if the data is more than 100,000 as we have in a real-time
system.
my solution was (lets take 6 months period - 100,000 records)
If you have to leave 10,000 on live production database and remove the rest
to history database.
This is how I did and its working great.
1. Check the record count of the database based on date.
2. Use Chunking method - means copy 1000-5000 every time depending on the
load from the live database to temp archive database on the live server then
call another str proc to copy data from temp archive to remote archive
database server.
(reason being if network goes down btw live server/remote server [reason
unknown] you can still have data in temp not lost and can come back redo the
data.)
3. once copied to remote archive server, now come back delete that data from
the live database by "delete from live where seqno in temp archive database"
and later delete from temp archive. (seqno is a primary key field)
I go beyond this, once all the data has been copied, i check for the first
and last copied to archive server exists meaning it has successfully copied.
I wrote a program to do this, the program just calls 3 stored procedures and
str proc does all the above function.
Too much isn't it.
Arun
"Narayana Vyas Kondreddi" wrote:
> Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
> Vyas has a great article on his web site about archiving.
> http://vyaskn.tripod.com/
>
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> > What's the best way to archive tables with binary data? Flat
> files/bcp/DTS
> > may not work if need to import binary data(not a fixed length column) with
> > quotes etc. What I am thinking is to export to another database and back
> it
> > up/archive on a regular basis. That way it will be easy to restore when
> > needed. Any other options?
> >
> > Thanks
> > BVR
> >
> >
>
>

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVR
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>
|||Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>
|||Narayanan,
I saw your Link, it looks good but i think it may or may not work if the
orders for 6 months is more than 100,000.
What happens if the data is more than 100,000 as we have in a real-time
system.
my solution was (lets take 6 months period - 100,000 records)
If you have to leave 10,000 on live production database and remove the rest
to history database.
This is how I did and its working great.
1. Check the record count of the database based on date.
2. Use Chunking method - means copy 1000-5000 every time depending on the
load from the live database to temp archive database on the live server then
call another str proc to copy data from temp archive to remote archive
database server.
(reason being if network goes down btw live server/remote server [reason
unknown] you can still have data in temp not lost and can come back redo the
data.)
3. once copied to remote archive server, now come back delete that data from
the live database by "delete from live where seqno in temp archive database"
and later delete from temp archive. (seqno is a primary key field)
I go beyond this, once all the data has been copied, i check for the first
and last copied to archive server exists meaning it has successfully copied.
I wrote a program to do this, the program just calls 3 stored procedures and
str proc does all the above function.
Too much isn't it.
Arun
"Narayana Vyas Kondreddi" wrote:

> Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
> Vyas has a great article on his web site about archiving.
> http://vyaskn.tripod.com/
>
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> files/bcp/DTS
> it
>
>

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVRWhy not just back up the database itself?
One thing you could do is put the tables w/ binary data on their own
filegroup -- then just back up that filegroup more regularly -- if that's
what you need?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:eMjAtpdKFHA.3552@.TK2MSFTNGP12.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>sql

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVRVyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Narayanan,
I saw your Link, it looks good but i think it may or may not work if the
orders for 6 months is more than 100,000.
What happens if the data is more than 100,000 as we have in a real-time
system.
my solution was (lets take 6 months period - 100,000 records)
If you have to leave 10,000 on live production database and remove the rest
to history database.
This is how I did and its working great.
1. Check the record count of the database based on date.
2. Use Chunking method - means copy 1000-5000 every time depending on the
load from the live database to temp archive database on the live server then
call another str proc to copy data from temp archive to remote archive
database server.
(reason being if network goes down btw live server/remote server [reason
unknown] you can still have data in temp not lost and can come back redo the
data.)
3. once copied to remote archive server, now come back delete that data from
the live database by "delete from live where seqno in temp archive database"
and later delete from temp archive. (seqno is a primary key field)
I go beyond this, once all the data has been copied, i check for the first
and last copied to archive server exists meaning it has successfully copied.
I wrote a program to do this, the program just calls 3 stored procedures and
str proc does all the above function.
Too much isn't it.
Arun
"Narayana Vyas Kondreddi" wrote:

> Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
> Vyas has a great article on his web site about archiving.
> http://vyaskn.tripod.com/
>
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> files/bcp/DTS
> it
>
>

Archive Strategy

In our Inventory Control database we have tables that grow and grow and grow
as the database gets used. Tables like StockResults, Deliveries, StockCount
etc.
I want to archive the data into archive tables, which I can later moved to
another database, or even another server. This way I'll have current tables
and archived tables (eg. Deliveries and Deliveries_Archive).
Here are some considerations:
1. We will need to report on both the current tables and the archived tables
.
2. data that is archived might need to be put back into its current table.
What I'm after is some advice on what strategy to use.
Is there a framework or design for doing this that would help me ?
Do you have an archive strategy that works for you ?
Do you have a clever naming convention that you use ?
Any good articles on this ?
Any help would appreciated.
Thanks,
CraigCheck this link.. Hope this helps.
http://www.dbazine.com/sql/sql-articles/charran12|||Why do you feel it is necessary to archive the data?|||To keep the transactional database's size down
We want to do this because
1. speeds up backups
2. if we have a problem and need to restore, that will be quicker
3. we often backup the db and download it to a testing server to test new
functionality -- the smaller db will download quicker

Archive data before deletion

Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
ChrisThe two-database approach is a good way of doing this. Instead of using the
Deleted flag, you can now issue actual delete statements to remove old
records, but not before you design an ON DELETE trigger to propagate the
deleted rows to the archive database.
So, yes - you do need all those queries... :) This is what being a database
designer is all about.
Lookup CREATE TRIGGER in Books Online. Designing these triggers is easy -
simply use the 'deleted' table.
Example:
insert archive_db.dbo.table1
(...columns...)
select ...columns...
from deleted
Don't forget to include appropriate error-handling, so no delete goes
unnoticed.
ML|||I have an example here: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<devccon@.gmx.de> wrote in message
news:1122542261.075469.237440@.f14g2000cwb.googlegroups.com...
Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
Chris|||Thanks to you all for the directions. I'm still struggling with the
trigger but it shouldn't be that of a problem.
I really hoped there would be some other way, but, hey anything that
does the job is good...
Thx again,
Chris
devccon@.gmx.de wrote:
> Hi all,
> how can I copy all dependant child records into duplicate tables before
> deleting them.
> The situation is that I have a master table "customer" with 20 other
> tables that depend on this master table.
> Foreign keys are all set up correctly and cascading delete is enabled.
> Now when a customer wants to cancel his subscription, I don't want to
> delete all referenced data immediatly without saving, because I need
> them for possible future references, like billing addr. etc.
> Currently I'm setting a "Deleted" flag so that in any query this
> customer doesn't show up.
> What would be the best approach to archive all dependent data before
> deleting the parent and the child records.
> I'm thinking of duplicate tables and/or a duplicate database.
> I've also played around with triggers but can't get the automatic
> insert of the child records working.
> Do I really have to do something like this for every child table?
> Insert into dupAddr (select * from addr where customerid = 1)
> Insert into dupTrx (select * from trx where customerid = 1)
> Insert into dupCustomer (select * from customer where customerid = 1)
> etc.
> I'm using SQL2000/W2K3.
> Any help would be appreciated.
> thx in advance,
> Chris

Architecture question grouping different kinds of stuff...

Hi all,

I'm hoping somebody here might be able to point me in the right
direction.

Basically, I'm looking for a "good" way to build tables such that
different kinds of stuff can grouped.

For example, let's say I have "rooms" and they can contain "people"
and/or "books" and/or "furniture" -- each of those objects (rooms,
people, books, furniture, etc) may have their own attributes -- but the
idea is that a room may contain 0 or more of each of the others.

I'll need to do lots of selects to retrieve the contents of specific
rooms.

Is there a standard / good way to go about this sort of thing?

Somehow giving people, books, furniture a room_ID (foreign key) seems a
bit clumbsy -- ie, do I have to run a select for each?

thanks kindly, -ScottFollowing up on my own question above, let's say I have these tables:

Rooms
.. . RoomID

People
.. . PersonID
.. . Name

Books
.. . BookID
.. . Title

Furniture
.. . FurnitureID
.. . FurnitureType

I could group stuff into rooms with something like:

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID

(where ContentType could be person, book, furniture, etc)

But it seems like if there were 20 different kinds of things, I'd have
to run a select for each.

I'm hoping this is a standard SQL architecture problem, but I lack the
vocabulary to know what to search for.

thanks, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> Following up on my own question above, let's say I have these tables:
> Rooms
> . . RoomID
> People
> . . PersonID
> . . Name
> Books
> . . BookID
> . . Title
> Furniture
> . . FurnitureID
> . . FurnitureType
> I could group stuff into rooms with something like:
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> (where ContentType could be person, book, furniture, etc)
> But it seems like if there were 20 different kinds of things, I'd have
> to run a select for each.

I would look into gathering all these contents into one supertable.
If then there are specicic attributes for person, books etc you can
can have sub tables for this.

This contents table would have a type field specifying the type of object.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland, thanks -- are you suggesting something like:

Rooms
.. . RoomID

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
.. . PersonName
.. . BookTitle
.. . FurnitureType

or even

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
.. . VariableText1
.. . VariableText2

(where 'VariableText' would store different kinds of stuff, depending
on the ContentType)

Since some of these objects may have a few attributes, such a
supertable could have lots of nulls -- is that "ok"?

Thanks again for your help, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> Hi Erland, thanks -- are you suggesting something like:
> Rooms
> . . RoomID
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> . . PersonName
> . . BookTitle
> . . FurnitureType
> or even
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> . . VariableText1
> . . VariableText2
> (where 'VariableText' would store different kinds of stuff, depending
> on the ContentType)
>
> Since some of these objects may have a few attributes, such a
> supertable could have lots of nulls -- is that "ok"?

Nah, rather I was thinking you would keep the existing tables, and
just add this RoomContents table. Probably you should move some columns
into this table, for instance a string that represents a name of some
sort. (That is "sofa" for furnitures.)

This would make it easier to write a simple query that lists all the
contents in the room. If you need furniture-specific information you would
go to that table.

In the end it may be a trade-off where you place things. If some attributes
are common to most contents, it may be better to have it as a nullable
column in the main table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The trouble is that each time I "load a room" I'll need at least a few
of the attributes of the things in that room.

So, if I understand correctly, I would keep all of those various
objects' attributes in one "supertable," and perhaps keep some
additional attributes (those not needed when the room loads) in the
extra object-specific tables.

Does that sound about right?

Thanks again, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> The trouble is that each time I "load a room" I'll need at least a few
> of the attributes of the things in that room.
> So, if I understand correctly, I would keep all of those various
> objects' attributes in one "supertable," and perhaps keep some
> additional attributes (those not needed when the room loads) in the
> extra object-specific tables.
> Does that sound about right?

Yes, that was my thought.

In an object-oriented language, this is a little easier since you would
have "Contents" a base class (possibly virtual) and then have Furtinure
etc to inherit from that group. In SQL you cannot do this very well.
You could have a view that unites all tables, and then have an INSTEAD
OF trigger on the view, so when you inserts into the view, things fall
down in their respective places, although I am not sure that this would
be worth the effort.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Architecture

Hello,
I want to kow if the following architecture is good :
Disque 1&2 ( Raid 1)
c: OS
d: sqlserver + system tables + log files
Disque 3&4&5 (Raid 5)
e: dataIf you don't plan on having any volume it would be fine. Otherwise, it would suck. A 3 disk RAID array is the slowest RAID 5 configuration possible. In addition, if you want high performance you should have the tempdb and/or the log files on seperate disks.

If you only have five disks though, this should work.

Architects.......

I have tables in a web site database which customers fill their
preferences to receive emails regarding some topics. New customers are
added everyday and existing customer update their profile. I have also
an internal system for internal employees. The table in the website and
the one in the internal system are similar but with minor differences
in their column name (just for general info...this is not my concern at
this time).I am trying to synch this two databases. I want to do an
automatic update from the web db to the internal database. After
importing the tables to the internal system I want to run a stored
procedure which adds/updates new in-house codes (unique to the internal
db) into the imported tables. Update will be done only for those
records which don't match the records in the internal system (i.e.
for those who are new).
Currently I am using a DTS to import data from the web site, T-Sql
query to do matching b/n the web db and add the internal codes to the
imported tables.
But now I want to do it automatically: I know i have a lot of options.
Jobs, DTS with stored procedures ... Can any one suggest a way how to
integrate my options and do it correctly.
Any suggestion would be appreciated
If I need to elaborate the question please email me.
Thanks.>Currently I am using a DTS to import data from the web site, T-Sql
>query to do matching b/n the web db and add the internal codes to the
>imported tables.
>But now I want to do it automatically: I know i have a lot of options.
>Jobs, DTS with stored procedures ... Can any one suggest a way how to
>integrate my options and do it correctly.
First put all the T-SQL into stored procedures. Then, since half the
work is already being done in DTS, it would seem simple enough to
expand the DTS to also execute the T-SQL procs.
Roy

approach for sharing data between servers

hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
--
atte,
HernánDoing cross-server joins can be quite slow. Consider using replication and
replicate the two Person tables to SQL01 and SQL02. Then, do the joins
locally.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
--
atte,
Hernán|||in that case
i will have a problem
with the (unique) ID of the table
how can i sincronize the ID ?
i think i will need a kind of semaphor
to lock a resource (table)
that holds the "NEXT-ID" ...
--
atte,
Hernán
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribió en el mensaje
news:O8vHpbfxGHA.4764@.TK2MSFTNGP02.phx.gbl...
| Doing cross-server joins can be quite slow. Consider using replication
and
| replicate the two Person tables to SQL01 and SQL02. Then, do the joins
| locally.
|
| --
| Tom
|
| ----
| Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
| SQL Server MVP
| Toronto, ON Canada
| .
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
| hi
| i have two "Person" tables
| one in server SQL01
| and the other in server SQL02
|
| i will integrate these two tables
| in a single "Person" table in other server SQL03
|
| then, i think
| all the "inner/left joins" to "Person" table
| won't work because "Person" table was moved
|
| how can i "join" to the "Person" table
| that is in other server '
|
| in other words, wich ways do i have
| for accessing a table that is not
| in the same server than the stored proc ?
| what is the efficience hit ?
|
| thanks
|
| --
| atte,
| Hernán
|
|

Applying the default collation sequence

How can I apply to the all database tables columns the default collation
sequence for the database ?Hi
If you have changed the database collation then you can use
http://tinyurl.com/lpj7k
John
"Alur" wrote:

> How can I apply to the all database tables columns the default collation
> sequence for the database ?|||Thank you very much indeed.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If you have changed the database collation then you can use
> http://tinyurl.com/lpj7k
> John
> "Alur" wrote:
>

Applying the default collation sequence

How can I apply to the all database tables columns the default collation
sequence for the database ?Hi
If you have changed the database collation then you can use
http://tinyurl.com/lpj7k
John
"Alur" wrote:
> How can I apply to the all database tables columns the default collation
> sequence for the database ?|||Thank you very much indeed.
"John Bell" wrote:
> Hi
> If you have changed the database collation then you can use
> http://tinyurl.com/lpj7k
> John
> "Alur" wrote:
> > How can I apply to the all database tables columns the default collation
> > sequence for the database ?