SQL Express w systemie magazynowym WMS.net

Studio WMS.net zapisuje stany magazynowe i dokumenty w relacyjnej bazie Microsoft SQL Server, a w małych instalacjach może pracować na bezpłatnej edycji Express. Pytanie nie brzmi więc, czy ta edycja działa, tylko kiedy jej limity zaczną przeszkadzać w pracy magazynu. Odpowiedź zależy od liczby użytkowników i tempa przyrostu danych, a także od sposobu wykonywania kopii zapasowych.

Czego oczekiwać od edycji Express

Program Studio Magazyn WMS.net może korzystać z platformy SQL Server w wersji Express. Użytkownik powinien jednak znać ograniczenia wydajnościowe tej edycji, zwłaszcza gdy z systemu ma korzystać wielu użytkowników naraz. W takim układzie rozważa się wersję Standard, która ma większe możliwości i wydajność.

Edycje bezpłatne SQL Server wolno stosować także komercyjnie. Wersje sprzed 2005 roku (8.0) miały ograniczoną liczbę połączeń i nie zawierały graficznych narzędzi do zarządzania. Od wersji 2005 (9.0) Microsoft udostępnia Express z pełnymi narzędziami graficznymi i bez limitu połączeń, więc dziś ograniczenia dotyczą zasobów, a nie funkcji administracyjnych.

Limity zasobów zapisano w dokumentacji edycji. Poniższe wartości dotyczą SQL Server 2022 i warto sprawdzić je dla swojej wersji w opisie edycji i składników SQL Server 2022.

Reguła: po osiągnięciu limitu rozmiaru baza odrzuca zapisy. Operator na terminalu zobaczy błąd w trakcie przyjęcia towaru, więc zmianę edycji planuje się przy zajętości około 80 procent limitu, a nie po pierwszym komunikacie.

Kontrola rozmiaru bazy w zapytaniu

Granicę 10 GB liczy się dla plików danych, a nie dla dziennika transakcji. Rozmiar obu części można odczytać z widoku systemowego, co daje podstawę do prognozy przyrostu. Przykład jest ilustracyjny i można go uruchomić w SQL Server Management Studio.

SELECT DB_NAME(database_id) AS Baza,
       SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8.0 / 1024 AS DaneMB,
       SUM(CASE WHEN type_desc = 'LOG'  THEN size END) * 8.0 / 1024 AS DziennikMB
FROM sys.master_files
GROUP BY database_id;

Kolumna size zawiera liczbę stron po 8 KB, stąd przeliczenie na megabajty. Wynik zapisany raz w miesiącu pokazuje tempo przyrostu i pozwala przewidzieć moment, w którym baza dotknie limitu. Najszybciej rosną tabele z historią ruchów i dziennikiem operacji, a najwolniej kartoteki asortymentu i kontrahentów.

Które tabele zapełniają bazę najszybciej

Granica 10 GB nie rośnie równo we wszystkich tabelach. Rejestry operacji i pozycje dokumentów powstają przy każdym skanie, a kartoteki zmieniają się rzadko. Najpierw sprawdza się więc, który obszar danych odpowiada za większość przyrostu.

Zapytanie do widoku partycji pokazuje, które tabele zajmują najwięcej miejsca. Przykład jest ilustracyjny i działa na dowolnej bazie SQL Server.

SELECT TOP (10) t.name AS Tabela,
       SUM(ps.used_page_count) * 8 / 1024 AS UzyteMB,
       SUM(CASE WHEN ps.index_id < 2 THEN ps.row_count END) AS Wiersze
FROM sys.dm_db_partition_stats AS ps
JOIN sys.tables AS t ON t.object_id = ps.object_id
GROUP BY t.name
ORDER BY UzyteMB DESC;

Warunek index_id mniejszy niż 2 liczy wiersze tylko raz, bo tabela ma wpis dla sterty albo indeksu klastrowanego. Zestawienie wykonane przed i po kwartale pokazuje, które tabele przyrastają szybciej niż zakładano.

Kopie zapasowe bez SQL Server Agent

Zautomatyzowane zadania, na przykład archiwizacja bazy o określonej godzinie, w edycji Express nie są dostępne w standardowy sposób, bo brakuje SQL Server Agent. Harmonogram przenosi się wtedy do Harmonogramu zadań systemu Windows, który o wyznaczonej porze uruchamia narzędzie sqlcmd z plikiem skryptu.

-- plik kopia_wms.sql
BACKUP DATABASE WMS
TO DISK = N'D:\Kopie\WMS_pelna.bak'
WITH INIT, CHECKSUM;

-- uruchomienie z Harmonogramu zadań:
-- sqlcmd -S .\SQLEXPRESS -E -i D:\Skrypty\kopia_wms.sql

Połączenia sieciowe z instancją Express

Instalacja domyślna przyjmuje połączenia lokalne. Gdy serwer WWW z aplikacją i baza stoją na osobnych maszynach, w Configuration Managerze włącza się protokół TCP/IP, otwiera odpowiedni port w zaporze i restartuje usługę bazy. Uwierzytelnianie Windows i konto usługi aplikacji ustawia się już po stronie serwera WWW. W ciągu połączenia nazwa instancji ma postać serwer\SQLEXPRESS, o ile instalator nie otrzymał innej nazwy.

Opcja CHECKSUM wykrywa uszkodzone strony podczas zapisu kopii. Plik .bak powinien trafić na inny dysk lub serwer niż sama baza, bo kopia leżąca obok danych nie chroni przed awarią macierzy. Sprawdzenie odtwarzania na zapasowej instancji wykonuje się okresowo, bo kopia, której nikt nie przywrócił, pozostaje założeniem.

Pamięć, indeksy i liczba użytkowników

Przy buforze rzędu 1,4 GB silnik trzyma w pamięci tylko część danych roboczych. Każdy odczyt spoza bufora oznacza dostęp do dysku, więc dobrze zaprojektowane indeksy mają w Express większe znaczenie niż na dużym serwerze. Architekturę indeksów opisuje dokumentacja indeksów w SQL Server.

Brak indeksu widać w planie wykonania zapytania jako Table Scan zamiast Index Seek. Przy powolnym oknie z listą dokumentów warto najpierw obejrzeć plan, a dopiero potem rozważać mocniejszy serwer, bo często wystarcza jeden dodatkowy indeks na kolumnie filtrowanej. Sposób czytania planów opisuje dokumentacja planów wykonania.

• Użytkownicy. Dla jednego magazynu z kilkoma stanowiskami Express zwykle wystarcza, a wielu równoczesnych użytkowników uzasadnia wersję Standard.
• Raporty. Zestawienia za wiele miesięcy czytają duże zakresy danych i konkurują z operacjami na terminalach o ten sam bufor.
• Historia. Starsze dokumenty można przenieść do osobnej bazy archiwalnej, żeby główna zostawała poniżej limitu.
• Dziennik zdarzeń. Tabele audytu wymagają okresowego czyszczenia, bo rosną szybciej niż kartoteki.

Ścieżka przejścia na wyższą edycję

Decyzję o zmianie edycji podejmuje się na podstawie pomiarów, a nie przeczucia. Dwa miesiące zapisów z jednej instalacji mówią więcej niż oszacowanie z projektu, bo uwzględniają rzeczywiste szczyty zamówień i prawdziwą liczbę skanów.

1. Pomiar. Rozmiar plików danych i czas odpowiedzi terminali zapisywane przez co najmniej kwartał, z odczytem zajętości pamięci obok.
2. Próba przywrócenia. Odtworzenie kopii na instancji testowej i sprawdzenie, czy aplikacja łączy się z nową bazą. Do prób wystarcza edycja Developer, bezpłatna poza produkcją.
3. Okno serwisowe. Zmiana edycji lub przełączenie na nowy serwer poza godzinami pracy zmian.
4. Zadania harmonogramowe. Przeniesienie kopii z Harmonogramu zadań Windows do SQL Server Agent albo pozostawienie ich bez zmian.

Express w architekturze Studio WMS.net

Aplikacja Studio WMS.net działa na platformie ASP.net i łączy się z bazą jako zwykły klient SQL Server, więc zmiana edycji nie zmienia samego programu. Platformę serwerową opisuje strona o pracy w sieci lokalnej lub przez Internet, a instalację serwera WWW i bazy strona o konfiguracji IIS i SQL Server. Program magazynowy opisuje także serwis programmagazyn.pl.

Systemy magazynowe zwykle opierają się na bazie relacyjnej, bo dokument magazynowy wymaga transakcji i spójnych powiązań między kartoteką a stanem. Rozwiązania nierelacyjne, takie jak MongoDB, spotyka się rzadziej i nie dotyczą one Studio WMS.net, które korzysta z SQL Server. Wybór edycji zmienia więc zasoby bazy, ale nie jej model danych.

Przejście z Express na Standard zwykle polega na zmianie edycji instancji albo przywróceniu kopii bazy na nowej instancji. Aplikacja wymaga wtedy tylko nowego ciągu połączenia. Codzienną administrację bazą, w tym kopie zapasowe i monitoring, opisuje strona o zarządzaniu bazą danych SQL.

W modelu chmurowym wybór edycji bazy należy do dostawcy, więc klient nie pilnuje limitów rozmiaru. Opis tego wariantu znajduje się na stronie o programie magazynowym w chmurze. Wpływ bazy na stany i raporty pokazuje strona o stanach magazynowych.

Pytania i odpowiedzi o SQL Express w WMS

Glosariusz pojęć związanych z edycjami SQL Server

SQL Server Express

Bezpłatna edycja Microsoft SQL Server z limitami rozmiaru bazy, pamięci i mocy obliczeniowej, zawierająca graficzne narzędzia zarządzania.

SQL Server Agent

Usługa uruchamiająca zadania harmonogramowane, takie jak kopie zapasowe. Edycja Express jej nie zawiera.

Pamięć bufora

Obszar pamięci, w którym silnik trzyma odczytane strony danych, żeby nie sięgać po nie ponownie do dysku.

Instancja

Osobna kopia silnika SQL Server z własnymi bazami i ustawieniami. W Express nazwa domyślna to SQLEXPRESS.

Kopia zapasowa

Plik z zapisem bazy danych, z którego można ją odtworzyć. Poleceniem T-SQL jest BACKUP DATABASE.

sqlcmd

Narzędzie wiersza poleceń do uruchamiania zapytań i skryptów T-SQL, także z Harmonogramu zadań Windows.

Dziennik transakcji

Plik, w którym baza zapisuje kolejne zmiany przed utrwaleniem ich w plikach danych. Nie wlicza się do limitu 10 GB.

Indeks

Struktura przyspieszająca wyszukiwanie wierszy po wybranych kolumnach, zmniejszająca liczbę odczytów z dysku.

Warto przeczytać