tracking Update, insert, delete during/after replication
seanD
Update AND delete in one " because I was thinking a trigger was the
way to go, but now I'm looking for a better idea from people with
experience in replication
I'm trying to create a table of tracking records for a replicated
table. Example: I have table myReplicatedTable (id int, name nvarchar
(50)) which is part of a subscription. (transactional replication)
After a replication transaction is completed which causes a record (1,
'name1') I would like to have a record put into another table
track_myReplicationTable(id, TransacttionType) like (1, 'I') I for
insert. same for deletes and updates.
My question is, Will a trigger perform ok on large sets of updates?
And is there an easier way using replication to achieve this goal?
Any and all ideas would be greatly appreciated.
Thanks
Sean
Hugo Kornelis
Hi Sean,
>My question is, Will a trigger perform ok on large sets of updates?
First question: I don't know if you refer to updates that affect large
amounts of rows, or to large amounts of (small scale) updates in a short
time, but a trigger will work fine either way.
In the first case, make sure the trigger handles multii-row updates. Do
not assume that only a single row will be updated (common error!) and if
you like performance, code ecerything set-based; resist the temptation
to loop over rows in the inserted and deleted pseudo-tables.
In the latter case, make sure that the trigger does only what is
required and make sure that all locks it needs can be row-level locks to
minimize blocking between parallel threads executing the trigger.
>And is there an easier way using replication to achieve this goal?
Let's first see if I understand you correctly. Your server is a
subscriber to modifications in myReplicatedTable, which is published by
some other server. The tracking table is not replicated, lives only on
your server (the subscriber) and needs to hold a log of changes in the
replicated table. Correct so far?
I *think* that using a trigger is the easiest solution here, though if
you are on SQL Server 2008, you should also look into the two new
features CDC (Change Data Capture) and CT (Change Tracking).
If you want to hear from the true replication experts, then I advice you
to move to microsoft.public.sqlserver.replication, as that is where the
true replication experts hang out.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
seanD
> First question: I don't know if you refer to updates that affect large
> amounts of rows, or to large amounts of (small scale) updates in a short
> time, but a trigger will work fine either way.
--- It will mostly be small updates but many of them
> In the latter case, make sure that the trigger does only what is
> required and make sure that all locks it needs can be row-level locks to
> minimize blocking between parallel threads executing the trigger.
I'm unsure of how to approach making sure they can be row level locks.
I didn't think that was something I could affect.
> Let's first see if I understand you correctly. Your server is a
> subscriber to modifications in myReplicatedTable, which is published by
> some other server. The tracking table is not replicated, lives only on
> your server (the subscriber) and needs to hold a log of changes in the
> replicated table. Correct so far?
Yes, correct.
> I *think* that using a trigger is the easiest solution here, though if
> you are on SQL Server 2008, you should also look into the two new
> features CDC (Change Data Capture) and CT (Change Tracking).
Good to hear. It certainly seems to be working so far.
> If you want to hear from the true replication experts, then I advice you
> to move to microsoft.public.sqlserver.replication, as that is where the
> true replication experts hang out.
-- Thanks much for the detailed post!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
>
> - Show quoted text -
Hugo Kornelis
(snip)
>> In the latter case, make sure that the trigger does only what is
>> required and make sure that all locks it needs can be row-level locks to
>> minimize blocking between parallel threads executing the trigger.
>
>I'm unsure of how to approach making sure they can be row level locks.
>I didn't think that was something I could affect.
Hi Sean,
If the WHERE clause contains all the columns in the table's clustered
index, you'll almost certainly get row-level locks only on the table. If
there are no indexes that include any of the updated columns, than
you'll almost certainly get no other locks.
In other cases, you can easily check. Start a transaction, query the
current locks (see
http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx),
perform the update, query the locks again, then commit or rollback the
transaction.