Shulou(Shulou.com)05/31 Report--

How to solve the blocking problem of slave nodes caused by the execution of alter table in Sqlserver 2014 alwayson architecture? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Environment:

Sqlserver 2014 alwayson architecture

1 primary node

1 standby nod

6 read-only nod

The problem shows:

A read-only node is blocked and the master node cannot synchronize data to this read-only node.

The cause of the problem:

A DDL statement is executed at the primary node to modify the field type of a table.

ALTER TABLE table1 ALTER COLUMN [Name] [NVARCHAR] (MAX)

At this point, the read-only library has a long transaction query about the table1 table.

The long transaction query of this table1 table blocks the master node from performing ALTER TABLE table1 ALTER COLUMN [Name] [NVARCHAR] (MAX) synchronization with the read-only library.

Causes the table1 table on the read-only node to fail to perform alter table operations.

As a result, the logs of all master nodes cannot be synchronized from the master node to this read-only node.

All subsequent queries facing the table1 table on the read-only node are blocked.

Query master-slave synchronization script:

SELECT availability_mode_desc

Role_desc

Replica_server_name

Last_redone_time

GETDATE () now

DATEDIFF (ms, last_redone_time, GETDATE ()) diffMS

FROM ((sys.availability_groups AS ag

JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id

)

JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id

)

JOIN sys.dm_hadr_database_replica_states dr_state ON ag.group_id = dr_state.group_id

AND dr_state.replica_id = ar_state.replica_id

Solution:

In order to solve this problem as soon as possible, the fastest way is to kill the session of the long transaction query on the read-only node

So that the DDL operation of the alter table of the master node can be synchronized to the read-only node, the blocking is resolved, and the synchronization returns to normal.

This is the answer to the question on how to solve the problem of blocking the slave node caused by the execution of alter table in the master node of the Sqlserver 2014 alwayson architecture. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.