Répliquer des colonnes d’identité

Lorsque vous affectez une propriété IDENTITY à une colonne, Microsoft SQL Server génère automatiquement des nombres séquentiels pour les nouvelles lignes insérées dans la table contenant la colonne d’identité. Pour plus d’informations, consultez IDENTITY (Propriété) (Transact-SQL). Étant donné que les colonnes d’identité peuvent être incluses dans le cadre de la clé primaire, il est important d’éviter les valeurs en double dans les colonnes d’identité. Pour utiliser des colonnes d’identité dans une topologie de réplication qui a des mises à jour à plusieurs nœuds, chaque nœud de la topologie de réplication doit utiliser une plage différente de valeurs d’identité, afin que les doublons ne se produisent pas.

Par exemple, l’éditeur peut recevoir la gamme 1-100, l’abonné A la gamme 101-200 et l’abonné B la gamme 201-300. Si une ligne est insérée sur le serveur de publication et que la valeur d’identité est, par exemple, 65, cette valeur est répliquée sur chaque Abonné. Lorsque la réplication insère des données sur chaque Abonné, elle n’incrémente pas la valeur de colonne d’identité dans la table Abonné ; à la place, la valeur littérale 65 est insérée. Seules les insertions des utilisateurs, mais pas celles de l’agent de réplication, provoquent l’incrémentation de la valeur de la colonne d’identité.

La réplication gère les colonnes d’identité sur tous les types de publication et d’abonnement, vous permettant de gérer les colonnes manuellement ou de laisser la réplication les gérer automatiquement.

Spécification d’une option de gestion des plages d’identité

La réplication offre trois options de gestion des intervalles d'identités.

• Automatique. Utilisé pour la réplication de fusion et la réplication transactionnelle avec des mises à jour chez l’Abonné. Spécifiez des plages de taille pour les serveurs de publication et les abonnés, et la réplication gère automatiquement l’attribution de nouvelles plages. La réplication définit l'option NOT FOR REPLICATION sur la colonne d'identité chez l'abonné, de sorte que seules des insertions d'utilisateur provoquent l'incrémentation de la valeur chez l'abonné.

  Remarque

  Les abonnés doivent se synchroniser avec le serveur de publication pour recevoir de nouvelles plages. Étant donné que les abonnés reçoivent automatiquement des plages d’identités, il est possible pour tous les Abonnés d’épuiser l’ensemble de l’approvisionnement des plages d’identité s’il demande à plusieurs reprises de nouvelles plages.

• Manuelle. Utilisé pour la réplication instantanée et transactionnelle sans mises à jour sur l’abonné, la réplication transactionnelle pair-à-pair ou si votre application doit contrôler les plages d'identifiants de manière programmatique. Si vous spécifiez une gestion manuelle, vous devez vous assurer que les plages sont affectées au serveur de publication et à chaque Abonné et que de nouvelles plages sont affectées si les plages initiales sont utilisées. La réplication définit l’option "NOT FOR REPLICATION" sur la colonne d’identité de l’abonné.

• Aucun. Cette option est recommandée uniquement pour la compatibilité descendante avec les versions antérieures de SQL Server et est disponible uniquement à partir de l’interface de procédure stockée pour les publications transactionnelles.

Pour spécifier une option de gestion des plages d’identité, consultez Gérer les colonnes d’identité.

Affectation de plages d’identités

La réplication de fusion et la réplication transactionnelle utilisent différentes méthodes pour affecter des plages ; ces méthodes sont décrites dans cette section.

Il existe deux types de plages à prendre en compte lors de la réplication des colonnes d’identité : les plages affectées au serveur de publication et aux abonnés, ainsi que la plage du type de données dans la colonne. Le tableau suivant présente les plages disponibles pour les types de données généralement utilisés dans les colonnes d’identité. La plage est utilisée dans tous les nœuds d'une topologie. Par exemple, si vous utilisez smallint à partir de 1 avec un incrément de 1, le nombre maximal d’insertions est de 32 767 pour le serveur de publication et tous les Abonnés. Le nombre réel d’insertions varie selon qu’il existe des lacunes dans les valeurs utilisées et si une valeur de seuil est utilisée. Pour plus d’informations sur les seuils, consultez les sections suivantes « Réplication de fusion » et « Réplication transactionnelle avec abonnements mis à jour en file d’attente ».

