添加Distributor失败

 

上周做了一个case，客户无法为SQL Server instance配置remote distributor。 下面分享一下排查问题的过程，希望对您排查类似的问题所有帮助。

 

客户的环境中的SQL Server均为sql server 2012 RTM.

Distributor server: SQL108W2K8R22

Publisher: SQL108W2K8R21

Subscriber: SQL108W2K8R23

SQL108W2K8R23为SQL108W2K8R21的订阅服务器，模式为push.

现在要为SQL108W2K8R23创建发布，将SQL108W2K8R22添加为分发服务器时出错。

 

配置时会出现下面的错误：

Error 21670只是一般性的登录错误：Connection to server [%s] failed.没什么价值

于是尝试手工添加：exec sp_adddistributor @distributor = N'SQL108W2K8R22', @password = N'StrongPassword'

得到下面的错误：

Msg 18483, Level 14, State 1, Line 1

Could not connect to server 'SQL108W2K8R22' because 'distributor_admin' is not defined as a remote login at the server. Verify that you have specified the correct login name.

 

搜索了错误信息，得到下面这篇 KB

http://support.microsoft.com/kb/818334

按照KB上进行检查，发现这三台SQL SERVER的@@servername和hostname都是匹配的，这篇KB没有起到效果。

 

确认在添加前，distributor SQL108W2K8R22已经将SQL108W2K8R23添加为发布服务器。

在distributor里找到了名称为SQL108W2K8R23的linked server，也可以在sys.servers查看到相应的记录

在distributor中的sys.link_logins中也包含distributor_admin相应的记录。

 

虽然 SQL108W2K8R23添加remote distributor失败，但仍然创建名为出[repl_distributor]的linked server。

于是在distributor和SQL108W2K8R23上同时抓取trace，看看添加remote distributor(sp_adddistributor)时究竟发生了什么。

在SQL108W2K8R23的trace中，发现SQL108W2K8R23通过linked server repl_distributor向distributor 提交一个查询，用于比对distributor 上SQL 版本。

EXEC @retcode = repl_distributor.master.sys.sp_executesql N'select @dist_ver = @@microsoftversion', N'@dist_ver bigint output', @dist_ver output

这个语句是添加distributor操作中的最后一个语句。

查看distributor上的trace，发现distributor上没有接收到任何语句，只有之前提到的报错信息

        

 

看起来linked server可以工作，成功地将语句发送到了target server（distributor），可能是linked server有些信息没有传递给distributor或者是distributor缺失了一些metadata。但检查了linked server的属性，没有发现异常。但如果是metadata丢失的话，那么删掉重建应该是不错的选择：

于是尝试将SQL108W2K8R23从distributor的publisher list中删除，并再次添加. 但在删除的时候出现如下错误：

 

 

真是诸事不顺！打开profiler trace，发现UI操作实际上执行的是exec sp_dropdistpublisher @publisher = N'SQL108W2K8R21',    @no_checks=1

于是手工执行了一把，得到了同样的错误：

 

Msg 20584, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 67

Cannot drop server 'SQL108W2K8R21' because it is used as a Subscriber to remote Publisher 'SQL108W2K8R23' in replication.

 

在sp_dropdistpublisher内部，主要会调用两个存储过程：

       sys.sp_dropremotelogin    @publisher,'distributor_admin','distributor_admin'

 

 sys.sp_dropserver @publisher, 'droplogins'

 

sp_dropremotelogin的作用如下：

Removes a remote login mapped to a local login used to execute remote stored procedures against the local server running SQL Server

 

而sp_dropserver内部调用了sp_MSrepl_check_server，检查（distribution.dbo. MSsubscriber_info）将要删除的publisher是否同时也是一个订阅。如果是，就抛出异常，然后退出。

不幸的是，sp_dropremotelogin和sp_dropserver是单独执行的，并不在一个事务里。所以虽然最终执行失败，但sp_dropremotelogin造成的影响却没有回滚。

 

将distribution.dbo. MSsubscriber_info里的相关记录删除。

然后将publisher删除后重建，之后添加distributor的操作就成功了J

 

 

扩展：

1）

sp_dropremotelogin：Removes a remote login mapped to a local login used to execute remote stored procedures against the local server running SQL Server

sp_addremotelogin：Adds a new remote login ID on the local server. This enables remote servers to connect and execute remote procedure calls.

 

MSsubscriber_info ：it contains one row for each Publisher/Subscriber pair that is being pushed subscriptions from the local Distributor. This table is stored in the distribution database.

这些存储过程和表都是sql server 2000时代的产物，用于linked server相关操作。在2005里就已经depreciated。

 

2）后来分析了内部的调用逻辑，实际上只需要在distributor执行sp_addremotelogin就可以解决这个问题，没有必要重建. 如果您以后遇到了下面这个错误，可以尝试在distributor中执行sp_addremotelogin。

Could not connect to server 'SQL108W2K8R22' because 'distributor_admin' is not defined as a remote login at the server. Verify that you have specified the correct login name.

 

3）如果在移除publisher时遇到下面的错误，可以将distribution.. MSsubscriber_info的相关记录删除，然后再次尝试。

Cannot drop server 'server1' because it is used as a Subscriber to remote Publisher 'serve2' in replication.

 

4）您在SQL SERVER 2014版本的distributor中不会遇到第三个问题，这并因为不是sp_addDistributor或sp_dropdistpublisher在2014中有了逻辑上变更，而是在添加订阅的时候，不会在向distribution.. MSsubscriber_info插入记录。这样sp_MSrepl_check_server就不会抛出异常了。