DDL Trigger

DDL Trigger ابزاری برای آگاهی از تغییرات ساختاری

شاید برای شما هم اتفاق افتاده باشه، توی تیمتون یک نفر ساختار جداول رو روی سرور Production تغییر میده بدون اینکه به شما اطلاع بده و بیزنس هایی که روی اون جداول داشتید با خطا مواجه میشه و بعد از مدتی آزمون و خطا به این نتیجه می‌رسید که ساختار جدول مورد نظر تغییر کرده، یا اینکه خودمون یا هر کس دیگه‌ای با اعتماد به نفسی مثال زدنی یک SP رو بدون تهیه بکاپ از ساختار قبلی تغییر می‌دیم و به قول معروف میایم ابروش رو درست کنیم میزنیم چشمش هم کور می‌کنیم. توی این مقاله قصد داریم با بررسی نحوه استفاده از DDL Trigger ها  از غافلگیر شدن تا حدی جلوگیری کنیم و یا بتونیم بعد از تغییر ساختار به ساختار قبلی نیز دست پیدا کنیم.

اجازه بدید فرض کنیم به عنوان یک مدیر بانک اطلاعاتی همه چیز طبق استانداردهای جهانی یک تیم برنامه نویسی کناره هم چیده شده! به نظرتون ی همچین تیمی به چه شکل خواهد بود؟

  • دسترسی‌های sa تنها در اختیار مدیر یا مدیران بانک اطلاعاتی قرار داره و هر کسی با توجه به میزان نیازش به بانک اطلاعاتی براش دسترسی مناسب تعریف شده
  • یک مدیر بانک اطلاعاتی خوب هر بار که اقدام به زدن دکمه F5 می‌کنه، قبلش تمامی جوانب این اقدام رو سنجیده و دقیقاً میدونه که با زدن این دکمه چه کدی اجرا میشه و تاثیرش در کجای سیستم به چه شکلی خواهد بود
  • تمام تغییرات  پس از اعمال برروی بانک اطلاعاتی با مکانیزم های Source Control داره Capture میشه و یک سابقه کاملاً واضح و مشخص از تغییرات بانک اطلاعاتی در طول زمان داریم
  • طی فرایندی اقدام به تدوین یک Recovery Plan برای زمان های مواجه شدن با Disaster کردیم و به تمام جوانب Full Backup و Differential Backup و Point in Time Recovery با Log Backup توجه کردیم

 

اما اجازه بدید با دنیای واقعی خودمون روبرو بشیم، خیلی به ندرت پیش میاد که ی همچین شرایط خوبی توی یک تیم نرم‌افزاری برقرار باشه (حداقل توی ایران کم پیدا میشه این شرایط نه که کلاً نباشه) توی دنیا واقعی زیاد پیش میاد شرایطی که خودمون یا ی نفر دیگه بعد از اجرای یک دستور روی بانک اطلاعاتی دست به سر بگیریم و بگیم “اوپس…! حالا چطور درستش کنم؟” یا یک سناریو دیگه که بارها دیدم که شخصی یک SP رو بارها طی بکاپ های مختلف تغییر میده و آخرش یادش نیست که نسخه قبلی، قبل از تغییر این SP چی بود؟ نمیتونه بکاپ هم ریستور کنه، چون هنوز از ورژن قبلیش بکاپ گرفته نشده که بتونه از اون برشگردونه و البته با بستن Query Window بدون ذخیره راه رو برای CTRL+Z که تو این مواقع  نجاتمون میده هم بسته.

خب برای ی همچین شرایطی چه کاری میتونیم انجام بدیم؟ راه‌های زیادی برای جلوگیری از بوجود اومدن ی همچین مشکلاتی وجود داره:

  • دسترسی سرور رو به هر کسی ندیم برای کار روی بخش های حساس دیتابیس
  • یک مکانیزم Source Control برای نگهداری تاریخچه تغییرات ساختاری دیتابیس پیاده سازی کنیم
  • حتی اگر مجبور شدیم دسترسی به افراد مختلف برای تغییرات بدیم با تدوین یک Deployment Process سعی کنیم از خطاهای احتمالی انسانی جلوگیری کنیم

تمامی مواردی که عنوان شد ممکنه نیازمند هماهنگی کل تیم باشه و به نوعی پیروی از موارد عنوان شده نیازمند نوعی تغییر رفتار در کل تیم و پیروی از روش جدیده، اما اگر بخواهیم از بروز مشکلاتی از این دست با کمترین انرژی و در کوتاهترین زمان تا حدی جلوگیری کنیم، DDL Trigger به عنوان یک انتخاب کوتاه مدت میتونه گزینه مناسبی باشه چرا که هم پیاده سازی اون خیلی راحته و هم مدیریت اون نیازمند انرژی خاصی نیست، روش کار هم بدین صورته که در ابتدا یک Snapshot از وضعیت کنونی Objectهای دیتابیس تهیه و در جدولی ذخیره می‌کنیم و بعد از اون هرگونه تغییری توی اون حوزه توسط DDL Triggerها به صورت خودکار Capture و در جای مناسب ذخیره خواهد شد. با این روش شما میتونید وضعیت یک Object خاص رو در بازه های زمانی مختلف بررسی و با همدیگه مورد مقایسه قرار بدید(البته به شرط اینکه اون Object به صورت Encrypt نباشه).

پیاده‌سازی DDL Trigger

اجازه بدید کار رو مثل همیشه با ساختن یک دیتابیس تست شروع کنیم، توی قطعه کد زیر یک دیتابیس با نام TestDDL ایجاد می‌کنیم:

IF DB_ID('TestDDL') > 0
BEGIN
   USE master 
   ALTER DATABASE TestDDL SET SINGLE_USER	WITH ROLLBACK IMMEDIATE
   DROP DATABASE TestDDL
END	

CREATE DATABASE TestDDL
GO	

برای اینکه همه‌چیز خوب و ساده پیش بره اجازه می‌خوام که مثالمون رو تنها روی مبحث Stored Procedure ها جلو ببریم و همچنین اشاره ای به پارامترهای وابسته به دیگر اشیاء هم خواهم داشت. برای شروع توی دیتابیس TestDDL که ایجاد شد، یک SP ساده که تنها لیستی از دیتابیس ها رو برمی‌گردونه  ایجاد می‌کنیم:

USE TestDDL
GO	
CREATE PROCEDURE dbo.SelectDatabases
AS
    SET NOCOUNT ON;
    SELECT * FROM sys.databases
GO

علاوه بر ساختار اشیاء بانک اطلاعاتی که قصد لاگ کردن آن را داریم، میتونیم موارد دیگه ای هم در کنار اون ذخیره کنیم که به واضح‌تر بودن لاگ در آن کمک زیادی می‌کنه،مثلاً :

  • نام دیتابیس
  • Schema و نام اون شیء خاص
  • این که اون دستور با چه لاگینی اجرا شده (شناسایی کاربر)
  • اینکه از طریق چه کلاینتی به SQL وصل شده و آدرس IP اون چی بوده

خب برای اینکه داده های مورد نظر رو بتونیم لاگ کنیم نیاز به یک جدول داریم که ساختار مورد نیاز ما را پوشش بده:

USE TestDDL
GO	

CREATE TABLE dbo.DDLEvents
(
    EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   EventType NVARCHAR(64),
   EventDDL NVARCHAR(MAX),
   EventXML XML,
   DatabaseName NVARCHAR(255),
   SchemaName NVARCHAR(255),
   ObjectName NVARCHAR(255),
   HostName VARCHAR(64),
   IPAddress VARCHAR(32),
   ProgramName NVARCHAR(255),
   LoginName NVARCHAR(255)
)
GO

ممکنه الان بگید چرا به جای نام شیء و نامSchema یک فیلد ساده Object-id که از مشکل تغییر نام هم جلوگیری کنه استفاده نکردم برای این جدول، حرفتون کاملاً درسته اما معمولاً باید طبق خواسته‌ها و نیازهاتون یک سیستم رو دیزاین کنید، توی این مورد خاص هم من حجم ذخیره سازی بیشتر رو در مقابل انعطاف در این مورد که بتونم براحتی از روی داده‌های جدول مورد نظر دستوراتم رو به شکل داینامیک تولید کنم به جون خریدم. به علاوه اینکه همونطور که قبلاً هم اشاره کردم این جدول یک راه‌حل کوتاه مدت هست که بهتره به صورت دوره‌ای داده‌هایی که قدیمی هستند از جدول حذف شوند.

ذخیره یک Snapshot از داده‌های موجود سیستم

بعد از ساخت جدول برای نگهداری لاگ‌ها بهتره داده های فعلی و موجود سیستم رو به عنوان ورژن اولیه اون object به جدول مورد نظرمون اضافه کنیم، توی این اسکریپت من فرض رو بر این گرفتم که لاگین سازنده داده‌های اولیه sa بوده که شما میتونید تغییرش بدید

USE TestDDL
GO	

INSERT INTO dbo.DDLEvents
        ( EventType ,EventDDL ,DatabaseName ,SchemaName ,ObjectName,LoginName)
        SELECT 'CREATE PROCEDURE', OBJECT_DEFINITION([object_id]),DB_NAME(),OBJECT_SCHEMA_NAME([object_id]),OBJECT_NAME([object_id]),'sa'
      FROM sys.procedures;