Si le serveur de publication épuise sa plage d’identité après une insertion, il peut affecter automatiquement une nouvelle plage si l’insertion a été effectuée par un membre du rôle de base de données fixe db_owner . Si l’insertion a été effectuée par un utilisateur qui ne fait pas partie de ce rôle, alors l'Agent de lecture du journal, l'Agent de fusion ou un utilisateur qui est membre du rôle db_owner doit alors exécuter sp_adjustpublisheridentityrange (Transact-SQL). Pour les publications transactionnelles, l’Agent de lecture du journal doit s’exécuter pour allouer automatiquement une nouvelle plage (la valeur par défaut est que l’agent s’exécute en continu).

Réplication de fusion

Les plages d'identité sont gérées par le serveur de publication et propagées aux abonnés par l'agent de fusion (dans une hiérarchie de republication, les plages sont gérées par le serveur de publication racine et les republicateurs). Les valeurs d’identité sont affectées à partir d’un pool sur le serveur de publication. Lorsque vous ajoutez un article avec une colonne d’identité à une publication dans l’Assistant Nouvelle publication ou à l’aide de sp_addmergearticle (Transact-SQL), vous spécifiez les valeurs pour :

• Le paramètre @identity_range, qui contrôle la taille de la plage d'identité initialement allouée à la fois au serveur de publication et aux abonnés avec des abonnements client.

  Remarque

  Pour les Abonnés exécutant des versions antérieures de SQL Server, ce paramètre (plutôt que le paramètre @pub_identity_range ) contrôle également la taille de la plage d’identité lors de la republiation des Abonnés.

• Paramètre @pub_identity_range , qui contrôle la taille de la plage d’identité pour la republication allouée à l’abonné avec des abonnements serveur (requis pour la republication des données). Tous les abonnés disposant d'abonnements serveur reçoivent une plage pour la republication, même s'ils ne republient pas réellement de données.

• Paramètre @threshold , utilisé pour déterminer quand une nouvelle plage d’identités est requise pour un abonnement à SQL Server Compact ou une version précédente de SQL Server.

Par exemple, vous pouvez spécifier 10000 pour @identity_range et 500000 pour @pub_identity_range. Le serveur de publication et tous les Abonnés exécutant SQL Server 2005 ou une version ultérieure, y compris l’Abonné avec l’abonnement au serveur, sont affectés à une plage principale de 1 0000. L’Abonné avec l’abonnement au serveur est également affecté à une plage principale de 500000, qui peut être utilisée par les Abonnés qui se synchronisent avec l’Abonné de re-diffusion (vous devez également spécifier @identity_range, @pub_identity_range et @threshold pour les articles de la publication sur l’Abonné de re-diffusion).

Chaque Abonné exécutant SQL Server 2005 ou une version ultérieure reçoit également une plage d’identités secondaire. La plage secondaire est égale à la taille de la plage primaire ; lorsque la plage primaire est épuisée, la plage secondaire est utilisée et l’Agent de fusion affecte une nouvelle plage à l’Abonné. La nouvelle plage devient la plage secondaire et le processus se poursuit à mesure que l’Abonné utilise des valeurs d’identité.

Réplication transactionnelle avec des abonnements à mise à jour en file d'attente

Les plages d’identité sont gérées par le serveur de distribution et propagées aux Abonnés par l’Agent de distribution. Les valeurs d’identité sont affectées à partir d’un pool sur le serveur de distribution. La taille du pool est basée sur la taille du type de données et l’incrément utilisé pour la colonne d’identité. Lorsque vous ajoutez un article avec une colonne d’identité à une publication dans l’Assistant Nouvelle publication ou à l’aide de sp_addarticle (Transact-SQL), vous spécifiez des valeurs pour :

• Paramètre @identity_range , qui contrôle la taille de plage d’identité initialement allouée à tous les Abonnés.

• Paramètre @pub_identity_range, qui contrôle la taille de plage d'identité allouée à l'éditeur.

• Paramètre @threshold , utilisé pour déterminer quand une nouvelle plage d’identités est requise pour un abonnement.

