SQL Server Transactional Replication Configuration

Transactional Replication in SQL Server is one of the most commonly used Replication techniques to copy or distribute data across multiple destinations. In previous articles, we discussed SQL Server Replication and how Replication internally works. Now our goal is to see how to Configure Transactional Replication in SQL Server using the Backup approach and how to add or remove Articles to Replication properly. Without proper measures, we risk invalidating the snapshot and face the necessity to reconfigure Replication.

CodingSight - SQL Server Transactional Replication Configuration

Transactional Replication Administration

In my previous articles, we went through the step-by-step instructions about the below items:

  • Configure Distribution
  • Configure Publication with Snapshot option
  • Configure Subscription with Snapshot option

While configuring Replication, we have first configured the Distributor. Then we proceeded to Create a Publication and Subscription. To drop or clean up Replication, we need to do the reverse process. First, we’d need to delete the Subscription, then Publication, and, finally, drop the Distributor or the distribution database.

In this article, we try dropping the Transactional Replication in SQL Server that we previously configured. We’ll use the Backup approach and add or remove articles from Replication in the following way:

  • Drop Subscription
  • Drop Publication
  • Drop Distributor or distribution database
  • Clean up Replication completely if any of the above steps do not work correctly
  • Configure Replication with Database Backup
  • Drop articles from Replication using both the Wizard and T-SQL approach
  • Add new articles to Replication using both the Wizard and T-SQL approach
  • Add a Stored Procedure article and study the difference between the Table article and the Stored Procedure article

Drop Subscription

To drop any configured Replication, we need to Drop the Subscription first.

In SSMS, connect to the Publisher instance > Replication > Local Publications. Right-click on the Subscription > Delete or Drop Subscription:

Drop Subscription

SQL Server will ask you to confirm your action:

SQL Server asks to confirm the action

Click on Yes to drop the Subscription. This will drop the Subscription completely.

In my current setup, both Publisher and Subscriber are on the same instance. Hence, no requests were sent to connect to the Subscriber instance for validation. In case we’d have it on a different instance of SQL Server, it would ask to connect to the Subscriber instance to validate before deleting the Subscriber.

Object Explorer

Dropping Publication internally uses the sp_droppublication procedure and we can use this procedure for manually dropping the Publication via T-SQL approach

Drop Publication

Once the Subscription is deleted, we can go ahead and drop the Publication. Right-click AdventureWorks_Pub and select Delete from the menu:

drop the Publication

You will see a message asking you to confirm that action. Note that you are going to delete the Publication. However, all records that were replicated to the Subscriber database won’t be deleted. We’ll need to drop the database manually to clean up those replicated records. Click Yes.

drop the database manually
Local Publications

Dropping a Publication internally applies the sp_droppublication procedure.

Drop the Distributor or the Distribution Database

Earlier we mentioned that the distribution database is a system database. Hence, we can’t drop it by right-clicking on the database and selecting the delete option like with user databases. If we right-click on the distribution database, we only get the below options:

Drop the Distributor or the Distribution Database

When we need to delete the distribution database, first we must right-click on the Replication node > Disable Publishing and Distribution.

Disable Publishing and Distribution

It will open the Wizard.

By default, the second option (No, continue using this server as a Publisher) is selected to avoid accidental dropping all publications on the server.

In our case, we have only one Publication, and we would like to clean up. Hence, we select the first option – Yes, disable Publishing on this server. It will drop all Publications along with Subscriptions if not dropped already along with disabling the Distributor.

We can use this wizard itself to clean up everything if our Server has only one Replication configured. However, if there are multiple Replications configured, we drop Transactional Replication in SQL Server by following the standard steps shared above.

Now, we need to select the first option Yes, disable publishing on this server, and click Next.

 Disable Publishing and Distribution Wizard

In the new window, check both options: Disable publishing and distribution and Generate a script file with steps…

Disable publishing and distribution and Generate a script file with steps...

