Transcript Slide 1

Auditing Database DDL
Changes with SQLVer
About PASS
The PASS community encompasses everyone who uses the Microsoft SQL Server
or Business Intelligence Platforms. This includes database administrators,
application developers, Business Intelligence professionals, information technology
professionals, executives, consultants, educators, instructors, and students.
The Professional Association for SQL Server (PASS) is the only independent, userrun, not-for-profit association dedicated to the SQL Server community helping its
members Connect, Share, and Learn by:
–
Facilitating member networking and exchange of information through our websites, Chapters, annual
Summits, regional and local events, and virtual communities
–
Working with Microsoft to influence the evolution of SQL Server products and services
–
Delivering high quality, up-to-date, technical content, presentations, and other educational material
for professional development
DDL, DML?
• Data Definition Language
– Vocabulary used to define data structures in SQL Server
– ALTER, CREATE, DROP, etc.
– https://msdn.microsoft.com/en-us/library/ff848799.aspx
• Data Manipulation Language
–
–
–
–
–
Vocabulary used to retrieve and work with data in SQL Server
Add, modify, query, or remove data from a SQL Server database.
INSERT, UPDATE, SELECT, DELETE, etc.
https://msdn.microsoft.com/en-us/library/ff848766.aspx
DML Trigger (Caution Will Robinson!)
DDL Triggers
• Fire in response to DDL Events
• DDL Events
–
–
–
–
–
–
–
–
CREATE
ALTER
DROP
GRANT
DENY
REVOKE
UPDATE STATISTICS
SP_CONFIGURE
DDL Triggers
• Uses
– Prevent changes in schema
– Fire another event in response to schema change
– Log changes or events in schema
• Types
– Transact-SQL DDL Trigger
• Special T-SQL SPROC executes one or more TSQL
– Database Scoped (e.g. DROP TABLE)
– Server Scoped (e.g. ALTER SERVER
CONFIGURATION, SP_CONFIGURE)
DDL Triggers
• Types (cont.)
– CLR DDL Trigger
• Executes one or more managed code statements (.NET
Methods)
• Other Important Stuff to Know
–
–
–
–
EVENTDATA function captures info (XML)
Only fire when associated DDL statements run
Cannot be used as INSTEAD OF triggers
Ignore DDL statements effecting local or global temporary tables and
stored procedures (http://sqlblog.com/blogs/jamie_thomson/archive/2009/11/25/temporary-procedures-sql-server.aspx)
– Template Explorer
EventData Function
• Captures info about event that fired the DDL trigger and
subsequent changes
• Returns XML - https://msdn.microsoft.com/en-us/library/hh213611.aspx
–
–
–
–
Time of Event
SPID
Event Type
Additional info depending on Event. e.g.,
• Database in which the event occurred
• Object against which the event occurred
• Transact-SQL statement of the event.
– Captured using XQuery against the xml data that is generated by
EVENTDATA
DDL Triggers
• Selecting a Particular DDL Statement to Fire a DDL Trigger
– DDL Events - https://msdn.microsoft.com/en-us/library/bb522542.aspx
• sys.triggers catalog view
• DDL trigger can fire after execution of any T-SQL event
belonging to a predefined grouping of similar events.
– e.g. Specify FOR DDL_TABLE_EVENTS in the CREATE TRIGGER
statement for DDL trigger to fire after on any CREATE TABLE,
ALTER TABLE, or DROP TABLE statement is run
– DDL Event Groups - https://msdn.microsoft.com/en-us/library/bb510452.aspx
Example DDL Trigger
• TriggerToCreateDatabase.sql
– Event
– Event Group
SQLVer
• Uses the EVENTDATA function to create a log of events. A
DDL trigger is created on the current database that populates
the table with event info whenever any database-level DDL
event occurs - http://www.sqlservercentral.com/articles/version+tracking/119029/
• A lightweight DDL auditing solution
• Alternative to Source Control
–
–
–
–
Requires discipline
Not easy to audit exact changes
Not easily exposed outside of source control environment
Not exactly fast
• Real World
SQLVer
• Has a lot of other features, but main focus is Auditing feature
• All objects use SQL schema SQLVER
• 2 Main Tables:
– sqlver.tblSchemaManifest - populated automatically at installation
with every SQL object in your database.
– sqlver.tblSchemaLog. - A row gets inserted into tblSchemaLog any
time you make a DDL change.
– 3rd table sqlver.tblSysRTLog, not used by version tracking.
• Uninstall SP - EXEC sqlver.spUninstall
• EXEC sqlver.ver - to inspect the version information that has
been logged
Demo SQLVer
SQLVer Mod 1
References
•
https://msdn.microsoft.com/en-us/library/ff848766.aspx
•
https://msdn.microsoft.com/en-us/library/ff848799.aspx
•
https://msdn.microsoft.com/en-us/subscriptions/index/ms186406(v=sql.105).aspx
•
https://msdn.microsoft.com/en-us/library/ms173781.aspx
•
https://msdn.microsoft.com/en-us/library/hh213611.aspx
•
https://msdn.microsoft.com/en-us/library/ms187909.aspx
•
https://msdn.microsoft.com/en-us/library/ms175941.aspx
•
http://www.sqlservercentral.com/articles/DDL+Auditing/88433/
•
http://sqlver.sourceforge.net/
Thank You from PASS
www.sqlpass.org