Microsoft® SQL Server® 2012 Bible

947 Chapter 41: Data Change Tracking and Capture 41 Change Tracking may also be confi gured with T-SQL using SQL ALTER SET: ALTE ...

948 Part VII: Monitoring and Auditing Enabling Tables The ease to confi gure Change Tracking continues with enabling Change Trac ...

949 Chapter 41: Data Change Tracking and Capture 41 Enabling Change Tracking for a table can affect other tasks: ■ The primary k ...

950 Part VII: Monitoring and Auditing Result (abbreviated): table ChangeTracking ------------------------- --------------------- ...

951 Chapter 41: Data Change Tracking and Capture 41 Result: 1 The inserts added two new rows, with primary key values of Departm ...

952 Part VII: Monitoring and Auditing In this case, the Change Tracking table is HumanResources.Department and the begin- ning v ...

953 Chapter 41: Data Change Tracking and Capture 41 FROM ChangeTable (Changes HumanResources.Department, 1) as CT LEFT OUTER JOI ...

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

955 Chapter 41: Data Change Tracking and Capture 41 There are only a few drawbacks to CDC: ■ Cost: It requires Enterprise Editio ...

956 Part VII: Monitoring and Auditing Enabling the Database To enable the database, execute the sys.sp_cdc_enable_db system stor ...

957 Chapter 41: Data Change Tracking and Capture 41 ■ (^) @filegroup_name: The fi legroup the CDC will be stored on. If not spec ...

958 Part VII: Monitoring and Auditing INSERT HumanResources.Department (Name, GroupName) VALUES ('CDC New Row', 'SQL Rocks'), (' ...

959 Chapter 41: Data Change Tracking and Capture 41 The following sample query defi nes a range beginning with Jan 20 and ending ...

960 Part VII: Monitoring and Auditing sys.fn_cdc_map_time_to_lsn ('largest less than or equal', '20121231'); SELECT __$start_lsn ...

961 Chapter 41: Data Change Tracking and Capture 41 as StartLSN, * FROM cdc.fn_cdc_get_all_changes_HumanResources_Department (sy ...

962 Part VII: Monitoring and Auditing 2012-06-04 23:06:51.350 insert 20 Row Four TVP Rocks 2012-06-04 23:06:54.570 update/delete ...

963 Chapter 41: Data Change Tracking and Capture 41 WHERE Name = 'Test Two'; -- Querying Net Changes - 'all with mask' option SE ...

964 Part VII: Monitoring and Auditing The following script uses the sys.fn_cdc_get_min_lsn() function to identify a start- ing p ...

965 Chapter 41: Data Change Tracking and Capture 41 @source_name = 'Department', @capture_instance = 'all'; Summary The need to ...

c41.indd 966c41.indd 966 7/31/2012 10:17:22 AM7/31/2012 10:17:22 AM http://www.it-ebooks.info ...