For generating the script file, you’ll need to provide the path where to store it.

Path for script file

Click Next and view the options selected in the wizard. Check and ensure that you’ve selected everything correctly.

Complete the Wizard

Click Finish.

Disabling Distribution

Dropping the distribution database internally uses the sp_dropdistributor procedure.

Once the Distributor is disabled, we can see that the distribution database is dropped out from system databases.

distribution database is dropped out from system databases

Clean up Replication Completely if any of above Steps aren’t Working Properly

If the Subscription or Publication is dropped via any other approaches, we land up into inconsistent removal of Transactional Replication in SQL Server and get a lot of errors. To clean up all residues of Subscription or Publication, we can use the system procedure sp_removedbreplication.

exec_spremovedbreplication

Execute this procedure only if there are still any Replication issues after trying out all other approaches mentioned. Stored Procedure sp_removedbreplication should be executed on the Publisher database or from master database and using the below command after replacing the @dbname with the name of Publisher Database.

exec_spremovedbreplication @dbname

Configure Replication using the Backup Approach

After removing the Replication completely, let’s reconfigure the Transactional Replication in SQL Server using the Backup Approach. It involves the below steps:

  • Configure the Distributor
  • Create the Publication
  • Modify the Publication properties to enable creating Subscription from the Full or Differential Backup.
  • Take a Full Backup of Publisher and restore it as a Subscriber.
  • Configure Subscription and start initializing from Backup.

We have already performed most steps earlier when configured the Replication. Hence, we won’t go into detail about those steps here.

Configure Distributor & Publication

Refer to the Step-by-Step instructions from the previous article on how to configure both Distribution and Publication using the Create Publication wizard. To learn the T-SQL scripts used by the wizard to create Distribution and Publication, just generate the scripts to a file during the last step of the wizard and don’t execute the scripts by unchecking the “Create the Publication” option as shown below.

Now open the script file saved in a new Query Window to create the Distributor and Publication using those Scripts:

Configure Distributor & Publication

Kindly notice the second commented line – it states that all password values we entered in the wizard were converted to NULL or empty string for Security Reasons. Have a look at the highlighted line with @password = blank values. Replace that with the correct password values, do the same for other sections with passwords, and execute the script.

Query executed successfully
The script was executed successfully

The script was executed successfully. We can see that the execution of scripts has created the distribution database and all system tables inside it. At the end of the message, we can see that the Log Reader Agent job has been created and launched as well.

If needed, you can store the results to learn about all tables, views, and critical procedures in the distribution database. This information will be helpful for further troubleshooting.

After the successful execution of the scripts, we can see the distribution database and the publication created successfully.

distribution database and the publication created successfully

Modify Publication Properties to Enable Creating Subscription from Full or Differential Backup

If the database size is very small, the time required to send the Initial Snapshot will be faster.

On the other hand, creating Transactional Replication in SQL Server using Snapshot is not efficient in the following cases:

  1. If the database is huge (300 GB or more). The time required to send the Initial snapshot will be too long.
  2. If the Subscriber is located across different locations with low network bandwidth. Then the initial snapshot process will be happening for multiple days.

Thus, making a Full Backup, transferring it via FTP or physically to the other location, restoring that backup, and initializing the Subscriber will be significantly faster in comparison with the Snapshot approach.

To enable the Publication to support initialization from Backups, we have to modify one of the Publication properties. It can be done either via SSMS or T-SQL.

SSMS Approach

Right-click the AdventureWorks_pub publication and choose Properties:

SSMS Approach

Click on Subscription Options:

Subscription Options

Set True for Allow Initialization from backup files and click OK. This will allow us to initialize from both Full and Differential Backup.

T-SQL Approach

In T-SQL, we can call the procedure sp_changepublication to modify this property.

The script to change this property is below:

USE AdventureWorks
GO
exec sp_changepublication @publication = 'AdventureWorks_pub', @property = 'allow_initialize_from_backup', @value = 'true'

Take a Full Backup of Publisher and Restore it as Subscriber

A key factor to notice that we must take a Full Backup after implementing the above Publication property. If the database size is huge, we can take a Full Backup and restore it in the RECOVERY mode in the Subscriber instance, and take a Differential backup after making the above configuration change and restore it in the Subscriber database with the NORECOVERY mode.

Configure Subscription and Start Initializing from Backup

Again, refer to the Step-by-Step instructions. We need to generate the necessary scripts but don’t execute them. The thing is, we’ll Initialize the Subscription from Full or Differential Backup using T-SQL scripts only. I created those scripts during the Subscription creation last time. See the open file below.

Note: The scripts for the Subscription creation must be executed from the Publisher database. Hence, open the Query window connecting to the Publisher instance.

Configure Subscription and Start Initializing from Backup

We need to make a few changes for the Subscription to initialize from backup:

  • Change the @sync_type value from automatic to initialize with backup
  • Provide the correct passwords for those replaced with NULL or empty strings. Since I’ve used the Agent Service account within the server, I don’t need to change passwords.
  • Add the parameters @backupdevicetype and @backupdevicename and provide the path to Full or Differential backup on the Publisher Server (the script will be executed on it).

When done, our script will look like this:

Configure Subscription and Start Initializing from Backup

Execute the script to complete the Subscription configuration and we will receive the successful completion of the script as shown below.

successful completion of the script

As the status indicates, the Distribution agent SQL Server Agent job was created and launched while creating the Subscription.

Thus, we have created our Replication successfully using the Backup approach. Now we can verify the available Subscription.

Replication is created successfully using the Backup approach

Launch the Replication Monitor and right-click the Subscriber. It will show the Replication status:

Replication status
Replication status

As we can see, all data have been successfully initialized from the Backup without the need for running Snapshot Agent job. Since there are no active transactions happening on database, we get the “No Replicated transactions are available” message in Replication Monitor at this moment.

Drop Articles from Replication

Having learned how to configure Transactional Replication in SQL Server via the Replication wizard or T-SQL scripts, we can now check how to drop an article from the Replication via both these methods.

Using the Wizard

Right-click on the AdventureWorks_pub Publication > Properties. Click on Article to view the list of articles included in Replication.

Drop Articles from Replication using the Wizard

By default, it will list the database objects in the format OBJECT_NAME (SCHEMA_NAME). For testing purposes, let’s drop the Person.ContactType table from the Replication.

For that, simply uncheck the box before ContactType (Person). SQL Server will show the warning or confirmation message:

warning or confirmation message

As it explains, if there are any snapshots currently available, it will invalidate those snapshots due to the changes in articles.

Since we have initialized using the Backup approach and haven’t used Snapshots, we can safely ignore this message and click Yes to remove this table Article from the Replication. Click OK to complete removing the article from the Replication.

Now the Person.ContactType table is removed from the Replication. Any changes happening on the Publisher won’t be sent to the Subscriber database. We can test this by INSERT/UPDATE/DELETE records on the Person.ContactType table.

Using T-SQL

Another way is to drop an article from the Replication with the help of the sp_droparticle procedure.

USE [AdventureWorks]
GO
EXEC sp_droparticle 
  @publication = N'AdventureWorks_pub', 
  @article = N'ContactType',
  @force_invalidate_snapshot = 1;
GO

Add New Articles to Replication via Wizard or TSQL Approach

In some cases (like table maintenance), we might need to remove few articles and add them back to the Replication after maintenance is completed.

We have successfully learned how to remove articles from Replication. Let’s consider how to add new articles to Replication. We’ll add the Person.ContactType table which we have removed earlier back to Replication.

Using Wizard

To add a table article back to the Replication, right-click Publication > Properties > Articles. It will display the list of articles available in the Publication.

Add New Articles to Replication via Wizard

