Configuring Transactional Replication Step by Step

Configuring Transactional Replication Step by
Step (via SSMS GUI)

Transactional Replication configuration involves 3 major steps:
1. Configuring Distribution database

2. Publication Creation
3. Subscription Creation
Before trying to configure Replication, make sure that Replication Components are
installed as part of SQL Server installation or use SQL Server media to install Replication
Components, as they are necessary for the task.
In SSMS, connect to the Publisher Database Instance and right-click on Replication:
Distribution is not configured right now. Hence, we have the Configure Distribution
option. We can either Configure the Distribution database using the Configure
Distribution wizard or via the Publication Creation wizard.
To configure the Distribution database and Publication, follow the below steps:
Expand Replication and right-click on New Publication.
New Publication Wizard will launch. Click Next to see the Distributor configuration
options.
By default, it chooses the Publisher server to hold the distribution database. If you wish
to use a remote distribution database, choose the second option. Click Next.
The next option is for configuring the Snapshot Folder. Change it to the required folder.
Otherwise, it will be created on the SQL Server installation folder path by default.
Click Next.
Select the Publication Database (here it is CamprodOLTP) and click Next.
Choose the Publication Type – Transactional Replication. Click Next.
Choose Articles for this publication. For testing purposes, select all Tables and Views:
Before clicking on Next, expand tables once more to verify some issues.
Some tables are marked by red icons. When we click on those tables, we see the warning
indicating that a table can’t be replicated because it doesn’t have a Primary Key, one of
the crucial requirements for Transactional Replication. We will later go into more detail.
Now, click Next.
A page with Article Issues related to dependencies will appear. Click Next.
The next option is to Filter Table Rows – since we are testing the basic replication, we can
ignore it. Click Next.
Configure Snapshot Agent – ignore and click Next.
Agent Settings – click Security Settings to configure account to execute the Snapshot
agent and Log Reader Agent under it.
Then, change the Snapshot Agent Process to run under SQL Server Agent Service
Account.
Set the Log Reader Agent to Connect to Publisher > By impersonating the Process
Account. Click OK.
The Agent Security will get updated.
Thus, we have configured the Distributor and all elements of

the Publication like Articles, Snapshot Agent, Log Reader Agent, and Agent Securities. We
have almost completed the Publication creation via Wizard.
If you need to study further on TSQL scripts used to create Publication, we can check
the Generate a script file to create the Publication option. Otherwise, click Next.
Since I chose to save the file, the wizard allows me to set the Script file path and name.
Provide these details and click Next.
The Wizard finally asks for the Publication Name, I’ve named it CampPub with the
database name and keywords to indicate it as a publication for easier identification.
Verify all data provided on the Summary page and click Finish.
The Wizard will display the progress in Creating Publication. When it is complete, we’ll
see the confirmation. Click Close.
To verify the successful creation of the Distributor (Distribution database), expand the
system databases:
To verify the successful creation of Publication, expand Local Publication:

We have configured the Distribution Database and created the Publication database on
the CamprodOLTP database successfully. Now we can proceed with
the Subscription creation
Right-click on the new Publication we have just created and select New Subscriptions:
The New Subscriptions Wizard will appear. To start the process, click Next.
The Publication page asks to ensure that both
the Publication and Publisher databases are selected. Click Next.
Set the Distribution Agent to either Push or Pull Subscription. We are going to use
the Publisher Server as the Subscriber, and that type won’t have any impact. Hence, we
leave the default Push Subscription. Click Next.
Select the Subscribers (database). I’m selecting CamPub restored from the
same CamprodOLTP Database backup. Click Next.
Set the Agent Security:
Since I’m going to do everything within a single Server, I’m using the Agent service
account.
The next window presents the Distribution Agent Security values already configured.
Click Next.
Synchronization Schedule – leave it to the default. Click Next.
Initialize Subscriptions – leave it with the default values. Click Next.
After you provide all the necessary details, you will be able to complete the process of
creating the Subscription. Check the Generate Script file… option to study the scripts later
and click Next.
Provide the path to save the files, click Next.
Have a look at the summary and check all the configured values. Once verified,
click Finish.
The Subscription Creation is completed. Click Close.
Now we can see the Subscription displayed under our Publication.
Configure the Snapshot Agent
Our next step is to work on the Snapshot Agent to send the initial data
from Publisher to Subscriber.
Before stepping into it, we need to notice the Replication Monitor. This critical tool is
available in SSMS to view the Replication status at various levels, Server Level, Publisher
Database level, Subscription level, and Replication Agents level.
Right-click on Replication/Local Publication/Local Subscription/Publication or
the Subscription we created to launch the Replication Monitor as shown below:
In the Replication Monitor, expand Publisher Server (PUBLISHER_SERVER) > Publication
([CamprodOLTP]: CampPub) to display the Subscription details. Right-click
on Subscription and select View Details.
As we can see, the information about the Initial Snapshot for our publication CampPub is
not yet available. We’ll need to execute the Snapshot agent job to send initial data to the
Subscriber database.
Keep this window open to see the progress of Snapshot after starting the Snapshot Agent
job.
Right-click on Publication > View Snapshot Agent Status:

The agent has never been run message states that we have never executed the Snapshot
Agent. Click Start.
While the Snapshot Agent is executing, you can watch the progress:
When all snapshots are created, it will produce the confirmation message:
We can see the Snapshot files created newly in the Snapshot folder for which we
provided the path earlier.
After all snapshots are applied by the Distribution Agent to the Subscriber database, it will
display the below status in the open Replication Monitor window:
Congrats! We have successfully configured Transactional Replication using Snapshot
Agent.
Note: If we have a huge Publisher Database, creating snapshot might take a lot of time.
Thus, it is recommended to use the full backup of the Publisher database instead of
executing the Snapshot Agent – we’ll cover this issue in subsequent articles.
Verifying Replication Components

Every Replication Components can be verified by both SSMS GUI and TSQL queries. We’ll
discuss it in further articles, and here we’ll quickly explain how to view the properties of
the below components.
Publisher
In SSMS, right-click Replication > Publisher Properties > Publication Databases:
To view details about the Publisher, execute the below queries against the distribution
database.
USE distribution
GO
exec sp_helpdistpublisher
GO
select * from MSpublisher_databases
GO
Subscriber
Subscriber info can be obtained with the below query in SSMS.

USE distribution
GO
exec sp_helpsubscriberinfo
GO
select * from MSsubscriber_info
Distributor
In SSMS, right-click Replication > Distributor Properties:
Click on Publishers to display the list of all Publishers using this Distribution database.
In SSMS, we can run the below query to obtain the same details.
USE distribution
GO
exec sp_helpdistributor
GO
exec sp_helpdistributiondb
GO
Articles
Right-click on Publication > Publication Properties > Articles. You will see the list of all
Articles available. The properties of Individual articles can be modified by clicking
on Article Properties as well.
USE CamprodOLTP
GO
-- To View all articles available under a Publication
exec sp_helparticle @publication = 'CampPub'
GO
-- To View all article columns for a particular article available under a Publication
exec sp_helparticlecolumns @publication = 'CampPub', @article = 'Address'
GO
USE distribution
GO
SELECT * from MSArticles
Publication
Right-click on Publication > Properties:
In SSMS, we can run the below query to view the Publication properties:
USE CamprodOLTP
GO
exec sp_helppublication
GO
USE distribution
GO
SELECT * FROM MSPublications
Subscription
Right-click on Subscription > Subscription properties:
In SSMS, we can execute the below script to get the Subscription info:
USE CamprodOLTP
GO
exec sp_helpsubscription
GO
USE distribution
GO
SELECT * FROM MSsubscriptions
GO
Replication Agents
Under SQL Server Agent Jobs, we can find the specific Jobs created for all Replication
Agents:
In SSMS, we can execute the query to find out which job is the necessary Log Reader
Agent Job, Snapshot Agent Job, and Distribution Agent Jobs. Besides, we can see
the Distribution Agent Cleanup job and several other jobs related to Replication created
while we were setting Publication and Subscriptions internally.

Configuring Transactional Replication Step by Step

Uploaded by

Copyright:

Available Formats

Configuring Transactional Replication Step by Step

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Configuring Transactional Replication Step by Step

Uploaded by

Copyright:

Available Formats

Configuring Transactional Replication Step by

Step (via SSMS GUI)

1. Configuring Distribution database

In SSMS, connect to the Publisher Database Instance and right-click on Replication:

Expand Replication and right-click on New Publication.

Thus, we have configured the Distributor and all elements of

To verify the successful creation of Publication, expand Local Publication:

Right-click on Publication > View Snapshot Agent Status:

Verifying Replication Components

Subscriber info can be obtained with the below query in SSMS.

You might also like