Configurer la détection et la résolution des conflits de dernière écriture

  • Article

À compter de SQL Server 2019 (15.x) CU 13, vous pouvez configurer la réplication d’égal à égal pour résoudre automatiquement les conflits en autorisant l’insertion ou la mise à jour la plus récente pour gagner le conflit. Si l’une des deux écritures supprime la ligne, SQL Server donne la priorité à la suppression. Cette méthode est connue comme la « priorité à la dernière écriture ».

Utilisez des procédures stockées pour configurer la priorité à la dernière écriture. N’utilisez pas l’Assistant Topologie d’égal à égal pour ajouter ou supprimer des nœuds lorsque vous utilisez le dernier enregistreur.

Considérations importantes relatives à la configuration

Remarque

Après la mise à jour vers SQL Server 2019 (15.x) CU13 ou version ultérieure, lorsque vous configurez une publication avec une résolution de conflit définie sur la dernière victoire d’écriture, des métadonnées supplémentaires sont incluses dans la publication. Si vous désinstallez/rétrogradez ultérieurement vers une version antérieure à SQL Server 2019 (15.x) CU13, ces métadonnées supplémentaires entraîneront des problèmes. Nous vous recommandons de supprimer ces publications avant le passage à une version antérieure, puis de les recréer sur la version inférieure.

Quand vous configurez la réplication de pair à pair avec la découverte et la résolution automatiques de conflit donnant la priorité à la dernière écriture, ajoutez les configurations et les paramètres suivants :

  • Créez la publication avec les paramètres suivants :

    • Définissez @p2p_conflictdetection_policy = 'lastwriter' pour spécifier la priorité à la dernière écriture. Consultez sp_addpublication (Transact-SQL). Ce paramètre est introduit dans SQL Server 2019 (15.x) CU 13. La valeur originatorid par défaut résout le conflit en fonction de l’ID d’origine et est identique à la résolution de conflit antérieure à SQL Server 2019 (15.x) CU 13.
    • Définissez @p2p_continue_onconflict = 'true' pour permettre à l’agent de distribution de résoudre le conflit.

  • Quand vous ajoutez l’article (sp_addarticle), confirmez le comportement du type de la commande de mise à jour (@upd_cmd). Options disponibles :

    • CALL (valeur par défaut)
    • SCALL

    Consultez les détails dans sp_addarticle (Transact-SQL).

  • Quand vous ajoutez un article (sp_addarticle) dans une publication avec une stratégie de détection de conflit de dernière écriture, utilisez CALL ou SCALL comme type de commande pour le paramètre @upd_cmd, CALL étant la valeur par défaut.

    Remarque

    SQL Server prend en charge SCALL pour @upd_cmd. Avec SCALL, lorsqu’une transaction met à jour une valeur sur la même valeur, elle n’est pas considérée comme une modification et SCALL un format ne fournit pas la valeur des colonnes qui ne sont pas mises à jour ou modifiées. Pour plus d’informations sur le format de l’appel SCALL, consultez Syntaxe d’appel pour les procédures stockées.

  • Vous pouvez utiliser la publication de pair à pair avec la détection et la résolution de conflit de dernière écriture dans un groupe de disponibilité. Consultez l'article :

  • Vous pouvez voir le conflit et sa résolution.

    • Dans SQL Server Management Studio, cliquez avec le bouton droit sur la publication et sélectionnez Voir les conflits.

    ou

  • Les conflits d’insertion et de mise à jour sont résolus en fonction de la dernière écriture, mais la suppression est toujours prioritaire. Par exemple, si vous avez un conflit suppression-mise à jour et que la mise à jour a été effectuée en dernier, la suppression est quand même prioritaire.

  • La détection et la résolution de conflit de dernière écriture sont déterminées en fonction d’une colonne masquée $sys_mw_cd_id. Le type de données de cette colonne est datetime2.

Comparaison de détection de conflit

Le tableau suivant compare la façon dont les conflits sont détectés et résolus avec la réplication de pair à pair classique, et quand la résolution de conflit de dernière écriture est activée :

Type de conflit Détails du conflit Pair à pair Dernière écriture
Insertion-insertion Toutes les lignes de chaque table qui participe à la réplication d'égal à égal sont identifiées de façon unique à l'aide de valeurs de clés primaires. Un conflit d'insertions se produit lorsqu'une ligne avec la même valeur de clé a été insérée sur plusieurs nœuds. Si la ligne entrante est prioritaire, nous mettons à jour la ligne de destination. Dans les deux cas, nous enregistrons les informations. Si la ligne entrante est prioritaire, nous mettons à jour la ligne de destination. Dans les deux cas, nous enregistrons les informations.
Mise à jour-mise à jour Se produit lorsque la même ligne a été mise à jour sur plusieurs nœuds. Si la ligne entrante est prioritaire, nous modifions UNIQUEMENT les colonnes changées. Si la ligne entrante est prioritaire, nous modifions toutes les colonnes sur la destination (si @upd_cmd est défini sur default (CALL)).
Mise à jour-insertion Se produit si une ligne a été mise à jour sur un nœud et que la même ligne a été supprimée, puis réinsérée sur un autre nœud. Si la ligne entrante est prioritaire, nous modifions UNIQUEMENT les colonnes changées. Cela se produit quand une ligne est mise à jour sur Peer1 et que la même ligne est supprimée, puis réinsérée sur Peer2. Au moment de la synchronisation, la ligne sur Peer1 est supprimée puisque la suppression est toujours prioritaire, puis la même ligne est insérée, alors que la ligne est mise à jour sur Peer2 puisque la mise à jour a eu lieu après. Cela entraîne une non-convergence.
Insertion-mise à jour Se produit si une ligne a été supprimée, puis réinsérée sur un nœud et que la même ligne a été mise à jour sur un autre nœud. Si la ligne entrante est prioritaire, nous mettons à jour toutes les colonnes. Cela se produit quand une ligne est supprimée, puis réinsérée sur Peer1 et que la même ligne est mise à jour sur Peer2. Au moment de la synchronisation, la ligne est supprimée sur Peer2 puisque la suppression est toujours prioritaire, puis elle est réinsérée. Sur Peer1, la mise à jour est ignorée.
Delete-Insert

Insertion-suppression		 Se produit si une ligne a été supprimée sur un nœud et que la même ligne a été supprimée puis réinsérée sur un autre nœud. Nous pensons qu’il s’agit d’un conflit suppression-mise à jour et, si la ligne entrante est prioritaire, nous supprimons la ligne de la destination. Cela se produit quand une ligne est supprimée sur Peer1 et que la même ligne est supprimée et réinsérée sur Peer2. Au moment de la synchronisation, la ligne sur Peer2 est supprimée, tandis que la ligne est insérée sur Peer1. Cela se produit parce que nous ne stockons pas d’informations sur la ligne supprimée. Nous ne savons donc pas si la ligne a été supprimée ou n’était pas présente sur l’homologue. Cela entraîne une non-convergence.
Suppression-mise à jour Se produit si une ligne a été supprimée sur un nœud et que la même ligne a été mise à jour sur un autre nœud. Nous pensons qu’il s’agit d’un conflit suppression-mise à jour et, si la ligne entrante est prioritaire, nous supprimons la ligne de la destination. Il s’agit d’un conflit suppression-mise à jour. Comme la suppression est toujours prioritaire, la suppression entrante gagne et nous supprimons la ligne dans la destination.
Mise à jour-suppression Se produit si une ligne a été mise à jour sur un nœud et que la même ligne a été supprimée sur un autre nœud. Dans la procédure stockée de mise à jour de pair à pair, s’il y a un conflit mise à jour-suppression, nous imprimons le message suivant et ne le résolvons pas.

An update-delete conflict was detected and unresolved. The row could not be updated since the row does not exist. 		Il s’agit d’un conflit mise à jour-suppression. Comme la suppression est toujours prioritaire, la mise à jour entrante est ignorée.
Suppression-suppression Se produit lorsqu'une ligne a été supprimée sur plusieurs nœuds. Dans la procédure stockée de suppression de pair à pair, s’il y a un conflit suppression-suppression, nous ne traitons pas les changements, mais nous les enregistrons. En cas de conflit suppression-suppression, nous ne traitons pas les changements, mais nous les enregistrons.

Remarque

Dans l’implémentation actuelle de la stratégie de détection de conflit de dernière écriture, la suppression est toujours prioritaire en cas de conflit insertion-suppression, suppression-insertion ou mise à jour-suppression.

Exemples

Créer une publication sur le premier pair (Node1)

Dans cet exemple, le script :

  • Publie une base de données appelée MWPubDB.
  • Nomme la publication PublMW.
  • Configure la détection de conflit et la stratégie de résolution avec la priorité à la dernière écriture :
    , @p2p_continue_onconflict= 'true'
    , @p2p_conflictdetection_policy = 'lastwriter'
USE [MWPubDB]
EXEC sp_replicationdboption @dbname = N'MWPubDB'
  , @optname = N'publish'
  , @value = N'true'
GO
-- Adding the transactional publication
USE [MWPubDB]
EXEC sp_addpublication @publication = N'PublMW'
  , @description = N'Peer-to-Peer publication of database ''MWPubDB'' from Publisher ''Node1''.'
  , @sync_method = N'native'
  , @retention = 0
  , @allow_push = N'true'
  , @allow_pull = N'true'
  , @allow_anonymous = N'false'
  , @enabled_for_internet = N'false'
  , @snapshot_in_defaultfolder = N'true'
  , @compress_snapshot = N'false'
  , @ftp_port = 21
  , @allow_subscription_copy = N'false'
  , @add_to_active_directory = N'false'
  , @repl_freq = N'continuous'
  , @status = N'active'
  , @independent_agent = N'true'
  , @immediate_sync = N'true'
  , @allow_sync_tran = N'false'
  , @allow_queued_tran = N'false'
  , @allow_dts = N'false'
  , @replicate_ddl = 1, @allow_initialize_from_backup = N'true'
  , @enabled_for_p2p = N'true'
  , @enabled_for_het_sub = N'false'
  , @p2p_conflictdetection = N'true'
  , @p2p_originator_id = 100
  , @p2p_continue_onconflict= 'true'
  , @p2p_conflictdetection_policy = 'lastwriter'
GO

USE [MWPubDB]
EXEC sp_addarticle @publication = N'PublMW'
  , @article = N'tab1'
  , @source_owner = N'dbo'
  , @source_object = N'tab1'
  , @type = N'logbased'
  , @description = null
  , @creation_script = null
  , @pre_creation_cmd = N'drop'
  , @schema_option = 0x0000000008035DDB
  , @identityrangemanagementoption = N'manual'
  , @destination_table = N'tab1'
  , @destination_owner = N'dbo'
  , @status = 16
  , @vertical_partition = N'false'
  , @ins_cmd = N'CALL sp_MSins_dbotab1'
  , @del_cmd = N'CALL sp_MSdel_dbotab1'
  , @upd_cmd = N'CALL sp_MSupd_dbotab1'
GO

Créer une publication sur le deuxième pair (Node2)

Le script ci-dessous crée la publication sur le deuxième pair (Node2).

USE [MWPubDB]
EXEC sp_replicationdboption @dbname = N'MWPubDB'
 , @optname = N'publish'
 , @value = N'true'
