Radzenie sobie z zakresami tożsamości dla replikacji transakcyjnej

Zauważyłem, że po skonfigurowaniu replikacji transakcyjnej SQL Server ustawi zarządzanie zakresem tożsamości na ręczne. Oznacza to, że w mojej bazie danych subskrypcji, gdy próbuję wstawić nowy rekord do tabeli, której PK jest kolumną tożsamości, da mi błąd i powie, że próbował wstawić PK „1”, „2 ”,„ 3 ”itd. Jest tak, ponieważ bieżąca wartość tożsamości dla wszystkich kolumn tożsamości subskrybenta zostaje zresetowana do wartości początkowej (zwykle 1) zamiast pozostawania na poziomie, w jakim była u wydawcy.

Rozumiem, dlaczego SQL Server to robi - powinieneś zostawić tabelę subskrybentów jako tylko do odczytu. Jednak mój scenariusz jest trochę niekonwencjonalny - od czasu do czasu aktualizuję subskrybenta poprzez replikację, tworzę natychmiastową kopię zapasową tej bazy danych, a następnie chcę dokonać aktualizacji subskrybenta, aby NIE ZOSTAŁ przekazany do wydawcy, a następnie kiedy idę, aby ponownie zaktualizować subskrybenta, przywracam jego bazę danych z wcześniejszej kopii zapasowej i ściągam najnowsze aktualizacje. Ponieważ chcę robić aktualizacje subskrybentowi między tymi aktualizacjami („tymczasowe delty”, jeśli chcesz), potrzebuję kolumny tożsamości do działania i nie resetuję do 1 po replikacji.

Próbowałem włączyć automatyczne zarządzanie zakresem tożsamości podczas konfigurowania mojej publikacji, ale to daje mi następujący błąd podczas próby dodania tabeli do publikacji:

Msg 21231, poziom 16, stan 1, procedura sp_MSrepl_addarticle, wiersz 2243 Obsługa
automatycznego zakresu tożsamości jest przydatna tylko w publikacjach, które umożliwiają aktualizowanie subskrybentów.

Czy jest jakiś sposób na obejście tego problemu? W pewnym sensie chcę przedstawić tę replikę SQL Serverowi, tak jakby była ona tylko do odczytu po stronie subskrybenta, ponieważ nie planuję aktualizacji, które zostaną przekazane z powrotem do wydawcy , ale chcę wprowadzić tymczasowe aktualizacje, które zostaną usunięte przed następną replikacją.

Rozważyłem również, że replikacja migawek może być bardziej odpowiednią metodą niż replikacja transakcyjna dla mojego wzorca użytkowania, ale problem polega na tym, że replikacja migawki wymaga wysyłania całego darn DB przy każdej aktualizacji; ponieważ planuję wykonać natychmiastową kopię zapasową bazy danych po najnowszej replikacji, nie powinienem za każdym razem robić tego całego transferu; tylko zmiany od ostatniego razu.

Zakładając, że wydawca używa tożsamości int, która zaczyna się od 1, możesz wydać DBCC CHECKIDENT('dbo.mytable', RESEED, -2147483648) subskrybentowi. Następnie możesz użyć zakresu od -2147483648 do 0, aby zatrzymać „tymczasowe delty”.

Skończyło się na tym, że trzymałem się replikacji transakcyjnej opartej na ściąganiu i kazałem mojemu programowi aktualizować wartości tożsamości subskrybenta, aby były takie same jak te w bazie danych publikacji natychmiast po synchronizacji (co chciałbym, żeby agent dystrybucyjny zrobił z własnej woli ). W pseudokodzie wyglądało to trochę tak:

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

Wydaje się działać OK. Bit HACK polega na tym, że chociaż domyślnie i we wszystkich moich tabelach wartość tożsamości zwiększa się tylko o jeden, można ją skonfigurować inaczej, więc technicznie tutaj powinieneś dowiedzieć się, w jaki sposób wartość tożsamości zwiększa się w tabeli wydawcy i zwiększa ją ta sama droga.

Moją preferowaną metodą radzenia sobie z tym jest:

za. Najpierw zatrzymaj agenta replikacji (aby nie wprowadzać żadnych nowych danych do bazy danych subskrybenta)

b. Po drugie zmień nazwę istniejącej tabeli

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

do. Stwórz ponownie swój stół z zestawem IDENTITY

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

re. Wypełnij tabelę (z [BackupTableName]) za pomocą SET IDENTITY_INSERT

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

Po wprowadzeniu ograniczenia tożsamości na bazie danych można wykonać niestandardową replikację (np .: zmienić proces wstawiania repl insert na SET IDENTITY_INSERT [nazwa_tabeli] WŁĄCZONY lub ustawić flagę NOT FOR REPLICATION na tabeli (która informuje serwer SQL, że jeśli użytkownik łączący się jest agentem replikacji, należy spodziewać się podania wartości TOŻSAMOŚCI) ( wolę niestandardowe podejście do replikacji, ponieważ daje mi to większą elastyczność )

mi. Zmodyfikuj procedurę składowaną replikacji wstawiania (zwykle o nazwie sp_MSins_CurrentTable), aby również wstawić za pomocąSET 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

fa. Teraz możesz zrestartować agenta replikacji.