Предисловие
Случалось ли Вам когда-нибудь сталкиваться с тем, что нужно очень быстро внести изменения в хранимую процедуру или в представление, или еще куда? У меня такое происходит нередко. А в период внедрения вообще постоянно. И здесь боюсь системы контроля версиями не всегда смогут помочь. Но как же понять что поменялось? Как поменялось? Что было до изменения? Когда поменялось?
Данная статья не является руководством. В ней я хотел просто показать возможные решения данной проблемы.
Буду рад, если предложат альтернативные решения.
Решение
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, что позволяет не просто узнать что и когда и на что изменилось, а также быстро откатить эти изменения. В основном данный механизм выручает на этапе внедрений, когда допускаются больше всего ошибок, и копии баз данных внедренцами (в том числе и мной) на столько расходятся, что нужно проанализировать что и когда и зачем было изменено. Вот как раз зачем можно спросить у конкретного внедренца (и у самого себя), получив историю изменений, т. к. в нашей голове при бурной деятельности, увы не все запоминается.
ссылка на оригинал статьи https://habrahabr.ru/post/314630/
Добавить комментарий