Change data capture on log ship replica

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    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.

  • rraja

    Default port

    Points: 1486

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

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    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

    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,

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    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: 1004446

    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

    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

    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

    GilaMonster,

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

    Thanks,

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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

    SSC Rookie

    Points: 46

    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: 1004446

    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

    SSC Rookie

    Points: 46

    Sure

  • Anthony Scott

    SSC-Addicted

    Points: 408

    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

    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?

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

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