Konfiguruję CDC. Kolejne kroki to:
1. włączenie CDC dla bazy danych
USE AdventureWorks2014
EXEC sys.sp_cdc_enable_db
2. Dodanie określonej tabeli do CDC
EXEC sys.sp_cdc_enable_table @source_schema = 'HumanResources’,
@source_name = 'Shift’,
@role_name = NULL,
@supports_net_changes = 1
Niestety na tym etapie otrzymuję błąd:
Could not update the metadata that indicates table [HumanResources].[Shift] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N’capture”. The error returned was 916: 'The server principal „……” is not able to access the database „msdb” under the current security context.’. Use the action and error to determine the cause of the failure and resubmit the request.
Zgodnie z sugestią na http://blogs.msdn.com/b/ramoji/archive/2010/05/13/error-while-enabling-cdc-on-a-table-the-server-principal-xyz-is-not-able-to-access-the-database-msdb-under-the-current-security-context.aspx
uruchomiłem sys.sp_cdc_add_job ręcznie, co rozwiązało problem:
EXEC [sys].[sp_cdc_add_job] @job_type = N’capture’
