Implement
Transactional Replication in Microsoft SQL Server
Donatien Mbadi,
Database Expert – dmbadi@blogspot.com
1.
Introduction
MS SQL Server replication is a
useful feature that allows you to create a copy of your database that can be
used for recovery after disaster, building distributed databases and
synchronizing data between database servers that can infrequently connect to
each other. There are four types of MS SQL Server replication — snapshot,
transactional, peer-to-peer and merge replication. As transactional replication
is widely used, we will configure this SQL Server replication type in this article.
The Distributor, the Publisher and the Subscriber must be configured to make
database replication work. The Subscriber can be configured on a source server
(push replication) and target server (pull replication). Now you know what
replication in SQL Server is and how to configure database replication.
Database replication is a useful feature; however, you should consider using
both replication and backup
of MS SQL databases to increase chances of a successful database
recovery.
2.
Transactional
replication
Transactional
replication is the periodic automated replication when the data is distributed
from a master database to a database replica in real time or near-real-time.
Transactional replication is more complex than snapshot replication because not
only is the final state of database replicated, but all made transactions are
also replicated, which makes it possible to monitor the entire transaction
history on the database replica.
Transactional
replication starts with a snapshot of the publication database objects and
data. As soon the initial snapshot is taken, subsequent data changes and schema
modifications made at the publisher are usually delivered to the subscriber as
they occur (in near real time). The data changes are applied to the subscriber
in the same order and within the same transaction boundaries as they are
occurred at the publisher; therefore, within a publication, transactional
consistency is guaranteed.
Three
agents are used in this type of replication:
-
Snapshot
Agent
-
Log
Reader Agent
-
Distribution
Agent
The
Log Reader Agent runs at the distributor; it typically runs continuously but
can also run according to a schedule you establish. When executing, the Log
Reader Agent first reads the publication transaction log and identifies any
INSERT, UPDATE and DELETE statements, or other modifications made to the data
in transactions that have been marked for replication. Next, the agent copies
those transactions in batches to the distribution database at the distributor.
The Log Reader Agent use internal stored procedure called sp_replcmds to
get the next set of commands marked for replication from the log. The
distribution database then becomes the store-and-forward queue from which
changes are sent to subscribers, only committed transactions are sent to the
distribution database. After the entire batch of transactions has been written
successfully to the distribution database, it is committed. Following the
commit of each batch of commands to the distributor, the Log Reader Agent calls
sp_repldone procedure to mark where replication was last completed.
Finally, the agent marks the rows in the transaction log that are ready to be
purged. Rows still waiting to be replicated are not purged.
The
Distribution Agent runs at the distributor for push subscriptions and at the
subscriber for pull subscriptions. The agent moves transactions from the
distributions database to the subscriber. Il a subscription is marked for
validation, the Distribution Agent also checks whether data at the publisher
and subscriber match.
The
transaction replication is typically used in each of the following cases:
-
You
want incremental changes to be propagated to subscribers as they occur.
-
The
application requires low latency between the time changes are made at the
publisher and the changes arrive at the subscriber
-
The
application requires access to intermediate data states. For example, if a row
changes five times, transactional replication allows an application to respond
to each change not simply the net data change to the row.
-
The
publisher has a very high volume insert, update and delete activity
-
The
publisher or subscriber is a non-SQL Server database, such as Oracle
By
default, subscribers to transactional publications should be treated as
READ-ONLY, because change are not propagated back to the publisher. However,
transactional replication does offer options that allow updates at the
subscriber.
How it
works?
1. The snapshot Agent prepares
snapshot files containing schema and data of published tables and other
database objects, stores the files in the snapshot folder and records
synchronization jobs in the distribution database on the distributor.
2. The Log Reader Agent monitors the
transaction log of each database configured for transactional replication and
copies the transactions marked for replication from the transaction log into
the distribution database which acts as a reliable store-and-forward queue.
3. The Distribution Agent copies the
initial snapshot files from the snapshot folder and the transactions held in
the distribution database tables to subscribers.
4. Incremental changes made at the
publisher flow to subscribers according to the schedule of the Distribution Agent,
which can run continuously for minimal latency or at scheduled intervals.
3.
Configuring
the environment
3.1.
Servers
configurations
We are using Oracle Virtual Box
and running two windows 2012 virtual machines and Microsoft SQL Server 2016 in
this document to explain SQL Server transactional replication.
Our environment is as below:
Server 1:
VM Name: MSSQLSVR1
IP Address: 192.168.56.101
Hostname: MSSQL01
SQL Server instance ID: MSSQLSVR1
Server 2:
VM Name: MSSQLSVR1
VM Name: MSSQLSVR2
IP Address: 192.168.56.102
Hostname: MSSQL02
SQL Server instance ID: MSSQLSVR2
3.2.
Microsoft
SQL Server installation
Both servers are installed, and
Widows Firewall is temporary disable. Find below the few installation screens
of Microsoft SQL Server 2016.
…
…
…
…
…
…
3.3.
SQL
Server Management Studio installation
After Microsoft SQL Server 2016
installed, install SSMS (SQL Server Management Studio) on both machines.
One SSMS installed, you have to
restart both machine and then launch SSMS.
3.4.
Preparing SQL Server for
replication
You have to configure the servers
before you can start database replication. We will use one Windows account for
SQL Server replication agents
3.4.1. Creating
the user for Agents
Create mssql user on both
servers as member of the following groups:
-
Administrators
(Local administrator on local machine, not domain administrators)
-
SQLServer2005SQLBrowserUserXXXXXXXX
Notice that the two Windows
servers we are using in this lab are not in Active Directory. If you use AD, you
can create the mssql user on the domain controller.
3.4.2. Configuring
the SQL Server Agents
Now Login on SSMS using the sa
account we specify when installing the SQL Server on both nodes and then start the
SQL Server Agent.
You can also connect to the
second MS SQL Server instance (MSSQLSERVER2) from the first MS SQL Server
(MSSQL01) by entering the appropriate credentials in SQL Server Management
Studio. You can connect to both MS SQL Server instances (MSSQLSVR1 and MSSQLSVR2)
in the single instance of SQL Server Management Studio. To do this, in the
object Explorer, click Connect > Database engine. In this tutorial, we
will connect to MSSQLSVR1 from MSSQL01 and to MSSQLSVR2 from MSSQL02 by using
SQL Server Management Studio to configure the MS SQL servers.
Configure the Agent Service to
start automatically.
Open the SQL Server Agent service
properties and set Startup Type to Automatic.
3.4.3. Configuring
users for SQL Server
After connecting to the MSSQLSVR1
instance in SQL Server Management Studio, go to the Object Explorer and
open SecurityàLogins
Right click Logins and
select New Login. Select Windows authentication and click on
Search. Then enter mssql and click Check Names to confirm and Ok
to save settings.
Add the mssql user to the
sysadmins server roles in the security configuration of the database in the SQL
Server Management Studio.
On both Servers, Right click on mssql
user and Properties. Select Server Roles and check sysadmin.
3.4.4. Importing
a database from a backup
Let’s import a sample database
from a backup that we will use for replication from the first machine to the
second machine.
Download the AdventureWorks2016.bak
database backup file from https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms
Copy the AdventureWorks2016.bak backup
file to your backup directory. In our case, this directory on the first server
is C:\Program files\Microsoft SQL Server\MSSQL13.MSSQLSVR1\MSSQL\backup.
On the first machine in SQL
Server Management Studio to MSSQL02\MSSQLSVR1 and right click Databases and
select Restore Database in the context menu.
In the Restore Database
window, select the needed parameters.
Source: Device.
Click on the three dots
to browse the database backup file.
In the Select Backup
Devices window, select backup media type: file.
Click Add.
Select the needed .bak
file - C:\Program files\Microsoft SQL Server\MSSQL13.MSSQLSVR1\MSSQL\backup\AdventureWorks2016.bak
Click OK.
Click OK once again.
The AdventureWorks2016
database was restored successfully.
3.4.5. Adjusting
mssql user privileges
After importing a database, you
have to perform some tuning to prepare the SQL Servers. On the first machine,
go to MSSQL01\MSSQLSVR1 àSecurityàLogins, select MSSQL01\mssql on
SQL Server Management Studio. Right click on Properties.
In Server Roles, check the dbcreator
role.
On the User Mapping page, select
users mapped to this login and check the AdventureWorks2016 checkbox
(select AdventureWorks2016r on the second server accordingly). In the database
role membership section, tick the db_owner checkbox.
4.
Performing
Transaction Database replication
We are going now to implement the
Full Transactional Database Replication using SQL Server Management
Studio. The view on the main and second database servers (MSSQL01\MSSQLSVR1 and
MSSQL02\MSSQLSVR2) are showed as below:
4.1.
Configuring Distribution
In this lab, Distribution is
configured on the main server on which a source database is stored. On the main
server (MSSQL01\MSSQLSVR1), right click on Replication and in the
context menu, select Configure Distribution.
Ouuups!!! I have this error that
I need to share with you.
First notice that the error
message is talking about wrong server name: MSSQL01\MSSQLSVR1 and
WIN26641JL986B\MSSQLSVR1 by running the two meaningful queries:
SELECT SERVERPROPERTY(N'servername')
SELECT @@SERVERNAME
After seeing this, I remember
that I changed my server name recently and that causing a problem.
So, to fix it, I ran the below:
DECLARE @actualname NVARCHAR(100)
DECLARE @currentname NVARCHAR(100)
SELECT @actualname = CONVERT(NVARCHAR(100), SERVERPROPERTY(N'servername'))
SELECT @currentname = @@SERVERNAME
EXEC sp_dropserver @currentname
EXEC sp_addserver @actualname, local
After running above, we need to
restart the SQL Server Service. Then make sure that we are getting the same
output queries:
Note: I do the same on the second
server.
Let’s continue with our
replication.
After Right Click on Replication
and then click on Configure Distribution, Configure Distribution Wizard
opens.
Distributor: Select the current database
instance running on the main server (MSSQL01\MSSQLSVR1) to act as the
distributor.
Snapshot Folder: You can leave the default path
here. A snapshot is need for initializing replication. Make sure that there is enough
space available on the disk where your snapshot directory is located. The amount
of free space must correspond to at least the size of a replicated database.
Distribution Database: Enter the distribution database
name. You can leave the default name (distribution) and folders
for the distribution database file and log file.
Publishers: Define SQL Server
replication publishers that can access the Distributor. Tick the checkbox near
the distribution database name on the primary instance (That hosts a source
database that will be replicated). In our case, this is the MSSQL01\MSSQLSVR1
instance and the distribution database name is distribution.
Wizard Actions: Tick the
Configure Distribution checkbox to configure distribution during the final step
of the wizard. You can also generate the script just to see the content that is
really executed by checking the Generate a script with steps to configure
distribution checkbox.
File Name: Since you are choosing to also
generate the script, specify the script file location.
Complete the Wizard: Check the distribution configuration
summary and click on Finish to create the Distributor,
Success status should appear if the
Distributor was created and configured successfully.
4.2.
Configuring the Publisher
One the Distribution is
configured, it’s time to configure the publisher. The publisher should be
configured on the main server (MSSQL01\MSSQLSVR1) where the master database to
be replicated stored. Expand Replication, right click Local Publications and in
the context menu, select New Publication.
New Publication Wizard opens.
Publication Database: Select the database you want to
replicate; in our case AdventureWorks2016). Click the Next for each step in the
wizard to continue.
Publication Type: Select Transaction publication
which is a widely used type of replication
Articles: Select the needed database objects,
such as tables, procedures, vies etc. to publish as articles. It is also possible
to select replication of the customs fields in the tables and select the
article properties if needed. In this lab, we are choosing all the database
objects as you can see on the screenshot below.
In some cases, you can just choose
some tables or other objects as articles for your replication. But doing that
you have to do an assessment concerning those objects and all their references.
An example below:
Let’s continue with our full
replication.
Filter Table Rows: No filters are added in this
lab. As mentioned earlier, you can add filters if needed.
Snapshot Agent: You should specify when to run
the Snapshot Agent. Let’s configure the Agent to run immediately. Click the
Checkbox Create a Snapshot immediately and keep the snapshot available to
initialize subscriptions.
Agent Security: Click Use the security
settings from the Snapshot Agent. Click the Security Settings
button to select the account under which the Agent will run.
In the Snapshot Security windows,
enter the credentials of the mssql Windows user we have created
before and specify the password.
Select connect to the publisher By
impersonating the process account. Click Ok to save settings and go
back to the wizard.
After defining the user, you can
see this user in the Snapshot Agent and Log Reader Agent sections.
Wizard Actions: Click the checkbox to Create
the publication during the final step wizard. You can also choose to generate
the script for publication.
Script file properties: In case you have choosing to
generate the script, specify the script file and location.
Complete the Wizard: Check your publication
configuration and then click on Finish to create a new publication.
Creating Publication: Monitor the progress of
creating a new publication. Wait for a while and you should see the success status
if everything has been done correctly.
Click on Report to see all the
articles that have been selected for replication
The publication is now created,
and you can see the publication on Object Explorer by expanding Replication/Local
Publications.
4.3.
Configuring the Subscriber
Microsoft SQL Server Replication
can be either pull or push replication. If you configure push replication, you
should configure the Subscriber to run the agents on the main database server
(MSSQL01). If you configure pull replication, the Subscriber must be configured
to run the agents on the second machine (MSSQL02), the machine on which the
database replica will be created.
In this, we will configure push
replication and create new subscription on the first SQL Server
(MSSQL01\MSSQLSVR1) here the master database resides.
In Object Explorer Go to Replication,
right click Local Subscriptions and in the context menu, select New
Subscriptions.
The new Subscription Wizard opens.
Publication: Select the publication for
which to create a new subscription. In our lab, the name of the Publisher is
MSSQL01\MSSQLSVR1 and the publication name (that was created earlier) is AdvWorksFullRepl.
Click Next for each step in the wizard to continue.
Distribution Agent Location: Fir this step, you have to
select the replication type by selecting either push subscription or pull subscription.
Subscribers: By default, the server on which
we run the wizard (MSSQL01\MSSQLSVR1) is displayed as the subscriber and the
subscription database is defined. Let’s add a new subscriber and select a
subscription database located on the second database server
(MSSQL02\MSSQLSVR2). Click Add Subscriber and in the context menu, select
Add SQL Server Subscriber.
In the popup window enter the
credentials for the second MSSQL Server instance (MSSQL02\MSSQLSVR2) and click Connect.
Subscribers: Since we need to replicate the
entire database from de the main server, <choose New database…>
New Database: Specify the replication
database name. In our lab it’s AdventureWorksR where ‘R’ is for
replication. Click Ok.
Oooops, this is an error that I also
need to share with you. This error can occur concerning exclusive lock on
database ‘model’ on the second server (on which you need to replicate the
database)
Using SQL Server Management
Studio, connect to the second server. We fixed this error using the below link:
1. Find who is having connection? Below query can
help in that.
IF EXISTS
(
SELECT request_session_id
FROM sys.dm_tran_locks
WHERE resource_database_id =
DB_ID('model')
)
BEGIN
PRINT
'Model Database in use!!'
SELECT *
FROM sys.dm_exec_sessions
WHERE session_id IN (
SELECT request_session_id
FROM sys.dm_tran_locks
WHERE resource_database_id =
DB_ID('model')
)
END
ELSE
PRINT
'Model Database not in used.'
2. Below query would provide KILL
command which we can run to kill ALL connections which are using model
database.
SELECT 'KILL ' + CONVERT(varchar(10),
l.request_session_id)
FROM sys.databases d, sys.dm_tran_locks l
WHERE d.database_id =
l.resource_database_id
AND d.name
= 'model'
3. Kill the connection.
4. Check if the lock persists
Distribution Agent Security: Click the button with three
dots(…) and select the user and other security options for the Distribution
Agent.
In the opened Distribution Agent
Security Window, set the Distribution Agent to run on the MSSQL01 host under
the mssql user account. Enter the password for the mssql Windows
user. Select Connect to the Distributor by impersonating the process
account and select Connect to the Subscriber by impersonating the
process account options. Click Ok to Save settings.
Now your subscription properties
are set.
Synchronization Schedule: Select the Agent that is
located on the Distributor to Run continuously for the current Subscriber.
Initialize Subscriptions: Click the Initialize checkbox
and in drop-down menu, select immediately for when to initialize subscription.
Wizard Actions: Select the upper checkbox to
create the subscription(s) at the end of the wizard.
Complete the wizard: You can check your
subscriptions settings and click Finish to create the subscription.
Wait for a while until the
subscription is created. If you see the Success status, it means that the
subscription was created successfully.
After configuring replication in
SQL Server, three jobs are displayed in Object Explorer and you can see them by
going to SQL Server Agent > Jobs.
4.4.
Finalizing replication
configuration
Once you have configured the
Distributor, the Publisher and the Subscriber, you can check the MS SQL Server
replication status.
On the first server
(MSSQL01\MSSQLSERVER1), launch the replication monitor to see the MS SQL Server
replication status. In SQL Server Management Studio, select your MS SQL Server
instance (MSSQLSERVER1), go to Replications, right click Local
Publications and, in the context menu, select Launch Replication Monitor.
In the opened window, you can see
the agent history and error messages. In our case we have the below message:
We fixed this error using the
below link:
https://jbswiki.com/2020/08/13/the-process-could-not-connect-to-subscriber/
4.5.
Finalizing replication
configuration
Let’s see Microsoft SQL Sever
replication in action. View the list of tables on the second database
AdventureWorks2016R.
Let’s delete one row in the
Person.AdressType table from the AdventureWorks2016 database (Source) on the
main server (MSSQL01\MSSQLSVR1). Run the query to delete a row that contains ‘Billing;
in the name and to display the contents of the table after that:
As you can see, the first row
with the AddressTypeID 1 and name ‘Billing’ was deleted from the Person.AddressType
table in the AdventureWorks2016 database on the MSSQL01 machine.
Transactional replication is
running. Let’s check the contents of the Person.AddressType table in the
AdventureWorks2016R database on the MSSQL02 machine. Execute a
similar query as above once again to see the contents of the table:
Database replication in SQL
Server is working properly.
