SQL server skjema revisjon?

stemmer
2

Vi har en Enterprise database SQL Server 2008 med to forskjellige skjemaer, en låst en som vi opprettholder og en åpen en som vi tillater utenfor utviklingsteam for å legge til og endre til eget behov. Vanligvis er dette funker OK for oss, men en bestemt gruppe liker å virkelig muck den opp og den preger alle andre. Så 2 spørsmål:

  1. I ettertid jeg skulle ønske vi hadde satt opp noe robust fra begynnelsen, men vi gjorde ikke, bare standard installasjon. Det ville være fint å kunne se hva som er gjort til skjemaet så langt, selv om det er så enkelt som 'Bruker XYZ endret Prosedyre ABC på 07/12/2012 ved 09:00'. Er det noe innebygd i SQL Server og aktivert som standard som sporer dette at vi kan utnytte, og i så fall hvor / hvordan?
  2. Så langt som en langsiktig løsning går, hva ville du anbefale for dette? Jeg har lest opp på DDL trigger litt og som virker som en lovende alternativ. Hvis du har brukt denne tilnærmingen kan du dele litt med hvordan det fungerte og hva du kan gjøre med det?

Takk skal du ha

Publisert på 13/07/2012 klokken 12:14
kilden bruker
På andre språk...                            


4 svar

stemmer
2

Jeg har fått et system som bruker en DDL trigger for akkurat denne type ting. Det fungerer godt nok for mine behov. Det ble opprinnelig utviklet på SQL Server 2005, og bor nå på en SQL Server 2008R2 system. Det er lik den som er beskrevet av koblingen i Aaron Bertrand kommentar.

Lag en tabell som ligner på denne.

CREATE TABLE [Audit].[SchemaLog](
    [SchemaLogID] [int] IDENTITY(1,1) NOT NULL,
    [PostTimeUtc] [datetime] NOT NULL,
    [DatabaseUser] [nvarchar](128) NOT NULL,
    [Event] [nvarchar](128) NOT NULL,
    [Schema] [nvarchar](128) NULL,
    [Object] [nvarchar](128) NULL,
    [TSQL] [nvarchar](max) NOT NULL,
    [XmlEvent] [xml] NOT NULL,
 CONSTRAINT [PK_SchemaLog_1] PRIMARY KEY CLUSTERED 
(
    [SchemaLogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Sørg for at alle har innleggs tillatelser på bordet og deretter opprette en DDL trigger som ligner på dette.

CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE  FOR DDL_DATABASE_LEVEL_EVENTS AS  
BEGIN     
    SET NOCOUNT ON;     
    DECLARE @data XML;     
    DECLARE @schema sysname;     
    DECLARE @object sysname;     
    DECLARE @eventType sysname;     
    SET @data = EVENTDATA();     
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');     
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');     
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')      
    IF @object IS NOT NULL         
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;     
    ELSE         
        PRINT '  ' + @eventType + ' - ' + @schema;     

    IF @eventType IS NULL         
        PRINT CONVERT(nvarchar(max), @data);     

    INSERT [Audit].[SchemaLog]          (         
        [PostTimeUtc]
    ,          [DatabaseUser]
    ,          [Event]
    ,          [Schema]
    ,          [Object]
    ,          [TSQL]
    ,          [XmlEvent]         )      
    VALUES          (         
        GETUTCDATE()
    ,          CONVERT(sysname, CURRENT_USER)
    ,          @eventType
    ,          CONVERT(sysname, @schema)
    ,          CONVERT(sysname, @object)
    ,          @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
    ,          @data         ); 

END;
Svarte 13/07/2012 kl. 14:48
kilden bruker

stemmer
1

De Redgate alternativene er som følger.

1) Den enkleste måten du kan starte revisjon skjemaendringer er ved å installere DLM Dashboard . Dette er et gratis verktøy som varsler og logger alle endringer med DDL triggere, og vil inneholde den informasjonen du ber om.

2) Som allerede nevnt av Andy Davies, den riktige måten å gjøre dette på er å starte kilde kontrollere skjemaet på samme måte som du gjør for applikasjonskode. Når du har gjort dette, kan du øke din database livssyklusadministrasjon modenhet ved å inkludere din database i kontinuerlig integrasjon og slipp forvaltningspraksis.

Svarte 04/08/2015 kl. 09:41
kilden bruker

stemmer
0

For det andre spørsmålet kan du vurdere triggere som et alternativ. I det følgende eksempel er informasjon om hendelsen som avfyrte avtrekkeren tatt ved bruk av SQL Server EVENTDATA () -funksjonen. SQL-skriptet skaper DDL trigger som fanger CREATE, ALTER og DROP hendelser på databasenivå (selv, triggere kan opprettes på servernivå for å fange opp hendelser for alle databaser på serveren, PÅ-server bør brukes, i stedet for PÅ DATABASE)

CREATE TRIGGER Audit_DDL ON DATABASE
FOR CREATE_TABLE , ALTER_TABLE , DROP_TABLE
AS
DECLARE
@event xml;
SET @event = EVENTDATA(
                  );
INSERT INTO Audit_DDL_Events
VALUES( REPLACE( CONVERT( varchar( 50
                             ) , @event.query( 'data(/EVENT_INSTANCE/PostTime)'
                                             )
                    ) , 'T' , ' '
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/LoginName)'
                                    )
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/UserName)'
                                    )
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/DatabaseName)'
                                    )
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/SchemaName)'
                                    )
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/ObjectName)'
                                    )
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/ObjectType)'
                                    )
           ) , 
    CONVERT( varchar( max
                    ) , @event.query( 'data(/EVENT_INSTANCE/TSQLCommand/CommandText)'
                                    )
           )
  );

En passende lagringstabellen for revisjon av data fra EVENTDATA XML må skapes også:

CREATE TABLE Audit_DDL_Events( DDL_Event_Time datetime , 
                           DDL_Login_Name varchar( 150
                                                 ) , 
                           DDL_User_Name varchar( 150
                                                ) , 
                           DDL_Database_Name varchar( 150
                                                    ) , 
                           DDL_Schema_Name varchar( 150
                                                  ) , 
                           DDL_Object_Name varchar( 150
                                                  ) , 
                           DDL_Object_Type varchar( 150
                                                  ) , 
                           DDL_Command varchar( max
                                              )
                         );
Svarte 24/02/2014 kl. 18:31
kilden bruker

stemmer
0

Du kunne se på å sette databasen i henhold til kildekontroll. Kanskje gjør hver utenfor teamet ta en gren eller gaffel av databasen. Dette gir deg auditting av endringer via inger og muligheten til å velge hvilke endringer å fusjonere inn og / eller gjennomgang / redigere disse endringene før sammenslåing i.

Se på http://www.red-gate.com/products/sql-development/sql-source-control/ for et produkt som er tilgjengelig.

Kombinert med en løsning som Github eller Bitbucket som åpner opp kildekontroll repositories mer direkte til eksterne bidragsytere via internett.

Svarte 13/07/2012 kl. 13:22
kilden bruker

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more