Jak wykryć wszelkie zmiany w bazie danych (DDL i DML)

Istnieje wiele baz danych na serwerze SQL mojego klienta. Te bazy danych są w fazie rozwoju, więc programiści mogą projektować, refaktoryzować, modyfikować dane i tak dalej. Niektóre bazy danych zmieniają się rzadko. Mój klient musi zabezpieczyć je wszystkie (wykonać kopię zapasową) i poświęcić trochę czasu na zarządzanie środowiskiem. (W firmie nie ma stanowiska administratora DB). Po długiej dyskusji klient postanowił zastosować codzienną strategię pełnej kopii zapasowej, ze względu na łatwość przywracania.

Oto podsumowanie sytuacji:

• Liczba baz danych może się zmieniać każdego dnia.
• Kopie zapasowe baz danych, które zostały zmienione (co oznacza, że ​​dane i / lub struktura zostały zmienione) powinny zostać zapisane.
• Bazy danych, które nie zostały zmienione, NIE powinny być archiwizowane.
• Rozwiązanie nie wpłynie na strukturę bazy danych (nie jest to wymóg ograniczony)
• Ten „silnik rezerwowy” powinien działać automatycznie.

Główny problem: jak wykryć, że baza danych została zmieniona. Pierwszą część problemu (zmiany DDL) można rozwiązać za pomocą wyzwalaczy DDL . Ale zmiany danych (zmiany DML) stanowią problem. Niemożliwe jest zastosowanie wyzwalaczy DML do wszystkich tabel we wszystkich bazach danych w celu śledzenia zmian (wydajność, zarządzanie rozszerzonymi obiektami ...). Mechanizm tworzenia kopii zapasowych musi śledzić wszystkie zmiany, aby oznaczyć każdą bazę danych jako gotową do utworzenia kopii zapasowej.

• Zmiana przechwytywania danych jest rozwiązaniem, ale wydaje się zbyt ciężka (wymaga również SQL Server Enterprise Edition).

• Innym sposobem jest śledzenie zmian w pliku bazy danych (rozmiar lub czas ostatniej zmiany), ale nie działa ono poprawnie: baza danych może zmienić swój rozmiar, gdy przekroczy całe zarezerwowane wolne miejsce, a sp_spaceused nie jest rozwiązaniem.

• Śledzenie jest rozwiązaniem, ale powoduje problemy z wydajnością i wymaga dodatkowego zarządzania.

Czy istnieją jakieś rozwiązania do obliczania rzeczywistego rozmiaru użycia bazy danych bez wpływu na inne obiekty zarządzania bazą danych (takie jak statystyki ...)? Przyznaję, że zmiana danych tabeli, która nie zmienia jej rozmiaru, nie wywołałaby (tak sądzę), ale jest lepsza niż nic. Naprawdę szukam bezpośredniego lub pośredniego rozwiązania dla SQL Server 2008.

Dziękujemy za wszelkie uwagi, rozwiązania i przemyślenia.

DODANY:

Oto rozwiązanie (dzięki Marianowi ):

Select
    NextLSN = MAX(fn.[Current LSN])
    ,Databasename = DB_NAME()
 from fn_dblog(NULL,    NULL) fn
     LEFT JOIN sys.allocation_units au
         ON fn.AllocUnitId = au.allocation_unit_id
     LEFT  JOIN sys.partitions p
         ON p.partition_id = au.container_id
     LEFT  JOIN sys.objects so
         ON so.object_id = p.object_id  
    WHERE 
    (
        (Operation IN 
       ('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
            'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT') 
            AND so.is_ms_shipped = 0)
        OR 
        ([Lock Information] like '%ACQUIRE_LOCK_SCH_M OBJECT%')
    )

Jednym z pomysłów byłoby codzienne tworzenie migawki i monitorowanie rozmiaru pliku migawki na dysku za pomocą monitora plików. Migawka zwiększa swój rozmiar tylko wtedy, gdy są tam dodawane dane, więc dobrym pomysłem byłoby znalezienie narzędzia do monitorowania rzeczywistego rozmiaru (zgłaszanego rozmiaru).