Par exemple, vous pouvez spécifier 10000 pour @pub_identity_range, 1000 pour @identity_range (en supposant moins de mises à jour sur l’Abonné) et 80 % pour @threshold. Après 800 insertions sur un Abonné (80 % de 1 000), un Abonné reçoit une nouvelle plage. Après 8000 inserts chez l'éditeur, celui-ci reçoit une nouvelle plage. Lorsqu’une nouvelle plage est affectée, il y aura un écart dans les valeurs de plage d’identité dans la table. La spécification d’un seuil plus élevé entraîne des lacunes plus petites, mais le système est moins tolérant aux pannes : si l’Agent de distribution ne peut pas s’exécuter pour une raison quelconque, un Abonné peut plus facilement manquer d’identités.

Affectation de plages pour la gestion manuelle des plages d’identités

Si vous spécifiez une gestion manuelle des plages d’identités, vous devez vous assurer que le serveur de publication et chaque Abonné utilisent différentes plages d’identités. Par exemple, considérez une table sur le serveur de publication avec une colonne d’identité définie comme IDENTITY(1,1)suit : la colonne d’identité commence à 1 et est incrémentée de 1 chaque fois qu’une ligne est insérée. Si la table sur le serveur de publication comporte 5 000 lignes et que vous prévoyez une croissance de la table sur la durée de vie de l’application, le serveur de publication peut utiliser la plage de 1 à 10 000. Compte tenu de deux Abonnés, l’Abonné A peut utiliser 10 001-20 000 et l’Abonné B peut utiliser 20 001 à 30 000.

Une fois qu’un Abonné est initialisé avec un instantané ou via un autre moyen, exécutez DBCC CHECKIDENT pour affecter à l’Abonné un point de départ pour sa plage d’identité. Par exemple, chez l'abonné A, vous devez exécuter DBCC CHECKIDENT('<TableName>','reseed',10001). Chez l’abonné B, vous exécuteriez CHECKIDENT('<TableName>','reseed',20001).

Pour affecter de nouvelles plages aux serveurs de publication ou aux abonnés, exécutez DBCC CHECKIDENT et spécifiez une nouvelle valeur pour réexécuter la table. Vous devez avoir un moyen de déterminer quand une nouvelle plage doit être affectée. Par exemple, votre application peut avoir un mécanisme qui détecte lorsqu’un nœud est sur le point d’utiliser sa plage et d’affecter une nouvelle plage à l’aide de DBCC CHECKIDENT. Vous pouvez également ajouter une contrainte de vérification pour vous assurer qu’une ligne ne peut pas être ajoutée si elle entraînerait l’utilisation d’une valeur d’identité hors plage.

Gestion des plages d’identité après une restauration de base de données

Si vous utilisez la gestion automatique des plages d’identités, lorsqu’un Abonné est restauré à partir d’une sauvegarde, il demande automatiquement une nouvelle plage de valeurs d’identité. Si un éditeur est restauré à partir d'une sauvegarde, vous devez veiller à ce que l'éditeur reçoive une plage de valeurs appropriée. Pour la réplication de fusion, affectez une nouvelle plage en utilisant sp_restoremergeidentityrange (Transact-SQL). Pour la réplication transactionnelle, déterminez la valeur la plus élevée utilisée, puis définissez le point de départ des nouvelles plages. Utilisez la procédure suivante après la restauration de la base de données de publication :

1. Arrêtez toutes les activités sur tous les Abonnés.

2. Pour chaque table publiée qui inclut une colonne d’identité :

   1. Dans la base de données d’abonnement sur chaque Abonné, exécutez IDENT_CURRENT('<TableName>').

   2. Notez la valeur la plus élevée trouvée chez tous les abonnés.

   3. Dans la base de données de publication sur le serveur de publication, exécutez DBCC CHECKIDENT(<TableName>','reseed',<HighestValueFound+1>).

   4. Dans la base de données de publication chez l'éditeur, exécutez sp_adjustpublisheridentityrange <PublicationName>, <TableName>.

   Remarque

   Si la valeur de la colonne d'identité est configurée pour décroître au lieu d'augmenter, enregistrez la plus petite valeur trouvée, puis réinitialisez avec cette valeur.

Voir aussi

BACKUP (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL)
IDENT_CURRENT (Transact-SQL)
IDENTITY (Propriété) (Transact-SQL)
sp_adjustpublisheridentityrange (Transact-SQL)