Database and Datascience: Implement Transactional Replication in Microsoft SQL Server

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:

https://blog.sqlauthority.com/2016/05/25/sql-server-fix-error-1807-not-obtain-exclusive-lock-database-model-retry-operation-later-part-2/

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: