Replicar colunas de identidadeReplicate Identity Columns

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure (somente a Instância Gerenciada) nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Quando se atribui uma propriedade IDENTITY a uma coluna, o MicrosoftMicrosoft SQL ServerSQL Server gera automaticamente números sequenciais para novas linhas inseridas na tabela que contém a coluna de identidade.When you assign an IDENTITY property to a column, MicrosoftMicrosoft SQL ServerSQL Server automatically generates sequential numbers for new rows inserted in the table containing the identity column. Para obter mais informações, consulte IDENTITY (Property) (Transact-SQL).For more information, see IDENTITY (Property) (Transact-SQL). Como as colunas de identidade podem ser incluídas como parte da chave primária, é importante evitar valores duplicados nas colunas de identidade.Because identity columns might be included as a part of the primary key, it is important to avoid duplicate values in the identity columns. Para que colunas de identidade sejam usadas em uma topologia de replicação que tenha atualizações em mais de um nó, cada nó da topologia de replicação precisará usar um intervalo diferente de valores de identidade, de modo que não ocorram duplicatas.To use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur.

Por exemplo, o intervalo de 1 a 100 poderia ser atribuído ao Publicador; o intervalo de 101 a 200 ao Assinante A e o intervalo de 201 a 300 ao Assinante B.For example, the Publisher could be assigned the range 1-100, Subscriber A the range 101-200, and Subscriber B the range 201-300. Se uma linha for inserida no Publicador e o valor de identidade for, por exemplo, 65, esse valor será replicado para cada Assinante.If a row is inserted at the Publisher and the identity value is, for example, 65, that value is replicated to each Subscriber. Quando a replicação insere dados em cada Assinante, isso não incrementa o valor da coluna de identidade na tabela Assinante. Em vez disso, o valor literal 65 é inserido.When replication inserts data at each Subscriber, it does not increment the identity column value in the Subscriber table; instead, the literal value 65 is inserted. Somente as inserções do usuário, não as inserções do agente de replicação, causam o incremento da coluna de identidade.Only user inserts, but not replication agent inserts cause the identity column value to be incremented.

A replicação trata colunas de identidade em todos os tipos de publicação e assinatura, o que permite gerenciar as colunas manualmente ou fazer com que a replicação as gerencie de forma automática.Replication handles identity columns across all publication and subscription types, allowing you to manage the columns manually or have replication manage them automatically.

Observação

Não há suporte para adicionar uma coluna de identidade a uma tabela publicada, pois isso pode resultar em não convergência quando a coluna é replicada no Assinante.Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. Os valores na coluna de identidade no Publicador dependerão da ordem em que as linhas para a tabela afetada forem armazenadas fisicamente.The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. As linhas podem ser armazenadas de forma diversa no Assinante; assim, o valor da coluna de identidade pode ser diferente para as mesmas linhas.The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows.

Especificando uma opção de gerenciamento de intervalo de identidadeSpecifying an Identity Range Management Option

A replicação oferece três opções de gerenciamento de intervalo de identidade:Replication offers three identity range management options:

  • Automático.Automatic. Usado para replicação de mesclagem e replicação transacional com atualizações do Assinante.Used for merge replication and transactional replication with updates at the Subscriber. Especifique os intervalos de tamanho para o Publicador e para os Assinantes, e a replicação gerenciará automaticamente a atribuição de novos intervalos.Specify size ranges for the Publisher and Subscribers, and replication automatically manages the assignment of new ranges. A replicação define a opção NOT FOR REPLICATION na coluna de identidade do Assinante, de modo que somente as inserções do usuário geram o valor a ser incrementado no Assinante.Replication sets the NOT FOR REPLICATION option on the identity column at the Subscriber, so that only user inserts cause the value to be incremented at the Subscriber.

    Observação

    Para que novos intervalos sejam recebidos é preciso que haja sincronização entre os Assinantes e o Publicador.Subscribers must synchronize with the Publisher to receive new ranges. Como os intervalos de identidade são atribuídos automaticamente aos Assinantes, é possível que qualquer Assinante esgote todo o fornecimento de intervalos de identidade quando novos intervalos são solicitados repetidamente.Because Subscribers are assigned identity ranges automatically, it is possible for any Subscriber to exhaust the entire supply of identity ranges if it repeatedly requests new ranges.

  • Manual.Manual. Usado para replicação de instantâneo e transacional sem atualizações no Assinante, replicação transacional ponto a ponto ou quando é necessário que o aplicativo controle de forma programática os intervalos de identidade.Used for snapshot and transactional replication without updates at the Subscriber, peer-to-peer transactional replication, or if your application must control identity ranges programmatically. Se o gerenciamento manual for especificado, será preciso assegurar que intervalos sejam atribuídos ao Publicador e a cada Assinante e que novos intervalos sejam atribuídos, caso os intervalos iniciais sejam utilizados.If you specify manual management, you must ensure that ranges are assigned to the Publisher and each Subscriber and that new ranges are assigned if the initial ranges are used. A replicação define a opção NOT FOR REPLICATION na coluna de identidade do Assinante.Replication sets the NOT FOR REPLICATION option on the identity column at the Subscriber.

  • Nenhum.None. Essa opção é recomendada apenas para compatibilidade com versões anteriores do SQL ServerSQL Server , sendo disponibilizada apenas na interface do procedimento armazenado para as publicações transacionais.This option is recommended only for backwards compatibility with earlier versions of SQL ServerSQL Server and is available only from the stored procedure interface for transactional publications.

