Réplication Transactionnelle avec Mise à jour des abonnés …

 

Pour ceux qui n’ont pas encore utilisé la Réplication sous SQL Server, et en particulier la réplication transactionnelle avec mise à jour des abonnés, voici une petite info qui pourrait être utile …

 

Prenez une table toute simple.

 

CREATE TABLE [dbo].[Table_1](
  [ID]     [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

  [Server] [varchar] (10) NULL, — sert seulement à savoir sur que serveur je fais l’insert ….
  [Value]  [datetime]  NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
  ([ID] ASC)
) ON [PRIMARY]

 

Je vous avais dit une table vraiment simple ….

Vous ajoutez un zeste de réplication transactionnelle avec mise à jour des abonnés. Sachant que la primary key est de type integer, SQL Server va ajouter une colonne de type uniqueidentifier pour s’y retrouver lorsque vous allez faire des mises à jour de données sur l’abonné.

 

Cela peut donner quelque chose comme :
[msrepl_tran_version] [uniqueidentifier] NOT NULL CONSTRAINT
[MSrepl_tran_version_default_64008311_A5F8_4D7D_AF2B_E7E017117C3F_2073058421] DEFAULT (newid())
si vous allez jeter un oeil  la table une fois la réplication mise en place. Je vous fais grâce des contraintes qui vont avec.

 

Maintenant, passons à ce qui nous préoccupe vraiment : Quid de cet autoincrément lorsque je vais faire des Inserts dans la table, sur l’éditeur, et sur l’abonné …

 

Mais, tant que j’y pense, au niveau des requêtes SQL, pensez spécifier tous les champs dans les clauses Insert …

 

Cela nous fait donc une requête du style :
INSERT INTO [dbo].[Table_1]
  ([Server],[Value])
VALUES
  (‘Editeur’,Getdate())

Ou bien

INSERT INTO [dbo].[Table_1]
  ([Server],[Value])
VALUES
  (‘Abonné’,Getdate())

 

Exécutons une dizaines de fois la première requête sur le serveur éditeur.

Nous constatons, fort logiquement, que le champ ID contient des valeurs séquentielles.

 

Exécutons maintenant une dizaine d’inserts sur le serveur abonné (seconde requête).

Nous constatons alors que la valeur du champ incrément ID est … 20001, alors que la définition de la table répliquée spécifie bien Identity(1,1).

 

Exécutons à nouveau la requête insert sur le serveur éditeur : le champ ID reprend là où il en est resté.

Faisons donc des inserts en masse (mais par paquet) pour nous rapprocher des 20000 enregistrements dans la table, pour voir le comportement de SQL Server.

Résultat : SQL Server va combler les trous, ou du moins en partie …. Dans mon cas précis, au delà de 19982, SQL est passé à 21001.

 

Faisons un insert en masse sur l’abonné. Il reprend sa course dans les 20012 (oui, j’avais seulement fait 11 inserts sur l’abonné).

Résultat : SQL a comblé les trous jusqu’à 20898 (a partir de 20895, je faisais 1 insert a la fois). Donc, nous n’avons pas de valeur pour 20899, 20900, 20901 …. 21000. La reprise de la séquence s’est faire à 31001.

 

Voici donc quelques informations que l’on peut tirer de ce petit exemple :

• SQL Server est suffisamment bien conçu pour ne pas se retrouver dans une situation avec des doublons pour la primary key
• SQL Server est capable de combler un certain nombre de trous dans la séquence de l’autoincrément.
• Par contre, si vous êtes attaché à ce numéro de séquence, qu’il ne doit pas y avoir d’interruption dans la suite, il vous faudra le gérer différemment : PK sur un champ de type uniqueidentifier (dans ce cas SQL Server ne rajoute pas de colonne) et un champ integer dont vous gérez seul la séquence …
• Par ailleurs, portez attention au tri des données. Si par exemple cette table traite de commandes, l’ordre des enregistrements suivant le PK n’est pas du tout représentatif de l’ordre de création de l’enregistrement. Une ligne ayant pour PK 18000 peut avoir été créé bien après une ligne ayant pour identifiant 20500 !!!

Pour conclure, n’hésitez pas, cela fonctionne vraiment très bien. Vous pouvez même vous amuser à mettre offline une des bases (pour simuler une panne sur un serveur), l’activité se poursuit sur l’instance etant opérationnelle. Si vous avez opté pour une mise à jour au travers de file d’attente, dès que les bases sont opérationnelles, SQL Server va synchroniser toutes les données manquantes.