Porównywanie struktury i wykrywanie zmian pomiędzy bazami danych

Porównywanie struktury i wykrywanie zmian pomiędzy bazami danych oraz ich projektem

Wprowadzenie

Porównywanie struktury i wykrywanie zmian pomiędzy bazami danych wydaje się problemem dość powszechnym. Motywacja może być skrajnie różna, ale jako przykład można podać:

  • Sprawdzanie zmian pomiędzy projektem bazy danych oraz samą bazą danych przed jej aktualizacjom
  • Sprawdzenie zmian pomiędzy dwiema różnymi bazami danych
  • Sprawdzanie aktualnej struktury bazy danych z projektem w celu wykrycia manualnych zmian w bazie danych

Scenariusze te mogą wynikać czy to z przygotowania procesu publikacji zmian czy też zapewnienia odpowiedniej higieny bazy danych. Trzeba pamiętać, że w przypadku rozwiązań hurtowni danych dość często spotkać można różne “tymczasowe” obiekty, takie jak tabele z kopią danych przed przeładowaniem danych czy też różne wersje procedur, które tworzy programista w trakcie testowania ich wydajności. Wraz z upływem czasu ilość takich obiektów może być całkiem spora… Oczywiście powinien istnieć projekt bazy danych, nikt nie powinien wprowadzać manualnie żadnych zmian bezpośrednio na bazie danych, a projekt bazy danych powinien być publikowany bez jakiejkolwiek ingerencji użytkownika z wykorzystaniem narzędzi i procesów “Continiuus Integration”. Niestety przeważnie przynajmniej jeden z tych punktów okazuje się prawdą i wtedy też albo manualnie, albo najlepiej automatycznie jesteśmy zmuszeni do porównania dwóch baz danych lub bazy danych z projektem. W dzisiejszym poście postaram się przybliżyć różne metody oraz narzędzia, z których możemy skorzystać, aby to osiągnąć w przypadku SQL Server.

  1. Przedstawienie problemu

    Skrypt bazy danych w tym przykładzie nie ma większego znaczenia, natomiast w celach poglądowych załóżmy, że posiadamy projekt bazy danych z następującą strukturą:

    oraz bazę danych na serwerze z następującą strukturą:

    Jak widać na powyższych skryptach struktura obu baz danych jest dość trywialna, natomiast bazy te nieznacznie się różnią. W projekcie dodaną nową tabelę, natomiast zmianie uległa również sama baza danych znajdująca się na serwerze. Obrazuje to natomiast ideę problemu, jaki może się pojawić.

  2. Proste porównywanie z wykorzystaniem zapytań w T-SQL

    W przypadku porównywania dwóch baz danych, które znajdują się na serwerze najprostszą metodą może okazać się skorzystanie z widoków systemowych, które za pomocą T-SQL pozwolą wyekstrahować strukturę obu baz danych i za pomocą prostego zapytania pozwoli znaleźć różnicę pomiędzy nimi. Przykładowo:

    Powyższy skrypt pozwoli wykryć różnice w ilości oraz strukturze tabel. Oczywiście skrypt powinien być rozszerzony przynajmniej o typy danych kolumn, klucze czy też indeksy dla tabel oraz o pobieranie informacji o pozostałych obiektach (widoki, procedury, etc) w celu bardziej szczegółowego porównania. Plusem takiego rozwiązania niewątpliwie jest prostota oraz fakt, że jest to tak naprawdę jeden skrypt T-SQL. Jeżeli bazy znajdują się na różnych serwerach można skorzystać z “Linked Servers” czy też przygotować prosty projekt w “SQL Server Integration Services”. Z całą pewnością nie jest to jednak wydajna i polecana opcja szczególnie dla większych projektów.

  3. Wykrywanie zmian z wykorzystaniem Visual Studio

    “Visual Studio” w ramach projektu bazy danych dostarcza narzędzie “Schema Compare”, które pozwala w bardzo prosty sposób porównać bieżący projekt bazy danych z bazą danych na serwerze,ale również między innymi dwie różne bazy danych znajdujące się na różnych serwerach.

    Wynikiem operacji jest szczegółowy raport ze zmianami pomiędzy dwiema bazami.

    Dodatkowo w prosty sposób można wygenerować skrypt różnicowy i zaktualizować bazę danych lub projekt zgodnie z oczekiwaniami. Co ważne “Visual Studio” w tym przypadku korzysta z narzędzi i bibliotek, które można również wykonać z poziomu linii poleceń co zostanie pokazane w następnych akapitach.

  4. Wykrywanie zmian z wykorzystaniem tablediff

    “Tablediff” (https://docs.microsoft.com/en-us/sql/tools/tablediff-utility?view=sql-server-2017) to narzędzie, które de facto nie służy do porównywania struktury bazy danych, ale myślę, że warto o nim tutaj wspomnieć. Służy on do porównywania zawartości (danych) pomiędzy dwoma tabelami. Gdy spróbujemy porównać dwie tabele, które nie są identyczne otrzymamy błąd. Przykładowo:

    Zwróci następujący komunikat:

    Poprawne wykonanie komendy zwróci natomiast następujący komunikat w sytuacji, gdy tabele będą identyczne (zarówno pod względem struktury jak i danych)

    Dodatkowo za pomocą dodatkowych parametrów można wpłynąć na zwracany rezultat, w tym przypadku parametr “et” oraz “f” pozwoli na wygenerowanie skryptu, który zadba o doprowadzenie drugiej tabeli do takiego samego stanu (pod względem danych)

    Przykładowy rezultat:

    Jak wspomniałem nie jest to narzędzie do porównywania schematu, natomiast pośrednio również zwraca informację o tym czy schemat pomiędzy dwoma tabelami jest identyczny.

  5. Wykrywanie zmian z wykorzystaniem sqlpackage

    Kolejnym przykładem narzędzia jest “sqlpackage” (https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-2017). Pozwala ono na szereg operacji związanych z pracą z projektem baz danych, cyklem życia bazy danych, a w szczególności z bazami danych zarejestrowanymi jako “data tier application” (https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-2017), ale nie tylko.

    1. DriftReport
      Pierwszą z opcji narzędzia jest opcja “DriftReport”, która pozwala na wykrycie zmian w bazie danych od czasu jej ostatniej publikacji. W celu skorzystania z tej opcji baza danych musi jednak być zarejestrowana jako “Data-tier application”. Wtedy bowiem, kopia projektu bazy danych (“DACPAC”) jest składowana na serwerze bazy danych i w chwili wykonania polecenia jest ona porównywana z aktualną strukturą bazy danych. Przykładowo:

      Rezultatem będzie plik z opisem zmian:

      Jest to świetne narzędzie do wykrywania obiektów, które zostały manualnie dodane lub zmienione bezpośrednio na serwerze. Pewnym minusem natomiast może być fakt, że baza danych musi być zarejestrowana jako “Data-Tier application”. Warto również dodać, że korzystając z projektu bazy danych, jeżeli baza danych jest zarejestrowana, można skorzystać z opcji “Block publish when database has drifted from registered version”, która przerwie publikowanie w chwili wykrycia zmian. Czasami jednak musimy pogodzić się z faktem, że baza danych może zawierać pewne obiekty, które pełnią rolę kopii tymczasowych. Szczególnie w przypadku rozwiązań hurtowni danych.
    2. DeployReport
      Kolejną opcją “sqlpackage” jest “DeployReport”. Pozwala ona na porównanie projektu bazy danych z bazą danych na serwerze oraz wygenerowanie podsumowania z informacją, które obiekty zostaną opublikowane podczas publikacji zmian. Niestety nie ma możliwości porównania dwóch baz danych, a taka próba:

      Zwróci błąd:

      Dozwolone natomiast jest porównywanie pliku projektu (pliku bazy danych DACPAC) z bazą danych na serwerze. Co ważne można korzystać z profilu publikacji co ułatwia pracę z wieloma środowiskami oraz parametrami.

      Z wykorzystaniem profilu publikacji:

      Przykładowy rezultat:

      Należy pamiętać, że domyślnie polecenie nie zwróci obiektów, które zostały manualnie dodane do bazy danych na serwerze. Problem ten można rozwiązać modyfikując profil publikacji oraz zaznaczając opcję w “Advanced > Drop: “Drop objects in target but not in source”. Rezultat:

      W tym przypadku komenda zwróci również listę obiektów, które znajdują się w bazie danych, natomiast nie znajdują się w projekcie. Niestety nie ma tutaj jawnego rozróżnienia pomiędzy tymi obiektami. Oczywiście powyższe ustawienie nie zawsze się sprawdzi, natomiast zawsze pozostaje opcja przygotowania dedykowanego profilu do tego typu porównań.
    3. Extract
      Ostatnią opcją “sqlpackage” o której chciałbym tutaj wspomnieć jest “Extract”. Przykład:

      Opcja “Extract” pozwala na wygenerowanie pliku bazy danych (“DACPAC”) z istniejącej bazy danych znajdującej się na serwerze. Co ważne baza nie musi być zarejestrowana jako “Data-Tier application”. Może to być bardzo przydatna opcja między innymi w przypadku korzystania z następnego narzędzia.
  6. Wykrywanie zmian z wykorzystaniem msbuild

    Kolejnym narzędziem, który możemy wykorzystać jest “msbuild” (https://blogs.msdn.microsoft.com/ssdt/2014/07/15/msbuild-support-for-schema-compare-is-available/). Korzystając z opcji “SqlSchemaCompare” jesteśmy w stanie porównać bazy danych oraz pliki projektów bazy danych (“DACPAC”). Niestety ja miałem sporo problemów z uruchomieniem polecenia oraz porównaniem z bazą danych bezpośrednio i polecenie, które teoretycznie powinno działać, w moim przypadku nie zawsze działa – na przykład w SQL Server 2017:

    Natomiast porównywanie dwóch plików “DACPAC” działa bez zarzutów. Tutaj można wykorzystać “sqlpackage” (opcja “Extract”) oraz wygenerować “DACPAC” w celu porównania z projektem. Przykładowo:

    Rezultat:

    Wynik jest zatem bardzo podobny, aczkolwiek bardziej szczegółowy…

  7. Wykrywanie zmian z wykorzystaniem dodatkowych narzędzi

    Na samym końcu należy wspomnieć o dodatkowych narzędziach, które można w tym celu wykorzystać. Mimo że w większości są one płatne to w większych projektach oraz większych zespołach przyśpieszają oraz upraszczają programowanie. Osobiście korzystałem z narzędzi RedGate, które mimo że są kosztowne to według mnie naprawdę są warte tej ceny (materiał niesponsorowany). Z pozostałych poniżej nie korzystałem, natomiast każdy z nich ma również spore grono zwolenników. Najpopularniejszymi narzędziami wydają się być:
    – RedGate SQLCompare (https://www.red-gate.com/products/sql-development/sql-compare/index)
    – DBComparer (https://dbcomparer.com/)
    – ApexSQL Diff (https://www.apexsql.com/sql_tools_diff.aspx)
    Oczywiście podobnych narzędzi jest znacznie więcej i dają podobne rezultaty, więc jeżeli tylko polityka firmy oraz budżet na to pozwolą to ich wykorzystanie wydaje się uzasadnione.

Zakończenie

Metod porównywania oraz wykrywania zmian czy to pomiędzy bazami danych czy też pomiędzy projektem bazy danych, a samą bazą danych jest stosunkowo wiele. Z wykorzystaniem “SQL Server Integration Services”, “SQL Agent”, “Windows Scheduler” czy też narzędzi do integracji oraz “Continiuus Integration/Delivery”, oprócz samego wykrywania zmian można ten proces również zautomatyzować. Należy dodać, że oczywiście warto dążyć do jak najlepszego kodu oraz dobre praktyki powinny zniwelować użycie powyższych narzędzi, natomiast jeżeli problem istnieje to, jak widać, narzędzi jest całkiem sporo. Zarówno płatnych, ale również darmowych.

PS1. Ścieżki do narzędzi mogą znajdować się w innych lokalizacjach, zgodnie z zainstalowaną wersją SQL Server.
PS2. Oczywiście “sqlpackage” posiada jeszcze kilka innych opcji, w tym między innymi “Deploy”, natomiast jest to poza zakresem tego postu. Podobnie wygląda sprawa “msbuild”

Slawomir Drzymala
Follow me on

Slawomir Drzymala

Still playing with data and .NET technologies
Slawomir Drzymala
Follow me on

2 Comments

  1. Sebastian Jóźwiak

    Witam. SchemaCompare jest dość wygodnym narzędziem. Jednak mam pewne problemy przy generowaniu skryptu, gdy chcę dane zmiany wprowadzić do bazy.
    Jeden z problemów to pojawienie się komunikatu “Target schema drift detected”. Zaraz po nim muszę ponownie wykonać Compare i potem wygenerować skrypt ponownie.
    Drugi to pojawienie się informacji o skutecznym wygenerowaniu kodu, jednak okno z kodem nie pojawia się.
    Czym te problemy mogą być to spowodowane?

    Reply
    1. Slawomir Drzymala (Post author)

      Cześć.
      Pierwszy problem może wynikać na przykład z ustawień bazy danych/profilu np. ustawienia “Block publish when database has drifted from registred version”, chociaż google pokazuje, że przyczyna może być nawet nie związana z samym projektem bazy danych.
      Wydaje mi się, że żeby określić źródło problemów potrzebne będzie więcej informacji jak wersja Visual Studio, ustawienia bazy danych, ustawienia profilu itd. Jeżeli nie uda się rozwiązać problemu zapraszam do kontaktu mailowego gdzie chętnie spróbuję pomóc.

      Reply

Leave a Comment

Your email address will not be published. Required fields are marked *