18

If you are restoring a CDC-enabled database onto a different instance, you must ensure the server principal that "owns" the database in question is present on the target instance prior to performing the restore. You must also ensure you use the KEEP_CDC option when restoring the database. From the MSDN page on restore options: KEEP_CDC should be used to ...


12

Firstly, Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server. 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. (As the spec includes "multiple future applications" this may be an real issue ...


12

Here is a very well written 9 part series that reviews the different ways of auditing SQL Server data changes. Parts 3, 4 & 5 focus on CDC. It's worth reading through all of the articles because this will answer your questions, like the different scenarios where the features would be appropriate and overhead. http://solutioncenter.apexsql.com/tag/...


12

dbo is mapped to an invalid login. Re-map it to a valid one: ALTER AUTHORIZATION ON DATABASE::[<dbname>] TO [sa];


11

A date/time value is generally not considered to be unique enough. No matter how fine you define it, it is possible to have 2 actions occurring for the same datetime value. Prior to SQL Server 2008, the most accurate value was a rounded 3.33 milliseconds with datetime which is an eternity really. datetime2 resolves to 100 nanoseconds which isn't accurate ...


11

And the moral of the story is... test, try other things, think big, then small, always assume there is a better way. As scientifically interesting as my last answer was. I decided to try one other approach. I remembered I could do concat with the XML PATH('') trick. Since I knew how to get the ordinal of each changed column from the captured_column list ...


9

For which tasks/scenarios is CDC the right tool? (e.g. Allowing users to restore a data object to a certain point in time? Maybe, it depends. Auditing? Yes. Showing the complete history of data?) Yes. When should you rather not use CDC, but resort to a custom trigger-based solution? When the data in the change table does not meet your needs....


7

I filed a bug about this, but it was closed as "by design." http://connect.microsoft.com/SQLServer/feedback/details/283707/cdc-options-to-capture-more-data-username-date-time-etc Unfortunately you will have to use another technique (e.g. SQL Audit or a trigger) to get this information (and some custom code to try to correlate it to the CDC data as much as ...


7

Remember that CDC uses a log reader agent to populate the change table. Why is that important? By that mechanism, rows show up in the change tables asynchronously to the changes made in the base tables. There are actually 3 different time points that can be recorded, in reverse chronological order: The time the change was delivered to the change table (...


7

(if someone finds a way - which I dont think exists, I will delete my answer :-)) Is there a way to determine if a backup contains CDC data? I dont think that there is any way to know from a backup, if CDC is enabled or not. The most you can know if the database was involved in replication (under the hood uses log reader agent that scans the Transaction ...


6

You could add the column and have a trigger on the table to populate the user on insert/update/delete and cdc would then store this. You can grab the username either from the UI by passing the username using context info or from the actual session SET @CapturedBy = CAST(REPLACE(CAST(CONTEXT_INFO() AS VARCHAR(10)),CHAR(0),'''''''') AS INT) IF(@CapturedBy) ...


6

Here is the MSDN Large Object Data Types Columns of data type image, text, and ntext are always assigned a NULL value when __$operation = 1 or __$operation = 3. Columns of data type varbinary(max), varchar(max), or nvarchar(max) are assigned a NULL value when __$operation = 3 unless the column changed during the update. When __$operation = 1, these columns ...


6

It looks like CU2+SP4 (12.0.5540) changes the structure of the underlying _CT system tables that get generated. It adds the column [__$command_id] int null. When you upgrade in place to the new CU, the capture jobs will start failing or go to 'between retries...' with the following errors: Dropping and recreating the capture job does not work (using ...


5

I have been able to find the documentation that I was looking for. CDC is supported off of the readable secondary but Change Tracking is not unfortunately. The link is specified below but here are the relevant parts for my needs. Redirecting the Query Load to a Readable Secondary While in many cases a client application will always want to connect to ...


5

LinkedIn has open-sourced their CDC tool Databus, which supports MySQL. http://engineering.linkedin.com/data-replication/open-sourcing-databus-linkedins-low-latency-change-data-capture-system Another open source CDC tool (implemented in PHP) was written as a part of the FlexViews project. http://swanhart.livejournal.com/132117.html


5

It depends on your business case, Temporal tables and change data capture offer different functionality. Temporal tables are used to provide a version of your table at a point in time. A use case might be a slowly changing dimension where you want to track the changes in dimension attributes and report them from any moment in time. Change data capture ...


4

Refer to Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server) From the reference : Change Data Capture: Databases enabled for change data capture (CDC) are able to leverage AlwaysOn Availability Groups in order to insure not only that the database remains available in the event of failure, but that changes ...


4

Input (hopefully stored procedure parameters): DECLARE @table1 NVARCHAR(513) = N'dbo.Users', @table2 NVARCHAR(513) = N'dbo.DataObjects', @ID INT = 10; Code: DECLARE @InstanceName1 NVARCHAR(513), @InstanceName2 NVARCHAR(513), @Begin_LSN BINARY(10), @End_LSN BINARY(10); SELECT @InstanceName1 = c....


4

Recently I had an academic project where I needed to use CDC with MySQL. There are a few tools available in the market. I was able to create a POC by using Maxwell CDC. A unique feature of Maxwell is it can parse DDL statements as well. Here is link to the page of Maxwell http://maxwells-daemon.io Here is complied list of all CDC's : https://github.com/...


4

I recommend you take a look at Debezium an open-source CDC platform which, amongst others, also supports MySQL. You can use Debezium to stream changes into Apache Kafka, but by means of its embedded mode you can also use the Debezium connectors as a library in your Java applications and easily propagate data changes to other streaming APIs such as Kinesis ...


4

I am not sure about CDC, but if the login has view server state permission you can use DMVs to get some information. This is given in Books Online here. I changed the query to add columns which would give you the IP address: SELECT c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, s.host_name, s.program_name, s.client_interface_name,...


4

When you say, "without using triggers", do you mean any triggers or just row-by-row triggers on tables? I ask because you may be able to get what you want with judicious use of the CONTEXT_INFO() function, but you would need to ensure that SET CONTEXT_INFO was called correctly before your operations take place. One place to do that might be a server-...


4

Given the choice between those two things, here's why I'd choose triggers: Triggers can make related table changes synchronously (like if you need to update reporting tables or check referential integrity) Triggers let you store the data in the format that works for you, whereas CDC puts it in its own CDC schema, in tables that may not work for your reports ...


4

@Brent Ozar has listed pros of using trigger (which are good and valid points), but to make an informed decision, I'd like to list some pros of CDC. Trigger is closely coupled (or synchronized) with your DML transactions, while CDC is not, because CDC is implemented via reading log by a separate sql job. The result is that your DML transactions are not ...


3

First you have to enable CDC at database level. USE database_name GO EXEC sys.sp_cdc_enable_db GO Then you have to enable CDC at table level USE database_name GO EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'table1', @role_name = NULL Below query will tell you what tables have cdc enabled SELECT [name], ...


3

A practical example: USE Database; GO DECLARE @from_lsn binary(10), @to_lsn binary(10) SET @from_lsn = sys.fn_cdc_get_min_lsn('schema_tablename') SET @to_lsn = sys.fn_cdc_get_max_lsn() SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'Time' ,[Field1] ,[Field2] ,[Field3] FROM [cdc].[fn_cdc_get_all_changes_schema_tablename] (@from_lsn, @...


3

You use built-in table-valued functions (TVFs) as described in Books Online : Using Change Data (MSDN). First you need to find the LSNs you want to use to filter the ranges you want to query. These LSNs will represent points in the log which you can correlate to datetime values. You can do this using sys.fn_cdc_map_time_to_lsn. Let's say you want to report ...


3

Yes CDC works with Failover Clustered Instances. It has since it was first introduced. There's nothing special that needs to be done to get it working. Just set it up like you would on a standalone non-clustered instance.


3

After you enable the CDC on a database 2 jobs are created for that database: cdc.DBName_capture (which will start the change data capture collection agent) cdc.DBName_cleanup (which will clean up the change tables periodically) the default retention is 3 days (data older than 3 days is removed) the default schedule for this job is daily at 02:00 AM The ...


3

Please take a look at the connect bug: Below is the relevant snippet from it This behavior is by design. CDC is designed to expose the following information about a change: updated columns, type of operation and transaction information. It has not been designed as an audit solution. It has been created to enable efficient Extract Transfer and Load ...


Only top voted, non community-wiki answers of a minimum length are eligible