Archive | Replication RSS feed for this section

Replication Logging

20 Nov

At SQLPASS 2015 I attended a presentation by Kendal Van Dyke, in which he told us that it was possible to log the actions of Replication to an output file.

This was news to me, so I just had to have a closer look.

The Setup

This will use the same Publication that I created in my earlier articles (Subscription and Distributor), which has been running on my test instances ever since.

The Commands

Replication is controlled via Agent Jobs and this is where the output commands are used. Every job involved in Replication (Log Reader, Queue Reader and Distributor) can make use of them.

-Output [Filename] is the name of the text file that the output will be written to. If the file does not exist then it will be created, otherwise it will be appended to.

-OutputVerboseLevel [0|1|2] is the level of output required, with 0 being the lowest and 2 the highest.


Implementing this is quite easy – located the Agent jobs for the Publication of interest and add the commands to end of the job’s command line.

The appropriate Log Reader Agent and Queue Reader Agent can be found via the Replication Monitor. Open Replication Monitor, from any of the Publisher, Distributor or Subscriber servers and locate the ‘Agents’ tab from the appropriate Publication. From the ‘Agent types’ drop-down select either ‘Log Reader Agent’ or ‘Queue Reader Agent’, then right-click on the entry and select ‘Properties’.


This will show the same control that selecting the job under SSMS ‘SQL Server Agent/Jobs’ and ‘Properties’ of the appropriate Job would have shown, but removes any doubt you have located the correct Log Reader Agent.

Of course, if you know the job involved then you can just locate each from the SSMS ‘SQL Server Agent/Jobs’ list on the appropriate servers and update them directly. In my Pull Subscription I have to locate the Distributor Job myself within SSMS – it doesn’t appear as an option via Replication Monitor.

Once the parameters have been added the jobs need to be stopped and started again, so the new command-line can be read.

The Output

With OutputVerboseLevel set to 0 only basic details are written to the text files – various settings of the publication, server names, job names and suchlike. The Distributor produces these details every time it runs, so even this can mean quite a large document given enough time. This is useful for getting error messages with basic history details.

OutputVerboseLevel of 1 also shows statistics, in addition to connection details and other information. This of course increase the output from any job using this setting.

OutputVerboseLevel of 2 is the most detailed level of output. This shows the stored procedure calls, batch details, connections, disconnections and so on. It is very useful for a short period of time but quickly fills drive space. Use this when you need a great deal of detail for a short period of time.

Generating Replication Scripts – Things To Watch Out For

5 Feb

Scripting a Publication is useful for both Disaster Recovery and copying a Publication to a test environment. If you use the Wizard each time then you have to make sure that you’re consistent with the options and choices made in Live and once again on the Test (or DR) server.  By creating the script you should get something that reproduces the Publication and Subscription in full.

However, generating the script doesn’t always give you an exact copy of what you are scripting from. Some defaults might be included that weren’t used originally and some settings may be specified that were simply not used with the original creation.

