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

Передмова
чи Траплялося Вам коли-небудь стикатися з тим, що потрібно дуже швидко внести зміни в збережену процедуру або подання, або ще куди? У мене таке відбувається нерідко. А в період впровадження взагалі постійно. І тут боюся системи контролю версій не завжди зможуть допомогти. Але як же зрозуміти що змінилося? Як змінилося? Що було до зміни? Коли помінялося?
Дана стаття не є інструкцією. У ній я хотів просто показати можливі шляхи вирішення даної проблеми. Буду радий, якщо запропонують альтернативні рішення.

Рішення
1) Створимо дві таблиці (перша-для кожної з спостережуваних баз даних, друга-по всіх спостережуваних баз даних сервера):
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 OFF ANSI_NULLS
GO

SET OFF QUOTED_IDENTIFIER
GO

ENABLE TRIGGER [SchemaLog] ON DATABASE
GO

звичайно Необхідно налаштувати фільтр, оскільки, наприклад, зміна індексу та оновлення статистики відслідковувати не хочу (але це суб'єктивно-може у Вашому випадку це знадобиться, але не знадобиться щось інше). Також не раджу робити DDL-тригер на весь сервер. Пробував, і повірте стільки зайвої інформації я ніде не бачив, та ще з такою швидкістю зростання. Хоча ні, бачив-дані, що надходять з AIS-приймачів судів. Але в загальному не рекомендую. Краще створити тригер на кожну з спостережуваних баз даних.
Даний тригер доведеться відключати на час складних операцій ініціалізації для реплікацій наприклад. Але потім його можна знову включити.
3) Потім яким-небудь способом зібрати інформацію в єдину таблицю (наприклад, завданням в Агенті 1 раз на добу)
4) Для декількох серверів можна зібрати все в одну таблицю теж якимось чином.
Не забуваємо видаляти дуже старі дані (таким, наприклад, яким більше місяця).
Результат
У даній статті було розглянуто приклад реалізації автоматичного збору даних про зміни схем баз даних в MS SQL Server, що дозволяє не просто дізнатися що і коли і на що змінилося, а також швидко відкотити ці зміни. В основному даний механізм виручає на етапі впроваджень, коли допускаються найбільше помилок, і копії баз даних упроваджувальниками (в тому числі і мною) на стільки розходяться, що потрібно проаналізувати, що і коли і навіщо було змінено. От як раз навіщо можна запитати у конкретного впроваджувача (і самого себе), отримавши історію змін, т. к. в нашій голові при бурхливої діяльності, на жаль не все запам'ятовується.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.