Para especificar uma opção de gerenciamento de intervalos de identidade, consulte Gerenciar colunas de identidade.To specify an identity range management option, see Manage Identity Columns.

Atribuindo intervalos de identidadeAssigning Identity Ranges

A replicação de mesclagem e a replicação transacional usam diferentes métodos para a atribuição de intervalos. Esses métodos são descritos nesta seção.Merge replication and transactional replication use different methods for assigning ranges; these methods are described in this section.

Há dois tipos de intervalos a serem considerados quando se replicam colunas de identidade: os intervalos atribuídos ao Publicador e Assinantes, e o intervalo do tipo de dados na coluna.There are two types of ranges to take into account when replicating identity columns: the ranges assigned to the Publisher and Subscribers, and the range of the data type in the column. A tabela a seguir mostra os intervalos disponíveis para os tipos de dados usados normalmente em colunas de identidade.The following table shows the ranges available for the data types typically used in identity columns. O intervalo é usado em todos os nós de uma topologia.The range is used across all nodes in a topology. Por exemplo, ao usar smallint começando em 1 com um incremento de 1, o número máximo de inserções será de 32.767 para o Publicador e todos os Assinantes.For example, if you use smallint starting at 1 with an increment of 1, the maximum number of inserts is 32,767 for the Publisher and all Subscribers. O número real de inserções depende da existência de lacunas nos valores usados e da utilização de um valor limite.The actual number of inserts depends on whether there are gaps in the values used and whether a threshold value is used. Para obter mais informações sobre limites, consulte as seções a seguir, "Replicação de mesclagem" e "Replicação transacional com assinaturas de atualização enfileirada".For more information about thresholds, see the following sections "Merge Replication" and "Transactional Replication with Queued Updating Subscriptions".

Se o Publicador esgotar seu intervalo de identidade após uma inserção, ele poderá atribuir um novo intervalo se a inserção tiver sido realizada por um membro de uma função de banco de dados fixa db_owner .If the Publisher exhausts its identity range after an insert, it can automatically assign a new range if the insert was performed by a member of the db_owner fixed database role. Se a inserção tiver sido realizada por um usuário que não estava nessa função, o Agente de Leitor de Log, o Agente de Mesclagem ou um usuário que é membro da função db_owner deverá executar sp_adjustpublisheridentityrange (Transact-SQL).If the insert was performed by a user not in that role, the Log Reader Agent, Merge Agent, or a user who is a member of the db_owner role must run sp_adjustpublisheridentityrange (Transact-SQL). Para publicações transacionais, o Agente de Leitor de Log deverá estar em execução para alocar automaticamente um novo intervalo (o padrão é que o agente seja executado continuamente).For transactional publications, the Log Reader Agent must be running to automatically allocate a new range (the default is for the agent to run continuously).

Aviso

Durante uma inserção de lote grande, o gatilho de replicação é disparado apenas uma vez, e não para cada linha da inserção.During a large batch insert the replication trigger is fired only once, not for each row of the insert. Isso pode resultar em falha na instrução de inserção se um intervalo de identidade for esgotado durante uma inserção grande, como a instrução INSERT INTO .This can lead to a failure of the insert statement if an identity range is exhausted during an large insert, such as an INSERT INTO statement.

