A SQL Server updatable subscription has a row filter in the Synchronous replication removal project without reinitialization of all subscriptions!

Original: The SQL Server updatable subscription has a row filter in the Synchronous replication removal project without reinitialization of all subscriptions!

In synchronous replication of updatable subscriptions, there is a row-filtered Item table, which prompts for reinitialization of all snapshots and applies this snapshot, which causes all subscription databases to be reinitialized.

For example, when removing an item, prompt:

Cancel the operation at this time

If you click "Flag for reinitialization (M)", tick "generate new snapshot Now" and immediately generate a new snapshot and initialize all! --Don't operate!

If you click "Flag for reinitialization (M)" and do not tick "generate a new snapshot now", generate a new snapshot and initialize all of them the next time you start the snapshot! --Don't operate!

Workaround:

Do not use interface operation, execute with SQL code!

--for example, a database has multiple subscriptions to exec sp_dropsubscription @publication =n ' publish Rank a ', @article =n ' table name ', @subscriber =n ' all ' exec sp_droparticle @publication = N ' Publish Rank a ', @article = n ' table name ', @force_invalidate_snapshot = 0GOexec sp_dropsubscription @publication =n ' Post rank b ', @article =n ' table name ', @subscriber =n ' all ' exec sp_droparticle @publication = n ' publish rank B ', @article = n ' table name ', @force_invalidate_ snapshot = 0go@subscriber=n ' All '--the name of the subscriber whose subscription you want to delete. The subscriber data type is sysname and has no default value. --If all, all subscriptions to all Subscribers are deleted. @force_invalidate_snapshot = 0--0 Specifies that changes made to the item do not cause the snapshot to fail. --1 specifies that changes made to the project may cause the snapshot to be invalidated

The execution command is complete!

Other sync is ok!

Note: Do not work in the subscription database at this time just drop the published table, because there are triggers that have to be disabled or deleted, or the queue agent will fail to synchronize the subscription back to the publication database.

(Working with other tables does not cause the queue agent to fail and is synchronized.) ）

If the operation drops the published table so that the queue agent error, you can add the table back to publish, the queue agent automatically return to normal!

(However, data that is operating in the subscription database during a queue outage is not synchronized back to the publication database, only manually)

At this point, if you continue to add the item (table) and have a row filter, normal operation with the interface, there is no hint.

Start the snapshot again, and only take a snapshot of this single table and initialize a single table!

Reference:

Sp_dropsubscription

Sp_droparticle

Remember: It is recommended to test multiple times during the operation!

A SQL Server updatable subscription has a row filter in the Synchronous replication removal project without reinitialization of all subscriptions!