SQL Server 2008 jako baza systemu magazynowego - możliwości i migracja

SQL Server 2008 przez lata był bazą danych wielu systemów magazynowych, w tym instalacji Studio WMS.net. Odpowiadał za zapis stanów oraz dokumentów, a także ruchów magazynowych, a aplikacja webowa czytała z niego dane w czasie rzeczywistym. Wersja jest dziś poza wsparciem, więc opis jej możliwości ma sens głównie przy planowaniu migracji.

Artykuł zaczyna od statusu wsparcia, potem pokazuje, co baza zapewniała procesom magazynowym. Omówiono transakcje i indeksy, a potem integrację z innymi systemami oraz zabezpieczenia. Na końcu opisano migrację na nowszą wersję z konkretnymi poleceniami kontrolnymi. Przykłady kodu używają nazw poglądowych i nie odzwierciedlają schematu żadnego produktu.

Koniec wsparcia SQL Server 2008

Wsparcie rozszerzone dla SQL Server 2008 i 2008 R2 zakończyło się 9 lipca 2019 roku, co potwierdza strona cyklu życia produktu w Microsoft Learn. Po tej dacie Microsoft nie wydaje regularnych poprawek bezpieczeństwa. Dodatkowe aktualizacje oferowano wyłącznie w programie rozszerzonych aktualizacji zabezpieczeń, przez ograniczony czas, jak opisuje komunikat o końcu wsparcia SQL Server i Windows Server 2008.

Reguła: SQL Server 2008 nie jest wybierany do nowych wdrożeń. Istniejąca instalacja wymaga planu migracji z datą i właścicielem, a nie jedynie zapisu o „docelowej wymianie”.

Poniższe rozdziały opisują możliwości tej wersji z perspektywy magazynu, bo takie rozumienie ułatwia ocenę, co trzeba zachować po migracji. Większość mechanizmów działa na nowszych wersjach identycznie lub lepiej, a różnice dotyczą głównie planów wykonania i nowych zabezpieczeń.

Rola SQL Server 2008 w systemach magazynowych

System magazynowy trzyma w bazie informacje o produktach i stanach oraz o zamówieniach i dostawach w jednym miejscu, więc wszystkie stanowiska widzą te same dane. Centralny zapis daje spójność, której nie zapewni praca na osobnych plikach. Opis samych stanów, które ta baza przechowuje, zawiera strona o stanach magazynowych.

Transakcje i spójność stanów

Przyjęcie palety zmienia co najmniej dwie tabele: stan lokalizacji i dziennik ruchów. Awaria po pierwszym zapisie rozjeżdża stan lokalizacji z dziennikiem ruchów. Transakcja gwarantuje, że oba zapisy zostają zatwierdzone razem albo oba wycofane, a polecenie MERGE, dodane w wersji 2008, łączy aktualizację i wstawienie w jednej instrukcji.

-- przykład poglądowy, nie schemat produktu
CREATE PROCEDURE dbo.PrzyjmijPalete
    @NrPalety varchar(18), @LokalizacjaId int,
    @Ilosc decimal(18,3), @OperatorId int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        MERGE dbo.StanLokalizacji WITH (HOLDLOCK) AS t
        USING (SELECT @LokalizacjaId AS LokalizacjaId, @NrPalety AS NrPalety) AS s
           ON t.LokalizacjaId = s.LokalizacjaId AND t.NrPalety = s.NrPalety
        WHEN MATCHED THEN
            UPDATE SET Ilosc = t.Ilosc + @Ilosc
        WHEN NOT MATCHED THEN
            INSERT (LokalizacjaId, NrPalety, Ilosc)
            VALUES (s.LokalizacjaId, s.NrPalety, @Ilosc);

        INSERT dbo.RuchMagazynowy (NrPalety, LokalizacjaId, Ilosc, OperatorId, Czas)
        VALUES (@NrPalety, @LokalizacjaId, @Ilosc, @OperatorId, SYSDATETIME());

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        DECLARE @Komunikat nvarchar(2048) = ERROR_MESSAGE();
        RAISERROR(@Komunikat, 16, 1);
    END CATCH
END;

Wskazówka HOLDLOCK zapobiega sytuacji, w której dwa terminale równocześnie nie znajdują wiersza i oba próbują go wstawić. Polecenie THROW pojawiło się dopiero w wersji 2012, dlatego w 2008 błąd przekazuje się dalej przez RAISERROR. Typ datetime2 i funkcja SYSDATETIME dają czas z dokładnością do setnych części mikrosekundy, co ułatwia porządkowanie ruchów w jednej sekundzie.