A couple of examples I can demonstrate using the Publication scripted in earlier articles (, a more entertaining example using partition switching will have to be described.

Example 1 – There wasn’t a Publication Snapshot in the Original

But if you ask SSMS to generate a Create Script for the Publication it will include one:

To generate the scripts from an existing Publication you need to right-click on the Publication name within Replication/Local Publications branch (on the Publisher):


Specify where you want the script to go and it will be generated.

In this option I’ve chosen to have the script sent straight to a New Query Window.

Now bear in mind that this Publication does not use a Snapshot. I never specified it when I originally scripted this publication.

So what is this here for?


If I’m going to use this script as a true copy of the original then I need to remove this entry.

Example 2 – The ‘sync_type’ has been set incorrectly

From the Subscriber, generate the Create Script just as with the Publisher, to a New Query Window.

This time the comments within the script do have the good grace to warn you that a default has been use that you might not want:



In this case I need that to be set to ‘none’.

As an aside, in SQL Server 2000 this setting was also case-sensitive – ‘none’ wouldn’t work as expected, but ‘None’ would.

Example 3 – Partition Switching fails with Replication

I have no test example to hand with which to demonstrate this (at least, not one that I can show outside of my employer’s environment) but it is simple enough to describe, now that I know the cause and resolution.

Several databases in use at my current workplace specify different partition schemes between the Publisher and the Subscriber. This is a common practice, particularly where the Subscriber might be a ‘staging’ database used to ultimately transfer data elsewhere.  So the Publisher might keep data for a couple of weeks but the Subscriber only needs to store it for a day or two, because another system is being used to store/consume that data for other purposes (reporting, analysis or whatever).

So, in my innocence I script the Publication and Subscription via SSMS, make the alterations shown in the previous two examples and create the Publication on a test environment. All is good and Replication works fine. Data that I insert into the Publisher appears in the Subscriber and I have that warm, smug feeling of having created a Publication without incident. Something to be savoured.

However, part of creating this test environment also includes setting up the jobs that purge the data in both Publisher and Subscriber, with the use of Partition Switching (for a basic example of Partition Switching, have a look at ).

When the job runs against the Publisher that executes Partition Switching I get the following error:

“The table ‘<schema>.<tablename>’ belongs to a publication which does not allow switching of partitions [SQLSTATE 42000] (Error 21867)”.

Just for a laugh, if you want to see just how many errors Replication can produce, go to (and to add to the fun, they aren’t logged).

After much digging around and asking others who have more Replication scars than myself it transpires that some settings aren’t scripted out and also aren’t found by any of the guid screens associated with Replication.

In the past I have right-clicked on the Publication name, selected ‘Properties’ and looked at ‘Subscription Options’, believing that comparing these between the original and the copy would be enough.  Ah, well.

There is a Replication command ‘sp_helppublication’ which shows several setting that are not visible elsewhere. At its most basic, running this command with just the Publication name will produce a row with all of the setting associated with that Publication:



With the particular Publication in mind I scrolled along to the far right, and the setting for ‘allow_partition_switch’ was set to 0. As BOL specifies for this parameter – “Specifies whether ALTER TABLE…SWITCH statements can be executed against the published database”.

Executing ‘sp_helppublication’ against the Live Publication shows this value as ‘1’ – so it is permitted in Live but wasn’t scripted anywhere by the automatic process through SSMS.

To change this to the value required requires the command ‘sp_changePublication’, executed against the Publisher DB in question:

EXEC sp_changepublication @publication=N'Publication Name;', @property=N'allow_partition_switch', @value = 'true';

However, that isn’t the end of it. In executing this command it also sets ‘replicate_partition_switch’ to ‘1’, which I don’t want. The publisher and Subscriber in our environments generally have different Partition Switching schemes, so just because the Publisher decides to purge any data doesn’t mean that the Subscriber does too. So I now need to unset that parameter:

--it also sets 'replicate_partition_switch' to 'true' when the previous command executes and we want that as --'false'
EXEC sp_changepublication @publication=N'Publication Name', @property=N'replicate_partition_switch', @value = 'false';

Having jumped through these hoops I now find that Partition Switching works fine and my Publication in Test really is a copy of the Publication in Live.

Replication – Monitoring via the Jobs

5 Nov

In the previous article I covered how to use the Replication Monitor to see what is happening with a Publication and to find out what the errors are.
However, in some circumstances Replication Monitor may not show the entire picture with regard to the health of a Publication.
Information can also be gleaned from the jobs that actually perform the tasks that make the Publication work.

In this example I have removed the login of the Subscriber from the Distributor with a simple DROP LOGIN command.

USE [master]



Now, on the Publisher I’ll add one row to the table that is part of the Publication:

USE [PublisherDB]

INSERT INTO [ReplTest].[PublicationTable]
           ,'23 Jul 1970'

With this Publication the data should be sent at about 30 seconds past each minute. Open the replication Monitor and look at the history of the Distribution:


However, this has been sitting here for several minutes now and the history has not moved – stuck at the last transaction that completed at 13:12:29. There is also no error message displayed, so this screen indicates that there must be a problem (because I know it should run every minute) but gives no details.

The tab for ‘Undistributed Commands’ shows that there is one transaction waiting to get to the Subscriber:


And firing a tracer token never gets to the Subscriber:


From the Tracer Token we can see that the Publisher to Distributor connection must be working and there is no response between the Distributor and Subscriber.

This Publication is a Pull Subscription, so on the Subscriber there is a job that pulls the data from the Distributor. So, look at the job history on the Subscriber. In this case there is only one Job so it is easy to select, but the name should have something within it that shows which Publication it is part of. Within the history for that job there is a long list of failures – one every minute oddly enough:


Expand the ‘tree’ from the red cross and the latest step just tells you there’s a problem and to look either in the previous job step or replication Monitor. We know that Replication Monitor has told us all it is going to, so look at the previous step of this job:


So now we know that the problem is that the job cannot connect to the Distributor. In this case recreating the LOGIN on the Distributor for this job will correct the issue:

USE [master]



And we can see that the Replication monitor shows two transactions successfully processed (because the Tracer Token is a transaction too):


The Tracer token finally completed its journey:


And the Job History looks fine too:


Replication Monitor Basic Overview

31 Oct

Most issues with Replication are detailed in either the Replication Monitor or in the history of the jobs used to run the Replication tasks. The following examples are based upon the Pull Subscription detailed in earlier articles.

Replication Monitor

This tool is available to anyone who is a member of the sysadmin role or replmonitor role. It is loaded from any SQL Server instance that has Replication enabled, although life is easier if you launch it from the Publisher or Distributor (anywhere else and you may have to configure it to look for the correct servers).
Within SSMS Object Explorer, right-click on ‘Replication’ and select ‘Launch Replication Monitor’, which will result in:


In the screenshot above the tree structure in the left window has been expanded. The highest level is the Distibutor, expand that and beneath it is the Publisher and beneath that is the actual Publication.
Select the Distributor and three tabs are available in the right-hand pane – ‘Publications’, ‘Subscription Watch List’ and ‘Agents’.

Publications shows the Publications that this Distributor is responsible for, providing the Publisher name, Publications name, the number of Subscriptions and basic performance data.

Agents shows the various Agents that can be involved in Replication. In this Publication select ‘Merge Agent’ will show nothing, because it isn’t a Merge Publication and selecting ‘Snapshot Agent’ will show a status of ‘Never Started’ because snapshots are not used with Publication. Other selections will show the status of various Agents and jobs connected to this Distributor.

Subscription Watch List is the tab probably used most often. Double-click on the entry in the right-hand window and another window pops up, providing a detailed history of that Publication:


There are three tabs in this control and the most important of these tends to be the ‘Distributor To Subscriber History’ tab. As a default it shows the last 100 synchronisations (in this Publication there is one synchronisation every minute) but can be changed by using the drop-down at the top of that tab.

To show what should normally happen with this tab clear the Subscriber table, generate 1000 rows of data within the Publisher and watch this screen as the data is Published across.
For ease, Redgate SQL Data Generator has been used to insert the 1000 rows. From the current display on that tab you can see that it normally refreshes at roughly 30 seconds past each minute, so once it is due press F5 to refresh the screen and the message can be seen giving basic details of what was Published from the Distributor to the Subscriber:


Errors within the Publication

Now repeat the insertion on the Publisher, having truncated the table on the Publisher first it will generate Primary Keys that already exist on the Subscriber. This of course, will not go down well when the data gets to the Subscriber.

Initially the History will show that there is a problem and it is going to retry the individual commands, instead of the entire batch in one go:


A short while later it gives up and provide a detailed error message:


In this case it is informing of a violation of the Primary Key constraint, as that PK already exists on the Subscriber. This series of retrying and error reporting will repeat until something is done about it. In this case remove the data from the Publisher that already exists with the Primary Keys being Published and all will return to normal:


Another way to use this tool for locating the problem is in the Error Details part of the screen, with the Transaction Number displayed.
To show what can happen if the customised Stored Procedure is wrong, within the Subscriber change the SP ‘dbo.sp_MSins_Repl_SubscriptionTable’ to remove reference to th ‘PK’ column, which is NOT NULL.

USE [SubscriberDB]

ALTER procedure [dbo].[sp_MSins_Repl_SubscriptionTable]
    @c1 bigint,
    @c2 varchar(50),
    @c3 date,
    @c4 datetime
	insert into [Repl].[SubscriptionTable](
	) values (
    @c4	) 

Of course, any attempt to write a row to this table via this SP now will result in an error. The trick is to work out what command is failing within replication.

On the Publisher, execute the following:

USE [PublisherDB]

INSERT INTO [ReplTest].[PublicationTable]
           ,'01 Jun 1970')

