Dodawanie nowych rekordów z zachowaniem uniklności.

Koledzy mam "zgryz" jak podejść do tematu...

Mam tabelę ważeń na serwerze obsługującym wagi w firmie.
Każdy rekord jest unikatowy (Do tabeli dane zrzuca 7 wag przemysłowych) rejestrujących poszczególne ważenia. Średnio około 1,5-2,0 mln rekordów, czas kopiowania tabeli pomiędzy serwerami 60sek (Cała tabela).

Muszę kopiować te dane na drugi serwer i nie za bardzo mam pomysł jak to zrobić aby nie "orać" tabeli źródłowej. Ze względów analityki ważeń kopia musi być co około 10 minut i nie stracić jakiś danych przy kopiowaniu.
Samo wybranie unikatowych nie stanowi problemu jednak jak to zrobić aby nie orać całej bazy rekord po rekordzie wyszukując "nowe".
Ograniczyłem do daty -3 dni... i raz dziennie puszczać bez ograniczenia?

INSERT INTO [dbo].[BIZERBA_packages]
([PLU]
,[Nazwa]
,[Partia]
,[Data]
,[Czas]
,[Waga_Netto]
,[Waga_Etyk]
,[Tps_Etykieta]
,[Tara]
,[Poziom]
,[Nr_Etykiety]
,[Nr_Urzadz]
,[Nazwa_Urzadz]
,[Klient]
,[Error])

SELECT
TZ.[PLU]
,TZ.[Nazwa]
,TZ.[Partia]
,TZ.[Data]
,TZ.[Czas]
,TZ.[Waga_Netto]
,TZ.[Waga_Etyk]
,TZ.[Tps_Etykieta]
,TZ.[Tara]
,TZ.[Poziom]
,TZ.[Nr_Etykiety]
,TZ.[Nr_Urzadz]
,TZ.[Nazwa_Urzadz]
,TZ.[Klient]
,TZ.[Error]
FROM [dbo].[V_BIZERBA_Wazenia] TZ
LEFT JOIN [dbo].[BIZERBA_packages] TD ON TZ.[Nr_Etykiety] = TD.[Nr_Etykiety]
WHERE TD.[Nr_Etykiety] IS NULL
AND TZ.[Data] >= DATEADD(DAY, -3, getdate())

Zależy jak masz zrobione klucze. Jeśli numer etykiety jest unikatowy i rosnący to pewnie wystarczy zrobić tabelę pomocnicza jak niżej i zapisujesz w niej ostatni rekord.

Opcja 1)

CREATE TABLE Sync_BIZERBA
(
    Nr_Urzadz int PRIMARY KEY,
    LastNrEtykiety bigint NOT NULL
);


SELECT *
FROM Sync_BIZERBA;

Zamiast raz dziennie możesz sobie sobie ustawić jakiegoś pg crona albo SQL Server Agenta co 10-15 minut, który tylko by dodawał nowe rekordy.
Dane wtedy będą bardziej aktualne.

INSERT INTO dbo.BIZERBA_packages
(
 PLU,
 Nazwa,
 Partia,
 Data,
 Czas,
 Waga_Netto,
 Waga_Etyk,
 Tps_Etykieta,
 Tara,
 Poziom,
 Nr_Etykiety,
 Nr_Urzadz,
 Nazwa_Urzadz,
 Klient,
 Error
)
SELECT
 PLU,
 Nazwa,
 Partia,
 Data,
 Czas,
 Waga_Netto,
 Waga_Etyk,
 Tps_Etykieta,
 Tara,
 Poziom,
 Nr_Etykiety,
 Nr_Urzadz,
 Nazwa_Urzadz,
 Klient,
 Error
FROM dbo.V_BIZERBA_Wazenia W
JOIN Sync_BIZERBA S
    ON W.Nr_Urzadz = S.Nr_Urzadz
WHERE W.Nr_Etykiety > S.LastNrEtykiety;

Po imporcie aktualizujesz tabelkę stanu.

UPDATE S
SET LastNrEtykiety =
(
    SELECT MAX(W.Nr_Etykiety)
    FROM dbo.V_BIZERBA_Wazenia W
    WHERE W.Nr_Urzadz = S.Nr_Urzadz
)
FROM Sync_BIZERBA S;

