Overview of Replication in SQL Server
In SQL Server the Replication is a set of techniques to transfer the database objects like schema, views, stored procedures, etc., from an existing database to another database. Replication is one of the best facility provided by MS SQL Server to maintains the consistency of the databases. With the help of SQL Server transaction replication, you can transmit data to different locations using local, wide area networks and the Internet.
The transactional replication helps publish as well as filter individual database object. The transactional replication technique can be implemented with the help of SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent.
There are three types of replication in SQL Server database. These are given below:
- Transactional Replication
- Merge Replication
- Snapshot Replication
Here in this article we will discuss about the transactional replication and its pros and cons. This article also describes SQL Server transactional replication error and how to overcome from this issue.
Use of Transactional Replication in SQL Server
In SQL Server databases the transactional replication is mostly used for server-to-server data transmission, when users require a high throughput and an improving scalability with high availability. Data warehousing integrates data across the multiple sites having heterogeneous data also for offloading and batch processing the replication is used.
The synchrozation facility can also be used as an alternative of replication with the help of Microsoft Sync Framework. The flexible API and intuitive components make it easy to Synchronize the SQL Server, SQL Server Compact, SQL Azure databases and SQL Server Express. The Sync Framework provides the facility to synchronize the classes from one database to another database.
Why SQL Server Transactional Replication Error Occurs
In SQL Server the transactional replication error occurs due to the missing row in a database at the subscriber’s end. When this error occurs, you cannot perform any action like update or delete because there is no matching record to the delete or update condition at the subscribers end.
Fix SQL Server Transactional Replication Error 20598
Follow the given below steps to fix SQL Server database transactional replication error 20598
- Start the replication monitor and in the replication monitor you can see the error message like this; 'the row was not found at the subscriber when applying the replicated command error number 20598'
- If you are getting this error message, then you have to copy the transaction sequence number: 0x0000003E00000045000800000000 and also notice the Command ID.
- Now you have to run the given command in an SSMS window after connecting to the distributor.
{CALL [sp_MSupd_dbot2] (,,,,,'nice 3',3,'good',2014-08-31 07:49:47.960,{C1479523-6839-4C90-9429-EE31CD2D5831},0x20)} - After performing these steps you can easily figure out the particular table name on which the replication command fails.
- Now, after this you can insert the missing row on the subscriber end table using the following command.
Syntax:
INSERT INTO subscriber_server sub_db schema
table_name column_list
SELECT column_list
FROM publisher_server pub_db schema table_name
WHERE pk_col_1 = value1 and pk_col_2 = value2 and pk_col_n = valuen You can also export the missing data from the publishing table to a file and then import the data back to the subscription table.
Conclusion
In this article, I have tried to discuss SQL Server Transaction replication in brief. Replication is a technique that is used for data distribution from one database to another database using any transmission medium like internet etc. I have also discussed about SQL Server transaction replication error 20598 and how can we fix the same.
EmoticonEmoticon