SQL server 2008 CDC

Question

Hi,

I have proposed CDC as an auditing solution to my company software. It is an MVC application that uses web API feature. I have following conditions that i need to meet in order to use CDC as an auditing tool.

1. We want to keep audited data forever or atleast up to our will. We dont want to delete the captured data. There is a CDC clean up job that deletes data after certain time period. Can we stop that service or is there an alternative that we can do to preserve captured data.

2.  There is a pretty good chance that there will be many ddl changes to a table. Currently if a new column is added to a table that is already running CDC. The newly added column in source table is manually captured  on a second or different captured instance and as SQL server can run only 2 captured instances of a single table what would happen if there is one more column added to the source table needs to be captures. Do we have to drop any one of existing captured instances ? If dropping any of exisiting captured is the solution then what will happen to audited data of previosuly captured instances. It is really important that none of the captured data is lost.

Please advise on both points asap. It is really a very high priority issue for me..

Thanks..

Answer 1

Hi SQLDev,

Answers as follow:

1) Yes you can disable CDC job. 

2) No you don need to drop any existing captured instance

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Thanks for your prompt response but SQL server is not letting me create a new captrure instance if i already have 2 instances created. I created 2 capture instances, one for the 1st time when i started tracking, 2nd instance due to column addition. Now i have i have to add 1 more column to the source table and capture it when i am trying to add one more capture isntance to accomodate for a new column , i am getting the following error. Again my focus is not to loose already captured data.

Msg 22962, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 113

Two capture instances already exist for source table 'dbo.Albums'. A table can have at most two capture instances. If the current tracking options are not appropriate, disable change tracking for the obsolete instance by using sys.sp_cdc_disable_table and retry the operation.

Please respond

Thanks..

---

CD

Answer 3

I can't answer your specific problem, but from what I have understood, CDC is not intended as an auditing solution, although you are not the first one to try it.

Have you looked at SQL Server Audit?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Thanks Erland,

I want to capture initial and after values of a change that is done to a row and found out that CDC as solution. But i am getting retricted due to ddl change. If i have multiple new columns added then i need to track those on new capture instance and in that case i am concerned about old instances data. I dont want to loose data from previous captured isntances. Is there a manageable work around for this ? DDL changes are pretty common and often to a table in an app.

Let me know..

Thanks..

---

CD

Answer 5

I have not worked enough with CDC to be able help. I just wanted to point out that CDC is not intended for auditing.

Maybe it would be better to use triggers and an audit table with XML columns. Here is one example:

   INSERT basiclogs2 (tablename, keyvalue1,
                      action, beforeimage, afterimage)
      SELECT 'accounts', coalesce(i.accno, d.accno),
             CASE WHEN i.accno IS NULL THEN 'D'
                  WHEN d.accno IS NULL THEN 'I'
                  ELSE 'U'
             END,
             (SELECT * FROM deleted d2 WHERE d2.accno = i.accno FOR XML RAW, TYPE),
             (SELECT * FROM inserted i2 WHERE i2.accno = i.accno FOR XML RAW, TYPE)
      FROM   inserted i
      FULL   JOIN deleted d ON i.accno = d.accno

By using XML you can easily withstand schmea changes.

I should add that it depends on what you want to use the auditing data for. Advanced queries on the XML data will not be very fun.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Thanks again Erland,

I think i will not go for CDC appraoch as ddl changes to source tables are not manageable.  I am interested in trigger based auditing approach. Can you tell me if only by using triggers at DB level we can accomplish auditing or we need interaction with business layer as well. I simply need to store initialcolumnvalue, finalcolumnvalue, whochanged and when for a auditing.

In regards to your code , what are these inserted and deleted tables ?

FROM   inserted i
      FULL   JOIN deleted d ON i.accno = d.accno

Can you provide me any more information (any good article or code sample) about this trigger based approach? 

Thanks for your help!!! Please respond

---

CD

Answer 7

In a trigger, you have access to two virtual tables, inserted and deleted. they have the same schema as the parent table. inserted holds the rows that were inserted for an INSERT trigger, and the resulting rows of an UPDATE trigger. deleted holds the deleted rows for a DELETE statement and for UPDATE deleted holds the rows as they looked before the update.

The trigger-based solution does not require any interaction with the business layer to work. You will need one trigger per table.

I don't have any more documentation about this idea. Quite a few years ago we were working with a potential customer for our system, and they had high requirements for auditing and I came up with this idea, as it would be simple to write a generic function that would display differences. The project was cancelled fairly on, and we never came around to use the idea on a broader scale.

A potential problem with this approach is that since if you have wide tables where only one or two columns are changed, the complete XML documents will take up quite some space.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

I second, Erland.  SQL Auditing is awesome!  You can specify exactly what you what operations to audit, for which objects and for whom.  See www.ultimatewindowssecurity.com/sqlserver.  And, at http://www.logbinder.com/products/logbindersql/resources/wizard.aspx we provide a free wizard for configuring server level audit policy. 

---

Randall F Smith

Answer 9

I can't answer your specific problem, but from what I have understood, CDC is not intended as an auditing solution, although you are not the first one to try it.

Have you looked at SQL Server Audit?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

I am completely and utterly baffled by your statement that "CDC is not intended as an auditing solution". CDC can capture all of the historical changes to a given table and can yield a complete record of every change that has been made to the data in a given table. This, at least as I understand it, is the primary purpose of auditing: to find out what changed and when and CDC seems perfect for that. Why do you say that CDC is not an "auditing solution" ?

Answer 10

One thing you expect from an auditing solution is who did the change and when. I would even go as far that is you don't have this information you are not auditing.

The latter can be deduced with some pain from CDC, but the first is simply not there. But, true, you could add auditing columns to the table you are tracking with CDC.

As I understand it, CDC is mainly intended to update data warehouses. Auditing-like capabilities are a by-effect.

---

Erland Sommarskog, SQL Server MVP, [e-mail]