SQL SERVER TRANSACTIONAL REPLICATION MISSES RECORDS SOMETIMES

Question

SQL SERVER TRANSACTIONAL REPLICATION MISSES RECORDS SOMETIMES

DM 546

We have setup of SQL SERVER transactional replication between multiple servers. It happens that replication monitor shows the status as green but in between out of say 500 entries per day; randomly say 5 to 6 entries do not reach subscriber servers and replication monitor says records pending as zero. Also; frequently; edits that data user does at publisher end servers do reach partially to subscriber; means say we have edited 3 fields of a record at publisher out of it only 1 field value reaches and 2 columns updated value to not reach the subscriber. May suggest. Thanks for help.

Accepted answer

1 additional answer

Your answer

Answer 1

Tom Phillips 17,771

What you are describing is not possible with transactional replication from a publisher to a subscriber(s).

Transactional replication replays the COMMANDS run on the publisher on the subscribers, in the order they occur. It does not transfer the "data" changed and it is not possible to skip commands from replaying.

When you run a command on the publisher like:

UPDATE table SET col1=xxxx

Transactional replication stores that COMMAND and runs the same command on the subscriber. If the command errors, it stops and will not continue with other commands until that problem is fixed or the command is manually deleted from the distributor commands table.

Unless what you are really using is "merge" or "bidirectional" replication. In which case, you can have "conflicts" in changes, and those are automatically handled and logged. See: https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-conflict-detection-in-peer-to-peer-replication?view=sql-server-ver15

DM 546 Reputation points

2021-10-30T18:29:34.21+00:00

Dear TomPhillips, Thanks for your revert. Shall go through the link suggested by you and see as to why the cases fail to reach subscriber. Shall revert. Thanks in advance.
DM 546 Reputation points

2021-11-03T18:29:08.45+00:00

Dear TomPhillips, as will need to do checking on case to case basis of missing entries or half updated entries; and may take more time at my end..am accepting this answer as of now; may need your suggestion in case during troubleshooting am stuck with some nasty issue in replication. We are using SQL Server 2008 R2; and replicating over the internet; with frequent structural updates of tables.

Any suggestions for replication over the internet; also how to make out that a record(s) has been missed at subscriber due to insert-insert conflict or update update conflict or others without checking each node/server. Is there some report in sql server that tell that all records of publisher has reached subscriber(s) or have not reached. Can you please suggest Microsoft Learn link which explains and gives generic solutions to sql server replication error messages. Thanks in advance.
DM 546 Reputation points

2021-11-12T19:23:15.737+00:00

Dear TomPhillips,

Didn't got exact reason for missing entries or half updated entries; however noticed a message 'skipped xxxx transactions while applying records at subscriber'; please guide to troubleshoot; it happens randomly.
Any suggestions for replication over the internet over multiple servers; how to make out that a record(s) has been missed at subscriber due to insert-insert conflict or update update conflict or others without checking each node/server. Is there some report in sql server /log that tells that all records of publisher has reached subscriber(s) or have not reached. Thanks in advance.

Answer 2

YufeiShao-msft 7,146

Hi @DM ,

if the schema mismatch at the publisher and subscriber, the subscriber does not get the column:
https://www.mssqltips.com/sqlservertip/4262/tracking-sql-server-replication-subscriber-schema-changes-disrupting-data-flow/

it is a good idea to check the underlying tables directly, they will provide information faster and are more up-to-data.

here is way to troubleshoot:
https://www.mssqltips.com/sqlservertip/2853/troubleshooting-sql-server-replication/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

DM 546 Reputation points

2021-11-03T18:34:28.83+00:00

Hi Yufeishao-msft,
Thanks for your revert; shall check and revert on same.
DM 546 Reputation points

2021-11-06T18:24:16.503+00:00

Hi Yufeishao-msft,

Thanks for your informative revert. Went through the tables; however there was no difference in table structure. Is there any other possibility due to which partial updates happen or records are skipped; shall check that too. However; after each structural change/update shall make it a point now onward to check 'before' and 'after' status; and if 'after status' of tables is as desired; before starting the data entry. As you have rightly pointed out; comparing underlying tables will be helpful.

Also, in replicating over the internet; in the best practices document it is specified that 'Minimize transaction size in your application design.'; can you please give any reference link for a standard operating procedure(s)/ maximum limits for transactional replication/peer to peer replication in SQL SERVER 2008 R2 standard edition ; so that if at all we are crossing limits some where due to which records don't reach subscriber; will be known and corrective action taken.

Thanks in advance.

Share via

SQL SERVER TRANSACTIONAL REPLICATION MISSES RECORDS SOMETIMES

1 additional answer

Your answer