not been designated as a valid Publisher
Today while walking customer through Replication + Always On combination we encountered various errors like
Msg 14080, Level 11, State 1, Procedure sp_MSvalidate_distpublisher, Line 17 [Batch Start Line 0]
The remote server “SQL502” does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
While other customer had independent set of Always On replicas for Publishers and Distributors, this customer was using same severs for Publisher and Distributor roles. This is possible as long as the Distributor is installed in its own SQL Server instance. Their default instance supports Publisher functionality while Distributor was installed on another set of SQL Server Named Instances on same servers.
-
Publisher and distributor need to be on separate SQL Server instances.
Using SQL Management Studio, we attempted a connection the Distributor’s Listener only to discovers a list of Publisher database appeared. Turns out every time we tried to connect the Distributor’s Listener, the connection was being redirected to the default instance running the Publisher role, not the Named Instance running the Distributor role.
Re-reading the same link above, discovered step we missed!
-
If the listener for the availability group hosting the distribution database is configured to use a non-default port, then its required to setup an alias for the listener and the non-default port.
Using SQL Server Configuration Manager, we created both 32bit and 64bit alias for the Distributor’s Listener. As you can see, both the Alias Name and Server are identical with Listener name while the Port No has Listeners TCP/IP port.
We created these 32\64 bit aliases on both Primary and Secondary Distributors.
Chris Skorlinski
SQL Server Escalation Services
