CDC(변경 데이터 캡처, Change Data Capture Functions) 설정

-- 1. cdc 설정

select is_cdc_enabled, * from sys.databases -- cdc가 걸려있는 DB 확인

go

use db1 -- cdc를 설정할 DB로 이동

go

exec sys.sp_cdc_enable_db --  DB에 cdc 설정

go

--메시지 22830, 수준 16, 상태 1, 프로시저 sp_cdc_enable_db_internal, 줄 193

--데이터베이스 db1이(가) 변경 데이터 캡처용으로 설정됨을 나타내는 메타데이터를 업데이트할 수 없습니다. 명령 'SetCDCTracked(Value = 1)'을(를) 실행하는 동안 오류가 발생했습니다. 반환된 오류는 15517: '보안 주체 "dbo"이(가) 없거나 이 유형의 보안 주체를 가장할 수 없거나 사용 권한이 없기 때문에 데이터베이스 보안 주체로 실행할 수 없습니다.'입니다. 동작 및 오류를 사용하여 오류의 원인을 파악하고 요청을 다시 제출하십시오.

--> sp_changedbowner 'sa' -- sa권한으로 변경 후 실행

--exec sys.sp_cdc_disable_db --  DB해제

-- cdc 기록할 테이블 설정

EXEC sys.sp_cdc_enable_table  

@source_schema = N'play', -- 스키마

@source_name   = N'users', -- 테이블명

@role_name     =  NULL, 

@capture_instance =  N'sync_portal', -- 기록될 이름

@supports_net_changes = 1, --  캡처 인스턴스에 순 변경 함수 생성

@captured_column_list = N'user_id,x_user_id, manager_id,grade, recommand_level', -- 변경시 등록되는 컬럼명

@filegroup_name = N'PRIMARY'

go

--메시지 22943, 수준 16, 상태 1, 프로시저 sp_cdc_enable_table_internal, 줄 313

--순 변경 추적에 대한 행을 고유하게 식별하는 데 사용되는 열이 캡처된 열 목록에 포함되어 있어야 합니다. 원본 테이블의 기본 키 열이나 매개 변수 @index_name에 지정된 인덱스에 대해 정의된 열을 캡처된 열 목록에 추가한 후 작업을 다시 시도하십시오.

--> pk   @captured_column_list에 들어가 있어야 한다.

--exec sys.sp_cdc_disable_table -- Table 해지

--@source_schema = N'play',  

--@source_name   = N'users',

--@capture_instance =N'sync_portal' 

select * from cdc.change_tables -- 등록된 cdc 내역 확인

select * from cdc.sync_portal_CT order by __$start_lsn desc -- cdc에서 쓰여진 내역 확인 (* 해당이름_CT 형태로 호출해야 한다.)

--  열 __$operation은 변경 내용과 연결된 작업 기록. 1 = 삭제, 2 = 삽입, 3 = 업데이트(이미지 이전) 및 4 = 업데이트(이미지 이후)

-- 2. 테스트

-- 테스트 값 등록

--select top(100) x_user_id,userid,grade,grade,* from db1.play.users a order by a.user_id desc

select top(100) x_user_id,manager_id,userid,grade,grade,* from db1.play.users a  where user_id = 12345 

select top(100) user_id,manager_id,grade,* from  [LOGIN_SERVER].Vanilla.dbo.Users  a     where user_id = 12345 

select count(*) from db1.cdc.sync_portal_CT

select a.x_user_id, a.manager_id, a.grade   from (select ROW_NUMBER() over(partition by x_user_id order by __$start_lsn desc) as irow, __$start_lsn,  x_user_id, manager_id, grade  from cdc.sync_portal_CT with(nolock) where __$operation =4)  a where irow = 1  

 

update db1.play.users set manager_id = 111673  where x_user_id = 12345   

update db1.play.users set grade = 2  where x_user_id = 12345   

update [LOGIN_SERVER].Vanilla.dbo.Users  set user_nm = getdate()  where user_id = 12345  

-- exec dbo.sp_users_sync

-- 테스트 결과 확인 (아이디별 최종 변경된 값만 확인)

-- 3. 프로시져 생성 (cdc 기능 동기화, 아이디별 최종 변경된 값만 적용 ) 

create procedure dbo.sp_users_sync

with encryption 

as 

begin

 begin try

  declare @t_user_sync table(t_x_user_id bigint, t_manager_id bigint, t_grade smallint);

  -- 테이블 변수에 변경된 정보 넣기

  insert into @t_user_sync 

  select a.x_user_id, a.manager_id, a.grade   from (select ROW_NUMBER() over(partition by x_user_id order by __$start_lsn desc) as irow, __$start_lsn,  x_user_id, manager_id, grade  from cdc.sync_portal_CT with(nolock) where __$operation =4)  a where irow = 1  

 

  --select * from  @t_user_sync 

  -- 링크서버 변경

  if((select count(t_x_user_id) from @t_user_sync)  > 0)

  begin 

   update a

   set  a.manager_id = b.t_manager_id, a.grade= b.t_grade,  a.recommand_last_update = getdate() 

   from

    [LOGIN_SERVER].Vanilla.dbo.Users   AS a

    inner join @t_user_sync AS b

    on a.user_id = b.t_x_user_id 

   --where a.user_id = b.t_x_user_id

    

   truncate table  cdc.sync_portal_CT 

  end

  

 end try 

 begin catch 

  IF @@trancount > 0 

  begin 

   ROLLBACK TRANSACTION; 

     end 

 end catch

end

go

grant exec  on dbo.sp_users_sync to public

go

-- 4. 스케줄러 등록

스케줄러로 위 프로시저(dbo.sp_users_sync) 반복