Início / dba / Perguntas / 45600
Accepted
Jez
Asked: 2013-07-03 04:07:35 +0800 CST

Lidando com intervalos de identidade para replicação transacional

  • 772

Percebi que, quando você configura uma replicação transacional, o SQL Server define o gerenciamento de intervalo de identidade como manual. O que isso significa é que no meu banco de dados de assinatura, quando tento inserir um novo registro em uma tabela cujo PK é uma coluna de identidade, ele me dará um erro e dirá que tentou inserir um PK de "1", "2 ", "3", etc. Isso ocorre porque o valor de identidade atual para todas as colunas de identidade no assinante é redefinido para o valor inicial (geralmente 1) em vez de permanecer no que era no editor.

Entendo por que o SQL Server faz isso - você deve deixar a tabela de assinantes como somente leitura. No entanto, meu cenário é um pouco heterodoxo - atualizo meu assinante de tempos em tempos por meio de replicação, faço um backup imediato desse banco de dados e, em seguida, quero fazer algumas atualizações no assinante que NÃO serão enviadas de volta ao editor e, em seguida, quando vou atualizar o assinante novamente, restauro seu banco de dados do backup anterior e extraio as atualizações mais recentes. Como desejo fazer atualizações para o assinante entre essas atualizações ('deltas temporários', se preferir), preciso que a coluna de identidade funcione e não seja redefinida para 1 quando replicada.

Tentei ativar o gerenciamento automático de intervalo de identidade ao configurar minha publicação, mas isso me dá o seguinte erro quando tento adicionar uma tabela à publicação:

Msg 21231, nível 16, estado 1, procedimento sp_MSrepl_addarticle, linha 2243
O suporte ao intervalo de identidade automática é útil apenas para publicações que permitem a atualização de assinantes.

Existe alguma maneira de contornar esse problema? Eu meio que quero apresentar essa replicação ao SQL Server como se fosse somente leitura no final do assinante porque não pretendo fazer atualizações que serão enviadas ao editor , mas quero fazer atualizações temporárias que serão apagados antes da próxima replicação.

Também considerei que a replicação de instantâneo pode ser um método mais apropriado do que a replicação transacional para meu padrão de uso, mas o problema é que a replicação de instantâneo requer o envio de todo o maldito banco de dados a cada atualização; como estou planejando fazer um backup imediato do banco de dados após a última replicação, não devo precisar fazer toda essa transferência todas as vezes; apenas as mudanças desde a última vez.

sql-server sql-server-2008-r2

3 respostas

  1. Supondo que seu editor esteja usando uma identidade int que começa em 1, você pode emitir DBCC CHECKIDENT('dbo.mytable', RESEED, -2147483648) no assinante. Você pode então usar o intervalo de -2147483648 a 0 para manter seus "deltas temporários".

    • 3
  2. Best Answer

    O que acabei fazendo foi ficar com uma replicação transacional baseada em pull e fazer com que meu programa atualizasse os valores de identidade do assinante para serem iguais aos do banco de dados de publicação imediatamente após a sincronização (mais ou menos o que eu gostaria que o agente de distribuição fizesse por conta própria ). Em pseudo-código, ficou mais ou menos assim:

    synchronize databases with TransSynchronizationAgent

equivalentTablesNotFound is a list of strings
for each table in publisher tables:
    try:
        check table identity value (this is via functionality provided by .NET's Microsoft.SqlServer.Management.Smo.Server class)
        parse identity value as integer to newIdentity
        if the table's identity value was NULL, skip to next loop iteration
        (HACK) increment newIdentity value by 1
        if there is no subscriber table with the same name as this one:
            record its name in equivalentTablesNotFound and skip to next loop iteration
        set subscriber table with same name's identity value to newIdentity using TSQL: DBCC CHECKIDENT ("tableName", newIdentity)
    catch:
        if exception shows that the error was because the table doesn't have an identity column, drop the exception

if equivalentTablesNotFound has more than zero entries, warn about tables on publisher without an equivalent name on subscriber

    Parece funcionar bem. O bit HACK é porque, embora por padrão e com todas as minhas tabelas, o valor de identidade apenas incrementa em um, ele pode ser configurado de forma diferente, então tecnicamente aqui você deve descobrir como o valor de identidade incrementa na tabela do publicador e incrementá-lo o mesma maneira.

    • 1

  3. Meu método preferido para lidar com isso é fazer o seguinte:

    uma. Primeiro pare seu agente de replicação (para que você não obtenha novos dados em seu banco de dados de assinante)

    b. Em segundo lugar, renomeie sua tabela existente

    exec sp_rename '[CurrentTable]', '[BackupTableName]'

    c. Recrie sua mesa com IDENTIDADE definida

    CREATE TABLE [CurrentTable]
(
   ID INT NOT NULL IDENTITY(1,1), 
   OtherField VARCHAR(10) NULL,
   ....
)

    d. Preencha sua tabela (do [BackupTableName]) com SET IDENTITY_INSERT

    SET IDENTITY_INSERT [CurrentTable] ON
INSERT INTO [CurrentTable] (ID, OtherField, ...)
SELECT ID, OtherField, ....
FROM [BackupTableName]
SET IDENTITY_INSERT [CurrentTable] OFF

    Depois de ter a restrição IDENTITY em seu banco de dados, você pode fazer a replicação personalizada (ou seja: alterar seu insert repl proc para SET IDENTITY_INSERT [TableName] ON ou definir o sinalizador NOT FOR REPLICATION na tabela (que informa ao servidor SQL que se o usuário conectado for o agente de replicação, espere que o valor IDENTITY seja fornecido) ( eu prefiro a abordagem de replicação personalizada, pois me dá mais flexibilidade )

    e. Modifique seu procedimento armazenado de replicação de inserção (geralmente denominado sp_MSins_CurrentTable) para também inserir usandoSET IDENTITY INSERT

    ALTER procedure [dbo].[sp_MSins_CurrentTable]
    @c1 int, @c2 varchar(50), ...
as
begin
    /* allow replication to insert values for IDENTITY */
    SET IDENTITY_INSERT [CurrentTable] ON
    insert into [CurrentTable]
        ([ID], [OtherField], ...)
    values
        (@c1, @c2, ...)
    /* now turn off Identity insert */
    SET IDENTITY_INSERT [CurrentTable] OFF
end

    f. Agora você pode reiniciar seu agente de replicação.

    • 0

relate perguntas