13 Aralık 2013 Cuma

Veritabanınızı versiyonlayın (DDL Event)


 Merhaba, 
Bu makalede Sql Server üzerinde yapılan sp view yada function create drop vb. gibi aksiyonların loglarını tutan bir trigger yazacağız.

Problem 
Development aşamasında kodlarımızı versiyonlamaya ihtiyaç duyarız. Kod tarafında bu işin hakkını veren pek çok uygulama mevcut. Ancak iş veritabanına geldiğinde bu seçenekler oldukça azalıyor. Yanlışlıkla çalıştırdığımız bir sp yi eski haline getirmek bu tür bir uygulama kullanmıyorsak çok zor.

Çözüm
Aslında bu tür bir uygulama olmadan da bu bilgileri loglayabiliriz. Şöyle bir senaryo düşünelim. Örneğin her
alter,create, drop aksiyonunda hangi data üzerinde işlem yaptıysak t-sql kodlarımızın bir kopyasını saklayalım. İhtiyacımız olduğunda istediğimiz zamandaki kodlarımızı görüntüleyebilelim.

Bunun için bir trigger e ihtiyacımız olacak. Ama öncelikle loglarımızın atılacağı tableyi hazırlayalım

CREATE TABLE [dbo].[DDLEvents](
[EventDate] [datetime] NOT NULL,
[EventType] [nvarchar](64) NULL,
[EventDDL] [nvarchar](max) NULL,
[EventXML] [xml] NULL,
[DatabaseName] [nvarchar](255) NULL,
[SchemaName] [nvarchar](255) NULL,
[ObjectName] [nvarchar](255) NULL,
[HostName] [varchar](64) NULL,
[IPAddress] [varchar](32) NULL,
[ProgramName] [nvarchar](255) NULL,
[LoginName] [nvarchar](255) NULL
) ON [PRIMARY] 
GO 
SET ANSI_PADDING OFF
GO 
ALTER TABLE [dbo].[DDLEvents] ADD  DEFAULT (getdate()) FOR [EventDate]
GO
-------------------------------------------------------------------------------
Şimdi de Trigger'imizi yazalım. 
-----------------------------------------------------------------------------

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDLTrigger]
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,CREATE_FUNCTION,CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData XML = EVENTDATA();
    DECLARE 
        @ip VARCHAR(32) =
        (
            SELECT client_net_address
                FROM sys.dm_exec_connections
                WHERE session_id = @@SPID
        );
    INSERT INTO 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(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END


GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [DDLTrigger] ON DATABASE
GO

ENABLE TRIGGER [DDLTrigger] ON DATABASE
GO

Artık tüm create drop alter hareketlerimiz oluşturduğumuz table ye yeni bir insert olarak eklenecektir. Herkese iyi çalışmalar.