SQL Replication is one of my favorite things about SQL Server. For the most part, and as long as it’s properly maintained, it’s rock solid! It’ll just churn along without too much fuss. In this post, we’ll show you how to add a subscription with just a script. Why? There’s two main reasons:
- The default UI for adding a subscription is too screen-y! There’s too many steps for something that is relatively simple.
- If you’re sending data to a Always-On subscriber, you can’t even use they UI! That’s because the wizard will perform some validation on the host name. Since your AG listener is different from your nodes, the listener name will fail the validation since it isn’t a “true” host.
So the actual solution here is to use a script! It always surprises me that SSMS doesn’t offer an out-of-box option to generate this script, like it does in so many options. Lucky for you, I have just the script you need, right here:
--RUN THIS FROM YOUR PUBLISHER DATABASE!
--Not from the distribution or subscriber database
USE [Publishing database]
DECLARE @DBName sysname
DECLARE @PubName sysname
DECLARE @SubDB sysname
DECLARE @Subscriber sysname
SELECT @DBName = DB_NAME() --current server
DECLARE @pubName2 sysname = 'YourPubName' --publication name
SELECT @PubName = COALESCE(@PubName, @pubName2)
SELECT @SubDB = 'YourSubscriptionDatabase' --subscription database
SELECT @Subscriber = 'YourSubscriptionServer' --server name
IF (SELECT COUNT(name) FROM dbo.syspublications) =1
SELECT @PubName = name FROM dbo.syspublications
ELSE
IF @pubName2 IS null
BEGIN
PRINT 'This database has multiple pubs! You need to set the variable of @pubname2 above!'
RETURN
END
IF (DB_NAME() <> @DBName)
BEGIN
PRINT 'you are in the wrong database!'
RETURN
end
DECLARE @error VARCHAR(100)
SELECT @error = 'The publication ' + COALESCE(@PubName,@pubName2) + ' does not exist!'
IF NOT EXISTS (
SELECT 1 FROM dbo.syspublications WHERE name = @PubName)
BEGIN
PRINT @error
RETURN
end
--No more validation, this is the real thing!
--First we add the subscription
EXEC sp_addsubscription @publication = @PubName,
@subscriber = @Subscriber,
@destination_db = @SubDB,
@subscription_type = N'Push',
@sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0;
--This is for push subscriptions only!
EXEC sp_addpushsubscription_agent @publication = @pubname,
@subscriber = @Subscriber,
@subscriber_db = @SubDB,
@job_login = 'LogReaderServiceAccount', @job_password = 'LogReaderServiceAccountPassword'
, @subscriber_security_mode = 1;
GO
The script starts with a bunch of data validations: This is just to ensure that you’re in the correct database, and that the pub exists. Once we complete the validation, we add the subscription to the proper system tables, and then we create the actual SQL Agent job that pushes the data to that particular subscriber. This script is meant only for push subscriptions, where the jobs run from a centralized replication server. This differs from a pull subscription, where the pull jobs run from the subscribers. Neither approach is “better”, it just really depends on your environment.
