Jak mogę śledzić zależności bazy danych?

Ponieważ aplikacje wewnętrzne ewoluują przez wiele lat, od czasu do czasu zdarza się, że istnieje wiele tabel, które ludzie uważają za nieistotne i chcą się wycofać. Jakie są praktyczne metody identyfikowania zależności od bazy danych, zarówno w środowisku SQL, jak i później w SSIS?

Pracowałem w miejscach, w których zastosowano dość brutalne opcje, takich jak:

• Upuść najpierw, zadaj później pytania (może zabić kompilację hurtowni danych, jeśli spróbuje ona wyodrębnić tabelę, która już nie istnieje)
• Najpierw usuń uprawnienia i poczekaj na zgłoszenie błędów (może powodować ciche błędy, jeśli awaria nie zostanie poprawnie obsłużona)

Doceniam to, że SQL Server jest wyposażony w narzędzia do śledzenia zależności w tej instancji, ale wydaje się, że mają problemy, jeśli masz bazy danych w różnych instancjach. Czy istnieją opcje, które ułatwiają sprawdzanie zależności, może odpowiadając na pytania typu „Gdzie jest używana ta kolumna?” z odpowiedziami typu „Over na tym drugim serwerze w tej procedurze składowanej” lub „Over w tym pakiecie SSIS”?

Nie ma łatwego sposobu na zrobienie tego. Wyzwalacze nie działają, ponieważ jeśli wybierzesz z tabeli, wyzwalacz nie zostanie uruchomiony. Najlepszym sposobem, w jaki to zrobiłem, jest umożliwienie programistom śledzenia tego, czego używają. Kiedy coś zostanie upuszczone, sprawdź ze wszystkimi zespołami deweloperów i po tym, jak wszyscy się wylogują, zmień nazwę obiektu. Wówczas nic się nie psuje przez miesiąc, a obiekt można bezpiecznie upuścić.

1. Wyszukaj kod w celu użycia z sys.sql_modules.definition: czy jest do niego odniesienie? Następnie...
2. Sprawdź uprawnienia: jaki kod klienta może to nazwać? Następnie...
3. Profiler

A zatem:

• W przypadku tabeli bez odniesienia i bez uprawnień nie jest używana.
• Bez odniesień i niektórych uprawnień uruchom profiler, aby zobaczyć użycie
• Bez uprawnień i referencji dodaj rejestrowanie użycia

To, co zrobiłem wcześniej, to uczynienie tabeli widokiem maskującym stół, a następnie sprawienie, by widok działał źle: (łączenie krzyżowe, wyraźne). Tak naprawdę nie usuwasz go, ale generujesz limity czasu lub skargi klientów ...

Jednym z szybkich sposobów, z których korzystałem w przeszłości (a tak naprawdę zależy to od wielkości tabel, liczby wyników indeksów itp.), Jest dodanie wyzwalacza, który rejestruje znacznik czasu podczas wykonywania akcji na stole. Jak powiedziałem, może to mieć problemy z wydajnością, dlatego należy zachować ostrożność - uważaj także, aby tabela rejestrowania nie używała pól tożsamości, ponieważ może to popsuć stary kod, który używa @@ IDENTITY. Oczywiście może to po prostu pokazać, że funkcja w aplikacji nie była przez jakiś czas używana.

Bardzo trudno jest śledzić zależności, gdy cały kod, który może trafić do bazy danych, nie znajduje się w bazie danych, tj. Przypadkowi klienci kwerendują bazę danych.

EDYCJA: Aby rozwiązać problem polegający na tym, że tabela nie może mieć wyzwalaczy SELECT, oto kolejna opcja, która powinna działać, zakładając, że tabele mają indeksy (testowane tylko w 2008 r.).

SELECT          
    last_user_seek,
    last_user_scan,
    last_user_lookup,
    last_user_update
FROM
    sys.dm_db_index_usage_stats AS usage_stats
INNER JOIN
sys.tables AS tables ON tables.object_id = usage_stats.object_id
WHERE
    database_id = DB_ID() AND
    tables.name = 'mytable' 

ale zwróć uwagę, że tabela statystyk użytkowania jest czyszczona po ponownym uruchomieniu serwera, odłączeniu itp. Dlatego konieczne będzie skonfigurowanie zadania w celu gromadzenia danych. Trochę hacka, wiem.

Jednym ze sposobów, których użyłem w przeszłości, było ustanowienie listy kandydatów tabel do usunięcia, a następnie zmiana ich nazw i poszukiwanie błędów.

Jak ustaliłem listę:

1. zobacz, które tabele nie są używane w bieżących procedurach przechowywanych, wyzwalaczach i funkcjach

2. puste tabele (zero rekordów);

3. tabele bez odnośników (tabele, które nie mają żadnych relacji);

4. zobacz, które tabele nie były używane od momentu uruchomienia serwera DB (DMV)

Po zbudowaniu listy w pliku tekstowym stworzyłem skrypt wsadowy, który parsowałby nasze pliki .cs (mamy tylko projekty .net) z lokalnego mapowanego folderu kontroli wersji i sprawdzał, czy te tabele są używane w plikach .cs ( nie powinno się zdarzyć, ale hej ... miałem niespodzianki). Jeśli nie, to jasne, jeśli tak, to tworzymy listę i dajemy programistom sprawdzenie, czy ten moduł jest nadal w użyciu.

Krótko mówiąc, poprzedni goście mają rację, nie ma srebrnej kuli.

Zasady, które wdrażam w mojej firmie, to umieszczenie wszystkiego, co dotyczy SQL Servera, pod kontrolą źródła, w centralnej lokalizacji.

