How to setup Transactional Replication? Part 2 (Publications and articles)

In Part 1 we covered configuring Publisher and Distributor server. Now you are ready to create first publications and add articles. We will focus on doing this by using SSMS.


First step is to create Publication on a database that you want to publish. Since you already setup Publisher and Distributor you should be able to extend Replication tree and Right Click on Publication, choose New Publication:


This will open new Publication Wizard:


On the Next window you will need to choose which database you want to publish. This will enable given database for Publishing:


In next step you need to choose type of replication. We focusing only on Transactional Replication and we choosing that one:


Now you need to choose articles. You can choose Tables, Procedures, Functions etc. In my DB I have only Tables:


You can extend each Table to get column list and you can “filter” it vertically by removing some columns from replication. You don’t need to replicate entire table schema.

On the right side you have Article Properties button. You either set Properties for highlighted article or for ALL articles in this Publications. The list of the settings is quite impressive.

Articles Properties

You can decide if you want to copy triggers, all indexes, if table will be dropped on subscriber, choose if you want to send deletes or not etc. There are plenty of settings that you can modify. Choose wisely as some of these settings can invalidate snapshot and you will need to re-snapshot publication. As there are so many of these properties I will go describe it more in separate post.

In next step you can “filter” articles horizontally by adding WHERE condition. For Example you want to replicate only new data then you can set condition on date column etc.


As you can see amount of properties and configurations that you can use are making replication very flexible:)

In next step you need to decide when Initial Snapshot will be run. If you don’t want to choose anything, you can always run it manually from replication Monitor. You can either schedule Snapshot or choose to generate snapshot straight after publication will be created:


Next step is to configure Security settings for LogReader and Snapshot agent:


I already explained (Understanding Replication Agents Part 1 , Understanding Replication Agents Part 2 ) How Replication Agents work. In that place you configuring Processing Account (under what account jobs and exec will be running) and what account will be used in order to connect to Publisher. You can setup different account for LogReader and Snapshot. You also need to keep in mind that account that will be used to connect to Publisher need to have relevant permissions (sysadmin) on Publisher. That account will be generating Snapshot and reading transaction log on Published database.


For this example I choose for both SQL Service account. NOTE: I needed to grant permission for Distributor (if remote one) SQL service account on Publisher. If you choosing different processing account for Snapshot than SQL service account, you need to make sure that this account have permissions do file system on Distributor. Snapshot agent will be creating folders and files on disk. SQL Service will have that permissions but if you use different account you may have issues when generating snapshot.

In next step you can choose that either Publication will be created or just generate the script for the Publication you just configuring (or both):


It could be useful to save T-Sql script as it may me useful in future. It is also nice to see what exactly happen when you configuring Publication (as there are many procs executed and configuration changed during that process that you do not see when using SSMS).

Finally we can put name of our Publication and Finish configuration:

Naming Publication

If all went well you will see new Publication in your SSMS:



This is pretty much entire process of creating basic publication for Transactional Replication. Easy?? Yeah it is easy we will go bit “deeper” in next post.

In next posts will describe some of the articles settings that you can modify, creating subscriptions and configuring Publications/Subscriptions by  using T-sql.

Execs and desktop heap

Agent Processing Account

In previous post I explained how Replication agents works. It is pretty mych exe running on Distributor (or in pull replication distrib.exe on Subscriber). As always each exe is running under some kind of Windows account. Like here:execs

As you can see distrib.exe and logread.exe are running on SQL Service account and also you notice each if these is using RAM. That account you choosing when you configure publication (configuring snapshot and logreader processing account) and subscriber (configuring distribution agent). Every time you configuring one of these things you need to provide Security settings. There are two things that need to be provided. First processing account and second authentication account. In some cases you can use same account for both, like here:


As you can see we will be using SQL Service account for processing logread.exe and also we will use same account in order to connect to Publisher (in order to read transactions). Side note: If you choosing to do so, you need to make sure that this account has all necessary permissions on Publisher server.

Side note 2: You can modify these settings always by going to Publication settings/properties (to modify snapshot and logreader security settings) and Subscription settings/properties (to modify Distribution Agent settings).

