SQL Server (MSSQL)

Two-way sync between Microsoft SQL Server (MSSQL) and your other systems.

Introduction

SQL Server is Microsoft's enterprise relational database management system known for high performance, advanced security features, and scalability. Stacksync enables seamless data synchronization between Microsoft SQL Server and other CRM and database systems.

Things to keep in mind

During setup

• All SQL Server tables must have a (single) primary key We use the primary key to keep your data in sync. The primary key must be a single column; composite primary keys are not supported.

• Primary keys must be auto-generated Ensure your primary keys are auto-generated by your database (e.g. use IDENTITY for primary keys).

• Check user permissions Verify that the SQL Server user used by Stacksync to connect to your database has sufficient privileges (e.g., SELECT, INSERT, UPDATE, and DELETE) on the tables you want to map. Additionally, the user must have EXECUTE permission on the StackSync CDC wrapper procedures (created during one-time setup).

• Native CDC Setup SQL Server connector uses Native Change Data Capture (CDC) for efficient change tracking. A database administrator must run a one-time setup script to enable CDC and create wrapper procedures. To enable CDC functionality, the DBA should execute the CDC setup script. See the setup instructions below.

• Why CDC Setup is Required: Stacksync uses SQL Server's built-in Change Data Capture to track data changes. Since enabling CDC requires elevated privileges, a DBA must run a setup script once. After this setup, your regular Stacksync user can enable CDC on tables without needing sysadmin privileges.

• CDC Setup Script: The setup script must be executed by a user with sysadmin privileges.

  -- ================================================================
  -- 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
  
  

• SQL Server Agent SQL Server Agent must be running for CDC to capture changes:

  • On-premise: Start SQL Server Agent via SQL Server Configuration Manager

  • AWS RDS: Enable via RDS Parameter Group setting agent XPs = 1

  • Azure SQL Database: CDC is managed automatically

Important Considerations

• Renaming schemas, tables, or columns will break Stacksync configuration. If you need to rename objects, refer to the Update Sync Configuration guide for remediation steps.

• Schema Names: SQL Server supports multiple schemas per database. Ensure you specify the correct schema when setting up your connection (default is dbo).

Troubleshooting

If tables don't appear in Stacksync during setup, one of the above requirements is likely unmet. Common issues include:

• Missing primary keys or composite primary keys

• Insufficient user permissions

• CDC wrapper procedures not created (setup script not run)

• SQL Server Agent not running

For diagnostic queries and troubleshooting steps, refer to the SQL Server Snippets page.

Feel free to reach out to us at [email protected]