Transactional Replication – The Publisher
The Publisher is the database where all
replicated data originates. Each Publisher can have multiple publications
defined within it. Each publication contains a set of articles that all need to
reside in a single database. Each article corresponds to all or part of a single
database object. A single database object can map to an article in more than one
Setting up a publication
To be able to create a publication, you
have to first associate the publisher with a distributor. The distributor can be
the same SQL Server instance (Local Distributor) or it can be a separate
instance on a separate machine (Remote Distributor). Details about setting up
the distributor and establishing the publisher to distributor link can be found
in Level 2 of this Stairway.
Who can create a publication?
Any member of the db_owner fixed
database role can define a publication in a database, once the database has been
enabled for replication by a member of the sysadmin fixed server role. To enable
a database for replication, connect to the publisher in Object Explorer and
right click on the replication folder. Select “Publisher Properties…” in the
drop down menu, as shown in Figure 1.
The dialog that opens has two pages. The
first (“General”) allows you to change the Administrative Password that is used
to connect to the distributor. The second page (Publication Databases), shown in
Figure 2, is the place to enable databases for publishing. For each database you
can select if transactional and/or merge replication should be allowed.
Enabling databases for publishing
Once you have a distributor associated
with your publisher and the database you want to publish from is enabled for
publishing, connect to the publisher in Object Explorer. Open the “Replication”
folder and right click on “Publications”. Select “New Publication…” in the drop
down as shown in Figure 3.
Selecting "New Publication..."
The “New Publication Wizard” opens. The
first screen in the wizard that requires a selection is the “Publication
Database” screen, as you can see in Figure 4.
Choosing the publication database
Select the database that you want to
publish from and move on to the following screen. Here, as you can see in Figure
5, you get to select the publication type. As we’re discussing transactional
replication at the moment, choose “Transactional publication”.
Selecting the Publication Type
The other options are “Snapshot
publication”, “Transactional publication with updatable subscriptions” and
“Merge replication”. Snapshot and Merge replication have been briefly introduced
in Level 1 of this Stairway. Merge replication will also be covered in greater
detail during later Levels.
Transactional replication with
updateable subscription is an advanced topic that allows a subscriber to relay
changes back to the publisher. This is however not a truly bidirectional setup,
so if your goal is bidirectional replication check out Merge Replication. If you
have Enterprise Edition of SQL Server you can also use Peer-To-Peer
Transactional Replication which is truly bidirectional. For more information on
these advanced topics check out Books Online.
After the publication type is selected,
the next step is to define the articles for the publication. An article is
always based on a single object in the publisher database.
As you can see in Figure 6, the Articles
page of the New Publication Wizard lists all objects that can be replicated. The
list of objects includes Tables, Stored Procedures, Views, Indexed Views and
User Defined Functions.
A table article by default replicates
the data in the table as well as schema changes to the table. For a stored
procedure you can choose to replicate the procedure definition, or the
definition and each execution. Views and functions replicate the schema only.
Indexed views get replicated as tables, including schema changes and data. For a
complete list of possible articles and their restrictions check out
and Database Objects in Books Online.
For table articles
you have a choice of having all columns published or only a subset. If you click
on the little plus sign next to a table on the Articles form in Figure
all columns of that table appear beneath it. An example of this is shown in
Figure 7. The only requirement for filtering columns is that the primary key
needs to be replicated. All other columns can be omitted. (The Wizard does not
allow all types of columns to be dropped. See
and Database Objects in Books Online for details).
This type of filter that chooses individual columns is called a “vertical
Select Columns for Publication
With the “Article
Properties” button (see Figure
you can set several options for each article in your publication. Some of these
options are shown in Figure
You can also set the options for all articles of a single type in one shot. The
list of available options changes depending on the type of article you have
selected. Here you can define the name of the target object, what to do if the
target object does already exist, if you want to replicate only the definition
or also each execution of a stored procedure, etc. For a detailed list I again
refer you to the above mentioned article
and Database Objects in Books Online.
If you selected at least one table for
publication, the following screen in the wizard will allow you to define row
filters. A filter looks like a SELECT statement in which you can define a
WHERE clause. This type of filter is called a
“Horizontal Filter” and is shown in Figure 9.
Defining a Horizontal Filter
After the articles are defined, the next
step in setting up a publication is to schedule a snapshot for the initial
synchronization from source to target. The snapshot agent is responsible for
creating this initial copy of object definitions and data and storing it in the
snapshot folder on the distributor.
You can choose to run this step right
away, or schedule it for execution at a later time, as you can see in Figure 10.
If you are going to schedule the execution, you can create a schedule using all
the options that are available for scheduling a SQL agent job.
In the case where you are setting up a
publication that is going to have only one subscriber, your best bet is probably
to go for immediate snapshot creation. If you have the need to often synchronize
or resynchronize subscribers, having the snapshot refreshed regularly will
insure that you always work with fresh data. Keep in mind however that creating
a snapshot, particularly of big tables, can be quite expensive. The snapshot
generation by default takes a table lock on all tables involved until the entire
snapshot generation process is finished. Depending on the size of the tables and
the network speed, this can take several hours. There are ways to make snapshot
generation less intrusive, but the SSMS GUI does not give us access to those
The next step on our way to a working
publication is to set the accounts under which the agents are to run. This
selection happens on the “Agent Security” page of the New Publication Wizard,
shown in Figure 11.
The two agents that are involved in the
processing of a transactional publication are the snapshot agent and the log
For the snapshot agent you can provide a
Windows login or you can choose to run the agent using the SQL Server Agent
Service Account. Figure 12
shows you these two choices. The documentation however warns that the latter is
not a recommended security best practice.
The second choice on this form tells the
agent how to connect to the publisher. You can either select for the agent to
use Windows authentication, or provide a SQL Login for the connection to the
For the log reader agent you have the
same options to choose from. You can also just pick to use the same settings
that you provided for the snapshot agent.
While there is one snapshot agent per
publication, there is only a single log reader agent for each publication
database. If you already have a publication defined you cannot change the log
reader agent account at this point. You can however still define a different SQL
Server Login for the log reader to use to connect to the publication database
for the new publication.
The snapshot agent is responsible for
moving data from the publication database to the snapshot folder/share as well
as to the distribution database. To be able to do that, the snapshot agent
account needs to be a member of the db_owner fixed database role in the
publication database and in the distribution database. The account also needs to
have write permission on the snapshot folder/share. If you selected to use SQL
Server authentication to connect to the publication database, that account needs
to be a member of the db_owner fixed database role in the publication database.
The snapshot agent account does not need to have access to the publication
database in this case.
The log reader agent also copies data
from the publication database to the distribution database, but it does not make
use of the snapshot folder share. Therefore the log reader agent account needs
to be a member of the db_owner fixed database role in the distribution database.
The account used to connect to the publisher (either the log reader agent
account itself or a provided SQL Server Login) needs to be a member of the
db_owner fixed database role in the publication database.
Completing the Publication Setup
After the agent accounts are setup you
have two more wizard pages remaining. The first asks you to specify whether the
publication should be creating immediately on completion of the wizard, or if a
script should be created to allow execution at a later time.
The last page, shown in Figure 13,
displays a summary and asks for a publication name. The name needs to be unique
per publication database. If you publish from several databases you can reuse
the same name. However, if you have multiple publications, I strongly recommend
using meaningful and distinguishable names for them. There are a lot of moving
parts involved in each replication setup. There is no need to cause additional
confusion by choosing the same or similar names for multiple publications.
Selecting a Publication Name
Because there are so many steps and so
many decisions when setting up replication, there is a chance that some aspect
of the replication process might not be set up correctly. The most common
problem is that one of the accounts does not have appropriate rights.
If you run into problems go through all
accounts used in this setup and make sure that the right permissions have been
granted. The error messages are not always pointing in the right direction. For
remote server "W7A\R2A" does not exist, or has not been designated as a valid
Publisher...” means that the snapshot agent cannot
access the distribution database. It is also not always straight forward to find
the error messages in the first place. Details on how to troubleshoot
replication will be given in Level 9 of this Stairway.
If you are using a snapshot share that
is located on the Distributor, you will have to grant write access for the
snapshot agent account on both the share and the underlying folder.
If you use Windows domain accounts for
the different agents and use impersonation to connect to the other servers, you
might have to grant the Windows account that is executing the SQL Server Service
read access to the Active Directory. Check out Books Online and the Windows
documentation for details on this.
In general it is advisable to execute
the replication wizards when connected using a SQL Server Login (e.g. SA),
instead of a Windows login. There are a lot of reasons for this that go beyond
the scope of this article. But in general you will have a lot less issues,
particularly but not only with replication, if the objects you are dealing with
are owned by a user associated with a SQL Login and not a Windows login.
Executing the wizard as ‘SA’ will make
sure that you do not run into any issues because of this.
This Stairway Level introduced the
publisher. We learned who is allowed to create a publication, how to add
articles to a publication and what article types there are. We discovered how to
schedule snapshot generation and what permissions are required by the different
agents involved in a transactional replication publication. Some potential
problems you might run into where also pointed out.
As with the distributor, after setting
up the publisher there is really not a lot to see yet. But there are two signs
now. One you can see by drilling down into the Local Publications in Object
Explorer. This is shown in Figure 14.
The other one is not directly visible
but might become a visible problem. It is the fact that all changes to the
publication articles are now recorded in the distribution database, but nobody
is reading it from there. So the distribution database will grow, potentially a
lot. To prevent that from happening continue on with this Stairway and learn
about how to set up a subscriber to this publication in the next