We focusing now only on first part. Processing account this is the one that exe will be running under.

You probably wonder why I’m writing about this? Well it is to do with “noninteractive desktop heap “.

Noninteractive desktop heap

Now I would like to put small disclaimer. You may not get to this point where you will get that issue. What is the exact issue?

Let’s assume you have one remote distributor that is used to run 50,60, 100 or more distribution agents. Each of this agent is configured to run under SQL Service account. Each agent is running exe under that account and each process taking some amount of memory on that instance. It is also taking so called “noninteractive desktop heap” that is allowing running processes concurrently. That value is limited for each processing account and it means once u reach it for SQL Service account you wont be able to run any more replication agents.

Once you get to this point the replication agents just wont run… the problem is it wont give you ANY error or just fail without giving any description… it will just stuck on “Starting agent” and finally fail after sometime.

You can test if you have that issue already and just run exe command manually under your account (if you have all necessary permissions on the server) and if agent is running fine then most probably you reached that limit. How to run replication agents manually I described here:

Understanding Replication Agents Part 2


  1. Change processing account to use different Domain account. Remember it need to have proper permissions on Distributor. You will need to restart agent after you change processing account.
  2. Increase Desktop Heap Size. However this need server reboot:Increasing the noninteractive desktop heap size

I normally go with solution no. 1 and create few more domain account and run agent under these accounts.

Understanding Replication Agents Part 2

What is really happening when snapshot, logreader and distribution job is running on SQL Server? I will try to explain in that blog post.

SQL Jobs

As I described before in Part 1 All replication agents are really SQL Jobs located either on Distributor server or in some cases (pull replication) on Subscribers. When you go inside the SQL job (exactly second step of the job) like distribution agent u will see few strange (or not so strange) things/parameters like this:

-Subscriber [MAINTEST\SUBSCRIBER] -SubscriberDB [subscriberDB] -Publisher [MAINTEST\PUBLISHER] -Distributor [MainTest\DISTRIBUTOR] -DistributorSecurityMode 1 -Publication [Pub1] -PublisherDB [AdventureWorks2014] -Continuous

It is pretty self-explanatory however there is more than only above settings. In this place you can modify subscriber or Distributor Authentication, you add multiple streams or hard code what agent profile it should use etc. Full list of settings you can find here… and it is quite a list:)

Distribution Agent Settings

Snapshot Agent Settings

LogReader Agent Settings

I need to make note that if you make change in SQL job it will override specific settings  that you set via GUI. For Example if you use parameter  [-ProfileName profile_name] this setting will be always used, even when you change agent profile using Replication Monitor.

Last thing that is crucial, you need to stop and start agent to make new settings “live”.


Now, how these setting are really executed? How it really works? Well it is pretty straight forward. It is using execs from COM folder where you installed SQL Server (mostly Program Files etc.).You can also go to Distributor and look at Task Manager on the server, you will notice these execs running:


You can also see under what account that exec is running. There is one particular reason why you should not run ALL your agents under SQL Service account. I will describe it later in different post.

Each agent has different exec:

LogReader -> logread.exe

Snapshot -> snapshot.exe

Distribution -> Distrib.exe

The interesting thing is that you can execute this execs manually from cmd line, using your own account. You will ask why? I found few moments in my career that I need to check route cause of agent failure and replication monitor or SQL Job history did not give me any error. This is the way you can check if you have permission , authentication or user memory issue (more about memory in different post).

First stop the agent you want to test, otherwise you will get an error that agent already running. Then go to COM folder in your SQL server installation files:


Copy ALL settings from second step of SQL Job/Agent and paste it after either distrib.exe, snapshot.exe or logread.exe. I did distrib.exe:




You can see all the information about subscription you are trying to run and under what settings it is running. Note that SQL job wont be started and when you go to Replication Monitor you will see that agent is running, however you WONT be able to stop it from here. In order to stop agent you need to close cmd window or just press Ctrl+C, it will stop the exec process and Replication Monitor will be refreshed as well.

I used this way few times at my work and it is good to know how replication is working and operates. Once you know this it is easier to troubleshoot some of the issues that occur.