How to skip a transaction, Transactional replication

How to skip a transaction, Transactional replication

In transactional replication, Data Synchronization latency often occurs. Sometimes these latencies are due to the execution of an update in publication, such as update ta set col =? Where ?, This update contains a large amount of data. On the subscriber side, this update is divided into multiple commands (one command is run for each data row by default) and applied to subscribe. We have to skip this large transaction so that replication can continue to run.

Here we will introduce some principles and specific methods of transactional replication.

When the article of the publication database is updated, corresponding logs are generated. Log reader reads the logs and writes them to msrepl_transactions and msrepl_commands of the Distribution database.

Each record in Msrepl_transactions has a unique identifier xact_seqno, which corresponds to the LSN in the log. Therefore, xact_seqno can be used to infer the order in which they are generated in the publication database. The generation time of a large number is later, and the generation time of a small number is earlier.

The Distributionagent contains two sub-processes: reader and writer. Reader reads data from the Distribution database, and Writer writes the data read by reader to the subscription database.

Reader reads data from the Distribution database (read the Msrepl_transactions table and Msrepl_Commands table) through sp_MSget_repl_commands.

The following is the parameter definition of sp_MSget_repl_commands.

CREATE PROCEDURE sys.sp_MSget_repl_commands ( @agent_id int, @last_xact_seqno varbinary(16), @get_count tinyint = 0, -- 0 = no count, 1 = cmd and tran (legacy), 2 = cmd only @compatibility_level int = 7000000, @subdb_version int = 0, @read_query_size int = -1 ) 

This stored procedure has six parameters. In Transactional replication, only the first four parameters are used (and the values of the third and fourth parameters are fixed. 0 and 10000000 respectively ). The following is an example:

Execsp_MSget_repl_commands 46, 0x00000030f000002a900ea00000000, 0, 000

@ Agent_id indicates Distributionagentid. Each subscription has a separate Distributionagent to process data. After @ agent_id is included, you can find the corresponding publication and all articles of the subscription.

@ Last_xact_seqno indicates the last LSN passed to the subscription.

The general logic is: Reader reads the transaction_timestamp column of the MSreplication_subscriptions table of the subpartition database, obtains the last updated LSN number, and then reads the data whose LSN is greater than this number in the distribution database. Writer writes the read data to the subscription and updates the transaction_timestamp column of the MSreplication_subscriptions table. Then, Reader will continue to use the new LSN to read the subsequent data and pass it to Writer.

If we manually update the transaction_timestamp column and set this value to the LSN of the large transaction currently being executed, the distribution agent will skip this large transaction instead of reading it.

The following is an example.

The environment is as follows:

Publisher: sql1_w2k8r21

Distributor: sql1_w2k8r22

Subscriber: sql1_w2k8r23

The highlighted publication in the figure contains three aritcles, ta, tb, and tc

Ta contains 182182 million data, and then we performed the following operations:

An update statement was executed,

Update ta set c =-11

Insert operations on tables ta, tb, and tc in the future

Insert tb values (0, 0)

Insert tc values (0, 0)

Then we started replication monitor and found that there was a great delay, and the distribution agent was always passing data generated by a) operation.

Run the following statement in the subscribe database to obtain the transaction number of the latest record.

declare @publisher sysname declare @publicationDB sysname declare @publication sysname set @publisher='SQL108W2K8R22' set @publicationDB='pubdb' set @publication='pubdbtest2'select transaction_timestamp From MSreplication_subscriptions where publisher=@publisher and publisher_db=@publicationDB and publication=@publication 

In my environment, the transaction number is 0x0000014900004E9A0004000000000000

Return to the distribution database and run the following statement to obtain the transaction number that follows the large transaction. Replace the parameter with the data in your actual environment. (Note: If you encounter performance problems when executing the following statements, replace the parameters with values directly)

declare @publisher sysname declare @publicationDB sysname declare @publication sysname declare @transaction_timestamp [varbinary](16) set @publisher='SQL108W2K8R21' set @publicationDB='publicationdb2' set @publication='pubtest' set @transaction_timestamp= 0x0000014900004E9A0004000000000000select top 1 xact_seqno from MSrepl_commands with (nolock) where xact_seqno>@transaction_timestamp and article_id in (   select article_id From MSarticles a inner join MSpublications p on a.publication_id=p.publication_id and a.publisher_id=p.publisher_id and a.publisher_db=p.publisher_db   inner join sys.servers s on s.server_id=p.publisher_id   where p.publication=@publication and p.publisher_db=@publicationDB and s.name=@publisher ) and publisher_database_id =(     select id From MSpublisher_databases pd inner join MSpublications p on pd.publisher_id=p.publisher_id     inner join sys.servers s on pd.publisher_id=s.server_id and pd.publisher_db=p.publisher_db     where s.name=@publisher and p.publication=@publication and pd.publisher_db=@publicationDB ) Order by xact_seqno

