Change data capture on log ship replica

Question

Post reply

Change data capture on log ship replica

TangoVictor

SSChampion

Points: 10975
More actions
March 13, 2012 at 9:00 am

#252441

SQL 2008 R2 Enterprise x64
We will be getting a major upgrade to a mission critical application that will finally use SQL Server as the back end.
From this data we need to feed a custom application with updates being 30 minutes or less. Would be nice to be able to provide operational data to this custom system with a latency of less than 10 minutes.
The goal is to extract changed data from a production system with minimal impact and minimal time.
Change data capture would be the obvious choice but the assumption is that we cannot create the change capture tables within the database due to it being part of a 3rd party application (not saying we wouldn't do it though....)
My thought was log shipping to a separate database where we could create the data capture. But my understanding is that the replica is read only. I have not done any testing or proof of concept on this yet.
I thought I would look for ideas/pitfalls/suggestions before exploring this route. At this point we are in the brainstorming phase and open to any ideas, we have flexibility for just about anything.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

rraja Default port Points: 1486 More actions · Answer 1

yes, you are right Logshipping will not help you for your rquirement. what about repliaction??

TangoVictor SSChampion Points: 10975 More actions · Answer 2

After reading this http://msdn.microsoft.com/en-us/library/ms151198.aspx replication would appear to be a valid solution.

My concern is the synchronization, guessing that can be controlled and the replica would be able to be manipulated without sending changes back to the source.

Also could change data capture be implemented on the replica?

CDC is a very important feature that we need to leverage for this project since digging into the data every 10 minutes to find what has changed is not an option. We have done this and ran into a few issue, with the exiting system (non-sql) such as last update stamps being a mix of UTC and Local, and finding that some application processes don't actually update the last modified field (under app control not DBMS), these 2 items alone have added a lot of needless data processing.

sqlredcedar SSC Veteran Points: 256 More actions · Answer 3

Hello Tom,

We have a similar situation. The Production database developed and maintained by a different contractor does not have CDC turned on. We were brainstorming with a solution to apply Transaction Log Backups to create CDC entries on a secondary database. Please let me know how did your replication effort go? Do you have any thoughts on applying the transaction log backups to capture CDC?

Thanks,

TangoVictor SSChampion Points: 10975 More actions · Answer 4

this project was put on hold do to a major issue with the vendor software. we are expecting to be back on it by end of 2014 so I have not done any work on this.

I doubt you could apply a backup and see changes in the cdc tables, although im not sure.

another option possibly it's to read directly from the backup file, but not sure about that either.

if you come up with something please post it.

Tom

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sqlredcedar (1/10/2014)
We were brainstorming with a solution to apply Transaction Log Backups to create CDC entries on a secondary database.

That won't work. CDC uses the transaction log, reading the log records. When you restore a tran log backup, there are no log records written. Besides, to apply transaction logs, the database has to be recovering or standby.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlredcedar SSC Veteran Points: 256 More actions · Answer 6

We are ok with it being in Standby and Read Only. So applying Transaction Log Backup files in itself doesn't create any "Transactions" and hence CDC wont capture it?

Thanks,

sqlredcedar SSC Veteran Points: 256 More actions · Answer 7

Sorry you already answered my question in your previous post, about transaction logs not recorded while restoring!

On to thinking for other solutions....

Thanks

sqlredcedar SSC Veteran Points: 256 More actions · Answer 8

GilaMonster,

Will turning on Auditing on the secondary server capture the changes?

Thanks,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

No, because they're not executed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sujainarayanan Valued Member Points: 56 More actions · Answer 10

Hi Gail,

We have a requirement to capture the incremental changes on the source database and we have decided upon using the CDC feature in SQL Server for incremental data capture.

The below points are to be kept in mind

1. Our source is SQL Server with compatibility mode 80(deprecated mode). We are trying to work with the source system users and with Microsoft to remove the deprecated mode.

2. Even if we remove the deprecated mode, we would like to avoid implementing CDC on the source in order to avoid any additional overhead on the existing source system.

Hence we are looking at taking a copy of the existing source into another database in another box/server(target) and looking to implement CDC on the target, so that the overhead on the source can be avoided. Log-shipping is one of repliocation we are looking at but as far I have understood from various sources, log shipping requires the secondary(target) database to be READ-ONLY.

Therefore we probably cannot go for log-shipping, as CDC would make schema changes to the destination/target database and hence it would NO LONGER be READ ONLY.

Can you suggest any other feasible options for us?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Please post new questions in a new thread. Thank you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sujainarayanan Valued Member Points: 56 More actions · Answer 12

sujainarayanan

Valued Member

Points: 56

August 1, 2014 at 9:29 am

#1734476

Sure

Anthony Scott SSC-Addicted Points: 408 More actions · Answer 13

Came across this discussion and it fits in with a solution we are currently working in virtualizing the CDC process..

I have been asked if I can use CDC with a backup database using log shipping, but I was of the opinion that such logs are not actual written to and therefore CDC cannot 'read' from them.

Any further on this discussion at all?

________________________________________________

We passed upon the stair - and I was that man who sold the world

rshinnick Newbie Points: 1 More actions · Answer 14

I see this is an old thread, but I have the same requirement. Wondering if anyone considered Transaction Log Shipping to a read/write replica, turning on Change Tracking on the replica?