ReplTip – Publishing to Subscriber Alias

Should the need arise, you can publish to a Transactional Replication subscriber alias provided you setup the subscriber using scripts and not SQL Server Management Studio Replication Wizard.

First, verify client alias is configured to redirect connectivity to alias name. In example below SQL Server traffic to CHRISSK2012\SQL2K8_2 will be redirected to alias SUB. I created both 32bit and 64bit aliases using SQL Server Configuration Manager. I specified the “listen” port for that named instance.

I tested SQL Server Management Studio to ensure I could connect to the alias name.

Once publication is created, if I attempt to add subscription using SSMS Wizard I’ll get message below.

TITLE: Connect to Server
------------------------------
Cannot connect to SUB.
------------------------------
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, 'CHRISSK2012\SQL2K8_2'. (Replication.Utilities)
------------------------------

However, I can create a subscription to SUB using SQL commands as shown below. To get the sp_addsubscription syntax correct, I used the SSMS Add Subscription Wizard creating a subscription to the full server name ‘CHRISSK2012\SQL2K8_2’, scripted out the Publication\Subscription, dropped the subscription, edited the script below to replace server name with alias ‘SUB’ name, then ran script to add alias SUB as a subscriber.

— Adding the transactional subscriptions
use [Customer]
exec sp_addsubscription @publication = N’CcustomerPub’, @subscriber = N’SUB‘, @destination_db = N’Customer’, …
exec sp_addpushsubscription_agent @publication = N’CustomerPub’, @subscriber = N’SUB‘, @subscriber_db = N’Customer’,…
GO

This technique is same when connecting to a Subscriber which is part of an AlwaysOn Availability Group, except the subscriber alias is the “listener” name of the AG group.

Chris Skorlinski – Microsoft SQL Server Escalation Services