Replicating Microsoft SQL Server data

Supported versions

You can use the following options for a Microsoft SQL Server connection:

• Microsoft SQL Server 2019 or later (Enterprise and Standard editions)
• Azure SQL Managed Instance

Restrictions

• You can replicate only some Microsoft SQL Server data types. See Supported Microsoft SQL Server data types.
• Microsoft SQL Server can only be used as a source for Data Replication.

Configuring Microsoft SQL Server as a source

If you have the sysadmin permission, complete the following steps to configure Microsoft SQL Server as a replication source.

1. Grant the following permissions to a user who is in the DBOWNER group:

   • USE[master]
   • GRANT VIEW SERVER PERFORMANCE STATE TO [user]

2. Change data capture (CDC) uses the SQL Server Agent to log insertions, updates, and deletions that occur in a table and makes these data changes accessible in a relational format. When a database has CDC enabled, the cdc schema, cdc user, metadata tables, and other system objects are created for the database. Make sure you don't have a schema or user named cdc. CDC fails if a schema or user named cdc already exists. Use the following command to enable CDC at the database level:

   EXEC sys.sp_cdc_enable_db
   

3. If you want DDL triggers to be created automatically at the database level, then grant the ALTER ANY DATABASE DDL TRIGGER permission. If you don't want to grant this permission, then create a DDL trigger manually by completing the following steps:

   1. Set the database context:

   USE [database]
   GO
   

   2. Configure Microsoft SQL Server settings:

   SET ANSI_NULLS ON
   GO
   
   SET QUOTED_IDENTIFIER ON
   GO
   

   3. Create the DDL history table:

   CREATE TABLE ibm_ddl_history_v1
   (
        Id int identity NOT NULL,
        EventType nvarchar(50) NOT NULL,
        DdlTime datetime2(2) DEFAULT SYSDATETIME(),
        DatabaseName nvarchar(128) NOT NULL,
        ObjectId int,
        SchemaName nvarchar(128) NOT NULL,
        ObjectName nvarchar(128) NOT NULL,
        ObjectType nvarchar(100) NOT NULL,
        TSQLCommand nvarchar(max) NOT NULL,
        EventData xml NOT NULL
   );
   

   4. Create the DDL trigger:

   CREATE TRIGGER [IBM_DATA_REPLICATION_DDL_EVENT_TRIGGER_V1]
   ON DATABASE
   FOR DDL_TABLE_EVENTS, RENAME, DDL_INDEX_EVENTS, ALTER_SCHEMA
   AS
   SET NOCOUNT ON;
   SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;
   
   DECLARE
        @EventData xml
        , @EventType nvarchar(50)
        , @TSQLCommand nvarchar(max)
        , @DdlTime datetime2(2)
        , @DatabaseName nvarchar(128)
        , @SchemaName nvarchar(128)
        , @ObjectName nvarchar(128)
        , @NewObjectName nvarchar(128)
        , @NewSchemaName nvarchar(128)
        , @ObjectType nvarchar(100)
        , @ObjectId int
   
   DECLARE @DDL_HISTORY_TABLE nvarchar(128) = 'ibm_ddl_history_v1'
   
   SET @EventData = EVENTDATA()
   SET @EventType = @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(50)' )
   SET @TSQLCommand = @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)' )
   SET @DdlTime = @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime2(2)' )
   SET @DatabaseName = DB_NAME()
   SET @SchemaName = CASE WHEN (COALESCE(@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname' ), '** no schema **') = '') THEN '** no schema **' ELSE COALESCE(@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname' ), '** no schema **') END
   SET @ObjectName = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname' )
   SET @ObjectType = @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'sysname' )
   SET @ObjectId = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName))
   
   IF @EventType = 'RENAME'
   BEGIN
       SET @NewObjectName = @EventData.value('(/EVENT_INSTANCE/NewObjectName)[1]', 'sysname' )
        SET @ObjectId = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@NewObjectName))
   END
   
   IF @SchemaName <> 'cdc'
   BEGIN
        -- Check for protected table drop first
        IF @EventType = 'DROP_TABLE'
        BEGIN
           IF (@ObjectName = @DDL_HISTORY_TABLE AND @SchemaName = 'dbo')
           BEGIN
               RAISERROR('This table %s.%s is used by IBM Data Replication on Cloud and cannot be deleted. To delete it, first disable/drop the trigger IBM_DATA_REPLICATION_DDL_EVENT_TRIGGER_V1, but be aware that doing so will disrupt IBM Data Replication on Cloud functionality.', 16, 1, @SchemaName, @ObjectName);
               ROLLBACK;
                RETURN;
            END
       END
   
        -- Log DDL event
        INSERT INTO dbo.ibm_ddl_history_v1(EventType, DdlTime, DatabaseName, ObjectId, SchemaName, ObjectName, ObjectType, TSQLCommand, EventData)
        VALUES(@EventType, @DdlTime, @DatabaseName, @ObjectId, @SchemaName, @ObjectName, @ObjectType, @TSQLCommand, @EventData);
   
        DELETE FROM dbo.ibm_ddl_history_v1 where Id = SCOPE_IDENTITY();
   END
    GO
   

   5. Enable the trigger:

   ENABLE TRIGGER [IBM_DATA_REPLICATION_DDL_EVENT_TRIGGER_V1] ON DATABASE
   GO
   

Connecting to Microsoft SQL Server in a project

For Credentials and Certificates, you can use secrets if a vault is configured for the platform and the service supports vaults. For information, see Using secrets from vaults in connections.

To connect to Microsoft SQL Server in a project, see Microsoft SQL Server connection.

Creating a replication asset with Microsoft SQL Server

To create a replication asset, make sure that you understand the requirements and configuration options. Then, complete the procedure.