و سپس لیستی از وضعیت اولیه جدول مورد نظرمون می‌گیریم

SELECT * FROM dbo.DDLEvents

جدول با مقادیر اولیه

 

 

 

همونطور که مشاهده می‌کنید تنها یک SP در این دیتابیس موجود بوده است.

برای ساخت یک DDL Trigger از قطعه کد زیر استفاده می‌کنیم:

USE TestDDL
GO	

CREATE TRIGGER	DDLTrigger_Sample 
ON DATABASE
FOR CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE
AS 
BEGIN
   SET NOCOUNT ON;
   DECLARE @EventData XML=EVENTDATA();

   DECLARE	@IPAddress VARCHAR(32) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id=@@SPID);

   INSERT INTO dbo.DDLEvents
           ( EventType ,EventDDL ,EventXML ,DatabaseName ,SchemaName ,ObjectName ,HostName ,IPAddress ,ProgramName ,LoginName)
      SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(100)'),
            @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','NVARCHAR(MAX)'),
            @EventData,
            DB_NAME(),
            @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','NVARCHAR(255)'),
            @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(255)'),
            HOST_NAME(),
            @IPAddress,
            PROGRAM_NAME(),
            SUSER_SNAME();

END 
GO

در قطعه کد بالا برای ساخت یک DDL Trigger دقیقاً مشابه ساخت یک Trigger معمولی عمل می‌کنیم، تنها وجه تمایز برای DDL Trigger بخش بعد از FOR قرار داره که میتونیم یکی از موارد این لیست رو انتخاب کنیم، در ادامه برای دریافت اطلاعات مرتبط با اسکریپت اجرا شده برای تغییر آن Stored Procedure از فانکشن ()EVENTDATA استفاده کردیم، که خروجی این فانکشن یک رشته XML می‌باشد و سپس از طریق همون EventData به اجزای مختلف اسکریپت اجرا شده دسترسی پیدا کردیم.

فعال و غیر فعال کردن DDL Trigger

DDL Triggerها هم همانند دیگر Triggerها به محض ایجاد آنها فعال خواهند بود و برای غیر فعال کردن یک DDL Trigger از کد زیر استفاده می‌کنیم

USE TestDDL
GO	
DISABLE TRIGGER DDLTrigger_Sample ON DATABASE;

و همچنین برای فعالسازی مجدد آن

USE TestDDL
GO	
ENABLE TRIGGER DDLTrigger_Sample ON DATABASE;

خب فکر کنم توضیح تا همین جا برای نحوه پیاده سازیش کافی باشه، بریم سراغ تست این مکانیزم و صحت عملکرد اون رو بسنجیم، توی قطعه کد زیر همون SP که ایجاد کرده بودیم رو ویرایش می‌کنیم و به جای لیست دیتابیس‌ها اینبار لیست جداول رو در خروجی نمایش میدیم

USE [TestDDL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SelectDatabases]
AS
    SET NOCOUNT ON;
    SELECT * FROM sys.tables

GO

با اجرای این اسکریپت SP ما تغییر میکنه و برای مشاهده تاثیرش روی جدول مقصد DDL Trigger که DDLEvents نامگذاری شده بود کد زیر رو اجرا می‌کنیم

SELECT * FROM dbo.DDLEvents

و خروجی این دستور DDL Trigger Result

 

همونطور که توی تصویر مشاهده می‌کنید اطلاعات کاملی از دستور اجرا شده به همراه زمان آن و همچنین نحوه دسترسی کاربر به دیتا بیس که در این مثال به طور مستقیم روی سرور و با دسترسیWindows Authentication انجام شده در جدول DDLEvents ذخیره شده. اگر بخواهیم تغییرات خاصی را دنبال کنیم تنها کافیست با یک شرط مثلا بروی فیلد EventType تنها تغییرات مرتبط با ALTER_PROCEDURE را مشاهده کنید. حالا اگر بخواهیم پا را فراتر گذاشته و مقایسه ای بین نسخه های مختلف یک Procedure داشته باشیم، میتوان از CTE زیر استفاده کرد:

;WITH [Events] AS
(
    SELECT
        EventDate,
        DatabaseName,
        SchemaName,
        ObjectName,
        EventDDL,
        rnLatest = ROW_NUMBER() OVER 
        (
            PARTITION BY DatabaseName, SchemaName, ObjectName
            ORDER BY     EventDate DESC
        ),
        rnEarliest = ROW_NUMBER() OVER
        (
            PARTITION BY DatabaseName, SchemaName, ObjectName
            ORDER BY     EventDate
        )        
    FROM
        TestDDL.dbo.DDLEvents
)
SELECT
    Original.DatabaseName,
    Original.SchemaName,
    Original.ObjectName,
    OriginalCode = Original.EventDDL,
    NewestCode   = COALESCE(Newest.EventDDL, ''),
    LastModified = COALESCE(Newest.EventDate, Original.EventDate)
