W większości MŚP ten sam serwer SQL robi dwie rzeczy jednocześnie: obsługuje bieżącą pracę w ERP i próbuje jednocześnie wypluć 15-stronicowy raport sprzedaży z marżą za ostatni kwartał. Nic dziwnego, że o 10:30 w ostatni piątek miesiąca cała firma staje w miejscu.
Scenariusz, który widzieliśmy w ciągu ostatniego roku kilkanaście razy, zawsze wygląda podobnie. Prezes albo kontroler finansowy odpala ulubiony raport z Comarch Optima lub enova365. W tym czasie magazynierka nie może zeskanować przyjęcia, handlowcowi nie wczytuje się kartoteka klienta, księgowej wisi okno przy wystawianiu faktury. Po trzydziestu minutach raport się generuje, wszyscy wracają do pracy — i do następnego raportu. Nie jest to problem „słabego ERP". To problem jednego dysku, jednej instancji SQL i dwóch bardzo różnych obciążeń, które się nawzajem gryzą.
Rozwiązanie, które działa dla firm 15–60 osobowych, jest nudne, dobrze znane w branży od 20 lat i nadal rzadko wdrażane w polskim MŚP: oddzielna baza do raportowania, która co noc (albo co godzinę) kopiuje dane z bazy produkcyjnej. Ta instancja nie służy do niczego innego. Nie wystawia się tam faktur, nie wprowadza się tam zamówień. Dostają do niej tylko ludzie, którzy pytają: „ile tak naprawdę zarobiliśmy w zeszłym kwartale".
Dlaczego raporty i transakcje się gryzą
Baza produkcyjnego ERP jest zoptymalizowana pod krótkie, przewidywalne zapytania. Jeden handlowiec wystawia jedną fakturę. Jeden magazynier zdejmuje jedną sztukę ze stanu. SQL Server trzyma indeksy, blokady wierszy i cache dostrojony pod ten rodzaj ruchu.
Raport zarządczy robi coś odwrotnego. Czyta setki tysięcy wierszy z kilku tabel, agreguje je po klientach, po grupach produktowych, po regionach. Nakłada blokady, których transakcyjna praca normalnie nie potrzebuje. Jedno większe zapytanie potrafi na kilkadziesiąt sekund zablokować tabele, do których co pięć sekund odwołuje się moduł sprzedaży.
Nie da się tego zoptymalizować bez końca. W pewnym momencie jedyną sensowną odpowiedzią jest fizyczne rozdzielenie: niech raporty żyją na innej instancji niż transakcje.
U jednego z naszych klientów — 22-osobowej hurtowni elektrycznej z Comarch Optima — comiesięczny raport „sprzedaż z marżą po grupach produktowych" trwał 27 minut. W tym czasie czterech handlowców i dwie osoby w magazynie regularnie dostawało komunikaty o timeoutach. Po wdrożeniu repliki ten sam raport uruchamiany na kopii trwa 11 sekund (bo ma własne, lepiej dobrane indeksy pod raportowanie), a produkcyjny ERP w ogóle nie zauważa, że ktoś go odpalił.
Trzy sposoby, jak postawić replikę
W polskich MŚP zwykle wystarczy jeden z tych trzech wariantów. Nie trzeba kupować hurtowni danych.
Transactional Replication w SQL Server. Jeśli Twój ERP stoi na Microsoft SQL Server (Optima, enova365, Subiekt GT w wersji Pro, większość Dynamics AX), ten mechanizm jest wbudowany. Baza produkcyjna wysyła na bieżąco zmiany do bazy repliki — opóźnienie liczy się w sekundach. Minus: wymaga wersji Standard albo wyżej i odrobiny ostrożności przy aktualizacjach ERP (replikacja czasem gryzie się ze zmianami schematu).
Mirroring / Always On / log shipping. Jeśli i tak masz drugi serwer na potrzeby disaster recovery, możesz go wykorzystać do raportowania „przy okazji". Readable Secondary w Always On to najczystsze rozwiązanie — zapasowa kopia bazy, do której można czytać na żywo. Baza DR i baza raportowa to ta sama maszyna, dwie funkcje.
Prosta nocna kopia (ETL). Najprostszy wariant, do którego namawiamy większość 10–30 osobowych firm. Każdej nocy skrypt (SSIS, PowerShell, Python, czasem tylko bcp) wyciąga zmienione rekordy z produkcji i zapisuje je do osobnej bazy. Dane są o 24 godziny stare — i w 90% raportów zarządczych to zupełnie wystarczy. Zaleta: nie ingerujesz w produkcyjny ERP, dostawca ERP nie ma nic do powiedzenia, awaria procesu nie zatrzymuje firmy.
W tej samej hurtowni elektrycznej, o której pisaliśmy wyżej, zrobiliśmy wariant trzeci. Jeden skrypt w PowerShell, uruchamiany o 2:00 w nocy, kopiuje zmienione faktury, przyjęcia magazynowe i kartoteki do osobnej bazy OptimaRaport. Cały proces trwa 6 minut. Raporty w Excelu i Power BI podpięte są pod tę bazę, nie pod produkcję. Koszt wdrożenia: dwa dni roboczych nasze plus 1200 zł rocznie za drugą licencję SQL Server Web. Zwrot: brak komunikatów o timeoutach, brak dyskusji „kto odpala raport o której godzinie".
Co ustalić, zanim zaczniesz
Niezależnie od wariantu, kilka decyzji trzeba podjąć na początku. Pominięcie ich jest głównym powodem, dla którego widzimy w firmach „replika, która nie działała od pół roku, bo nikt nie zauważył".
- Opóźnienie, które akceptujesz. 1 sekunda (replication), 15 minut (log shipping), 24 godziny (nocny ETL). Większość raportów zarządczych działa na danych starszych niż doba — nie przepłacaj za czas rzeczywisty, którego nie potrzebujesz.
- Co ma się replikować, co nie. Historii zmian cen z 2008 roku nie trzeba kopiować co noc. Ale pełna tabela faktur sprzedaży, pozycji faktur, magazynu i kartotek klientów — tak.
- Kto ma dostęp do repliki. Osobne konta SQL (tylko do odczytu) dla raportowców, kontrolera, Power BI. Nikt nie siada na repliki z narzędziem transakcyjnym — to pierwszy krok do dziwnych bugów.
- Monitoring, że replika żyje. Prosty alert mailowy: „jeśli ostatni timestamp w tabeli
Fakturyna replice jest starszy niż 36 godzin, krzycz". Bez tego dowiesz się o awarii od kontrolera, który dostanie dane sprzed tygodnia i uwierzy im.
Czego nie robić
Dwa błędy, które widzimy regularnie, kosztują potem więcej niż samo wdrożenie.
Pierwszy to „zróbmy sobie od razu hurtownię danych". Dla 20-osobowej firmy to przerost formy nad treścią. Osobna baza SQL będąca lustrzaną kopią produkcji (plus kilka widoków raportowych) załatwia 95% potrzeb. Hurtownia zaczyna mieć sens przy kilkudziesięciu tysiącach transakcji dziennie i wielu źródłach — nie przy jednej instancji Optimy.
Drugi to „zrobimy raporty bezpośrednio na backupie”. Backup to backup — kopia, która ma zadziałać raz na kilka lat, kiedy wszystko spłonie. Odpytywanie backupu to pewność, że w dniu awarii okaże się, że ktoś zmienił strukturę i restore nie działa. Replika i backup to dwie różne funkcje — jedna do raportowania, druga do odzyskiwania. Nie łącz ich.
Co dalej
Jeśli u Was ulubiony raport prezesa trwa dłużej niż 2 minuty i w tym czasie ktokolwiek w firmie dostaje komunikat „operacja przekroczyła limit czasu" — macie dokładnie ten problem, o którym mowa w tym tekście. Nie trzeba wymieniać ERP. Trzeba rozdzielić dwa obciążenia na dwie bazy, nawet jeśli obie stoją na tej samej maszynie.
Pierwsze pół godziny spędzamy na rozpoznaniu, które raporty bolą najbardziej i który z trzech wariantów ma u Was sens. Zwykle wychodzi z tego konkretna lista do wdrożenia na dwa–trzy dni robocze.
Produkcyjny ERP ma obsługiwać dzisiejszą pracę. Raporty niech żyją obok — na kopii, do której zaglądają tylko ci, którzy pytają o liczby.