Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Building a DDL Audit Solution using SQL Server 2005 PowerPoint Presentation
Download Presentation
Building a DDL Audit Solution using SQL Server 2005

Building a DDL Audit Solution using SQL Server 2005

173 Vues Download Presentation
Télécharger la présentation

Building a DDL Audit Solution using SQL Server 2005

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

    1. Building a DDL Audit Solution using SQL Server 2005 Jasper Smith, MVP

    2. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Agenda DDL Audit Mechanisms Default Trace DDL Triggers Event Notifications Event Notifications in Detail Setting up Remote Event Notifications Putting it all together DDL Audit Admin DDL Audit Reports DDL Audit Viewer

    3. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Why ? Simple Audit who did what and when Change management database schema history Change auditing do effects of change match change description Troubleshooting performance issues can occur when schema or stored procedures are changed

    4. DBA-402S Building a DDL Audit Solution using SQL Server 2005 DDL Audit Mechanisms Default Trace On by default (controlled by sp_configure 'default trace enabled) Stored in the same folder as the SQL Server Error Log Location can be changed by modifying the e start up parameter using the SQL Server Configuration Manager Captures mainly audit type trace events The system function fn_trace_gettable has been enhanced to allow the reading of running server side traces SMO Trace API Microsoft.SqlServer.Management.Trace

    5. DBA-402S Building a DDL Audit Solution using SQL Server 2005 DDL Audit Mechanisms DDL Triggers React to CREATE,ALTER,DROP DDL statements Server or Database scoped Within a DDL trigger you can rollback the DDL that caused it to fire Use the eventdata() function to return data as xml Synchronous

    6. DBA-402S Building a DDL Audit Solution using SQL Server 2005 DDL Audit Mechanisms Event Notifications Leverage Service Broker infrastructure Asynchronous Target local or remote service React to DDL and subset of Trace events Integrate with WMI and SQL Agent alerts

    7. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Service Broker Service Broker provides queuing and reliable messaging as part of the Database Engine Event Notification Service is built in to all databases

    8. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Event Notification Syntax CREATE EVENT NOTIFICATION name ON { SERVER | DATABASE | QUEUE } [ WITH FAN_IN ] FOR { event_type | event_group } [ ,...n ] TO SERVICE broker_service { 'broker_instance_specifier' | 'current database' }

    9. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Event Groups DDL_DATABASE_LEVEL_EVENTS DDL_TABLE_VIEW_EVENTS DDL_TABLE_EVENTS (CREATE_TABLE,ALTER_TABLE,DROP_TABLE) DDL_VIEW_EVENTS (CREATE_VIEW,ALTER_VIEW,DROP_VIEW) DDL_INDEX_EVENTS (CREATE_INDEX,ALTER_INDEX,DROP_INDEX) DDL_STATISTICS_EVENTS (CREATE_STATISTICS,ALTER_STATISTICS,DROP_STATISTICS)

    10. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Trace Events In addition to the DDL events available in DDL Triggers, Event Notifications also allow a subset of Trace events to be captured Audit_Login Audit_Login_Failed Lock_Deadlock Data_File_Auto_Grow Blocked_Process_Report

    11. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Creating Event Notifications Create a QUEUE Create a SERVICE on a QUEUE Create a ROUTE for the SERVICE Create an EVENT NOTIFICATION to a SERVICE Create a SERVICE PROGRAM to process notification events in the QUEUE

    12. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Demonstration 1 Event Notification Basics

    13. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Remote Event Notifications Endpoint In order for Service Broker messages to pass between instances we need to create a Service Broker Endpoint Define the TCP port and encryption the endpoint will support and who can connect to it CREATE ENDPOINT SSBENDPOINT STATE = STARTED, AS TCP (LISTENER PORT = 4022) FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS, ENCRYPTION = SUPPORTED)

    14. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Remote Event Notifications Routes Need to specify Remote Service Name Remote Broker Instance Remote Address CREATE ROUTE ExpenseRoute WITH SERVICE_NAME = '//Adventure-Works.com/Expenses', BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89C1E315', ADDRESS = 'TCP://SERVER02:1234'

    15. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Remote Event Notifications - Security Can configure full dialog security as described in BOL however it is overkill for DDL Audit and hard to automate (requires certificate management) We will use Windows Authentication based on the SQL Server Service account with no encryption Grant connect on the Service Broker endpoints to the SQL Service accounts (local and remote) to allow communication NOTE guest user must have access to msdb (all object permissions can be denied)

    16. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Remote Event Notifications DMV sys.transmission_queue in msdb holds outbound messages from audited instance. Very useful for troubleshooting sys.conversation_endpoints describes service broker conversations and their states sys.server_event_notifications and sys.server_events hold metadata about event notifications sys.services and sys.routes hold service broker metadata

    17. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Demonstration 2 Remote Event Notifications

    18. DBA-402S Building a DDL Audit Solution using SQL Server 2005 DDL Audit Application Requirements Need a central data warehouse to store events Need a deployment mechanism Need an interface for viewing audit events Need to monitor event queues and health* Need to be able to alert on specific events* Need to be able to filter known common DDL* e.g. index drops/creates during ETL processes

    19. DBA-402S Building a DDL Audit Solution using SQL Server 2005 DDL Audit Application Technologies SQL Server 2005 Event Notifications Reporting Services 2005 Windows Forms 2.0 Windows Forms Report Controls

    20. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Demonstration 3 Putting it all Together

    21. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Additional Resources More on Auditing DDL in SQL 2005 Auditing Events in SQL Server 2005 Slides and Demo Applications on PASS site and also http://www.sqldbatips.com Further questions jas@sqldbatips.com

    22. DBA-402S Building a DDL Audit Solution using SQL Server 2005 Thank you! Thank you for attending this session and the 2006 PASS Community Summit in Seattle