GO
-- Adding the transactional publication
USE [MWPubDB]
EXEC sp_addpublication @publication = N'PublMW'
 , @description = N'Peer-to-Peer publication of database ''MWPubDB'' from Publisher ''Node2''.'
 ,@sync_method = N'native'
 , @retention = 0, @allow_push = N'true'
 , @allow_pull = N'true'
 , @allow_anonymous = N'false'
 , @enabled_for_internet = N'false'
 , @snapshot_in_defaultfolder = N'true'
 , @compress_snapshot = N'false'
 , @ftp_port = 21, @allow_subscription_copy = N'false'
 , @add_to_active_directory = N'false'
 , @repl_freq = N'continuous'
 , @status = N'active'
 , @independent_agent = N'true'
 , @immediate_sync = N'true'
 , @allow_sync_tran = N'false'
 , @allow_queued_tran = N'false'
 , @allow_dts = N'false'
 , @replicate_ddl = 1, @allow_initialize_from_backup = N'true'
 , @enabled_for_p2p = N'true'
 , @enabled_for_het_sub = N'false'
 , @p2p_conflictdetection = N'true'
 , @p2p_originator_id = 1
 , @p2p_continue_onconflict= 'true'
,  @p2p_conflictdetection_policy = 'lastwriter'
GO

USE [MWPubDB]
EXEC sp_addarticle @publication = N'PublMW'
 , @article = N'tab1'
 , @source_owner = N'dbo'
 , @source_object = N'tab1'
 , @type = N'logbased'
 , @description = null
 , @creation_script = null
 , @pre_creation_cmd = N'drop'
 , @schema_option = 0x0000000008035DDB
 , @identityrangemanagementoption = N'manual'
 , @destination_table = N'tab1'
 , @destination_owner = N'dbo'
 , @status = 16, @vertical_partition = N'false'
 , @ins_cmd = N'CALL sp_MSins_dbotab1'
 , @del_cmd = N'CALL sp_MSdel_dbotab1'
 , @upd_cmd = N'CALL sp_MSupd_dbotab1'
GO

Créer un abonnement de Node1 à Node2

USE [MWPubDB]
EXEC sp_addsubscription @publication = N'PublMW'
 , @subscriber = N'Node2'
 , @destination_db = N'MWPubDB'
 , @subscription_type = N'Push'
 , @sync_type = N'replication support only'
 , @article = N'all'
 , @update_mode = N'read only'
 , @subscriber_type = 0
GO
EXEC sp_addpushsubscription_agent @publication = N'PublMW'
 , @subscriber = N'Node2'
 , @subscriber_db = N'MWPubDB'
 , @job_login = null
 , @job_password = null
 , @subscriber_security_mode = 1
 , @frequency_type = 64
 , @frequency_interval = 1
 , @frequency_relative_interval = 1
 , @frequency_recurrence_factor = 0
 , @frequency_subday = 4
 , @frequency_subday_interval = 5
 , @active_start_time_of_day = 0
 , @active_end_time_of_day = 235959
 , @active_start_date = 0 
 , @active_end_date = 0
 , @dts_package_location = N'Distributor'
GO

Créer un abonnement de Node2 à Node1

USE [MWPubDB]
EXEC sp_addsubscription @publication = N'PublMW'
 , @subscriber = N'Node1'
 , @destination_db = N'MWPubDB'
 , @subscription_type = N'Push',
@sync_type = N'replication support only'
 , @article = N'all'
 , @update_mode = N'read only'
 , @subscriber_type = 0
go
EXEC sp_addpushsubscription_agent @publication = N'PublMW'
 , @subscriber = N'Node1'
 , @subscriber_db = N'MWPubDB'
 , @job_login = null
 , @job_password = null
 , @subscriber_security_mode = 1
 , @frequency_type = 64
 , @frequency_interval = 1
 , @frequency_relative_interval = 1
 , @frequency_recurrence_factor = 0
 , @frequency_subday = 4
 , @frequency_subday_interval = 5
 , @active_start_time_of_day = 0
 , @active_end_time_of_day = 235959
 , @active_start_date = 0
 , @active_end_date = 0
 , @dts_package_location = N'Distributor'
GO

Voir aussi