Fajnie jakby były indexy na źródle:

CREATE INDEX IX_Wazenia_Urzadz_Etykieta
ON BIZERBA_Wazenia
(
    Nr_Urzadz,
    Nr_Etykiety
);

i celu:

CREATE UNIQUE INDEX UX_Packages_Urzadz_Etykieta
ON BIZERBA_packages
(
    Nr_Urzadz,
    Nr_Etykiety
);

Sprawdź jakie masz możliwości (licencyjne i funkcjonalne) silnika bazodanowego, na którym masz bazę.
Szukaj pod hasłem: CDC (change data capture) + <Twój engine bazodanowy>

Takie rozwiązania często operują na poziomie logów transakcyjnych i są asynchroniczne, więc nie będziesz "orał" głównej tabeli.

Dziękuję,
Ale sprawa troszkę się posypała. Klucz unikatowy jest w kolumnie "1".

Może podejść do tego tak...?
Wyszukać MAX (DATA : GODZ : MIN : SEK...) grupując wg. urządzenia w tabeli docelowej.
Kopiować do niej dane z tabeli źródłowej > niż ten parametr...??
Oczywiście na początek kopia 1:1 jako wyjście.

Aby było weselej nie każda maszyna pracuje w tym samym czasie.

Więc parowanie jest wg. UNIKATOWEGO INDEKSU oraz DATY > niż DATA MIN.
Jakiś inny pomysł?

SELECT 
  TZ.IndexPrimary,
  TZ.PLU,
  TZ.Nazwa,
  TZ.Partia,
  TZ.Data,
  TZ.Czas,
  TZ.Waga_Netto,
  TZ.Waga_Etyk,
  TZ.Tps_Etykieta,
  TZ.Tara,
  TZ.Poziom,
  TZ.Nr_Etykiety,
  TZ.Nr_Urzadz,
  TZ.Nazwa_Urzadz,
  TZ.Klient,
  TZ.Error,
  TZ.TimeInsert,
  TZ.DeviceId,
  TZ.DeviceMachineNo
FROM
  dbo.V_BIZERBA_Wazenia TZ
  LEFT OUTER JOIN dbo.BIZERBA_packages TD ON (TZ.IndexPrimary = TD.IndexPrimary)
WHERE
  TD.IndexPrimary IS NULL
  AND TZ.Data > (SELECT MIN(CAST(TimeInsert as Date)) FROM dbo.V_BIZERBA_Wazenia_MAX)

Czy masz możliwość ingerowania w strukturę tabeli/bazy? Możesz coś tam zmienić?

Bo ogólnie albo ja nie rozumiem problemu, albo rozwiązanie jest trywialne - dodasz kolejna kolumnę (albo kolumny - w zależności od potrzeb) i dasz im autoincrement. Nie będzie to działać na wcześniejszych danych, ale po zrobieniu ALTER TABLE będziesz miał już rosnące ID dla każdego kolejnego odczytu. A potem tylko robisz SELECT WHERE ID-AUTOINCREMENT > LAST_ODCZYT_ID i masz to podane.

Poza tym - jaki to jest silnik? Bo wiele (większość/wszystkie) umożliwia wykonywanie różnych wariantów replikacji. I może, zamiast robić jakieś mechanizmy, które będą co jakiś czas zaczytywać nowe dane i je gdzieś przesyłać, skorzystaj z gotowych rozwiązań, jakie masz standardowo już w bazie i po prostu odpal replikację?

Skoro używane jest dbo. to jest to zapewne MSSQL.
Celem kopii jest?

Bo ja myślę, że chcesz zrobić Fail Over Claster. Inaczej wykonuj po prostu backup bazy do pliku przez wbudowane narzędzia. Może być cykliczny co np. godzinę.
@yarel zaproponował CDC co jest bardzo dobre, ale nie jest to failOver.
Zapytanie może wyglądać tak:

SELECT TOP 100 *
FROM cdc.fn_cdc_get_all_changes_dbo_BIZERBA_packages

Pamiętaj, że Twój sposób nie zadziała jeśli np. ktoś usunie lub zmodyfikuje rekord. Może i myślisz że to się nie zdarzy, ale kiedyś się zdarzy. Wtedy w tej skopiowanej rekord zostanie.
Jeszcze inna metodą na inżyniera Nachamowa to dodanie kolumny w tabeli typu: isSynchronized i potem odczyt rekordów z false, zapis do drugiej bazy, update wybranych wcześniej na true.

Jeszcze inna metodą na inżyniera Nachamowa to dodanie kolumny w tabeli typu: isSynchronized i potem odczyt rekordów z false, zapis do drugiej bazy, update wybranych wcześniej na true.

Też myślałem o jakiejś fladze - tylko to już jest problem logistyczny, bo trzeba działać na 2 bazach jednocześnie. Musisz mieć pewność, że dane z 1 do 2 się zassały i w bazie 2 są poprawne, żeby bezpiecznie przełączyć flagę w bazie 1. I albo idziemy na żywioł i uznajemy, że po SELECT z bazy 1 mamy zapisane do 2 wszystko co zostało zwrócone, albo odpytujemy z poziomu bazy 1 bazę 2 o każdy element bez ustawionej flagi i po potwierdzeniu że istnieje w bazie 2 - zmieniamy w pierwszej bazie flagę. No chyba że jest jakiś lepszy sposób, ale aktualnie nie przychodzi mi nic do głowy. W sensie - stosując flagę albo tak po prostu wierzymy że dane się przeniosły i odhaczamy isSynchronized na true, albo wykonujemy pełno dodatkowej roboty w weryfikację za każdym razem.

Gdyby to było na jednej bazie to można skorzystać z transakcji, ale na dwóch to temat się komplikuje. Niby jest coś takiego jak transakcje rozproszone, ale nie miałem z tym do czynienia realnie, jest to już trochę bardziej skomplikowane i z tego co czytałem - raczej odradzane rozwiązanie (głownie to kwestia wydajności oraz ryzyko, że jak kontroler się wysypie to obie bazy zostaną zawieszone czekając na koniec transakcji). Ewentualnie można pomyśleć o czymś w stylu poniższego screena - tylko znowu wracamy do mojego pytania - na ile masz możliwość grzebania w tej bazie, dodawania kolumn lub dodatkowych tabel. Czy to jest jakaś baza zewnętrznej aplikacji i masz ją tylko do odczytu, albo np. jest ryzyko, że nawet jak coś zmienisz, potem pójdzie aktualizacja apki i zmiany na bazie zostaną zaorane.

Ja i tak uważam, że backup to backup. Można go robić poprzez narzędzia i trzymać kopię na innym serwerze. Jeśli ktoś robi backup do drugiej bazy to pewnie w ramach awarii liczy na szybkie przełączenie. A to jest failOver i są już wbudowane mechanizmy do tego.
Także wpierw niech OP się wypowie co i jak, po co i dlaczego.

OK, tylko czemu zakładasz, że chodzi o backup?

OP napisał jedynie Muszę kopiować te dane na drugi serwer - nie wynika z tego, że celem jest posiadanie kopii (rozumianej jako zabezpieczenie przed awarią/backup). Ja zakładam (ale oczywiście - nie muszę mieć racji) że chodzi o to, że te dane są potem przetwarzane na innym serwerze/w ramach innej aplikacji, która korzysta ze swojej bazy. Ale to jest pytanie do OP - co chce osiągnąć i po co jest zastosowane to rozwiązanie.

Założyłem tak, bo kopia ma być 1:1, wykonywana cyklicznie. Ale ok. Niech OP przedstawi bardziej cel.

Temat jest prosty....
W Firmie mają być dwa Systemy wagowe:

• Bizerba - SQLExpress (6 wag pzremysłowych) docelowo na Serwer SQL
• Dibal - MySQL (Jeszcze nie wiem jak wygląda ich "baza", każde urządzenie jest "bazą" czy wysyłają na centralny serwer) (3 wagi przemysłowych)

