![]() ![]() The change table is not hidden nor does it have some weird schema or something. One can use that history to update a data warehouse instead of bulk copying in data, or one can use that history as data itself and generate reports off of it. An example of use would be a hand held device sync.ĬDC, on the other hand, keeps track of every little change, a history. Both have their roots in replication.Ĭhange Tracking provides a way to provide the net changes to a table. There is Change Tracking and there is Change Data Capture. I commonly hear that CDC is an audit tool, but isn't that what SQL Server Audit is for?Īre they both different tools for the same task? Or is this clearly a misuse of this feature? Is it ok to use CDC in an operational database and make use of the CDC data within an operational application? (e.g. When the data in the change table does not meet your needs. When should you rather not use CDC, but resort to a custom trigger-based solution? Allowing users to restore a data object to a certain point in time? However you may be best solving this at the application level, by say writing all update to a message queue that is then replayed to create a database at any given point of time, see Temporal Patterns on Martin Flowler blog for a good overview of options.įor which tasks/scenarios is CDC the right tool? (e.g. ![]() I worked on one system when we used triggers (generated by CodeSmith), as well as tracking all the changes to the records, we also linked the changes together to a “history” table that included the module of the application that made the change, and the UI item the user used to make the change. Using triggers always slow down an update. Unlike triggers it is not real time, this is both an advantage and a disadvantage. (As the spec includes "multiple future applications" this may be an real issue for you) So that may decide for you if any of your customers will not have the enterprise editions, or you don't yet know you will be using the enterprise editions. While I appreciate advice for my specific scenario, answers should give general advice about when or when not to use Change Data Capture.Ĭhange data capture is available only on the Enterprise, Developer, I am evaluating CDC right now as an option, but am uncertain if this is the way to go, because I can't really find any recommended use cases. The exact requirements are blurry, but one is that it should be able to track data history and restore older entries together with all related data from other tables. My current scenario is that I am asked to build a reliable data framework which is supposed to be the basis for multiple future applications. I commonly hear that CDC is an audit tool, but isnt that what SQL Server Audit is for? Are they both different tools for the same task? Or can CDC used for other things? showing it to the end user) Or is this clearly a misuse of this feature? Is it ok to use CDC in an operational database and make use of the CDC data within an operational application? (e.g.When should you rather not use CDC, but resort to a custom trigger-based solution?. ![]() Allowing users to restore a data object to a certain point in time? Auditing? Showing the complete history of data?)
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |