Tag Info

Hot answers tagged

10

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 ...


8

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 ...


6

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 ...


5

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 ...


4

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 ...


3

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 ...


3

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) ...


3

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 ...


2

So, after some research we decided to still do this on the SQL side before handing off to the data warehouse. But we're taking this much improved approach (based on our needs and new understanding of how the mask works). We get a list of the column names and their ordinal positions with this query. The return comes back in an XML format so that we can ...


2

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 ...


2

The only caveat I would give is that those tables are dropped automatically when CDC is disabled. The column not be recreated automatically when you reneable it


2

I agree with Aaron that for the purpose of a development system you may want to prove and re-prove at certain intervals and perhaps disable it through transitions presenting a more stable environment at the target. Please note that there are commercial tools out there which can readily perform this task. Attunity, which has a full featured 'Replicate' ...


2

The mechanism for schema changes is that you disable CDC for the table, and then re-enable it. This of course means you lose all the history, so you'll also want to build in a mechanism to offload that somewhere if you want history from before the most recent schema change. Maybe a better answer is, once you've tested CDC initially, keep it disabled until ...


2

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 ...


2

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.


2

Flashback Data Archive (formerly known as Oracle Total Recall) let you store history for all the changes to one or more tables automatically. If you have the Advanced Compression option, Oracle will apply a number of optimizations to the history table that will significantly decrease the amount of space that is required and that will generally make queries ...


2

No, it will only work within the same database. After all, what do you expect to happen if the second database goes offline, gets dropped, becomes corrupt, has permissions changes, etc.? Now, once the CDC tables are created, I guess one potential workaround would be to place INSTEAD OF INSERT triggers on them, and divert the data to identical tables in ...


2

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. ...


2

CDC relies on a SQL Server Agent job to capture information from the transaction log. You can customize the job parameters by updating msdb.dbo.cdc_jobs - there are multiple properties as described in this Books Online topic: maxtrans maxscans continuous pollinginterval That doc has some information about these parameters, as does this white paper. But ...


2

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 ...


2

The standard way is to use a History Table pattern. If you have a table like this: create table foo ( int foo_id primary key, name varchar(x) not null ); You would have a corresponding table create table foo_history ( int foo_id, int version, name varchar(x), inserted_at timestamp not null default current_timestamp, operation_code char(1) ...


1

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


1

It appears that this was added in the 2008 time period. You can see the Connect item with the title "MSIT-MSO: Use NOLOCK while querying the cdc._CT table in CDC TVF" at: http://connect.microsoft.com/SQLServer/feedback/details/331486/msit-mso-use-nolock-while-querying-the-cdc-captureinstance-ct-table-in-cdc-tvf It is interesting that the request was made ...


1

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 = ...


1

Figured out what the issue was. For some reason the primary database had been joined to the availability group but the secondary was not, do not know if another DBA forgot to join it when they restored or if there was an error. Either way, this wasn't allowing the logs to harden so CDC wasn't picking up the changes. Did a fresh backup of the primary, ...


1

As Phil mentions in his comments... WHY? It may be better/faster to get help on what actually needs to be done than on a perceived method, good or bad, of achieving that. All changes can be found in the REDO (and ARCHIVE) logs, not auditing tables. Depending on the real 'why' the one may be better than the other. You may also need "Supplemental logging" to ...


1

I'm not sure about the ROWVERSION behaviour, but ORA_ROWSCN I believe would still change if you were to update a row with completely identical values. In other words, the row contents would remain the same but its SCN would be different. The only practically reliable way to detect the actual change in column values would be to calculate the row checksum or ...


1

Change Data Capture, or CDC, does not require the KEEP_CDC keyword when restoring a database to the original server. When restoring a database to a server other than the original server, you can use the KEEP_CDC keyword to specify that the change data capture data is retained in the database. This is by design, and is clearly stated in BOL here under the ...


1

There is no definitive answer here because each solution has its own pros and cons. CDC Easier to setup and you don’t need to worry about creating triggers and storing trigger data Available only in SQL Server Enterprise edition so price may be an issue Triggers More flexibility to adopt this to your needs (you can audit sql server users, applications ...


1

I answered this question in my blog at Sqltimes.wordpress.com As far as I know, the only way to configure retention period is by updating the retention column in the msdb.dbo.cdc_jobs table. You want to update the retention column of the record with 'cleanup' in job_type column. It could be done in two ways: EXECUTE sys.sp_cdc_change_job @job_type = ...



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