Tipo de dadosData type IntervaloRange
tinyinttinyint Não tem suporte para gerenciamento automáticoNot supported for automatic management
smallintsmallint -2^15 (-32.768) a 2^15-1 (32.767)-2^15 (-32,768) to 2^15-1 (32,767)
intint -2^31 (-2.147.483.648) a 2^31-1 (2.147.483.647)-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
bigintbigint -2^63 (-9.223.372.036.854.775.808) a 2^63-1 (9.223.372.036.854.775.807)-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
decimal e numericdecimal and numeric -10^38+1 até 10^38-1-10^38+1 through 10^38-1

Observação

Para criar um número incrementado automaticamente, que possa ser usado em várias tabelas ou ser chamado de aplicativos, sem referenciar tabelas, consulte Números de Sequência.To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.

Replicação de mesclagemMerge Replication

Os intervalos de identidade são gerenciados pelo Publicador e propagados para os Assinantes pelo Merge Agent (em uma hierarquia de republicação, os intervalos são gerenciados pelo Publicador raiz e pelos republicadores).Identity ranges are managed by the Publisher and propagated to Subscribers by the Merge Agent (in a republishing hierarchy, ranges are managed by the root Publisher and the republishers). Os valores de identidade são atribuídos em um pool do Publicador.The identity values are assigned from a pool at the Publisher. Ao adicionar um artigo com uma coluna de identidade a uma publicação no Assistente para Nova Publicação ou ao usar sp_addmergearticle (Transact-SQL), especifique valores para:When you add an article with an identity column to a publication in the New Publication Wizard or by using sp_addmergearticle (Transact-SQL), you specify values for:

  • O parâmetro @identity_range, que controla o tamanho do intervalo da identidade inicialmente alocado tanto para o Publicador como para Assinantes com assinaturas de cliente.The @identity_range parameter, which controls the identity range size initially allocated both to the Publisher and to Subscribers with client subscriptions.

    Observação

    Com relação aos Assinantes que executam versões anteriores do SQL ServerSQL Server, esse parâmetro (em vez do parâmetro @pub_identity_range) também controla o tamanho do intervalo da identidade nos Assinantes de republicação.For Subscribers running previous versions of SQL ServerSQL Server, this parameter (rather than the @pub_identity_range parameter) also controls the identity range size at republishing Subscribers.

  • O parâmetro @pub_identity_range, que controla o tamanho do intervalo da identidade para republicação, alocado para Assinantes com assinaturas de servidor (necessárias aos dados de republicação).The @pub_identity_range parameter, which controls the identity range size for republishing allocated to Subscribers with server subscriptions (required for republishing data). Todos os Assinantes com assinaturas de servidor recebem um intervalo para republicar, mesmo se eles de fato não republiquem dados.All Subscribers with server subscriptions receive a range for republishing, even if they don't actually republish data.

  • O parâmetro @threshold, usado para determinar quando um novo intervalo de identidades é exigido para uma assinatura do SQL Server CompactSQL Server Compact ou para versões anteriores do SQL ServerSQL Server.The @threshold parameter, which is used to determine when a new range of identities is required for a subscription to SQL Server CompactSQL Server Compact or a previous version of SQL ServerSQL Server.

Por exemplo, você poderia especificar 10.000 para @identity_range e 500.000 para @pub_identity_range.For example, you could specify 10000 for @identity_range and 500000 for @pub_identity_range. Um intervalo primário de 10.000 é atribuído ao Publicador e a todos os Assinantes que executam o SQL Server 2005 (9.x)SQL Server 2005 (9.x) ou uma versão posterior, inclusive ao Assinante com a assinatura do servidor.The Publisher and all Subscribers running SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version, including the Subscriber with the server subscription, are assigned a primary range of 10000. Ao Assinante com a assinatura do servidor também se atribui um intervalo primário de 500.000, que pode ser usado pelos Assinantes que se sincronizam com o Assinante de republicação (é preciso também especificar @identity_range, @pub_identity_range e @threshold para os artigos na publicação no Assinante de republicação).The Subscriber with the server subscription is also assigned a primary range of 500000, which can be used by Subscribers that synchronize with the republishing Subscriber (you must also specify @identity_range, @pub_identity_range, and @threshold for the articles in the publication at the republishing Subscriber).

