SQL Server

Two-way sync between SQL Server 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 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.

  
  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('YOUR_SCHEMA.sp_stacksync_enable_cdc_db', 'P') IS NOT NULL
      DROP PROCEDURE YOUR_SCHEMA.sp_stacksync_enable_cdc_db;
  GO
  
  IF OBJECT_ID('YOUR_SCHEMA.sp_stacksync_enable_cdc_table', 'P') IS NOT NULL
      DROP PROCEDURE YOUR_SCHEMA.sp_stacksync_enable_cdc_table;
  GO
  
  IF OBJECT_ID('YOUR_SCHEMA.sp_stacksync_disable_cdc_table', 'P') IS NOT NULL
      DROP PROCEDURE YOUR_SCHEMA.sp_stacksync_disable_cdc_table;
  GO
  
  IF OBJECT_ID('YOUR_SCHEMA.sp_stacksync_disable_cdc_db', 'P') IS NOT NULL
      DROP PROCEDURE YOUR_SCHEMA.sp_stacksync_disable_cdc_db;
  GO
  
  -- Create procedure to enable CDC on database
  CREATE PROCEDURE YOUR_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 YOUR_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 @is_tracked BIT;
  
      IF @capture_instance IS NULL
          SET @capture_instance = @source_schema + '_' + @source_name;
  
      IF LEN(@capture_instance) > 100
          SET @capture_instance = LEFT(@capture_instance, 100);
  
      SELECT @is_tracked = is_tracked_by_cdc
      FROM sys.tables t
      INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
      WHERE t.name = @source_name AND s.name = @source_schema;
  
      IF @is_tracked = 1
      BEGIN
          RETURN 0;
      END
  
      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 YOUR_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 YOUR_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 YOUR_SCHEMA.sp_stacksync_enable_cdc_db TO [YOUR_USERNAME];
  GRANT EXECUTE ON YOUR_SCHEMA.sp_stacksync_enable_cdc_table TO [YOUR_USERNAME];
  GRANT EXECUTE ON YOUR_SCHEMA.sp_stacksync_disable_cdc_table TO [YOUR_USERNAME];
  GRANT EXECUTE ON YOUR_SCHEMA.sp_stacksync_disable_cdc_db TO [YOUR_USERNAME];
  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]