July 5, 2012 at 9:29 am
Hello everyone,
Not sure if anyone had to deal with this scenario like mine but hopefully someone can help.
Here is my situation..
We are planning to implement a process which will push real time data changes from our 3rd party vendor. The one option I’m interested in is enabling CDC at 3rd party site and replicate changes only to our site.
In this setup we basically get a copy of the database from vendor so we will have a baseline and from that point on, we will need to only load changes into our site.
I’ve tried CDC and it works great but with one caveat, It won’t allow me to replicate system tables in CDC schema (%%%%_CT) where all changes are saved.
Can someone please put me in the right direction? TIA
July 5, 2012 at 12:37 pm
Maybe I missed something, but why not just replicate the database and required tables instead of using CDC and then replicating? It seems to me like you are just adding processing that doesn't need to be there.
Jared
CE - Microsoft
July 5, 2012 at 1:12 pm
SQLKnowItAll (7/5/2012)
Maybe I missed something, but why not just replicate the database and required tables instead of using CDC and then replicating? It seems to me like you are just adding processing that doesn't need to be there.
Hi Jared,
Thanks for your reply. I actually considered just replicating data but we want our vendor to enable CDC and save changes so we can replicate the data and show those changes (history view) via website. We are are an insurance company and we do not have a warehouse setup yet..
Say for example.....
An insurance agent writes an incomplete policy and its a new record in a table and later he decides to complete. That's an update to the new record. We want to show 2 records for that policy if agent logs in to the website. Hope I'm clear.. Thanks
July 5, 2012 at 1:16 pm
Ok. Maybe you can achieve the same thing by replicating from the 3rd party and then use DML triggers on your database to insert into a log table. We are actually doing this in a similar way for a different purpose.
Jared
CE - Microsoft
July 5, 2012 at 1:35 pm
SQLKnowItAll (7/5/2012)
Ok. Maybe you can achieve the same thing by replicating from the 3rd party and then use DML triggers on your database to insert into a log table. We are actually doing this in a similar way for a different purpose.
Thanks. Just to clarify, you suggest creating DML triggers on the 3rd party database to insert logs in a table and replicate those log tables over to our database?
July 5, 2012 at 1:37 pm
SQL_Baltimore (7/5/2012)
SQLKnowItAll (7/5/2012)
Ok. Maybe you can achieve the same thing by replicating from the 3rd party and then use DML triggers on your database to insert into a log table. We are actually doing this in a similar way for a different purpose.Thanks. Just to clarify, you suggest creating DML triggers on the 3rd party database to insert logs in a table and replicate those log tables over to our database?
ope, put the triggers on your database that is getting replicated data from the 3rd party. Since that 3rd party is probably out of your control, I would try to keep as much control as possible on your server.
Jared
CE - Microsoft
July 5, 2012 at 1:40 pm
SQLKnowItAll (7/5/2012)
SQL_Baltimore (7/5/2012)
SQLKnowItAll (7/5/2012)
Ok. Maybe you can achieve the same thing by replicating from the 3rd party and then use DML triggers on your database to insert into a log table. We are actually doing this in a similar way for a different purpose.Thanks. Just to clarify, you suggest creating DML triggers on the 3rd party database to insert logs in a table and replicate those log tables over to our database?
ope, put the triggers on your database that is getting replicated data from the 3rd party. Since that 3rd party is probably out of your control, I would try to keep as much control as possible on your server.
Got it! Thanks Jared.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply