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.
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:
SQL Server will ask you to confirm your 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.
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:
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.
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:
When we need to delete the distribution database, first we must right-click on the Replication node > 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.
In the new window, check both options: 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.
Click Next and view the options selected in the wizard. Check and ensure that you’ve selected everything correctly.
Click Finish.
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.
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_spremovedbreplicationExecute 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:
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.
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.
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:
- If the database is huge (300 GB or more). The time required to send the Initial snapshot will be too long.
- 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:
Click on 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.
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:
Execute the script to complete the Subscription configuration and we will receive the successful completion of the script as shown below.
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.
Launch the Replication Monitor and right-click the Subscriber. It will show the 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.
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:
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.
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.
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.
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.
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'
GOAdding 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:
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.
See all options available for this table Article:
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.
Stored Procedure Article Properties
Click Article Properties on any one of the Stored Procedures to view the Properties
One of the key properties of the Stored Procedure is the Replicate option – see the available options below:
- 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:
Indexed View Article Properties
Click on Article Properties for any one of Indexed Views for the Properties:
User-Defined Function Article Properties
Click Article Properties on any User Defined Function for its 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.
Tags: replication, replication agents, transaction log Last modified: October 27, 2022
