Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
IronTiger
Starting Member
4 Posts |
 Posted - 2009-04-02 : 09:31:33
|
| I am planning to use Replication to populate my staging database for the dw I'm building. My questions are as follows.1) Are there any articles on the best steps to use transactional replication to populate and maintain a dw? Especially one that the source database doesn't keep last update timestamps.2) In the target staging database I only want to have the transactions that I have not applied to the dw yet. This is important since I don't have a way to tell what was updated since the last load to the dw.3) Is it possible to have the log reader dump the transactions into a database table?4) If I can't capture the transaction information into a database, how can I update additional attributes I'll need to add to the staging database, to show the transactional timestamp. |
|
|
zzzbla
Starting Member
13 Posts |
Posted - 2009-04-03 : 07:03:10
|
| Hi,The easy solution is to create the schema on the subscriber (staging area) yourself - the same as the schema on the publisher + a rowversion (timestamp) column. The data in the column is changed regardless of the DML (you actually can't change the data in it with DML). You can then use a control table that you use to store the latest rowversion handled, and query the tables for all rows with higher rowversions. Make sure that you handle both inserts and updates (each update to a row changes the rowversion as well).In SQL 2008 you can use one of two new features:1. Change Data Capture (CDC) which is practically based on transactional replication and does exactly what you want - writes the transactions into a table. (only available in the Enterprise Edition)2. Change Tracking (available in all editions) - a more lightweight mechanism that lets you query which rows were touched (by PK values).Hope that helps,S. Neumann |
 |
|
|
|
|
|
Transactional Replication to a DW stage