Initially it will retry the command, as before. Eventually it will show an error message, along with the Transaction Sequence Number:


That binary number is used to run a query against the Distributor database to get the data it is attempting to send. The query is ‘sp_browsereplcmds’, with two parameters – both are the binary value taken from the Replication Monitor:


If multiple rows are returned from this query then the required row is the one where ‘command_id’ matches ‘Command ID’ shown in the error message from Replication Monitor (although you should check the column ‘partial_command’, as the query might be split across several rows).

From the column ‘command’ we can see the call to ‘sp_MSins_Repl_SubscriptionTable’ along with the parameters used.
Copying the contents of this column to SSMS and running it against the Subscriber (with a minor amount of editing to keep SSMS happy) shows the error:


Now we have the command that is causing the issue and the parameters it is using. In this case it’s obviously a fault within the SP, so simply amend it back to save the PK column and all returns to normal.



Another basic way to check Replication is all connected up is to run a Tracer Token through it. A Tracer Token is just a small amount of data written to the log of the Publisher and then tracked through the Publication.
To run a Tracer Token through Replication Monitor go to the bottom of the tree structure in the left-hand window, selecting the Publication. Three tabs appear and one of these is ‘Tracer Tokens’. Select that tab and press the button ‘Insert tracer’.


The time for ‘Publisher to Distributor’ will generally be within a few second, as that part of the Publication is configured to run constantly. ‘Distributor to Subscriber’ can take a while, depending upon when that job is scheduled. In this Publication it is once every minute, so could take anywhere up to one minute to complete. Once completed it shows the total latency and is an indication that the connections are configured correctly for the three main elements (Publisher, Distributor and Subscriber) to communicate correctly.