Aby mieć dane do analizy muszę wrzucać rekordy ważeń jednostkowych z obu systemów na "wspólny" Serwer SQL, który jest podstawą do SSRS i docelowo BI.
Dodatkowo jest to powiązane z Systemem produkcji aby móc analizować wagi wg. Asortymentów, partii, generuje raporty wagowe dla Towarów paczkowanych zgodnie z ustawą.

Docelowo ma być pełny Serwer SQL, który będzie zbierał ważenia jednostkowe z poszczególnych wag...
Ale skoro mamy mamy różne bazy SQL i MySQL to musi być to ujednolicone w jednym miejscu.

Możliwe układy Baz:
Bizerba SQLExpress
DIBAL
Wysyła na Serwer SQL

Bizerba Serwer SQL
DIBAL
Wysyła na Serwer SQL Bizerby, który zbiera dwie bazy.

Dlaczego tak,
bo ceny licencji są kosmiczne licencja nie obejmuje tylko Serwera SQL (To mniejsza cena) ale każde urządzenie ważące wysyłające dane, obie licencje są niezależne od Siebie.
A cała zabawa w tym, aby przesłać ważenie jednostkowe.

Najprostszym, a jednocześnie najbezpieczniejszym rozwiązaniem będzie założenie triggera AFTER INSERT na tabeli z ważeniami. Trigger będzie zapisywał do pomocniczej tabeli (np. xxx_cache) wyłącznie identyfikatory nowo dodanych rekordów.Następnie aplikacja synchronizująca odczytuje listę ID z tabeli xxx_cache, pobiera odpowiadające im rekordy z tabeli głównej i zapisuje je do docelowej bazy. Po pomyślnej synchronizacji wpisy z xxx_cache są usuwane. Dzięki temu: nie musisz skanować całej tabeli ani szukać nowych rekordów, tabela xxx_cache pozostaje niewielka i zawsze zawiera tylko dane oczekujące na synchronizację,nie ma ryzyka pominięcia rekordów między kolejnymi synchronizacjami. W bazie docelowej warto zastosować MERGE (lub UPSERT) oraz mieć mapowanie wszystkich pól. Dobrze też przechowywać własny identyfikator wraz z Source albo oprzeć unikalność na kluczu ID(IndexPrimary) + Source.

W obu bazach masz możliwość modyfikacji tabel?
Czemu nie postawisz w takim razie trzeciej bazy która będzie zbierać dane z tych dwóch produkcyjnych? Bez dodatkowych licencji itp.
Jakie przewidujesz sytuacje nietypowe? Usunięcie danych? Sytuacja gdzie waga np. ma awarię i do bazy idzie np. 50 rekordów z danymi nieprawidłowymi zanim człowiek zareaguje i wykryje błąd?
Masz kilka propozycji rozwiązania problemu. Przemyśl tylko teraz scenariusze i wybierz najlepsze.

hzmzp napisał(a):

Najprostszym, a jednocześnie najbezpieczniejszym rozwiązaniem będzie założenie triggera AFTER INSERT na tabeli z ważeniami. Trigger będzie
...

Do tego pomysłu dodałbym obsługę sytuacji, że trigger nie działa przez jakiś czas (z bliżej nieokreślonego powodu). xxx_cache partycjonował po dacie i trzymał dane np. przez 14 dni (+ status replikacji per wiersz). Do tego 2 joby na koniec dnia:

a) rekoncyliacja danych z tabelą źródłową (czy są rekordy na źródle, których nie ma w xxx_cache)
b) wywalanie partycji starszych niż N dni (np. umowne 14 dni) - drop partition zamiast delete.

Ewentualnie olał fikuśne rozwiązania i na początek zrobił po prostu tansfer całej tabeli ze źródła do celu

Jak będą narzekać, że jest za bardzo obciążona baza -> optymalizacja.

Ogólnie dobrze byłoby z DBA pogadać jak to widzi (może się okazać, że logiczna replikacja działa dla niektórych tabel i dodanie kolejnej to nie problem).

Rozwiązań dużo, ale brak kontekstu biznesowego i tego jak wygląda integracja. Bo ciężko mi uwierzyć że waga bezpośrednio łączy się do bazy...
Może jest jakiś proces ETL, który zbiera dane z wagi co XX minut i wrzuca do loadera (wówczas móżna by zrobić odnogę takiego procesu i wrzucać do innej bazy..).

yarel napisał(a):

trigger nie działa przez jakiś czas (z bliżej nieokreślonego powodu)

Mógłbyś rozwinąć tę myś, bo nie wyobrażam sobie takiej sytuacji

hzmzp napisał(a):

yarel napisał(a):

trigger nie działa przez jakiś czas (z bliżej nieokreślonego powodu)

Mógłbyś rozwinąć tę myś, bo nie wyobrażam sobie takiej sytuacji

Nie są to sytuacje typowe, niemniej życie pisze różne scenariusze :-)

1. Czynnik ludzki - administrator z jakiegoś powodu wyłącza trigger, wykonuje określone operacje, idzie na kawę/papierosa, a po powrocie zapomina go ponownie włączyć. Dlaczego w ogóle miałby wyłączać trigger? Ddlatego, że spowalnia on operacje masowe, co może utrudniać usuwanie awarii w procesie ładowania danych. Jeśli danych jest dużo i szybko przyrastają (kolejka do ich załadowania rośnie), to pewnym momencie może być ją bardzo trudno rozładować.

Osobiście raz byłem świadkiem sytuacji, gdzie management był mocno "zatroskany" -> "Jeszcze kilka godzin i nie będziemy w stanie rozładować kolejki!".

1. Rozmyta odpowiedzialność za rozwiązanie - np "właścicielem" tabeli jest VendorA, natomiast właścicielem triggera jest VendorB. VendorA przeprowadza aktualizację wersji produktu i migruje tabelę źródłową w następujący sposób:

CREATE TABLE new_table AS
SELECT /* transformacja */
       ...
FROM source x
JOIN mapping m ON ...;

DROP TABLE source;

ALTER TABLE new_table RENAME TO source;

Nowe dane są od tej chwili wstawiane do nowej tabeli source.

W takim przypadku trigger przypisany do pierwotnego obiektu nie zostanie automatycznie przeniesiony na nową tabelę. VendorA może miec prostą komunikację "Not our trigger, not our problem".

1. Używanie narzędzi do masowego ładowania danych np.:
   a) M$ bcp
   b) Oracle SQL*Loader w trybie direct path.

W przypadku bcp uruchamianie triggerów wymaga użycia odpowiedniej opcji:
https://learn.microsoft.com/en-us/sql/tools/bcp/bcp-utility?view=sql-server-ver17

1. Szybkie dołączanie danych przez podmianę partycji
   a) dane są najpierw ładowane do tabeli pomocniczej
   b) następnie wykonywana jest operacja na metadanych, która powoduje, że istniejąca partycja
   zaczyna wskazywać na przygotowany wcześniej zestaw danych.

Oracle ma EXCHANGE PARTITION, w M$ jest SWITCH PARTITION. Nie używałem M$SQL od laaat, ale zgodnie z dokumentacją składnia dla ALTER TABLE jest taka:

 | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table
        [ PARTITION target_partition_number_expression ]
        [ WITH ( <low_priority_lock_wait> ) ]

ALTER TABLE source
SWITCH PARTITION 3 TO NewData;

W efekcie partycja numer 3 otrzymuje zawartość przygotowaną w tabeli NewData. A trigger bazuje na założeniu, że to zawsze przez INSERT leci.

Oczywiście są to przypadki z kategorii: "To nie powinno było się wydarzyć. Jak możemy uniknąć tego w przyszłości?". No ale czasem się zdarza.

-- edited
nie umiem używać tylch numbered list w edytorze Coyote, dlatego wszytkie punkty są jako 1. :-)

@yarel: Faktycznie to co opisałeś jest możliwe, ale

1. Taka osobo powinna dostać zjebe że grzebie na prodzie bez przygotowania i idzie se na kawke zanim nie skończy prac krytycznych
2. Jest to zdarzenie jednostkowe
3. Do tego typu problemów pisze się soft jako tool do naprawy spójności danych
4. To są raczej juniorskie błędy i nieznajomość narzędzi/technologii