Todo Assinante que executa o SQL Server 2005 (9.x)SQL Server 2005 (9.x) ou uma versão posterior também recebe um intervalo de identidade secundário.Each Subscriber running SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version also receives a secondary identity range. O intervalo secundário é igual em tamanho ao intervalo primário. Quando o intervalo primário se esgota, o intervalo secundário é usado, e o Merge Agent atribui um novo intervalo ao Assinante.The secondary range is equal in size to the primary range; when the primary range is exhausted, the secondary range is used, and the Merge Agent assigns a new range to the Subscriber. O novo intervalo passa a ser o intervalo secundário, e o processo continua à medida que o Assinante utiliza valores de identidade.The new range becomes the secondary range, and the process continues as the Subscriber uses identity values.

Replicação transacional com assinaturas de atualização enfileiradaTransactional Replication with Queued Updating Subscriptions

Os intervalos de identidade são gerenciados pelo Distribuidor e propagados para os Assinantes pelo Distribution Agent.Identity ranges are managed by the Distributor and propagated to Subscribers by the Distribution Agent. Os valores de identidade são atribuídos em um pool do Distribuidor.The identity values are assigned from a pool at the Distributor. O tamanho do pool baseia-se no tamanho dos tipos de dados e no incremento usado para a coluna de identidade.The pool size is based on the size of the data type and the increment used for the identity column. Ao adicionar um artigo com uma coluna de identidade a uma publicação no Assistente para Nova Publicação ou ao usar sp_addarticle (Transact-SQL), especifique valores para:When you add an article with an identity column to a publication in the New Publication Wizard or by using sp_addarticle (Transact-SQL), you specify values for:

  • O parâmetro @identity_range, que controla o tamanho de intervalo de identidade inicialmente alocado a todos os Assinantes.The @identity_range parameter, which controls the identity range size initially allocated to all Subscribers.

  • O parâmetro @pub_identity_range, que controla o tamanho de intervalo de identidade alocado a todos os Publicadores.The @pub_identity_range parameter, which controls the identity range size allocated to the Publisher.

  • O parâmetro @threshold, que é usado para determinar quando um novo intervalo de identidades é necessário à assinatura.The @threshold parameter, which is used to determine when a new range of identities is required for a subscription.

Por exemplo, você poderia especificar 10.000 para @pub_identity_range; 1.000 para @identity_range (assumindo menos atualizações no Assinante), e 80% para @threshold.For example, you could specify 10000 for @pub_identity_range, 1000 for @identity_range (assuming fewer updates at the Subscriber), and 80 percent for @threshold. Após 800 inserções em um Assinante (80 por cento de 1.000), um Assinante é atribuído a um novo intervalo.After 800 inserts at a Subscriber (80 percent of 1000), a Subscriber is assigned a new range. Depois de 8.000 inserções em um Publicador, um novo intervalo é atribuído ao Publicador.After 8000 inserts at the Publisher, the Publisher is assigned a new range. Quando o novo intervalo é atribuído, há uma lacuna nos valores de intervalo de identidade da tabela.When a new range is assigned, there will be a gap in the identity range values in the table. Especificar um limite superior resulta em lacunas menores, mas o sistema torna-se menos tolerante a falhas. Se o Merge Agent não puder ser executado por algum motivo, um Assinante poderá ficar mais facilmente sem identidades.Specifying a higher threshold results in smaller gaps, but the system is less fault-tolerant: if the Distribution Agent cannot run for some reason, a Subscriber could more easily run out of identities.

Atribuindo intervalos para o gerenciamento manual de intervalo de identidadeAssigning ranges for manual identity range management

Caso o gerenciamento manual de identidade seja especificado, será preciso assegurar que o Publicador e cada um dos Assinantes usem intervalos de identidade diferentes.If you specify manual identity range management, you must ensure that the Publisher and each Subscriber use different identity ranges. Por exemplo, considere uma tabela do Publicador com coluna de identidade definida como IDENTITY(1,1): a coluna de identidade começa com 1 e é incrementada em 1 toda vez que uma linha é inserida.For example, consider a table at the Publisher with an identity column defined as IDENTITY(1,1): the identity column starts at 1 and is incremented by 1 each time a row is inserted. Se a tabela do Publicador tiver 5.000 linhas, e houver expectativa de algum aumento da tabela durante a vida útil do aplicativo, o Publicador poderá usar o intervalo de 1 a 10.000.If the table at the Publisher has 5,000 rows, and you expect some growth in the table over the life of the application, the Publisher could use the range 1-10,000. Considerando-se dois Assinantes, o Assinante A poderá usar de 10.001 a 20.000 e o Assinante B poderá usar de 20.001 a 30.000.Given two Subscribers, Subscriber A could use 10,001-20,000, and Subscriber B could use 20,001-30,000.