Replication – Scripting the Pull Subscription – Basic Example

6 Oct

Having prepared the Distributor in the previous article it is now possible to create a Subscription.

First of all create a test database and table on the Publisher (Publisher-A):


USE PublisherDB; 


CREATE TABLE ReplTest.PublicationTable( 
	EmailAddress	VARCHAR(50), 
	DOB				DATE, 


A database is also required on the Subscriber (Subscriber-C):


USE SubscriberDB; 


CREATE TABLE Repl.SubscriptionTable( 
	EmailAddress	VarChar(50), 
	DOB				Date, 
	DateCreated		DateTime, 
	ReplDatecreated	DateTime DEFAULT GETDATE(), 


Now back to the Publisher. This server needs to be configured as a Publisher. The steps used to create the Distibution Server have already associated this server with the Distributor but now this server needs to be configured as a Publisher and also associated with the Distributor (Server B needs to know about Server A and then Server A needs to know about Server B as a seperate step):

use master  
exec sp_adddistributor @distributor = N'9999-PC\Distributor-B', @password = N'Spr0uts' 

The response should be ‘You have updated the Publisher property ‘active’ successfully.’

Having configured the server to allow Publications, the database itself needs to be configured for publishing:

exec sp_replicationdboption @dbname = N'PublisherDB', @optname = N'publish', @value = N'true' 

And Log Reader Agents need to be created:

exec PublisherDB.sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1 
exec PublisherDB.sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1 

The response from this will notify you of two Jobs, although the exact names may vary:
“Job ‘9999-PC\Publisher-A-PublisherDB-1’ started successfully.
Job ‘[9999-PC\Publisher-A].5’ started successfully.”

Now create the Publication:

use PublisherDB; 
exec sp_addpublication @publication = N'Test_Publication', @description = N'Transactional publication of database ''PublisherDB''.', @sync_method = N'native', @retention = 0, 
@allow_push = N'false', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', 
@snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', 
@allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', 
@independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', 
@allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', 
@enabled_for_p2p = N'false', @enabled_for_het_sub = N'false' 

If you’ve used the Wizard to generate the scripts, the next command adds the publication snapshot. Where I work snapshots aren’t used because the databases are rather large and the lock such a command takes would make database access somewhat problematic for the hundreds of users that connect every minute.
So we’ll skip the ‘sp_addPublication_Snapshot’ command and move onto the next step – granting access to specified accounts.

We now need to create the various accounts and permissions that this Replication will require:

First, the Distributor needs access to this Publisher:


And to the DB:

USE PublisherDB; 



Various accounts involved with the Replication process also require access:

exec sp_grant_publication_access @publication = N'Test_Publication', @login = N'sa' 
exec sp_grant_publication_access @publication = N'Test_Publication', @login = N'NT AUTHORITY\SYSTEM' 
exec sp_grant_publication_access @publication = N'Test_Publication', @login = N'NT Service\SQLAgent$PUBLISHER-A' 
exec sp_grant_publication_access @publication = N'Test_Publication', @login = N'NT Service\SQLAgent$DISTRIBUTOR-B' 

Now we tell the Replication process which table we want to Replicate. Within Replication the tables are referred to as ‘Articles’, borrowing the terms from the age-old process of newspapaer publishing. For this Article we’re only going to be interested in copying new rows across. For now we don’t care about updating or deleting data, so there’ll only be one Stored Procedure defined – against the parameter ‘@ins_cmd’. You specify the name and then the process of creating the Article takes care of generating the code:

exec sp_addarticle @publication = N'Test_Publication', @article = N'Test_Publication_Article', @source_owner = N'ReplTest', @source_object = N'PublicationTable', @type = N'logbased', 
@description = N'', @creation_script = N'', @pre_creation_cmd = N'none', @schema_option = 0x0000000000030002, @identityrangemanagementoption = N'none', 
@destination_table = N'SubscriptionTable', @destination_owner = N'Repl', @status = 24, @vertical_partition = N'false', 
@ins_cmd = N'CALL [dbo].[sp_MSins_Repl_SubscriptionTable]', @del_cmd = N'NONE', @upd_cmd = N'NONE' 

At this point you can right-click on ‘Replication/Local Publications’ and see the Properties, confirmimg the table is set up for Replication:

Going back to the Distributor, the Distributor needs to have the login details for the Subscriber, as this is a Pull Subscription and it will be the responsibility of the Subscriber to transfer the data from the Distributor:

USE [master] 
USE [distribution] 
USE [distribution] 

On the Subscriber the Pull Subscription now needs to be created:

use SubscriberDB 
exec sp_addpullsubscription @publisher = N'9999-PC\Publisher-A', @publication = N'Test_Publication', @publisher_db = N'PublisherDB', @independent_agent = N'True', 
@subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0 

As well as the Agent that will be responsible for it:

exec sp_addpullsubscription_agent @publisher = N'9999-PC\Publisher-A', @publisher_db = N'PublisherDB', @publication = N'Test_Publication', 
@distributor = N'9999-PC\Distributor-B', @distributor_security_mode = 1, @distributor_login = N'distributor_admin', @distributor_password = N'Spr0uts', 
@enabled_for_syncmgr = N'False', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, 
@frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 1, @active_start_time_of_day = 30, 
@active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @alt_snapshot_folder = N'', 
@working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0 

Now the custom Stored Procedure needs to be created within the Subscriber, to process the inserts. To do this, the Stored Procedure is generated from the Publisher with the command:

Use PublisherDB; 

EXEC sp_scriptpublicationcustomprocs 'Test_Publication' 

The respnse from this:
can then be copied and pasted into the Subscriber and executed against the Subscriber DB:

Finally, from the PublisherDB we need to active the Subscription:

exec sp_addsubscription @publication = N'Test_Publication', @subscriber = N'9999-PC\Subscriber-C', @destination_db = N'SubscriberDB', @subscription_type = N'Pull', @sync_type = N'None', 
@article = N'all', @update_mode = N'read only', @subscriber_type = 0 

A word of warning regarding the parameter ‘@sync_type’. Where I work, the use of ‘none’ is commonplace (because we don’t replicate schema changes automatically – we control it ourselves) and if you look at the documentation it is deprecated, so a better alternative is ‘replication support only’. However, when you generate the scripts from an existing Publication using the Wizard, you may find that the parameter has ‘automatic’ set against it – regardless of the value you initially created.
Replication has lots of lovely surprises like that.

At this point, from SSMS on the Subscriber you should be able to expand ‘Replication/Local Publications’ and see the name of the Publication and the Subscriber details:

A basic Pull Subscription has now been set up. In following articles I’ll cover how to test and monitor this, with basic steps on what to look for when it goes wrong.

Replication – Scripting the Distributor – Basic Example

2 Oct

By far the easiest way to create the Distributor is via the Wizard and examples abound of that – one such example is at SQL Server Central .

So why use scripts?

Mainly for Disaster Recovery and consistency. Should you have to rebuild the server then running the scripts is easier and more reliable than re-running the Wizard – there’s less chance somebody can enter different values and therefore cause issues with the Replication.

So, let’s have a look at the scripts behind this wizard.

Firstly – I have three servers, installed as separate instances on the same machine:
Publisher-A, as the Publisher
Distributor-B as the Distributor and
Subscriber-C as the Subscriber.
Currently none of these servers are configured for Replication. All instances were installed to cater for Replication but that is all.

Firstly, make sure the Agent Services are running – it makes life easier and you will need them to actual perform the replication tasks. SQL Configuration Manager can be used for this.

There are four scripts in the standard Distributor creation, gleaned from scripting the Wizard. All of these script are being executed on ‘Distributor-B’ and of course I’ve replaced the server name – I may well be working in a secure environment but there’s no need to tempt anybody.

1. Designate the Distributor server:

use master
exec sp_adddistributor 
     @distributor = N'9999-PC\Distributor-B', 
     @password = N'Spr0uts' 

Having executed this code you will see an additional entry in sys.sysservers, giving ‘srvname’ of ‘repl_distributor’ and showing ‘dist’ as 1. An associated entries under ‘Linked Servers’ within SSMS will also be visible.

Next, create the Distibutor database:

exec sp_adddistributiondb @database = N'distribution', 
			@data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.Distributor-B\MSSQL\DATA', 
			@log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.Distributor-B\MSSQL\DATA', 
			@log_file_size = 2, 
			@min_distretention = 0, 
			@max_distretention = 72, 
			@history_retention = 48, 
			@security_mode = 0, 
			@login = N'sa',--login used when connecting to the distributor to create the distribution database 

I’ve left the default name for the DB ‘distributor’ and in this case have left the other defaults too (retention periods and login).
As this code executes it displays various messages relating to the creation of the DB, as well as adding the roles ‘guest’ and ‘replmonitor’. Once completed the database is visible under ‘System Databases’ as a distributor:


The next command runs against this new distributor database and is used to record the location of the snapshot folder. Now as I don’t intend to use snapshots I’m only including this because all scripts appear to include it and I can’t find a decent explanation for it. When I have the time I’m going to try excluding this command and see what fun it creates. For now though I’ll just play safe:

use [distribution] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
	create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\9999-PC\C$\Replication\Snapshot', 'user', dbo, 'table', 'UIProperties' 
	EXEC sp_addextendedproperty N'SnapshotFolder', N'\\9999-PC\C$\Replication\Snapshot', 'user', dbo, 'table', 'UIProperties' 

Notice that the locations needs a fully-qualified name.

The final command registers a Publisher that will use this Distributor:

exec sp_adddistpublisher @publisher = N'9999-PC\Publisher-A', 
		@distribution_db = N'distribution', 
		@security_mode = 1, 
		@working_directory = N'\\9999-PC\C$\Replication\Snapshot', 
		@trusted = N'false', 
		@thirdparty_flag = 0, 
		@publisher_type = N'MSSQLSERVER'

This will create an entry under ‘Linked Servers’ for the Publisher. The only response from this code is the usual ‘Command completed successfully’ message. Trying it a second time will tell you that the server is already listed as a publisher.

Note: If you’ve used the ‘Configure Distribution Wizard’ to create the scripts, you’ll notice that the Distributor is also created as a Publisher. This step isn’t necessary for the configuration I’m using here.

Now we have a Distributor created and can use that to build upon in following articles.