Track data changes

May include but is not limited to: change tracking, database audit specification, CHANGETABLE

There are two tools in SQL Server serving the purpose of auditing data changes. The first is Change Tracking, which you can find in every version, the other is Change Data Tracking, can be found in Enterprise Edition.

The difference in a nutshell: using CDC, you can see what’s changed and how, in an asynchronous manner (there’s a latency involved). With CT, you can see that there’s a change occurred, but can’t see the changes themselves,  and do so in a synchronous manner.

You can imagine the benefits and drawbacks: CDC uses much more resources, while providing more information. Change Tracking doesn’t provide historical data, while still indicate the type of change made. Now let’s see both techniques in detail.

Change Tracking

The first thing to do with CT is to enable it. You must define that you need this functionality at the database level. You’d write a syntax like this:

ALTER DATABASE MyDataBase
SET CHANGE_TRACKING = ON

Advertisements

Integrate Database Mail

Database Mail is a shiny new feature of SQL Server, it is the successor of SQLMail, so use this one instead. It doesn’t rely on any external programs (e.g. Outlook) in contrast of its predecessor. It uses a simple SMTP server.

You should be familiar with one stored procedure: msdb.dbo.sp_send_dbmail, which accepts a whole lot of parameters. The most important ones are:

• @profile_name: specifies the Database Mail profile to use when sending the mail.
• @recipients: the recipients of the message, in a comma delimited list.
• @(blind_)copy_recipients: CC and BCC.
• @subject: the subject of the message.
• @body: the content of the message.
• @query: the query to attach to (or place in the body of) the message.
• @execute_query_database: the name of the database the query should be executed in.
• @append_query_error: a bit value. If you set  it to one, it sends the mail if the specified query fails, along with the error message.