Copy -- ================================================================
-- StackSync CDC Setup Script
--
-- Run this ONCE as a database administrator with sysadmin privileges.
--
-- StackSync automatically manages CDC using capture instances named:
-- stacksync_{app_id}_{table_name}
--
-- This allows multiple StackSync apps to use CDC on the same tables.
-- SQL Server supports maximum 2 capture instances per table.
-- ================================================================
USE [YOUR_DATABASE];
GO
-- ================================================================
-- Step 1: Enable CDC on database
-- ================================================================
EXEC sys.sp_cdc_enable_db;
GO
-- ================================================================
-- Step 2: Create helper procedures for StackSync
-- ================================================================
-- Drop existing procedures if they exist
IF OBJECT_ID ( 'SCHEMA.sp_stacksync_enable_cdc_db' , 'P' ) IS NOT NULL
DROP PROCEDURE SCHEMA.sp_stacksync_enable_cdc_db;
GO
IF OBJECT_ID ( 'SCHEMA.sp_stacksync_enable_cdc_table' , 'P' ) IS NOT NULL
DROP PROCEDURE SCHEMA.sp_stacksync_enable_cdc_table;
GO
IF OBJECT_ID ( 'SCHEMA.sp_stacksync_disable_cdc_table' , 'P' ) IS NOT NULL
DROP PROCEDURE SCHEMA.sp_stacksync_disable_cdc_table;
GO
IF OBJECT_ID ( 'SCHEMA.sp_stacksync_disable_cdc_db' , 'P' ) IS NOT NULL
DROP PROCEDURE SCHEMA.sp_stacksync_disable_cdc_db;
GO
-- Create procedure to enable CDC on database
CREATE PROCEDURE SCHEMA.sp_stacksync_enable_cdc_db
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @is_cdc_enabled BIT ;
DECLARE @database_name NVARCHAR ( 128 ) = DB_NAME ();
SELECT @is_cdc_enabled = is_cdc_enabled
FROM sys.databases
WHERE name = @database_name;
IF @is_cdc_enabled = 1
BEGIN
RETURN 0 ;
END
EXEC sys.sp_cdc_enable_db;
RETURN 0 ;
END ;
GO
-- Create procedure to enable CDC on table
CREATE PROCEDURE SCHEMA.sp_stacksync_enable_cdc_table
@source_schema NVARCHAR ( 128 ),
@source_name NVARCHAR ( 128 ),
@capture_instance NVARCHAR ( 128 ) = NULL ,
@supports_net_changes BIT = 1 ,
@role_name NVARCHAR ( 128 ) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @instance_exists INT ;
IF @capture_instance IS NULL
SET @capture_instance = @source_schema + '_' + @source_name;
IF LEN (@capture_instance) > 100
SET @capture_instance = LEFT (@capture_instance, 100 );
-- Check if THIS specific capture instance already exists
-- (Not just if table is tracked - we support up to 2 capture instances per table)
SELECT @instance_exists = COUNT ( * )
FROM cdc.change_tables
WHERE capture_instance = @capture_instance;
IF @instance_exists > 0
BEGIN
-- This specific capture instance already exists, nothing to do
RETURN 0 ;
END
-- Enable CDC with the specified capture instance name
-- SQL Server supports up to 2 capture instances per table
EXEC sys.sp_cdc_enable_table
@source_schema = @source_schema,
@source_name = @source_name,
@role_name = @role_name,
@supports_net_changes = @supports_net_changes,
@capture_instance = @capture_instance;
RETURN 0 ;
END ;
GO
-- Create procedure to disable CDC on table
CREATE PROCEDURE SCHEMA.sp_stacksync_disable_cdc_table
@source_schema NVARCHAR ( 128 ),
@source_name NVARCHAR ( 128 ),
@capture_instance NVARCHAR ( 128 ) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON ;
IF @capture_instance IS NULL
SET @capture_instance = @source_schema + '_' + @source_name;
IF LEN (@capture_instance) > 100
SET @capture_instance = LEFT (@capture_instance, 100 );
EXEC sys.sp_cdc_disable_table
@source_schema = @source_schema,
@source_name = @source_name,
@capture_instance = @capture_instance;
RETURN 0 ;
END ;
GO
-- Create procedure to disable CDC on database
CREATE PROCEDURE SCHEMA.sp_stacksync_disable_cdc_db
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON ;
EXEC sys.sp_cdc_disable_db;
RETURN 0 ;
END ;
GO
-- ================================================================
-- Step 3: Grant permissions to StackSync user
-- ================================================================
GRANT EXECUTE ON SCHEMA.sp_stacksync_enable_cdc_db TO [USERNAME];
GRANT EXECUTE ON SCHEMA.sp_stacksync_enable_cdc_table TO [USERNAME];
GRANT EXECUTE ON SCHEMA.sp_stacksync_disable_cdc_table TO [USERNAME];
GRANT EXECUTE ON SCHEMA.sp_stacksync_disable_cdc_db TO [USERNAME];
GO
-- Read CDC metadata and change data
GRANT SELECT ON SCHEMA ::cdc TO [USERNAME];
PRINT 'Granted SELECT on cdc schema' ;
GO
-- Query DMVs for partitioning and row counts
GRANT VIEW DATABASE STATE TO [USERNAME];
PRINT 'Granted VIEW DATABASE STATE' ;
GO
-- Read user tables and system catalog
ALTER ROLE db_datareader ADD MEMBER [USERNAME];
PRINT 'Added to db_datareader role' ;
GO
-- Write to user tables (for bidirectional sync)
ALTER ROLE db_datawriter ADD MEMBER [USERNAME];
PRINT 'Added to db_datawriter role' ;
GO
-- Create temporary tables during write operations
GRANT CREATE TABLE TO [USERNAME];
PRINT 'Granted CREATE TABLE' ;
GO
-- Drop temporary tables after write operations
GRANT ALTER ON SCHEMA :: SCHEMA TO [USERNAME];
PRINT 'Granted ALTER on schema' ;
GO
-- Required: To Check SQL Server Agent status
GRANT VIEW SERVER STATE TO [USERNAME];
PRINT 'Granted VIEW SERVER STATE' ;
GO