Indeksy i wyszukiwanie po stronie serwera

Baza wykonuje po stronie serwera wyszukiwanie i sortowanie, a także grupowanie, więc terminal nie pobiera całej tabeli, żeby znaleźć jedną paletę. Szybkość zależy od indeksów: klastrowego na kluczu głównym oraz nieklastrowych pod najczęstsze filtry, takie jak numer palety albo lokalizacja. Wersja 2008 wprowadziła indeksy filtrowane, które obejmują tylko wybrane wiersze, na przykład niezamknięte dokumenty.

• Indeks filtrowany - obejmuje wyłącznie wiersze spełniające warunek, więc jest mniejszy i szybszy w utrzymaniu niż pełny.
• Parametr tabelaryczny - pozwala przekazać do procedury całą listę pozycji przyjęcia jednym wywołaniem zamiast wielu.

Parametr tabelaryczny ogranicza liczbę wywołań między aplikacją a bazą, a przez to opóźnienie sieci przy dużych dostawach. Procedura odbiera tabelę z pozycjami, waliduje ją zbiorczo i zapisuje w jednej transakcji. Zasady projektowania takich procedur i schematów omawia strona o programowaniu baz danych.

Integracja z ERP i księgowością

Baza magazynowa zwykle nie działa samodzielnie. Dokumenty PZ i WZ muszą trafić do systemu księgowego, a zamówienia z ERP do magazynu, więc potrzebny jest kanał wymiany. Dane mogą płynąć przez usługi sieciowe, pliki wymiany albo kolejkę zdarzeń w tabelach, a wybór zależy od tego, czy druga strona udostępnia interfejs. Wymianę opisuje artykuł o integracji WMS z ERP przez REST i kolejki.

Kopie zapasowe i odtwarzanie po awarii

Baza magazynowa zmienia się przez całą dobę, więc kopia raz na noc oznacza utratę wszystkich ruchów od jej wykonania. Miarą jest czas, jaki firma może stracić, czyli RPO, oraz czas, po którym system musi znów działać, czyli RTO. Oba parametry ustala się z kierownikiem magazynu przed wyborem modelu odzyskiwania, a nie po awarii.

W modelu FULL kopie pełne uzupełnia się kopiami różnicowymi i regularnymi kopiami dziennika transakcji. Częstotliwość kopii dziennika wyznacza RPO: kopia co piętnaście minut oznacza, że w najgorszym razie ginie kwadrans ruchów. Sama kopia bez sprawdzenia jest tylko nadzieją, więc po wykonaniu weryfikuje się jej spójność i regularnie przywraca na osobnej instancji.

BACKUP DATABASE MagazynPrzyklad
TO DISK = N'D:\Kopie\Magazyn_pelna.bak' WITH CHECKSUM, INIT;

BACKUP LOG MagazynPrzyklad
TO DISK = N'D:\Kopie\Magazyn_dziennik.trn' WITH CHECKSUM;

RESTORE VERIFYONLY
FROM DISK = N'D:\Kopie\Magazyn_pelna.bak' WITH CHECKSUM;

Polecenie RESTORE VERIFYONLY sprawdza czytelność i sumy kontrolne kopii, ale nie dowodzi, że baza się uruchomi. Dopiero pełne przywrócenie na testowej instancji potwierdza, że kopia nadaje się do użycia, i mierzy realny czas RTO. Kompresja kopii zapasowych była w wersji 2008 dostępna wyłącznie w edycji Enterprise, a od wersji 2008 R2 także w Standard, co skraca czas i rozmiar plików.

Zabezpieczenia danych w wersji 2008

Dane magazynowe zawierają informacje o kontrahentach i cenach oraz o wolumenach, więc dostęp do nich wymaga kontroli. SQL Server 2008 oferuje uwierzytelnianie Windows i SQL, role z uprawnieniami na obiektach oraz szyfrowanie i audyt, ale część z tych funkcji dostępna jest tylko w edycji Enterprise. Ogólne zasady audytu opisuje dokumentacja SQL Server Audit.

Reguła: konto aplikacji webowej ma uprawnienia tylko do procedur, które wywołuje. Dostęp z prawem zapisu do wszystkich tabel zamienia każdą podatność aplikacji w utratę całej bazy.

Brak poprawek bezpieczeństwa po 2019 roku osłabia wszystkie powyższe mechanizmy, bo luka w silniku bazy omija role i audyt. Jeśli migracja nie jest możliwa od razu, instancję izoluje się w osobnym segmencie sieci i ogranicza połączenia do adresów serwera aplikacji. Takie środowisko wymaga też aktualnego systemu operacyjnego, co omawia strona o obsłudze Windows Server.

Dostęp do danych przez aplikację webową

Przeglądarka nie łączy się z bazą bezpośrednio. Między nią a SQL Server stoi serwer aplikacji, który uwierzytelnia użytkownika, a następnie wywołuje procedury i zwraca wynik, zwykle jako JSON. Taki układ trójwarstwowy pozwala udostępnić dane z dowolnego miejsca bez otwierania portu bazy w Internecie. Ogólny model opisuje strona o aplikacji webowej, a zestawienie rozwiązań znajduje się w tekście o aplikacjach webowych.

Serwer aplikacji utrzymuje pulę połączeń z bazą, więc setki żądań z terminali nie otwierają setek osobnych sesji. Pula skraca czas odpowiedzi i chroni instancję przed przeciążeniem liczbą połączeń. Rozmiar puli ustawia się na podstawie liczby równoczesnych użytkowników, a nie liczby zarejestrowanych kont.

Mobilność i reakcja na zmiany

Aplikacja webowa działa na komputerze i na urządzeniu mobilnym, więc pracownik z terminala widzi te same dane co biuro. Kierownik może z tabletu sprawdzić stan zlecenia w trakcie obchodu, a nie po powrocie do biurka. Odpowiedź na zmianę warunków, na przykład opóźnioną dostawę, opiera się wtedy na danych z ostatnich minut.

Raporty z bazy w przeglądarce

Raporty operacyjne generuje serwer raportów na podstawie zapytań do tej samej bazy. Od wersji 2008 usługa Reporting Services ma własny host HTTP i nie wymaga serwera IIS, co upraszcza instalację na jednym serwerze. Architekturę usług i subskrypcje opisuje strona o Reporting Services.

Migracja z SQL Server 2008 na nowszą wersję

Migracja zaczyna się od inwentaryzacji i przygotowania nowej instancji, a kończy przeniesieniem danych oraz testami. Najpierw ustala się zakres, czyli bazy i funkcje do przeniesienia, bo instancja bywa używana przez więcej niż jedną aplikację. Instalację serwera bazy w małym środowisku pokazuje przykład instalacji serwera SQL w narzędziowni.

Inwentaryzacja instancji

Pierwsze zapytanie odczytuje dokładną wersję i poziom zgodności każdej bazy. Poziom 100 odpowiada SQL Server 2008 i 2008 R2. Wynik wskazuje bazy, które po przeniesieniu warto sprawdzić pod kątem różnic w planach zapytań.

SELECT @@VERSION AS Wersja;

SELECT name, compatibility_level, recovery_model_desc
FROM sys.databases
ORDER BY name;

Kopia zapasowa i przywrócenie

Najprostszą ścieżką jest kopia zapasowa na starej instancji i jej przywrócenie na nowej. Według tabeli obsługiwanych ścieżek uaktualnienia kopię zapasową bazy z SQL Server 2008 można przywrócić na SQL Server 2022 bez zmiany poziomu zgodności, jeśli baza ma poziom 100 lub wyższy. Przenosi się osobno loginy i zadania agenta, bo nie znajdują się w kopii bazy użytkownika.

Po stronie kodu największe ryzyko stanowią konstrukcje wycofane z nowszych wersji. Stary zapis złączeń zewnętrznych operatorami *= i =* nie działa przy poziomie zgodności wyższym niż 80, a klauzula COMPUTE BY została usunięta w wersji 2012. Przegląd procedur na takie wzorce wykonuje się przed przeniesieniem bazy, a nie po pierwszym błędzie użytkownika.

Poziom zgodności i testy planów

Po przywróceniu baza zachowuje poziom zgodności 100, więc zapytania zachowują się jak w starej wersji. Podniesienie poziomu odblokowuje nowe mechanizmy optymalizatora, ale może zmienić plany wykonania, dlatego wykonuje się je po testach na kopii. Zasady opisuje dokumentacja poziomu zgodności bazy danych.

ALTER DATABASE MagazynPrzyklad
SET COMPATIBILITY_LEVEL = 150;

Reguła: przed migracją sprawdza się zgodność mechanizmów zabezpieczeń i audytu z docelową aplikacją webową, aby przy przenoszeniu danych logistycznych nie powstała luka w uprawnieniach.

Wdrożenie przełącza się poza godzinami pracy magazynu, a poprzednia instancja zostaje wyłączona, lecz nie usunięta, na okres wycofania. Wymiana połączeń w aplikacji i integracjach oraz w raportach wymaga listy wszystkich klientów bazy. Więcej o produkcie magazynowym pracującym na SQL Server opisuje strona Studio WMS.net.