select is_cdc_enabled, * from sys.databases
go
use db1
go
exec sys.sp_cdc_enable_db
go
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
select * from cdc.change_tables
select * from cdc.sync_portal_CT order by __$start_lsn 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
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
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
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
스케줄러로 위 프로시저(dbo.sp_users_sync) 반복
