Zarządzanie bazą danych SQL

Każde skanowanie na terminalu kończy się zapytaniem do bazy, więc tempo pracy magazynu zależy od tego, jak baza jest prowadzona. Studio WMS.net zapisuje stany magazynowe i historię dokumentów w Microsoft SQL Server. Zarządzanie tą bazą obejmuje kopie zapasowe i utrzymanie indeksów. Dochodzi do tego ochrona danych oraz kontrola spójności, więc praca administratora nie kończy się na jednorazowej instalacji.

Zadania administratora bazy WMS

Kopie zapasowe i odtwarzanie

Kopia zapasowa jest jedyną ochroną przed awarią sprzętu i atakiem na serwer. Pełna kopia zapisuje całą bazę, różnicowa tylko zmiany od ostatniej pełnej, a kopia dziennika transakcji pozwala odtworzyć stan z wybranej minuty. Dopiero przywrócenie testowe na zapasowej instancji potwierdza, że plik nadaje się do użycia.

W większych instalacjach do ochrony przed awarią serwera dochodzi mechanizm wysokiej dostępności Always On Availability Groups, który przełącza pracę na serwer zapasowy. Wybór edycji bazy i jej limity opisuje strona o SQL Express w WMS.

Indeksy i statystyki

Wyszukiwanie palety po numerze lub lokalizacji trwa ułamki sekund tylko wtedy, gdy silnik trafia w indeks. Z upływem czasu indeksy ulegają fragmentacji, a statystyki przestają odpowiadać rozkładowi danych. Przebudowa indeksów i aktualizacja statystyk należą do regularnej konserwacji. Poniższe zapytanie jest ilustracyjne i wskazuje indeksy wymagające zabiegu.

SELECT OBJECT_NAME(ips.object_id) AS Tabela,
       i.name AS Indeks,
       ips.avg_fragmentation_in_percent AS Fragmentacja
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 30
  AND ips.page_count > 1000;

ALTER INDEX IX_Stany_Lokalizacja ON dbo.StanyMagazynowe REBUILD;

Próg 30 procent jest konwencją z dokumentacji Microsoftu: przy niższej fragmentacji wystarcza reorganizacja indeksu, a przebudowa jest dla wyższej. Architekturę indeksów opisuje dokumentacja indeksów w SQL Server.

Monitoring zapytań

Administrator obserwuje, jak baza radzi sobie z obciążeniem, i szuka zapytań, które zużywają najwięcej zasobów. Narzędzie Query Store zapisuje plany i czasy wykonania zapytań, więc po wydaniu nowej wersji aplikacji widać, które zapytanie zwolniło. Optymalizacja polega zwykle na dodaniu indeksu albo przepisaniu warunku, a nie na rozbudowie serwera. Zapytania wysyłane z terminali jako procedury składowane z parametrami pozwalają ponownie używać planu, co zmniejsza koszt kompilacji przy każdym skanie.

• Kopie. Sprawdzenie, czy ostatnie kopie pełne i dziennika zakończyły się bez błędów.
• Miejsce na dysku. Wolna przestrzeń na plikach bazy oraz na dysku z kopiami.
• Indeksy. Fragmentacja na największych tabelach i aktualność statystyk.
• Blokady. Długie transakcje, które wstrzymują pracę terminali.

Ostatni punkt listy kontrolnej widać w widoku bieżących żądań. Zapytanie pokazuje sesje czekające na zwolnienie blokady założonej przez inną sesję i identyfikator sesji blokującej, od której trzeba zacząć diagnozę. Przykład jest ilustracyjny.

SELECT session_id, blocking_session_id, wait_type, wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Zwykle winna jest transakcja otwarta przez aplikację i niezamknięta po błędzie albo zapytanie raportowe czytające duży zakres na domyślnym poziomie izolacji. Rozwiązaniem bywa skrócenie transakcji lub wersjonowanie wierszy, a nie zabicie sesji bez analizy.

Reguła: zmianę indeksu albo zapytania sprawdza się najpierw na kopii bazy z realnym wolumenem danych. Plan, który działa na kilku tysiącach wierszy, przy kilku milionach potrafi zmienić się w pełny odczyt tabeli.

Archiwizacja starszych danych

Baza operacyjna powinna zawierać dane potrzebne w bieżącej pracy, a nie całą historię firmy. Dokumenty z zamkniętych okresów przenosi się do osobnej bazy archiwalnej, gdzie wystarczy dostęp tylko do odczytu. Partycjonowanie tabel według daty upraszcza ten zabieg, bo najstarszą partycję można odłączyć bez usuwania wiersz po wierszu.

Archiwum nadal podlega kopiom zapasowym, ale rzadziej, bo dane się w nim nie zmieniają. Zasady retencji ustala się z działem księgowości i jakości, ponieważ to one wyznaczają, jak długo dokument magazynowy musi być dostępny.

Ochrona danych w bazie

Szyfrowanie TDE chroni pliki bazy przed odczytem po wyniesieniu dysku lub kopii. Klucze szyfrujące wymagają osobnych procedur przechowywania, co opisuje strona o zarządzaniu kluczami szyfrującymi. Transmisję między aplikacją a serwerem zabezpiecza szyfrowane połączenie, a serwer dostępny z sieci wymaga poprawnie ustawionej zapory.

Uprawnienia stosuje się według zasady minimalnych praw: konto aplikacji dostaje dostęp tylko do potrzebnych obiektów, a nie rolę administratora serwera. Logowanie użytkowników i podział ról opisuje strona o logowaniu i autoryzacji. Aktualizacje oprogramowania zamykają znane luki, więc harmonogram poprawek należy do obowiązków administratora.

Integralność danych i transakcje

Relacje między tabelami definiuje się w bazie kluczami obcymi, co zapobiega osieroconym rekordom. Pozycja dokumentu nie może istnieć bez nagłówka, a stan nie może wskazywać nieistniejącej lokalizacji. Historię każdej jednostki towarowej da się wtedy odtworzyć w audycie jakości bez luk.

ALTER TABLE dbo.PozycjeDokumentow
ADD CONSTRAINT FK_Pozycje_Dokument
FOREIGN KEY (DokumentId) REFERENCES dbo.Dokumenty (DokumentId);

Przykład jest ilustracyjny. Składnię i opcje kaskadowego usuwania opisuje dokumentacja kluczy obcych. Każda operacja magazynowa jest zapisywana w transakcji, więc zmiana stanu i zapis dokumentu wchodzą do bazy razem albo wcale.

Procedury składowane i wyzwalacze

Logika, która musi zadziałać przy każdej zmianie stanu, nie powinna zależeć od aplikacji klienckiej. Procedury składowane i wyzwalacze automatyzują powiadomienia o niskich stanach zapasów albo o zbliżających się terminach ważności. Progi zapasu minimalnego i maksymalnego ustawia się w kartotece asortymentu, jak pokazuje zrzut.

CREATE TRIGGER dbo.trg_StanMinimalny ON dbo.StanyMagazynowe
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT dbo.Alerty (IndeksId, Tresc, Czas)
    SELECT i.IndeksId, N'Stan poniżej minimum', SYSDATETIME()
    FROM inserted AS i
    JOIN dbo.Asortyment AS a ON a.IndeksId = i.IndeksId
    WHERE i.Ilosc < a.StanMinimalny;
END;

Wyzwalacz jest ilustracyjny i nie odzwierciedla kodu produktu. Działa na zbiorze wierszy z tabeli inserted, więc obsługuje także zmiany masowe. Wyzwalacze wydłużają każdą operację zapisu, dlatego ich liczbę i złożoność trzeba ograniczać. Składnię opisuje dokumentacja CREATE TRIGGER.

Integracja bazy WMS z systemami ERP

Połączenie magazynu z działem handlowym wymaga spójnej wymiany danych. SQL Server pozwala łączyć zasoby z różnych źródeł w czasie rzeczywistym, a integracja systemów ERP synchronizuje zamówienia i faktury bez przepisywania. Dane z magazynu trafiają do finansów od razu, więc raporty obu działów opierają się na jednej wersji danych.

Infografika pokazuje drogę od analizy wymagań do wsparcia po uruchomieniu. Każdy etap kończy się sprawdzeniem zgodności danych w obu bazach. Szczegóły integracji z SAP opisuje strona o integracji WMS z SAP.

Wydajność przy dużej liczbie rekordów

Przy intensywnej kompletacji liczy się czas każdego zapytania. Struktura bazy jest dobierana tak, żeby szybka realizacja transakcji była możliwa przy tysiącach jednoczesnych operacji. Dostęp do informacji o lokalizacji i dostępności przyspieszają techniki takie jak partycjonowanie tabel i optymalizacja planów zapytań.

Filtrowanie i sortowanie tej tabeli działa szybko dzięki indeksom na kolumnach, po których operatorzy wyszukują. Opis kontroli zapasów znajduje się na stronie o stanach magazynowych.

Chmura i dane historyczne

W wariancie chmurowym baza działa na SQL Azure albo na prywatnej instancji serwerowej. Stały dostęp z wielu lokalizacji łączy rozproszone oddziały w jedną sieć logistyczną. Model abonamentowy opisuje strona o programie magazynowym w chmurze, a administrację bazą w tym modelu prowadzi dostawca.

Dane historyczne zapisane w bazie służą też do prognoz, na przykład sugestii rozmieszczenia towaru i zapotrzebowania na pracowników, jak opisuje strona o AI w logistyce.

Model relacyjny na tle innych modeli

System zarządzania bazą danych przechowuje dane i pozwala wielu użytkownikom pracować równocześnie. Odpowiada też za indeksowanie i spójność rekordów. Prowadzi kopie zapasowe i procedury odtwarzania. Modele, w których dane są zorganizowane, różnią się budową.

Pytania i odpowiedzi o zarządzanie bazą danych SQL w magazynie

Glosariusz pojęć związanych z bazą danych WMS

Kopia różnicowa

Kopia zawierająca tylko zmiany wprowadzone od ostatniej pełnej kopii bazy, szybsza w wykonaniu i odtworzeniu niż pełna.

Dziennik transakcji

Plik, w którym baza zapisuje każdą zmianę przed utrwaleniem jej w danych, co umożliwia odtworzenie stanu do wybranej minuty.

Fragmentacja indeksu

Rozproszenie stron indeksu w pliku, które wydłuża odczyty i usuwa się reorganizacją albo przebudową indeksu.

Query Store

Mechanizm SQL Server zapisujący plany i statystyki wykonania zapytań, przydatny do wykrywania spadków wydajności.

TDE

Przejrzyste szyfrowanie danych, które chroni pliki bazy przed odczytem poza serwerem.

Klucz obcy

Ograniczenie wiążące kolumnę jednej tabeli z kluczem innej, które zapobiega osieroconym rekordom.

Wyzwalacz

Kod uruchamiany automatycznie przy zmianie danych w tabeli, na przykład do zapisu alertu o niskim stanie.

Always On Availability Groups

Mechanizm wysokiej dostępności SQL Server, który utrzymuje kopie bazy na innych serwerach i przełącza pracę po awarii.

Partycjonowanie tabel

Podział dużej tabeli na części według klucza, na przykład daty, co upraszcza archiwizację i przyspiesza wybrane zapytania.

Warto przeczytać