大网数据广告活动
数据库与应用安全

Troubleshoot Issues Configuring Transactional Replication on Multiple SQL Server Linux Containers

· 52CE 我爱测

Learn how to fix duplicate subscription errors when setting up transactional replication on SQL Server Linux containers.

This article provides troubleshooting guidance for an issue that occurs when you configure two Microsoft SQL Server-based servers that run in Linux containers on the same computer as replication subscribers.

Symptoms

When you configure Transactional Replication on SQL Server 2022 running in Linux containers, you receive the following error message:

The subscription already exists.

This issue usually occurs when:

  • Two SQL Server instances are hosted in separate containers on the same Linux-based computer.
  • Both instances are added as subscribers to the same publisher.
  • The connection strings use the same hostname but different port numbers (for example, LINUXHOST,5455 and LINUXHOST,5465).

Cause

This issue occurs because subscriber metadata doesn't honor port numbers during persistence. Only the hostname is stored. This behavior causes the second subscriber to be seen as a duplicate of the first.

This behavior is specific to Linux container environments in which named instances don't exist and connections rely on hostname and port mapping.

Workaround

To successfully add multiple subscribers on the same computer but in different containers, follow these steps:

  1. Install Cumulative Update 20 (CU20) for SQL Server 2022.

  2. Enable trace flag 15005. This trace flag enables you to use a subscriber that has a nondefault port for transactional replication.

  3. Perform the following manual cleanup and configuration steps in Transact-SQL. In this script, replace the @hostname, @port_sub1, @port_sub2, @PublicationName, @SubscriberDb_Sub1, and @SubscriberDb_Sub2 variables with values that are specific to your environment:

    DECLARE @hostname AS SYSNAME = 'LinuxSubscriberHostName',
            @port_sub1 AS NVARCHAR (10) = '5455',
            @port_sub2 AS NVARCHAR (10) = '5465',
            @host_and_port_sub1 AS NVARCHAR (100),
            @host_and_port_sub2 AS NVARCHAR (100),
            @PublicationName AS SYSNAME = 'PublicationName',
            @SubscriberDb_Sub1 AS SYSNAME = 'SubscriberDB1',
            @SubscriberDb_Sub2 AS SYSNAME = 'SubscriberDB2';
    
    -- Concatenate the hostname and port for each subscriber (for example 'LinuxSubscriberHostName,5455')
    SET @host_and_port_sub1 = CONCAT(@hostname, ',', @port_sub1);
    SET @host_and_port_sub2 = CONCAT(@hostname, ',', @port_sub2);
    
    -- Step 1: Remove subscription metadata
    USE [PublicationDb];
    
    EXECUTE sp_dropsubscription
        @publication = @PublicationName,
        @article = 'all',
        @subscriber = @hostname,
        @destination_db = @SubscriberDb_Sub1;
    
    -- Step 2: Remove subscriber from publication
    EXECUTE sp_dropsubscriber @subscriber = @hostname;
    
    -- Step 3: Remove server entry for the first subscriber with no port information captured (on Publisher side)
    DELETE FROM distribution..MSreplservers
    WHERE UPPER(srvname COLLATE database_default) = UPPER(@hostname COLLATE database_default);
    
    IF EXISTS (SELECT 1 FROM sys.servers WHERE name = @hostname)
        EXECUTE sp_dropserver @hostname, 'droplogins';
    
    -- Step 4: Re-add linked server on Publisher side
    EXECUTE sp_addlinkedserver @server = @host_and_port_sub1;
    EXECUTE sp_serveroption @server = @host_and_port_sub1, @optname = 'sub', @optvalue = true;
    
    EXECUTE sp_addlinkedserver @server = @host_and_port_sub2;
    EXECUTE sp_serveroption @server = @host_and_port_sub2, @optname = 'sub', @optvalue = true;
    
    --Ensure entries are successful
    SELECT * FROM master..sysservers;
    
    --Step 5. Insert information about the subscribers to the Msreplservers table.
    INSERT INTO [distribution]..MSreplservers (srvid, srvname)
    SELECT srvid, srvname
    FROM master.dbo.sysservers
    WHERE UPPER(srvname COLLATE database_default) = UPPER(@host_and_port_sub1 COLLATE database_default);
    
    INSERT INTO [distribution]..MSreplservers (srvid, srvname)
    SELECT srvid, srvname
    FROM master.dbo.sysservers
    WHERE UPPER(srvname COLLATE database_default) = UPPER(@host_and_port_sub2 COLLATE database_default);
    
    --Verify entries
    SELECT * FROM [distribution]..MSreplservers;
    
    --Step 6. Create subscribers (push subscription)
    -----------------BEGIN: Script to be run at Publisher @hostname-----------------;;
    
    --Create subscriber to the first Linux container
    USE [PublicationDb];
    
    EXECUTE sp_addsubscription
        @publication = @PublicationName,
        @subscriber = @host_and_port_sub1,
        @destination_db = @SubscriberDb_Sub1,
        @subscription_type = N'Push',
        @sync_type = N'automatic',
        @article = N'all',
        @update_mode = N'read only',
        @subscriber_type = 0;
    
    EXECUTE sp_addpushsubscription_agent
        @publication = @PublicationName,
        @subscriber = @host_and_port_sub1,
        @subscriber_db = @SubscriberDb_Sub1,
        @job_login = NULL,
        @job_password = NULL,
        @subscriber_security_mode = 1,
        @frequency_type = 64,
        @frequency_interval = 0,
        @frequency_relative_interval = 0,
        @frequency_recurrence_factor = 0,
        @frequency_subday = 0,
        @frequency_subday_interval = 0,
        @active_start_time_of_day = 0,
        @active_end_time_of_day = 235959,
        @active_start_date = 20250711,
        @active_end_date = 99991231,
        @enabled_for_syncmgr = N'False',
        @dts_package_location = N'Distributor';
    
    --Create subscriber to the second Linux container
    USE [PublicationDb];
    
    EXECUTE sp_addsubscription
        @publication = @PublicationName,
        @subscriber = @host_and_port_sub2,
        @destination_db = @SubscriberDb_Sub2,
        @subscription_type = N'Push',
        @sync_type = N'automatic',
        @article = N'all',
        @update_mode = N'read only',
        @subscriber_type = 0;
    
    EXECUTE sp_addpushsubscription_agent
        @publication = @PublicationName,
        @subscriber = @host_and_port_sub2,
        @subscriber_db = @SubscriberDb_Sub2,
        @job_login = NULL,
        @job_password = NULL,
        @subscriber_security_mode = 1,
        @frequency_type = 64,
        @frequency_interval = 0,
        @frequency_relative_interval = 0,
        @frequency_recurrence_factor = 0,
        @frequency_subday = 0,
        @frequency_subday_interval = 0,
        @active_start_time_of_day = 0,
        @active_end_time_of_day = 235959,
        @active_start_date = 20250711,
        @active_end_date = 99991231,
        @enabled_for_syncmgr = N'False',
        @dts_package_location = N'Distributor';
    

Resolution

Currently, no permanent fix is available. Instead, see the "Workaround" section.

The product team is evaluating potential fixes for future releases. This article will be updated when a solution becomes available.

Related content

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 11/03/2025
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。