FROM
    [Events] AS Original
LEFT OUTER JOIN
    [Events] AS Newest
    ON  Original.DatabaseName = Newest.DatabaseName
    AND Original.SchemaName   = Newest.SchemaName
    AND Original.ObjectName   = Newest.ObjectName
    AND Newest.rnEarliest = Original.rnLatest
    AND Newest.rnLatest = Original.rnEarliest
    AND Newest.rnEarliest > 1
WHERE
    Original.rnEarliest = 1;

توسط این کوئری تفاوت بین نسخه‌های اولیه و آخرین نسخه این object رو مشاهده می‌کنیم.

یک سری موارد هم وجود داره که توی این روش قابل Track کردن نیست، مثلاً

  • اگر یک SP از Schema فعلی به یک Schema دیگه منتقل بشه توی این حالت Capture نخواهد شد، برای همچین حالتی هم میتونیم DDL Trigger مورد نظر رو تغییر بدیم و اینبار ALTER_SCHEMA هم به دستوراتش اضافه کنیم
USE YourDatabase;
GO


ALTER TRIGGER DDLTrigger_Sample
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
        ALTER_SCHEMA
AS
BEGIN
    -- ...
  •  یکی دیگه از حالت‌هایی که تغییرات capture نخواهد شد، زمانیه که نام یک SP تغییر داده میشه. این حالت رو میشه با اضافه کردن RENAME به DDL Trigger مورد نظر پوشش داد، نکته قابل توجه در مورد این حات اینه که توی SQL Server 2005 این امکان درون DDL Trigger ها وجود نداشت و میشه گفت که توی SQL Server 2005 نمیتونیم تمام Action های ممکن رو روی یک SP لاگ کنیم
USE YourDatabase;
GO


ALTER TRIGGER DDLTrigger_Sample
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
        ALTER_SCHEMA, RENAME
AS
BEGIN
    -- ...

 

ملاحظات دیگری در مورد DDL Trigger‌ها

  • سعی کنید توسط یک Job مکانیزم Cleanup رو هم برای این روش پیاده‌سازی کنید که هر روز داده‌های با عمر بیش از n روز رو حذف بکنه
  • برای بررسی صحت عملکرد DDL Trigger بهتره تاریخ ایجاد لاگ رو با فیلد modify_date درون sys.procedures چک کنید. البته این مورد برای procedure‌هایی که حذف کردید کاربرد نخواهد داشت
  • ممکنه از بایت یک سری جنبه‌ها با مشکلات امنیتی مواجه بشید، برای مثال  DDL Trigger ها از دید کاربران دیگه‌ای که به دیتابیس دسترسی دارند مخفی نخواهد بود و همچنین عملیاتی که درون DDL Triggerها داره انجام میشه درون Execution Plan کوئری‌ها نمایش داده میشه، اگر میخواهید این مورد هم اتفاق نیافته میتونید DDL Trigger خودتون رو Encrypt کنید
ALTER TRIGGER DDLTrigger_Sample
    ON DATABASE
    WITH ENCRYPTION
    FOR -- ...

در این حالت حتی اگر کاربران اقدام به گرفتن اسکریپت از DDL Trigger کنن با خروجی زیر مواجه میشن

TITLE: Microsoft SQL Server Management Studio
------------------------------
Script failed for DatabaseDdlTrigger 'DDLTrigger_Sample'.
Property TextHeader is not available for DatabaseDdlTrigger
'[DDLTrigger_Sample]'. This property may not exist for this
object, or may not be retrievable due to insufficient access
rights.  The text is encrypted.
...

با این حال کاربرانی که دسترسی لازمه رو دارن میتونن DDL Trigger شما رو غیرفعال کنن. با موارد عنوان شده امیدوارم بتونید با مشکلات احتمالی که به هنگام تغییر ساختار Object‌های دیتابیس مواجه میشید مقابله کرده و خواب بی استرس‌تری رو به عنوان یک مدیر بانک اطلاعاتی داشته باشید.

شاد باشید 🙂

دانلود اسکریپت

برنامه نویس و توسعه دهنده و علاقه مند به هر چیزی که با دیتا در ارتباط باشه
SQL Server , Big Data, Hadoop, R,Statistical Programming

Categories: SQL Server

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *