Uniwersalna procedura aktywacyjna Service Brokera

    No Comments

    Usługa Service Brokera, która pojawiła się wraz z wersja 2005 zadziwia mnie do tej pory.
    Od wielu lat producent niewiele w niej zmienia, co może świadczyć o dobrze przemyślanej architekturze.
    Z drugiej strony monitoring tej usługi dostarczony przez producenta pozostawia wiele do życzenia patrząc na to nawet z ergonomicznego puntku widzenia. Walka z wyłączającymi się kolejkami i komunikatami typu poison spędziła nie jeden sen z powiek. Jak sobie z tym najlepiej poradzić ?
    Zakładam, że czytelnik zna podstawową budowę Service Brokera i ma doświadczenie w jego stosowaniu w warunkach produkcyjnych. To o czym należy pamiętać, to świadomość, że wbudowano w serwer bazodanowy mechanizm, który potrafi obsłużyć za pomocą języka t-sql komunikację asynchroniczną bez pomocy zewnętrznych narzędzi. To niewątpliwie wielki plus funkcjonalny.
    Chciałbym skoncentrować się nad propozycją budowy szkicu uniwersalnej procedury aktywacyjnej, która obsługuje kolejkę targetową.
    Dokumentacja Microsoftu sprowadza się do budowy dedykowanej per kolejka procedury aktywacyjnej, gdzie spore fragmenty kodu są powtarzane i jest to nieco niezgodne z podejściem DRY.
    Przyjęte założenia:
    1. Przekazywane są jedynie komunikaty w formie XML
    2. Każdy typ komunikatu XML na podstawie jego root elementu będzie obsługiwany przez dedykowaną procedurę składowaną
    3. Każda z takich procedur ma standardową postać

    4. Wszystkie obiekty bazodanowe są obrębie jednej bazy.
    5. Wykorzystany zostanie dynamiczny sql.
    6. Procedura przyjmie minimalną liczbę argumentów i stanie się wewnętrzną procedurą aktywacyjną każdej z kolejek targetowych.

    Proponowany nagłówek:

    Powiązanie uniwersalnej procedury aktywacyjnej z przykładową kolejką

    Niech nasza kolejka nazywa się SampleTargetQueue.

    Przygotowujemy procedurę nakladkową definiowaną per kolejka

    Powiązanie kolejki docelowej z tak przygotowaną procedurą aktywacyjną.

    Szkic funkcjonalny uniwersalnej procedury aktywacyjnej

    1. Pobranie z kolejki pierwszego komunikatu XML

    2. Na podstawie root elementu XML ustalenie nazwy procedury składowanej odpowiedzialnej za logikę biznesową

    Proponowany kod transformacji :

    W tym przypadku wyznaczamy wartość nazwy procedury składowanej jako
    spInterfaceAccountV5getNumberGetAccountNumber

    3. Obsłużenie sytuacji wyjątkowych, komunikatów typu poison, XML-i niezgodnych ze schema parametru wejściowego procedury składowanej, wyłączonej kolejki, naruszenia więzów integralność.
    4. Wszystkie powyższe punkty powinny być realizowane w ramach jednej transakcji

    5. Zalogowane komunikaty błędów trafiają do jednej wspólnej tabeli.
    Propozycja takiej wspólnej tabeli

    Przykładowy fragment kodu uzupełniającego tabelę w przypadku wystąpienia błędu:

    Znaczenie pola status:

    wartość znaczenie czy do ponownego przetworzenia
    0 nowy wpis TAK
    1 wpis przetworzony z sukcesem NIE
    2 wpis przetworzony z błędem TAK
    3 nie wymaga przetworzenia NIE

     

    Proste zapytanie monitorujące błędne wpisy w tabeli

    Należy dodatkowo wprowadzić politykę retencji, gdzie po ustalonej liczbie dni, dane powinny być usuwane trwale, w szczegolności tam, gdzie status wskazuje na brak potrzeby ponownego przetworzenia.

     

     

    Literatura

    Configuring Service Broker for Asynchronous Processing

     

    Categories: implementacja, t-sql

    Generowanie losowych odpowiedników dla danych wrażliwych dla RODO . Część 3

    No Comments

    W poprzedniej części  przygotowaliśmy szkic  procedury składowanej implementującej prosty mechanizm wykrywania danych osobowych.

    To czego zabrakło, to wykorzystanie funkcji tabelarycznych  do wykrywania tych danych, które posiadają sumę kontrolną i same wyrażenia regularne mogą nie wystarczyć.

    Zajmijmy się   identyfikatorami PESEL, NIP, REGON. Zainteresowanych budową  takich identyfikatorów odsyłam do powszechnie dostępnych źródeł (m.in WIkipedia).

    Poniżej przedstawiono kod trzech prostych funkcji tabelarycznych napisanych w Transact SQL.

    PESEL

    NIP

    REGON

    Przykłady wywołania

    Wynik działania

     

    RAWPESEL PESEL isValid
    00000000001 00000000000 0

    RAWREGON REGON isValid
    000000001 000000000 0

    RAWNIP NIP isValid
    0000000001 0000000000 0

    Każda z tych funkcji jest zbudowana na podstawie tego samego schematu.
    Na poczatku pobieramy na wejściu danę do weryfikacji, następnie pobieramy cześć danych bez sumy kontrolnej i wyliczany na jej podstawie brakującą sumę kontrolną. Jeśli tak spreparowany identyfikator jest tożsami z wejściowym i dodatkowo składa się z samych cyfr, możemy uznać, ze mamy do czynienia z poprawnymi danymi. W ten sposón wyznaczana jest kolumna isValid.

    Jak można je zastosować ?

    Przydatne strony:

    http://walidator.testerzy.pl/

    http://zylla.wipos.p.lodz.pl/ut/nip-rego.html

    Categories: implementacja, RODO, t-sql

    Zanim błąd i szef zapukają do drzwi

    2 komentarze

    Czy zdażyło się wam odtwarzać po raz kolejny bazę danych po logicznym uszkodzeniu plików?

    Wlaściwie kiedy ostatnio sprawdzano ją dostępnymi narzędziami?
    Kiedy ostanio dokonano pełnej, różnicowej kopii bezpieczeństwa, jak często odkładane są logi transakcyjne?
    Czy baza ma poprawny model recovery ?
    Jeśli na chociaż jedno pytanie pojawiły się w głowie wątpliwości, to dobrze, że przed awarią…

     

    Pomocny skrypt

    Hipotetyczny rezultat powyższego zapytania

    database_name total_size_gb recovery_model_desc LastDBCCCHeckDB_RunDate
    XYZ 153.8079 SIMPLE 1900-01-01 00:00:00.000
    tempdb 58.6021 SIMPLE 1900-01-01 00:00:00.000
    HURT 49.2092 SIMPLE 1900-01-01 00:00:00.000
    ABC 46.6123 SIMPLE 1900-01-01 00:00:00.000
    ZZA 0.4893 FULL 1900-01-01 00:00:00.000
    model 0.1470 SIMPLE 1900-01-01 00:00:00.000
    master 0.0148 SIMPLE 1900-01-01 00:00:00.000
    msdb 5.8594 SIMPLE 2015-10-06 13:34:30.730

    Na co zwrócić uwagę ?

    Bazy systemowe (master, model,msdb,tempdb) mają ustawiony recovery model równy SIMPLE. Żadna z nich, poza msdb nie była nigdy sprawdzona narzędziem DBCC CheckDB ? To dobrze ?

    Jedyna baza w trybie FULL teź nigdy nie została sprawdzona.

    Do odważnych świat należy,ale nie do nieroztropnych.

    https://www.mssqltips.com/sqlservertip/4381/sql-server-dbcc-checkdb-overview/

    Categories: implementacja, migawka, t-sql

    Szyfrowanie danych dla RODO – droga na skróty

    No Comments

    SQL Server ma wbudowane dwie funkcje, które pozwalają na szyfrowanie i odszyfrowanie danych w sposób odwracalny.
    Czy to nie wystarczy do zapewnienia bezpieczeństwa i realizacji wymogów RODO ?

    Przykładowy kod:

    Wynik działania

    clearText :  Tajna wiadomosc do zaszyfrowania

    encrypted  : 0x010000007662E7BC0D6744BEE84DDD103891A2B21EEDE7DD99FEDD8E11D18F81FEA37028B5ED9060EC65FC9A19CD8ABDED32CA5D974D214D4D12109C495B2F2045F2B7F1A06A3D327EA6765C25EC8C8802E94A064ABCB41B91433E99

    decrypted : Tajna wiadomosc do zaszyfrowania

     

    Niestety w takim przypadku mamy do czynienia z pseudonimizacją. Dla tych, dla których to pojęcie jest nowe, proponuję na początek krótkie wprowadzenie

    http://blog.e-odo.pl/category/reforma-ochrony-danych-osobowych/anonimizacja-i-pseudonimizacja/

    Categories: implementacja, RODO, t-sql

    Generowanie losowych odpowiedników dla danych wrażliwych dla RODO . Część 2

    No Comments

    1. Dedykowany widok wykorzystujący funkcje pseudolosowe.

    Do czego jest potrzebny ten widok? To jest obejście znanego problemu braku możliwości wykorzystania funkcji pseudolosowych w funkcjach skalarnych/tabelarycznych.
    Przykładowo:

    2. Dedykowane funkcje tabelaryczne

    Z założenia jedynym wykorzystywanym obiektem jest powyższy widok, pozostała implementacja nie wymaga dodatkowych struktur,tabel,dedykowanych funkcji i innych obiektów.
    Każda z funkcji jest budowana schematycznie przy wykorzystaniu CTE, podobnie jest zapytania w pierwszej części artykułu. Dane generowane przez alias CTE zwracają wartość w postaci surowej i jeśli trzeba ją uzupełnić o poprawną sumę kontrolną odpowiednie podmiany postaci surowej na poprawną są w instrukcji select aliasu.
    Funkcje przyjmują dwa parametry wejściowe @value i @algorithm. Ta ostatnia jest do poźniejszego wykorzystania w ramach rozwoju grupy algorytmów dla takiej samej domeny. Aktualne wersje funkcji nie korzystają z wartości wejściowej, ale można będzie to zmodyfikować na następnych iteracjach w ramach rozwoju, tak by wykorzystywany kod np. w procedurach składowanych nie ulegał zmianie ze względu liczbę argumentów funkcji.

    Generowanie numerów PESEL

    Generowanie EMAIL

    Generowanie dowodu osobistego

    Generowanie rachunku bankowego

    Generowanie imion

    Generowanie kodu pocztowego

    3. Generowanie danych testowych (TODO)

    4. Wykorzystanie funkcji tabelarycznych do zmiany wygenerowanych danych testowych (TODO)

    Categories: implementacja, RODO, t-sql

    Generowanie losowych odpowiedników dla danych wrażliwych dla RODO . Część 1

    1 Comment

    Wyobraźmy sobie sytuację, że mamy w bazie danych mnóstwo wrażliwych informacji, numery PESEL, adresy e-mail, numery telefonów, numery dokumentów tożsamości, konta bankowe.

    Te dane należy tak anonimizować, by  uzyskany rezultat nie dawał  możliwości  odtworzenia oryginalnej wartości i jednocześnie spełniał, jeżeli trzeba reguły poprawności danego wzorca. Dotyczy to m.in. długości, formatu i sumy kontrolnej.

     

    Zacznijmy na przykładzie numeru dowodu osobistego w formacie ABC123456.  Czy jest on poprawny ?

    Dla tych, którzy przyzwyczaili się do sumy kontrolnej na ostatnim miejscu wzorca dziwne może być to, że suma kontrolna to pierwsza z cyfr (czwarty element).

    Powyższe zapytanie zwróci jako poprawny numer dowodu wartość ABC523456, czyli 5 jest poprawną cyfrą kontrolną, a nie 1.

    1. Na początku wygenerujmy  1 mln sztucznych dowodów osobistych.

    W posobny sposób można uzyskać inne typy danych wrażliwych

    Przykładowy generator numerów PESEL

    Generator polskich numerów NRB.

    Budowa mechanizmu generowania danych jest szablonowa. Wykorzystywany jest CTE (Common Table Expression) w taki sposób, że
    a) za pomocą kilku obiektów (aliasy PoziomX) można kontrolować liczbę zwracanych rekordów, ograniczenie dla pięciu poziomów jest liczbą rzędu 2 do potęgi 32 (ok 4 mld),
    b) budowany jest napis o formacie zgodnych z danym typem numeru (długość, format) ale bez poprawnej sumy kontrolnej, nazywamy postacią surową
    c)postać surowa jest dynamicznie podmieniana w taki sposób, że podmianiana jest część w tym miejsciu, gdzie występuje suma kontrolna, czasem jest to poczatek, środek lub koniec napisu.

    2. Załóżmy, że te wygenerowane dokumenty/numery/konta są prawdziwe. Jak je zaktualizować, tak by wyglądały na poprawne i spełniały reguły? Ważne jest też to, by generować nowe wartości optymalnie i w miarę możliwości prostym kodem t-sql.
    Zacznijmy od budowy pomocniczej perpektywy

    Widok zwraca jeden rekord, za każdym razie z innymi wartościami.

    CryptUID Crypt1 Crypt2 Crypt3 Crypt4 CryptYear CryptMonth CryptDay
    F463175E-DD3C-479E-834F-BB850552FF56 189347 549814 23651 661922 10 5 11

    Do czego ma służyć ten widok ? Funkcja systemowa RAND() nie może zostać użyta wewnątrz funkcji skalarnej, takie mamy ograniczenie silnika. A takie właśnie funkcje będą podstawą procesu podmiany danych.O tym napiszę w drugiej części.

    Categories: implementacja, RODO, t-sql

    Wykrywanie danych osobowych za pomocą T-sql – wersja dla ubogich

    3 komentarze

    Nadchodzi nowa regulacja dotycząca ochrony danych osobowych (RODO) termin wdrożenia  to maj 2018, nie ma więc wiele czasu.
    Regulacja wnosi wiele obostrzeń w stosunku do obecnie działających przepisów, ale i tak pracę organiczną należy zacząć od analizy danych przechowywanych na naszych serwerach.

    Na rynku jest sporo gotowych i drogich narzędzi, które potrafią wykryć dane za pomocą wbudowanych reguł.

    Postanowiłem się zmierzyć z takim zadaniem wykorzystując kod t-sql i zbudować proste narzędzie do Data Discovery, a właściwie jego pierwszy szkic.

    Poniżej znajdziesz propozycje implementacji niektórych zagadnień, które przydadzą się do budowy twojego własnego rozwiązania.

     

    Założenia

    Narzędzie będzie wymagać instalacji dodatkowej bazy danych np. o nazwie RODO, na której umieścimy kod procedury składowanej, dedykowane tabele i słowniki.

    Kod pozostałych na instancji baz nie powinien ulec zmianie.

    Budowa procedury składowanej na dedykowanej instancji serwera.

    Procedura pobiera listę tabel i kolumn tekstowych dla całej instancji serwera sql.

    Ograniczono listę kolumn do  typów char, nchar, varchar, nvarchar.

    Dodatkowo dla każdej z tabeli  odłożono liczbę rekordów i aktualnie ustawiony seed dla pól typu autonumer (identity)

    Procedura jest sterowana parametrem konfiguracyjnym w postaci XML-a.

    Dynamicznych sql pobierane są poszczególne kolumny danej tabeli . Tu można zastosować limit liczby rekordów lub limit procentowy, np 5% całości.

    Po załadowaniu do dedykowanej tabeli danych z całej instancji proces rozpoczyna analizę metadanych i danych.

    Zastosowane algorytmy

    a) nazewnictwo kolumn,

    zakładam na przykład , że pole char(11) lub varchar(11) o nazwie PESEL zawiera w sobie numer ewidencyjny

    b) wyrażenia regularne

    Ze względu na słabe wsparcie  natywne z poziomu języka sql zastosowałem dedykowaną procedurę CLR.

    Bardzo efektywne do weryfikacji np. kodu pocztowego w postaci

    c) funkcje skalarne zwracające wartość  1 (prawda) lub 0 (fałsz), parametrem wejściowym jest napis.

    Funkcje te można łączyć wraz z wyrażeniami regularnymi

    d) dedykowane słowniki

    Na początek warto zacząć od słownika imion i miejscowości w Polsce.

     

    Kod przetestowano na instancji serwera sql 2012 w wersji developer.

    To z czym się zmierzyłem na początku.

    Pobranie listy tabel i kolumn tekstowych w obrębie serwera

    Bazy mogę mieć róźne collation, wykorzystałem wymuszenie konwersji podczas operacji porównania

    Bazy techniczne master,msdb,model i tempdb zostały wykluczone

    Wykorzystano systemową procedurę sp_MSforeachdb,
    która jest wywoływana dla każdej bazy danych instancji serwera w taki sposób, ze fragment dynamiczny kodu sql  ‘?’ jest zamieniany na nazwę bazy.

     

    Sampling danych

    Wykorzystano rozszerzenie t-sql  znane od wersji 2005, czyli TABLESAMPLE

    Przykładowy kod na demonstracyjnej bazie danych

    Sapling danych zapisujemy do kolejnej tabeli trwałej np.

    dbo.DaneOsoboweDane

    Czyli dla każdego rekordu z  DaneOsoboweTabele

    zapisujemy po kilkaset/kilka tysięcy/kilkanaście tysięcy rekordów. Zależy to oczywiście od liczby rekordów w poszczególnych tabelach

    Przykładowy fragment kodu

    W tym momencie w tabeli DaneOsoboweDane mamy już próbkę danych z całęj instancji serwera sql.

    Teraz to co należy zrobić, to przeskanować każdą grupę danych za pomocą  np. wyrażeń regularnych.

    Wyrażenie regularne

    Przykładowe reguły:

    Kod pocztowy:

    ^\d{2}-\d{3}$

    Numer IP:

    ^\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0 -9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b$

    E-mail:

    ^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$

     

     

    Przykładowe wywołanie procedury do wykrywania danych osobowych

    Celem działania procedury powinien być raport, który pokaże w której kolumnie danej tabeli znajdują się

    dane spełniająca zastosowane wyżej kryteria

    Przykładowy raport z bazy  AdventureWorks2012:

    SchemaName TableName NumberOfRows ColumnName DataValue RuleName
    Person EmailAddress 19977 EmailAddress miguel46@adventure-works.com EMAIL:^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$

     

    Krzysztof Pudłowski

    Literatura:

    https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

    https://www.mssqltips.com/sqlservertip/1308/retrieving-random-data-from-sql-server-with-tablesample/

    https://technet.microsoft.com/en-us/library/ff878119(v=sql.110).aspx

    https://github.com/DevNambi/sql-server-regex

    Categories: RODO, t-sql

    Kiedy skończy się identity

    No Comments

    To była długa bezsenna noc. Szczekające psy sąsiada, deszcz bijący o szyby pokoju.
    Czwarta nad ranem, gdy w końcu udało się zasnąć, komórka służbowa zaczęła dzwonić.

    Msg 8115, Level 16, State 1, Line 1
    Arithmetic overflow error converting IDENTITY to data type int.
    Arithmetic overflow occurred.
    Po raz kolejny przepełnił się autonumer jednej z licznych tabel na serwerze produkcyjnym.

    Jak się przed tym obronić ? Jak oszacować kiedy skończy nam się możliwość generowania autonumeracji ?
    Czy da się opracować proaktywny mechanizm wspomagający codziennie utrzymanie systemów ?

    Spróbujmy oszacować to jedynie na podstawie metadanych.

    Co mamy do dyspozycji ?

    Liczbę rekordów w tabeli, jej datę utworzenia, aktualną wartość licznika, maksymalną wartość licznika.

    Zastosowany jest najprostszy z możliwych. algorytmów.

    Zakładamy, że liczba rekordów przyrasta liniowo w czasie, parametry tego wzrostu są tak dopasowane, że w chwili t0 (dla daty utworzenia) liczba rekordów wynosi 0, a dla chwili tk (dla daty katastrofy, czyli przepełnienia typu całkowitego) liczba rekordów jest równa maksymalnej.

    Poniżej umieszczono szkic skryptu:

    Dla tych, których takie rozważania nudzą, ciekawe może się wydać, to że przekroczenie zakresu zmiennej całkowitej spowodowało wiele katastrof, które wynikały ze złego oszacowania czasu pracy oprogramowania i zastosowanego typu zmiennej. Warto o tym pomyśleć podczas projektowania rozwiązania bazodanowego na długie lata.

    https://bulldogjob.pl/news/114-upiorne-bugi-male-bledy-wielkie-katastrofy

    Categories: migawka, t-sql

    Jak sprawdzić czy grupa tabeli ma takie same partycje

    No Comments

    Załóżmy, że w bazie wiele tabel partycjonowanych w ten sam sposób, ale do każdej z nich używamy schematu partycjonowania o dedykowanej nazwie. Chciali byśmy kontrolować, czy liczba partycji na tych tabelach jest taka sama i czy daty poszczególnych przedziałów funkcji partycjonującej się nie rozjechały.

    Mogą w tym pomóc dwa poniższe zapytania:

    Jeżeli w każdym zapytaniu w drugiej kolumnie mamy tylko jedną wartość i jest ona równa ilości wierszy w drugim zapytaniu, to nasze tabele są w porządku.
    Przykładowo:

    tabele_partycje

    W pierwszym zapytaniu w drugiej kolumnie jest tylko wartość 27 i podobnie w drugim zapytaniu wszędzie jest wartość 6. Do tego pierwsze zapytanie zwraca 6 wierszy, a drugie 27.

    Categories: t-sql

    Witaj, świecie!

    No Comments

    Dawno dawno temu w odległej galaktyce….

    Na ekranie komputera dowództwa.

    Categories: t-sql