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


9

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


5

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


5

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


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

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

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


3

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


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

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


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

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

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

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


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

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


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

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


2

That's an extremeley broad question. If your DB's tables use date in the primary keys you could run into duplicate problems. If you query by date range you may have duplicates or holes in your data. You may even have difficulty saying which row was written before another, depending on your design. Reproducing any previous state may become impossible e.g. ...


2

The DB2 for LUW reference does have a section under high availability entitled "Scenario: Changing the system clock" which goes through the implication of moving the system clock forwards or backwards. It is specifically to do with HADR (high availability), but it's worth a look anyway (it's also worth noting that it's talking about moving the clock back ...


2

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


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



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