In my environment, the transaction number is 0x0000018C000001000171

Execute the following statement in the subtransaction database to skip large transactions. Replace the parameter with the data in your actual environment

declare @publisher sysnamedeclare @publicationDB sysname declare @publication sysname declare @transaction_timestamp [varbinary](16) set @publisher='SQL108W2K8R22' set @publicationDB='pubdb' set @publication='pubdbtest2' set @transaction_timestamp= 0x0000018C000001000171update MSreplication_subscriptions set transaction_timestamp=@transaction_timestamp where publisher=@publisher and publisher_db=@publicationDB and publication=@publication 

After the execution is complete, enable the distribution agent job.

Next, you will find that the transaction has been successfully skipped, ta will not be updated on the subscription end, and subsequent updates will be gradually transmitted to the subscription, and the delay will disappear.

English vocabulary for computer majors

English Contrast in computer vocabulary
Author: Release Date: 17:15:23 Source:
________________________________________
All) level "(All)" level
Action
Active statement Activity statement
Active voice
ActiveX Data Objects ActiveX Data Object
ActiveX Data Objects (multidimencript) (ado md) ActiveX Data Object (multi-dimensional) (ado md)
Ad hoc connector name special connector name
Add-in add-on
Adjective phrasing adjective sentence
ADO
ADO MD
Adverb adverbs
Aggregate function
Aggregate query
Aggregation
Aggregation prefix
Aggregation wrapper aggregation Packaging
Alert alarm
Alias
Aliasing
All member "All" members
American National Standards Institute (ANSI) American National Institute of Standards (ANSI)
Analysis server
Ancestor
Annotational property annotation property
Anonymous subscribe anonymously
ANSI
ANSI to OEM conversion
API
API server cursor
Application programming interface (API) application interface (API)
Application role
Archive file
Article Project
Atomic
Attribute features
Authentication
Authorization
Automatic recovery
Autonomy independent
Axis
Backup
Backup device
Backup file
Backup media
Backup set
Balanced hierarchy
Base data type
Base table
Batch Processing
Bcp files
Bcp utility
Bigint data type
Binary data type
Binary large object
Binding
Bit data type
Bitwise operation
BLOB
Blocks Block
Boolean
Browse mode
Built-in functions built-in functions
Business rules
Cache aging high-speed cache aging data clearing
Calculated column
Cal ...... remaining full text>

English vocabulary for computer majors

English Contrast in computer vocabulary
Author: Release Date: 17:15:23 Source:
________________________________________
All) level "(All)" level
Action
Active statement Activity statement
Active voice
ActiveX Data Objects ActiveX Data Object
ActiveX Data Objects (multidimencript) (ado md) ActiveX Data Object (multi-dimensional) (ado md)
Ad hoc connector name special connector name
Add-in add-on
Adjective phrasing adjective sentence
ADO
ADO MD
Adverb adverbs
Aggregate function
Aggregate query
Aggregation
Aggregation prefix
Aggregation wrapper aggregation Packaging
Alert alarm
Alias
Aliasing
All member "All" members
American National Standards Institute (ANSI) American National Institute of Standards (ANSI)
Analysis server
Ancestor
Annotational property annotation property
Anonymous subscribe anonymously
ANSI
ANSI to OEM conversion
API
API server cursor
Application programming interface (API) application interface (API)
Application role
Archive file
Article Project
Atomic
Attribute features
Authentication
Authorization
Automatic recovery
Autonomy independent
Axis
Backup
Backup device
Backup file
Backup media
Backup set
Balanced hierarchy
Base data type
Base table
Batch Processing
Bcp files
Bcp utility
Bigint data type
Binary data type
Binary large object
Binding
Bit data type
Bitwise operation
BLOB
Blocks Block
Boolean
Browse mode
Built-in functions built-in functions
Business rules
Cache aging high-speed cache aging data clearing
Calculated column
Cal ...... remaining full text>