We couldn’t find the Person.ContactType table – the screen displays only those tables that were part of Replication. To see all tables available in the Publisher database, uncheck Show only selected articles in the list to view all tables.

Add New Articles to Replication via Wizard

Now, we can see the Person.ContactType table listed.

As we discussed earlier, all tables without Primary Keys will have a Red Circle icon that indicates that these tables can’t be included in the Replication either via Wizard or T-SQL approach.

Check the ContactType (Person) table to add it back to the Replication and click OK.

Check the ContactType (Person) table to add it back to the Replication

The table is added again to the Replication. However, we need to work out a method to send the initial snapshot for this newly-added table article.

If you have gone through the article so far, you would have guessed it right – just run the Snapshot Agent Job to send the initial snapshot for this table.

Let’s do that now – right-click on Publication > view the Snapshot Agent status.

view the Snapshot Agent status

Click on Start to send the snapshot for eligible articles. Send those data to the Distribution database, and, finally, to the Subscriber database.

T-SQL approach

We can perform the similar actions using the sp_addarticle procedure.

The below script will add Articles to the Replication.

use [AdventureWorks]
GO
exec sp_addarticle @publication = N'AdventureWorks_pub', @article = N'ContactType', @source_owner = N'Person', @source_object = N'ContactType'
, @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual', @destination_table = N'ContactType', @destination_owner = N'Person', @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_PersonContactType'
, @del_cmd = N'CALL sp_MSdel_PersonContactType'
, @upd_cmd = N'SCALL sp_MSupd_PersonContactType'
GO

We have earlier noticed how Replication works for a table article by applying 3 procedures created in the Subscriber database to handle the INSERT/UPDATE and DELETE operations.

Using the T-SQL approach, we know how these procedures are referenced. If needed, we can rename objects, destination table names, or default procedures. For that, we’ll make changes to the sp_addarticle procedure.

NOTE: If we do any modifications to the Replication, we should document them all properly. Otherwise, it might lead to a disaster later.

Add a Stored Procedure Article and Study the Difference between a Table Article and a Stored Procedure Article

To add a Stored Procedure to the Replication, we need to go to the Articles page and check the required Stored Procedure to get it Replicated. We can do the same for Views, Indexed Views, or User Defined Functions as well.

To learn more about the difference between Table Articles vs Views/ Stored Procedure/ Indexed Views/User Defined Functions, we can add one of each article for every category using T-SQL:

Adding a New Stored Procedure Article to Replication

use [AdventureWorks]
exec sp_addarticle @publication = N'AdventureWorks_pub', @article = N'uspGetBillOfMaterials', @source_owner = N'dbo'
, @source_object = N'uspGetBillOfMaterials', @type = N'proc schema only', @description = null, @creation_script = null
, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @force_invalidate_snapshot = 1
, @destination_table = N'uspGetBillOfMaterials', @destination_owner = N'dbo'

Adding a New View Article to Replication

use [AdventureWorks]
exec sp_addarticle @publication = N'AdventureWorks_pub', @article = N'vVendorWithContacts', @source_owner = N'Purchasing'
, @source_object = N'vVendorWithContacts', @type = N'view schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop'
, @schema_option = 0x0000000008000001, @destination_table = N'vVendorWithContacts', @destination_owner = N'Purchasing'
GO

Adding a New Indexed View Article to Replication

use [AdventureWorks]
exec sp_addarticle @publication = N'AdventureWorks_pub', @article = N'vStateProvinceCountryRegion', @source_owner = N'Person'
, @source_object = N'vStateProvinceCountryRegion', @type = N'indexed view schema only', @description = null, @creation_script = null
, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @force_invalidate_snapshot = 1
, @destination_table = N'vStateProvinceCountryRegion', @destination_owner = N'Person'

Adding a new User Defined Function article to Replication