• projekty asp.net
• Projekty SSRS
• Projekty SSIS
• Nawet skryptuję wszystkie obiekty bazy danych do pewnego rodzaju repozytorium.

Nie mam go jeszcze skonfigurowanego, ale ostatecznie chcę wdrożyć jakiś mechanizm wyszukiwania indeksowego / centralnego, którego mógłbym użyć do wyszukiwania określonych tabel, sproców itp. W rzeczywistości jesteśmy nowym sklepem SQL Server - konwersja z FoxPro . Stare obiekty SQL nie stanowią jeszcze większego problemu, ale planuję na przyszłość.

Problem, który widzę w podejściu polegającym na zmianie nazwy / śledzenia, polega na tym, że niektóre rzeczy działają tylko raz w roku, a nawet co roku. Nie wspominając o różnych rzeczach ad hoc, o które ludzie proszą cię o napisanie, a następnie o kolejne miesiące lub lata później.

Istnieje wiele narzędzi i technik używanych w śledzeniu zależności, w tym:

Narzędzia, które znam:

• Przeglądarka zależności programu SQL Server (ale może mieć problemy, jeśli sp przy użyciu tabeli został utworzony przed utworzeniem tabeli)
• Redgate SQL Dependency Tracker (przez odpowiedź @Eric Humphrey)
• Resharper (narzędzie .net, którego można użyć do przeglądania ścieżek wywoływania, myślę, że można go użyć do śledzenia, gdzie używane są kluczowe połączenia SQL)

Metody

• Kod wyszukuje użycie obiektów SQL (replikuje niektóre z powyższych narzędzi)

• Spójrz na statystyki użytkowania (tj .: kiedy ostatnio wywołano obiekt SQL), używam poniższego SQL:

  SELECT 
      last_execution_time,   
      (SELECT TOP 1 
          SUBSTRING(s2.text,statement_start_offset / 2+1 , 
              ((CASE WHEN statement_end_offset = -1 THEN 
                  (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
              ELSE statement_end_offset END) - statement_start_offset) / 2+1)
      )  AS sql_statement,
      execution_count
  FROM sys.dm_exec_query_stats AS s1 
  CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
  WHERE 
      s2.text like '%[OBJECT NAME]%' 
      and last_execution_time > [DATE YOU CARE ABOUT]
  ORDER BY last_execution_time desc
  

Uwaga : Tabela statystyk użytkowania jest czyszczona po ponownym uruchomieniu serwera, odłączeniu itp. Dlatego konieczne będzie skonfigurowanie zadania w celu gromadzenia danych. Trochę hacka, wiem. (z @Miles D)

Techniki

• Wyszukaj ostatnie użycie (patrz powyższe statystyki użytkowania)
• Wyszukaj, gdzie jest używany (patrz narzędzia)
• Przejrzyj użycie kodu z programistami (przez @MrDenny)
• Zmień nazwę obiektu (tj. Post / prefiks z _toBeDropped) i szukaj błędów
• Zmień uprawnienia i uważaj na błędy
• Upuść przedmiot i módl się

Kilka lat temu próbowałem zbudować narzędzie do sprawdzania podobnych rzeczy. Odpowiedź TL; DR jest taka, że ​​nie mogłem zrobić z dostępnymi zasobami w tym czasie.

Gdzie jest używana ta kolumna?

To pytanie staje się bardziej skomplikowane, gdy zdajesz sobie sprawę, że z zapytania, select *w którym rezyduje kolumna, korzysta wiele zapytań, widoków i procedur przechowywanych . Następnie musisz spojrzeć na programy, które używają tych wyników - więc potrzebujesz skanera / indeksatora / parsera zdolny do odczytu kodu źródłowego, który może być C #, Delphi, Java, VB, ASP (klasyczny) i tak dalej, aby po prostu spróbować wyśledzić każde odwołanie do tej kolumny. Następnie musisz przeanalizować te programy, aby spróbować ustalić, czy ten kod jest w ogóle wywoływany.

Nie będzie obsługiwał odwołań SQL, ale możesz chcieć sprawdzić śledzenie zależności SQL od Redgate . To miłe narzędzie do wizualizacji.

To nie jest tak naprawdę odpowiedź na twoje pytanie, ale myślę, że należy wspomnieć: jest to jeden z powodów, dla których wszystkie systemy poza bazą danych powinny komunikować się za pośrednictwem widoków i sproków . Masz skrypty kompilacji dla nich w przeszukiwalnych plikach .sql, dzięki czemu możesz łatwo sprawdzić, czy konkretna tabela lub kolumna jest używana zewnętrznie.

Oczywiście SSIS zwykle łączy się bezpośrednio z tabelami, więc prawdopodobnie nie jest to zbytnio pomocne w tej chwili. Ale kiedy programiści łączą się z Twoją bazą danych i narzekają, że musisz czekać na Ciebie (lub kogokolwiek, kto służy jako DBA), aby utworzyć potrzebne widoki i sproki, możesz im powiedzieć: „Każda tabela lub kolumna może zostać usunięta lub zmieniona jej nazwa.” m jestem tylko zobowiązany do informowania Cię o zmianach w widokach i sprockach. ” Muszą tylko przeprowadzić testy regresji dla tych konkretnych zmian.

TSQL można użyć następujących sys.dm_sql_referencing_entities lub sys.sql_expression_dependencies

Alternatywnie narzędzia takie jak SQL Negotiator Pro, Redgate itp. Mogą wygenerować to wizualnie za pomocą GUI