October 13, 2010 at 7:50 am
Hi Guys,
When I tried implementing the CDC and Configured Transctional Replication on the same database.. the CDC at the database level looks good/unchanged ..but CDC at the table level is disabling/tweeking,replication is changing the metadata tables of CDC..
So, Could you plzz suggest me on thiss……How to implement CDC and Transctional replication on the same database
Thanks
Anil Inampudi
October 13, 2010 at 8:04 am
CDC uses the transactional replication log reader agent to harvest changes from the transaction log. Could this be the reason why you are seeing this problem? I have never implemented CDC on a server that is being replicated.
-Roy
October 13, 2010 at 10:56 pm
Hi Roy,
Thanks for your answer,
But based on the requirement,we are replicating the data from sql2005 to sql 2008.. while the replication is going on.. we tried implemeting the cdc above the replication so that the CDC will track the changes(i.e. when we try to perform any DML operations the Sql2005 server that data will be replicated to sql2008 server.. and it will be automatically tracked by the cdc.. based on the cdc inbuild metadata tables and funcation we are tying to load that data into our Dataware housing to preserve the historical changes...)
Is this way is Correct or Can u suggest any gud mechanism to implement this.........(or) when we first create CDC then replication then only the problem occurs..if i Create replication and then CDC.. it will work fine.......
awaiting for your reply... Thanks in advance...
Thanks
Anil Inampudi
December 28, 2010 at 9:29 pm
Yes I have done something similar. Have a SQL 2005 database replicating with transactional replication to SQL 2008 R2 database. I have CDC enabled on the destination tables. Biggest issue discovered is that there is no redundancy in the CDC tables. We do not run the snapshot agent often, due to space issues. If there is a issue with replication and we have to reinitialise then you will lose your changes. The replicated tables are marked as being CDC enabled, when you reinitialise you lose this and the CDC checking. If you enable CDC on the tables too soon you will have a lot (depending on amount of data) of changes in the CDC tables. You will not lose any data, but will lose any changes. Depending on how important these are is something for you to determine. Best bet is to get your source onto SQL Server 2008 and enable CDC. Then replicate the both the data and CDC tables. Have not tried it with merge replication.
But in the meantime/nutshell:
1. Set up replication 2K5 --> 2K8
2. Enable CDC on selected tables/columns (2K8)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply