Hi,

Good article, great topic.

I agree with the use of change tracking in 2008, and would add that triggers are not always straight forward. You must consider referential integrity, cascading events , and the typically more complex object relationships in an enterprise system.

For simple structures, triggers can be used wisely - but watch them on any mass updates to your source as they can quickly become points of contention.

For more complex structures that may already have cascading triggers, or constraints - then things can get unruly fast and you may find your self spending more time coding than is worthy of the effort.

But still, options are our friends, and you've certainly presented some above with triggers and change tracking and RedGate

(also newer object level SQL replication services for that matter...)

One other I use on occasion (usually from a remote host via open datasource/openrowset) but basically the same on the local host, is a simple SQL statement (simple is my best friend - simple is fast, simple is stable - so whenever possible I say go simple).

If again, the table/object in question is fairly stand-alone and has good key values (basics required for a trigger copy as well usually), then another approach is to sync the copy from the source using straight TSQL.

Example:

Table1 has a key column named RecordID and a datetime column named EntryDate

Table2 is an exact duplicate structure of table1 (assumes initial bulk load).

-- declare a table variable

Declare @TABVAR Table(RecordID INT)

--Declare storage and set value for the latest record in your copy table

Declare @synctime datetime

set @synctime = Select max(entydate) from Table2

-- Fetch NEW IDs for processing (records in source - not yet in copy)

insert into @TABVAR

select RecordID from table1

where entrydate >= @synctime

--Delete records from your copy table, prepping for new inbound

--- Sometimes not required if source is insert only. But we'll assume some updates also occur...

delete from table2 where RecordID in (select RecordID from @TABVAR)

--Insert and Sync data

Insert into Table2

Select * from table1 where recordID in (select RecordID from @TABVAR)

Done

Run this on interval during your day and your copy is in sync.

May be similar to trigger code but is not bound to your source table and hence has no impact on application code or performance unless you are executing the task.

* Example may be lose, but it gives you the idea .

Deletes in the source are a consideration, but I suppose in any replication the first thing is a review of structures and limitations and finding the right fit.

And again, this is simple code that can also be run from a remote host without need for a link server or DSN. remote host selects just use OPENDATASOURCE/ROWSET calls...)

Enjoy and thanks for sharing your insights !