jobgemws Nov 8 2016 at 12:54

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

3 min

4.7K

SQL*Microsoft SQL Server*Database Administration*

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, что позволяет не просто узнать, что и когда и на что изменилось, а также быстро откатить эти изменения. В основном, данный механизм выручает на этапе внедрений, когда допускаются больше всего ошибок, и когда копии баз данных, созданные внедренцами (в том числе и мной), на столько расходятся, что нужно анализировать, что, когда и зачем было изменено. Причину изменений можно узнать как раз у конкретного внедренца (и у самого себя), получив историю изменений, т. к. в нашей голове при бурной деятельности, увы не все запоминается.

Tags:

MS SQL Server

Hubs: