none
distribution..MSreplservers table after SQL Server 2016 SP2 CU3 update

    Question

  • Hi Guys

    We recently faced an issue with some of our customers where they were unable to install SQL Server replication properly through our software. We have a .NET based component which is used by our software to install SQL replication. Internally this component uses SQL queries / stored procedure calls to install the replication. The problem we noted with these customers is that out software was unable to detect the publication on these customer systems. To detect the publication, we use a SQL like the one below.

    IF exists (select *from sys.databases where name ='distribution') 
    select top 1 publication from distribution..MSpublications p, distribution..MSsubscriptions s, sys.servers 
    where p.publication_id=s.publication_id and p.publisher_id=s.publisher_id 
    and (server_id = s.publisher_id or server_id=s.subscriber_id) and name = '<<server name>>' and s.publisher_db like '<<database>>' 
    ELSE Select 'NotExist'

    This SQL is supposed to return the publication name for the database specified in <<database>> that has the <<server name>> as the publisher. This SQL does not work for these customers and does not return the publication name. After investigation we found that these customers are using SQL Server 2016 SP2 CU3 or higher. What breaks our SQL is the fact that the publisher_id column of distribution..MSpublications table no more matches with the expected server_id value of sys.servers. In earlier versions of SQL Server, the publisher_id used to match the server_id value that corresponds to the  sys.servers record for <<server name>> (i.e. the publisher SQL server). Instead, the the publisher_id now matches the server_id value that corresponds to the sys.servers record for repl_distributor.

    With further investigation we can now see that the publisher_id column of distribution..MSpublications table now points to srvid of distribution..MSreplservers table. The srvid of distribution..MSreplservers table corresponds to the record for <<server name>> (i.e. the publisher SQL server). I can see some blog posts that suggest this is related to a distribution database in an Availability Group. However, we do not have any Availability Groups configured. I have tested this myself on a fresh SQL Server 2016 SP2 CU3 setup where no Availability Groups have been configured explicitly.

    If we have an existing replication on SQL Server 2016 version earlier than SP2 CU3, and then we upgrade to SP2 CU3 (or higher), the distribution..MSreplservers table is still created during the upgrade. However, in this case the table has a srvid that matches the pre-upgrade publisher_id value. So our SQL continues to work in this case because the srvid has a value that matches our expected record in sys.servers.

    The distribution..MSreplservers table does not exist in earlier versions of SQL Server 2016. I cannot find any Microsoft documentation that discusses the distribution..MSreplservers table. I also do not see any explanation as to why Microsoft decided to do such a change that could break existing implementations. I wonder if anyone here has any clues?

    Thanks



    • Edited by nhameed Monday, May 13, 2019 4:52 AM Missed some content
    Monday, May 13, 2019 4:36 AM