Após o Assinante ser iniciado com um instantâneo ou por outros meios, execute DBCC CHECKIDENT para atribuir ao Assinante um ponto inicial para o seu intervalo de identidade.After a Subscriber is initialized with a snapshot or through another means, execute DBCC CHECKIDENT to assign the Subscriber a starting point for its identity range. Por exemplo, no Assinante A, DBCC CHECKIDENT('<TableName>','reseed',10001)seria executado.For example, at Subscriber A, you would execute DBCC CHECKIDENT('<TableName>','reseed',10001). No Assinante B, CHECKIDENT('<TableName>','reseed',20001)seria executado.At Subscriber B, you would execute CHECKIDENT('<TableName>','reseed',20001).

Para atribuir novos intervalos ao Publicador ou aos Assinantes, execute DBCC CHECKIDENT e especifique um novo valor para semear novamente a tabela.To assign new ranges to the Publisher or Subscribers, execute DBCC CHECKIDENT and specify a new value to reseed the table. Deve haver algum modo de determinar quando um novo intervalo precisa ser atribuído.You should have some way to determine when a new range must be assigned. Por exemplo, o aplicativo pode ter um mecanismo que detecte quando um nó está prestes a esgotar seu intervalo e a atribuir um novo intervalo usando DBCC CHECKIDENT.For example, your application could have a mechanism that detects when a node is about to use up its range and assign a new range using DBCC CHECKIDENT. Você também pode adicionar uma restrição de verificação para assegurar que não seja possível adicionar uma linha caso ela possa causar o uso de um valor de identidade fora do intervalo.You can also add a check constraint to ensure that a row cannot be added if it would cause an out of range identity value to be used.

Tratando intervalos de identidade após uma restauração de banco de dadosHandling Identity Ranges after a Database Restore

Caso o gerenciamento de identidade automático seja utilizado, quando um Assinante for restaurado de um backup, ele solicitará automaticamente um novo intervalo de valores de identidade.If you are using automatic identity range management, when a Subscriber is restored from a backup, it automatically requests a new range of identity values. Se um Publicador for restaurado de um backup, assegure que um intervalo adequado seja atribuído ao Publicador.If a Publisher is restored from a backup, you must ensure that the Publisher is assigned an appropriate range. Para a replicação de mesclagem, atribua um novo intervalo usando sp_restoremergeidentityrange (Transact-SQL).For merge replication, assign a new range using sp_restoremergeidentityrange (Transact-SQL). Para a replicação transacional, determine o valor mais alto que foi utilizado; em seguida, defina o ponto inicial para os novos intervalos.For transactional replication, determine the highest value that has been used and then set the starting point for new ranges. Use o procedimento a seguir depois que o banco de dados de publicação tiver sido restaurado:Use the following procedure after the publication database has been restored:

  1. Pare toda a atividade em todos os Assinantes.Stop all activity on all Subscribers.

  2. Para cada tabela publicada que inclua uma coluna de identidade:For each published table that includes an identity column:

    1. No banco de dados de assinatura de cada Assinante, execute IDENT_CURRENT('<TableName>').In the subscription database at each Subscriber, execute IDENT_CURRENT('<TableName>').

    2. Registre o valor mais alto encontrado em todos os Assinantes.Record the highest value found across all Subscribers.

    3. No banco de dados de publicação do Publicador, execute DBCC CHECKIDENT(<TableName>','reseed',<HighestValueFound+1>).In the publication database at the Publisher, execute DBCC CHECKIDENT(<TableName>','reseed',<HighestValueFound+1>).

    4. No banco de dados de publicação do Publicador, execute sp_adjustpublisheridentityrange <PublicationName>, <TableName>.In the publication database at the Publisher, execute sp_adjustpublisheridentityrange <PublicationName>, <TableName>.

    Observação

    Se o valor da coluna identidade estiver definido para redução em vez de incremento, registre o valor mais baixo encontrado, depois semeie novamente esse valor.If the value in the identity column is set to decrement rather than increment, record the lowest value found, and then reseed with that value.

Consulte TambémSee Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL) DBCC CHECKIDENT (Transact-SQL)
IDENT_CURRENT (Transact-SQL) IDENT_CURRENT (Transact-SQL)
IDENTITY (Propriedade) (Transact-SQL) IDENTITY (Property) (Transact-SQL)
sp_adjustpublisheridentityrange (Transact-SQL)sp_adjustpublisheridentityrange (Transact-SQL)