Автосбор данных об изменениях схем баз данных в MS SQL Server

  • Tutorial

Предисловие


Случалось ли Вам когда-нибудь сталкиваться с тем, что нужно очень быстро внести изменения в хранимую процедуру или в представление, или еще куда? У меня такое происходит нередко. А в период внедрения вообще постоянно. И здесь боюсь системы контроля версиями не всегда смогут помочь. Но как же понять что поменялось? Как поменялось? Что было до изменения? Когда поменялось?


Данная статья не является руководством. В ней я хотел просто показать возможные решения данной проблемы. Буду рад, если предложат альтернативные решения.



Решение


1) Создадим две таблицы (первая-для каждой из наблюдаемых баз данных, вторая-по всем наблюдаемым базам данных сервера):


Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[ddl_log](
    [DDL_Log_GUID] [uniqueidentifier] NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DB_Login] [nvarchar](255) NULL,
    [DB_User] [nvarchar](255) NULL,
    [Event] [nvarchar](255) NULL,
    [TSQL] [nvarchar](max) NULL,
 CONSTRAINT [PK_ddl_log] PRIMARY KEY CLUSTERED 
(
    [DDL_Log_GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [srv].[ddl_log] ADD  CONSTRAINT [DF_ddl_log_DDL_Log_GUID]  DEFAULT (newid()) FOR [DDL_Log_GUID]
GO

USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[ddl_log_all](
    [DDL_Log_GUID] [uniqueidentifier] NOT NULL,
    [Server_Name] [nvarchar](255) NOT NULL,
    [DB_Name] [nvarchar](255) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DB_Login] [nvarchar](255) NULL,
    [DB_User] [nvarchar](255) NULL,
    [Event] [nvarchar](255) NULL,
    [TSQL] [nvarchar](max) NULL,
    [InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ddl_log_all] PRIMARY KEY CLUSTERED 
(
    [DDL_Log_GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [srv].[ddl_log_all] ADD  CONSTRAINT [DF_ddl_log_all_DDL_Log_GUID]  DEFAULT (newid()) FOR [DDL_Log_GUID]
GO

ALTER TABLE [srv].[ddl_log_all] ADD  CONSTRAINT [DF_ddl_log_all_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO

2) Создадим DDL-триггер на базу данных, который собирает изменения схемы:


Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [SchemaLog] 
ON DATABASE --ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    DECLARE @data XML
    begin try
    if(CURRENT_USER<>'NT AUTHORITY\NETWORK SERVICE' and SYSTEM_USER<>'NT AUTHORITY\NETWORK SERVICE')
    begin
        SET @data = EVENTDATA();
        INSERT srv.ddl_log(
                    PostTime,
                    DB_Login,
                    DB_User,
                    Event,
                    TSQL
                  ) 
        select 
                    GETUTCDATE(),
                    CONVERT(nvarchar(255), SYSTEM_USER),
                    CONVERT(nvarchar(255), CURRENT_USER), 
                    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)'), 
                    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
        where       @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)') not in('UPDATE_STATISTICS', 'ALTER_INDEX')
                and @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') not like '%Msmerge%'; --не нужно следить за изменения объектов репликации
    end
    end try
    begin catch
    end catch

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [SchemaLog] ON DATABASE
GO

Необходимо, конечно, настроить фильтр, т. к., например, изменение индекса и обновление статистики я отслеживать не хочу (но это субъективно-может, в Вашем случае это понадобится, но не понадобится что-то другое). Также не советую делать DDL-триггер на весь сервер. Пробовал, и поверьте, столько лишней информации я нигде не видел, да еще с такой скоростью роста. Хотя нет, видел-данные, поступающие с AIS-приемников судов. Но в общем не рекомендую. Лучше создать триггер на каждую из наблюдаемых баз данных.
Данный триггер придется отключать на время сложных операций-инициализации для репликаций, например. Но затем его можно вновь включить.


3) Затем каким-либо способом собрать информацию в единую таблицу (например, заданием в Агенте 1 раз в сутки)


4) Для нескольких серверов можно собрать все в одну таблицу тоже каким-либо способом.


Не забываем удалять очень старые данные (например, те, которым больше месяца).


Результат


В данной статье был рассмотрен пример реализации автоматического сбора данных об изменениях схем баз данных в MS SQL Server, что позволяет не просто узнать, что и когда и на что изменилось, а также быстро откатить эти изменения. В основном, данный механизм выручает на этапе внедрений, когда допускаются больше всего ошибок, и когда копии баз данных, созданные внедренцами (в том числе и мной), на столько расходятся, что нужно анализировать, что, когда и зачем было изменено. Причину изменений можно узнать как раз у конкретного внедренца (и у самого себя), получив историю изменений, т. к. в нашей голове при бурной деятельности, увы не все запоминается.

Поделиться публикацией

Комментарии 2

    +1
    Спасибо за новые знания — про триггер на базу не знал…

    Буквально на днях решал схожую задачу.
    Имеется база MSSQL которая периодически изменяется — в основном добавление полей в таблицы и изменение хранимок/триггеров/функций.

    пилю я эту базу уже года 4 и вдруг решил заносить все изменения в SVN при этом не делая лишних движений
    получилась следующая схема:
    в базе есть процедурка которая в возвращает метаданные разбивая по объектам
    по планировщику раз в сутки процедурка запускается и результат ее работы выгружается в файлы (100 таблиц, 50 хранимок, 50 функций)
    по планировщику эти файлы коммитятся в SVN
    в SVN попадают только измененые файлы
    позволяет увидеть модификации и их время.
      0
      У нас примерно также сначала было, но слишком сложно для поддержки

    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

    Самое читаемое