Teraz .. Nie użyłem tego, więc nie mogę dać ci technicznych wskazówek :-).

Innym pomysłem byłoby zweryfikowanie dziennika transakcji każdego db (jeśli korzystasz z nich w trybie pełnego odzyskiwania, oczywiście) za pomocą funkcji, którą widziałem na forach (db_fnlog .. lub coś takiego), która odczytuje operacje z dziennika i sprawdź, czy masz jakieś usunięcia / wstawienia / aktualizacje.

Nie są to łatwe rzeczy do zrobienia .. ale mam nadzieję, że okażą się przydatne.

PS: znalazłem artykuł z funkcją odczytu dziennika (przy okazji: fndblog :-): Przeczytaj dziennik transakcji Jensa K. Suessmeyera .

• W przypadku zmian DDL możesz przeczytać Domyślne dane śledzenia .
• W przypadku modyfikacji DML, ponieważ uważasz, że CDC jest nieco ciężki, możesz uruchomić własne lekkie śledzenie po stronie serwera, które śledzi tylko odpowiednie zdarzenia

W przypadku zmian DDL wyzwalacze DDL, ale zmiany DML możesz spróbować użyć 3 różnych opcji

1) Śledzenie zmian 2) CDC (zmiana przechwytywania danych) 3) Funkcja audytu

W celu śledzenia zmian .. można zobaczyć poniższy link http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/

to śledzenie zmian będzie stosowane tylko wtedy, gdy tabela się zmieniła, czy nie ... ale bardzo trudno jest znaleźć, które dane się zmieniły .. jeśli chcesz znaleźć, które dane się zmieniły, możesz przejść do przechwytywania danych Chnage.

Dla Aduit w sqlserver .. możesz sprawdzić poniższy link http://blogs.msdn.com/b/manisblog/archive/2008/07/21/sql-server-2008-auditing.aspx

W przypadku zmian DML można użyć dowolnej z poniższych funkcji kontroli SQL Server:

• Śledzenie zmian w programie SQL Server
• SQL Server Zmiana przechwytywania danych
• Audyt serwera SQL

Każda z nich ma swoje zalety i wady, ale Audytowanie jest najnowszą wersją wprowadzoną przez Microsoft, więc dobrym pomysłem byłoby zbudowanie obecnych i przyszłych rozwiązań z nią związanych.

Należy pamiętać, że tylko funkcja kontroli zapewnia informacje o tym, kto / kiedy / jak

Możesz wykryć wszelkie zmiany ddl za pomocą pliku śledzenia. poniżej znajduje się skrypt, aby uzyskać zmiany.

SELECT 
    te.name AS eventtype
    ,t.loginname
    ,t.spid
    ,t.starttime
    ,t.objectname
    ,t.databasename
    ,t.hostname
    ,t.ntusername
    ,t.ntdomainname
    ,t.clientprocessid
    ,t.applicationname  
FROM sys.fn_trace_gettable
(
    CONVERT
    (VARCHAR(150)
    ,(
        SELECT TOP 1 
            value
        FROM sys.fn_trace_getinfo(NULL)  
        WHERE property = 2
    )),DEFAULT
) T 
INNER JOIN sys.trace_events as te 
    ON t.eventclass = te.trace_event_id 
WHERE eventclass=164

Za pomocą tego skryptu możesz wykryć wszelkie modyfikacje tabeli i procedury składowanej:

SELECT 
    SO.Name
    ,SS.name 
    ,SO.type_desc 
    ,SO.create_date
    ,SO.modify_date 
 FROM sys.objects AS SO
INNER JOIN sys.schemas AS SS 
    ON SS.schema_id = SO.schema_id 
WHERE DATEDIFF(D,modify_date, GETDATE()) < 50
AND TYPE IN ('P','U')