By Chris Alliegro [bio]
Posted: Oct. 20, 2008

• Illustration: Configuring SQL Server Audit

The following is the full text of an article published by Directions on Microsoft, an independent research firm focused exclusively on Microsoft strategy & technology. More samples of our content, as well as a list of upcoming articles and reports are also available.

New mechanisms for tracking changes made to SQL Server 2008 settings and databases could reduce the work associated with data security and compliance, and with development of data warehousing applications or applications that maintain offline databases. The new features close gaps that were previously covered by tools from third-party vendors, and those vendors could find fewer opportunities as SQL Server 2008 makes its way into production. However, some of the new capabilities are limited to SQL Server's high-end Enterprise Edition and the product's Developer Edition.

More Granular Auditing

A new server and database auditing tool, SQL Server Audit, can automatically log changes made to databases and database server settings and allows administrators to audit a wider range of information and settings than was possible in previous versions, without requiring custom code or third-party tools. Administrators configure SQL Server Audit using T-SQL, SQL Server's main programming and query language, or the SQL Server Management Studio administrative tool. (See the illustration "Configuring SQL Server Audit".)

Most notably, administrators can now log changes made to database tables and schema—for example, an audit could examine all updates to tables in a specific database made by a specific user. Previous versions of the product could track a variety of server- and database-level events and changes—log-ons or the addition of new users, for instance—typically via a feature called SQL Server Trace. However, auditing changes in the actual database tables required customers to write custom applications using triggers, which take some action when data in a table is changed or deploy third-party solutions, such as ApexSQL Audit or Idera's SQL compliance manager.

The new SQL Server Audit tool has other advantages compared to SQL Server Trace. For example, SQL Server Audit can log events in a binary file that can be viewed using Management Studio, the Windows Application event log, or the Windows Security event log. SQL Server Trace logs events in a binary file format that requires a separate tool, the SQL Server Profiler, for viewing. (SQL Server Trace can also log events to a separate SQL Server table.) Writing SQL Server Audit events to Windows event logs will allow organizations to easily combine SQL Server auditing with other important monitoring processes and tools, such as Microsoft's Operations Manager, which is particularly relevant for large organizations that centralize monitoring operations.

Although SQL Server Audit is a marked improvement over earlier options, it nonetheless falls short of features in more advanced third-party tools. For example, while the tool can record user names and the time of a change, it does not record the change in the data itself. Although it is possible for organizations to correlate audit information with data from SQL Server 2008's new Change Data Capture (CDC) feature (described below), a fully realized solution will likely appear no sooner than the next major version of SQL Server, which Microsoft has suggested will ship in 2010. In the meantime, organizations needing such auditing capabilities may be best served by third-party tools.

Furthermore, SQL Server Audit is available only in SQL Server Enterprise Edition. Administrators working with other SQL Server editions must rely on tools such as SQL Server Trace or third-party products.

Improved Change Tracking

A pair of new features for capturing database changes could particularly benefit developers creating data warehouse applications or applications that must maintain and periodically synchronize offline copies of a master database.

The new features offer simpler, lighter-weight alternatives to SQL Server's existing replication features, giving developers more information about database changes with less maintenance overhead.

Change Capture Benefits Data Warehouses

Change Data Capture (CDC) automatically tracks changes made to specific database tables by reading SQL Server's transaction log, special files that keep a running record of database activities. CDC writes all changes found in the transaction log (such as new or updated table rows) into a separate group of database tables along with information about the nature of those changes, such as the SQL operation that changed a row and the specific columns in the row that were altered.

Among other uses, CDC could immediately benefit developers creating data warehouse applications, which must periodically extract raw data from transaction processing systems—for example, an application that records sales transactions, summarizes those data, and loads them into the data warehouse's database. By targeting CDC's change tables, rather than tables containing the data created by a transactional application, a developer can incrementally update the data warehouse. Incremental data warehouse updates in previous versions of SQL Server typically involved writing complex queries to identify changed data in the transactional system or deploying a custom or third-party CDC solution.

Organizations could accomplish this type of change-tracking by using SQL Server's transactional replication features, a mechanism for capturing database transactions and automatically replicating them in other databases. However, replication is a relatively heavyweight solution and will generally impose a greater burden on administrators than CDC. Furthermore, reproducing CDC's running record of database changes and granular information about changed columns in a solution based on transactional replication would require additional developer effort.

Although developers could build change capture mechanisms similar to CDC in previous versions, doing so involved considerably more work. For example, developers could write triggers to flag and capture changes and could write additional code to insert those data into separate tables. In addition to the burden of additional development and maintenance, this approach has a potentially greater performance impact—since triggers fire in sync with actual database transactions, they can slow processing, particularly on servers with high transaction volumes. CDC, on the other hand, is asynchronous and should thus have less impact on transaction processing.

In addition to benefiting developers, CDC closes one gap between SQL Server and data integration competitors, such as Informatica, or general database vendors, such as IBM and Oracle, all of which have fielded data change capturing solutions for at least a year.

Change Tracking Aids Offline Applications

A second mechanism for tracking and recording databases changes—called Change Tracking—is similar to CDC, but instead of tracking all data associated with changes it only flags the fact that rows have changed, records the type of changes made (for example, whether data was updated or deleted), and supplies keys that point to the changed rows in the table being tracked. To access the new values for a given row, developers must query the original table.

Change Tracking offers developers a useful mechanism for building applications that must periodically synchronize offline data caches or databases with a master online database. Microsoft's Dynamics CRM customer relationship management application is an example of the latter—the product's client application maintains a local copy of the CRM database, which allows users to work with the product offline, and synchronizes this copy with the master CRM database when the client reconnects with the network.

Change Tracking will simplify development of such applications, which previously required complex custom solutions using triggers, additional columns in the source table, and specialized tables for tracking change information. However, developers must still avoid potential data consistency problems in rapidly changing databases, such as when corresponding rows in master and offline databases have both changed since the last synchronization. Although Change Tracking will help developers deal with such issues, the process will still require careful implementation and testing.

As is true of CDC, Change Tracking overlaps with SQL Server's existing replication features—specifically, a feature called merge replication offers a solution for synchronizing client applications with a central database. However, Change Tracking gives more flexibility to developers building custom solutions. For example, merge replication is generally designed to synchronize data among databases with identical schemas. Change Tracking allows developers to synchronize client and server applications with different database schema (or even different database technologies).

Availability and Resources

Microsoft released SQL Server 2008 in Aug. 2008. The product comes in four main editions (Enterprise, Standard, Web, and Workgroup) and a pair of free editions (Express and Express Advanced). The product's new auditing and CDC features are available in Enterprise Edition and Developer Edition only. Change Tracking is available in all product editions. SQL Server 2008 pricing is unchanged from SQL Server 2005.

The SQL Server home page is www.microsoft.com/sqlserver

SQL Server 2008 Integration Services features are described in "BI Performance, Tools Improved for SQL Server 2008" on page 17 of the Oct. 2008 Update.