| Author |
Topic |
|
rubble
Starting Member
24 Posts |
 Posted - 2007-05-11 : 11:38:20
|
| We are implementing 2005 transaction replication on source database to target staging subscring database but we want to keep all transaction changes from source within staging subscribing tables.If source column gets updated we want to keep old record and new updated record in staging subscriber. Transaction replication synchronizes but does not keep history on subscriber. Do we update stored proc's anyone have examples of code or ideas?? |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-11 : 12:28:42
|
| Replication by itself does not maintain any history of changes. I have not done this but you can try modifying the procs replication uses sp_MSins<tablename, sp_upd<tableName etc to insert/update into your audit tables. I am thinking it might work except it might slow down the replication.Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 13:51:30
|
| I was thinking the same thing but you have to snapshot first then modify publications and then modify subscribing table and drop PK. I am wondering if someone has better idea, code examples, etc. How do other DW processes work when they need to capture all transactions from source and migrate those to DW or DW Mart????? |
|
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 13:51:38
|
| I was thinking the same thing but you have to snapshot first then modify publications and then modify subscribing table and drop PK. I am wondering if someone has better idea, code examples, etc. How do other DW processes work when they need to capture all transactions from source and migrate those to DW or DW Mart????? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-11 : 13:57:12
|
quote:
Originally posted by rubble
I was thinking the same thing but you have to snapshot first then modify publications and then modify subscribing table and drop PK.
After you set up replication, go in and modify the procs at the subscriber. Nothing at the publisher level needs to be changed, If I understood you correctly.Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-11 : 13:59:19
|
| Why not just use triggers?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 14:31:49
|
quote:
Originally posted by tkizer
Why not just use triggers?Tara Kizerhttp://weblogs.sqlteam.com/tarad/
We would have to put triggers on every column of all sourced tables then and some of our tables are 20 to 50 columns.Do you have examples of code or other ideas?? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-11 : 14:45:26
|
| You don't put triggers on columns. You put triggers on tables.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 14:53:49
|
| I know that.. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-11 : 15:03:23
|
Then what does this mean:quote:
We would have to put triggers on every column of all sourced tables
Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 15:12:41
|
| So, tara you recommend using triggers on every table and witin each table have a trigger for inserts, updates, and deletes. The Update trigger will capture data using from deleted to capture before update and from inserted to capture after update (new row)?? like capturing history?? Issue is we may need to synch and re-synch data that I have not thought about yet. Thanks for your help though and letting me know about triggers are at the table level not column level. |
|
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 15:12:45
|
| So, tara you recommend using triggers on every table and witin each table have a trigger for inserts, updates, and deletes. The Update trigger will capture data using from deleted to capture before update and from inserted to capture after update (new row)?? like capturing history?? Issue is we may need to synch and re-synch data that I have not thought about yet. Thanks for your help though and letting me know about triggers are at the table level not column level. |
|
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 15:12:49
|
| So, tara you recommend using triggers on every table and witin each table have a trigger for inserts, updates, and deletes. The Update trigger will capture data using from deleted to capture before update and from inserted to capture after update (new row)?? like capturing history?? Issue is we may need to synch and re-synch data that I have not thought about yet. Thanks for your help though and letting me know about triggers are at the table level not column level. |
|
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 15:12:53
|
| So, tara you recommend using triggers on every table and witin each table have a trigger for inserts, updates, and deletes. The Update trigger will capture data using from deleted to capture before update and from inserted to capture after update (new row)?? like capturing history?? Issue is we may need to synch and re-synch data that I have not thought about yet. Thanks for your help though and letting me know about triggers are at the table level not column level. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-11 : 15:24:41
|
Triggers are what people use for auditting data, which is what it sounds like you want to do based upon this:quote:
to keep old record and new updated record in staging subscriber
I'm pretty sure Brett (x002548) has a blog entry that will generate the trigger code for you for all tables. Check out his blog (brettk).Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 15:25:10
|
| I thought replication what ever type of replication captures all changes from source to the distributor or subscriber or somewhere that stores all transaction changes even multiple changes to the same record over and over again. You can go after these changes where ever to extract. Any ideas from anyone?? |
|
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 15:27:48
|
Where do I go to check out his blog?quote:
Originally posted by tkizer
Triggers are what people use for auditting data, which is what it sounds like you want to do based upon this:quote:
to keep old record and new updated record in staging subscriber
I'm pretty sure Brett (x002548) has a blog entry that will generate the trigger code for you for all tables. Check out his blog (brettk).Tara Kizerhttp://weblogs.sqlteam.com/tarad/
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 15:34:22
|
| Thank you |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-11 : 15:43:55
|
quote:
Originally posted by rubble
I thought replication what ever type of replication captures all changes from source to the distributor or subscriber or somewhere that stores all transaction changes even multiple changes to the same record over and over again. You can go after these changes where ever to extract. Any ideas from anyone??
Ntt quite right. Any transaction (whether insert/update/delete) on the publisher is executed as is on the subscriber. There's no "checking" done to see what the previous value was. So there is no history maintained anywhere, that you can go after. Either you modify the replication procs to insert into your audit tables along with the actual tables or use Triggers as Tara suggested. Understand the performance impact of Triggers first, if that is even an issue.Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
rubble
Starting Member
24 Posts |
Posted - 2007-05-11 : 15:48:39
|
| That is what I am concerned with performance on the source OLTP system. |
|
|
|
Next Page
|
SQL Server 2005 Transaction replication