use [AdventureWorks]
exec sp_addarticle @publication = N'AdventureWorks_pub', @article = N'ufnGetStock', @source_owner = N'dbo', @source_object = N'ufnGetStock'
, @type = N'func schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001
, @force_invalidate_snapshot = 1, @destination_table = N'ufnGetStock', @destination_owner = N'dbo'

We use the sp_addarticle procedure to add any type of article to the Replication with the necessary input parameters and options changing the included @type parameter.

We can also add these articles to the Publication by right-clicking Publication > Properties > Articles and adding them to the Publication.

Table Article Properties

Now, let’s change the Article properties via the Articles menu in the Publication properties:

Table Article Properties

Click on our favorite Person.ContactType table > Article Properties. There will be an option to either change the Article Properties for this Table only or all Tables included in the Replication. For a test, we select Set Properties of Highlighted Table Article to view the properties of the Person.ContactType table.

Set Properties of Highlighted Table Article

See all options available for this table Article:

Set Properties of Highlighted Table Article
Article Properties

Copy Objects and Settings to Subscriber and Statement Delivery are the most important settings for the Replication. We need to be very careful to change any of these parameters.

Click on the INSERT\UPDATE\DELETE delivery format to get the below options.

  • Do Not Replicate INSERT\UPDATE\DELETE statements – to customize Replication not to send specific commands to the Subscriber database
  • INSERT\UPDATE\DELETE statement – to send INSERT\UPDATE\DELETE statement directly to Subscriber instead of reconstructing the data from transactional logs
  • CALL <stored procedure> – Execute the built-in stored procedure shown above in sp_addarticle to replicate data.
  • XCALL <stored procedure> – Execute extended stored procedure to replicate the changes.
Article Properties

Stored Procedure Article Properties

Click Article Properties on any one of the Stored Procedures to view the Properties

Stored Procedure Article Properties

One of the key properties of the Stored Procedure is the Replicate option – see the available options below:

Replication Option for Stored Procedure
  • Stored Procedure definition only – replicates only the Stored Procedure DDL structure changes. This is the default option for any Stored Procedure.
  • Execution of the Stored Procedure – use this option to reduce the Replication load. It performs all changes via Execution of Stored Procedure on Subscriber without sending individual commands. We can see about this feature to resolve performance issues while replicating huge data changes in my next article.
  • Execution in a serialized transaction of the SP – a hybrid option to choose Execution of Stored procedure only if the procedure is executed within a serialized transaction. Else it would be replicated as individual DML commands.

View Article Properties

Click on Article Properties for any View to get the Properties:

View Article Properties

Indexed View Article Properties

Click on Article Properties for any one of Indexed Views for the Properties:

Indexed View Article Properties

User-Defined Function Article Properties

Click Article Properties on any User Defined Function for its Properties

User-Defined Function Article Properties

Properties of Views, Indexed Views, and User-Defined Functions are pretty much the same. Hence, we can’t customize them much.

Conclusion

Thanks for going through another power-packed article related to Replication. Today, we have clarified the details on dropping Subscription, Publication, Distribution Database, and cleaning up the Replication completely even if we encounter any issues.

We have configured a Replication newly initialized from the backup and tested how to add new articles to the Replication or remove them from it. In further work with databases and, in particular, finding the discrepancies between them, you will benefit from the professional tools greatly. The dbForge Compare Bundle for SQL Server identifies and analyzes all such differences and reports them. 

In our next article, we’ll examine frequently faced Replication issues and how to resolve them professionally.

Raja Jegan

Raja Jegan

Raja Jegan Ramesh is an experienced Database Architect with more than 15+ years of experience in and around various RDBMS technologies. Primarily focused on SQL Server and related areas, he handles other RDBMS platforms like Oracle, MySQL, etc., in his day to day work. During his spare time, he contributes in Experts-Exchange platform to help resolve day to day issues for several people which helps him encounter lot of scenarios related to SQL Server and how to handle those efficiently by working with other SQL Server experts as well.

Leave a Reply

Your email address will not be published. Required fields are marked *