954
Part VII: Monitoring and Auditing
JOIN sys.tables t
ON ct.object_id = t.object_id
JOIN sys.schemas s
ON t.schema_id = s.schema_id;
PRINT @SQL;
EXEC sp_executesql @SQL;Only after Change Tracking is disabled from every table can Change Tracking be removed
from the database:ALTER DATABASE AdventureWorks2012
SET Change_tracking = off;Even though Change Tracking is removed from the database, it doesn’t reset the Change
Tracking version number, so if Change Tracking is restarted it won’t cause a
synchronization nightmare. Although Change Tracking can be used with any edition, now
take a look at a more feature-rich technology that is only available in Enterprise Edition:
Change Data Capture.Change Data Capture
Where change data capture shines is in gathering data for ETL from a high-traffi c OLTP
database to a data warehouse. Of the possible options, change data capture has the least
performance hit and does a great job to provide the right set of data for the Business
Intelligence ETL (extract-transform-load). When you think big-dollar BI, think CDC.Any data written to the transaction log can be asynchronously captured using CDC from
the transaction log after the transaction is complete, so it doesn’t affect the original trans-
action’s performance. CDC can track any data from the T-Log, including any DML insert,
update, delete, and merge command, and DDL create, alter, and drop.Changes are stored in change tables — tables created by CDC with the same columns as the
tracked tables plus a few extra CDC-specifi c columns. All the changes are captured, so CDC
can return all the intermediate values or just the net changes.The new features (Considerations) for CDC introduced with SQL Server 2012 mostly fall into
two main categories: AlwaysOn and External Drivers. When working with AlwaysOn Replicas
there is only one way to collect the data properly. Some new third-party drivers have been
developed for use with external data sources like Oracle.For more information on the third-party drivers, see http://blogs.msdn.com/b/
mattm/archive/2012/03/26/cdc-for-oracle-in-sql-server-2012.aspx.Because CDC gathers its data by reading the log, the data in the change tables is organized the
same way the transaction log is organized — by T-log log sequence numbers, known as LSNs.c41.indd 954c41.indd 954 7/31/2012 10:17:21 AM7/31/2012 10:17:21 AM
http://www.it-ebooks.info