This is a “teaser” in that I’m not done writing the content yet, but I wanted to get it out there as “something I’m working on”. The problem at-hand is that we have a table trigger
which is constantly being disabled/enabled by a stored-proc
, which in turn controls the only acceptable method of updating the columns that would otherwise be forbidden from update by said trigger
. Clear as mud? K. Basically there’s a schema
change (“drift”) of a certain specific type that I want DLM Dashboard to always ignore on this DB.
Outline:
- Basic architecture
- RavenDB back-end
- SQL db, tables, procs, & triggers (“installation”)
- How it does event tracking
- Jessica = awesome support agent
- Modifying the event-reader to exclude app-names and other things
- XML event data
- XML indexes to help filter queries
- https://www.simple-talk.com/sql/database-administration/getting-started-with-xml-indexes/
- Schema-compare files to filter results
- Wholesale ignoring of a change-set (if possible)
Some code:
USE RedGate; EXEC dbo.RG_SQLLighthouse_ReadEvents; SELECT TOP 100 * FROM RedGate.SQLLighthouse.DDL_Events de ORDER BY de.PostTime SELECT TOP 100 * FROM RedGate.SQLLighthouse.DDL_Events de WHERE de.appname NOT IN ('SQLServerCEIP', 'Spotlight Diagnostic Server (Monitoring)') AND de.appname NOT LIKE 'SQLAgent%' ORDER BY de.PostTime DESC ALTER TABLE SQLLighthouse.DDL_Events DROP CONSTRAINT PK__DDL_Even__3213E83FB62EF9A3; ALTER TABLE SQLLighthouse.DDL_Events ADD CONSTRAINT PK_DDL_Events PRIMARY KEY NONCLUSTERED (id); CREATE CLUSTERED INDEX CX_DDL_Events_PostTime ON SQLLighthouse.DDL_Events (PostTime); CREATE INDEX IX_DDL_Events_SPID ON RedGate.SQLLighthouse.DDL_Events (spid) INCLUDE (transaction_id, options, nestlevel); CREATE INDEX IX_DDL_Events_AppName ON RedGate.SQLLighthouse.DDL_Events (appname) INCLUDE (client_net_address); CREATE PRIMARY XML INDEX XI_DDL_Events_EventData ON SQLLighthouse.DDL_Events ([eventdata]); CREATE XML INDEX XI_DDL_Events_EvenData_PATH ON SQLLighthouse.DDL_Events ([eventdata]) USING XML INDEX XI_DDL_Events_EventData FOR PATH; CREATE XML INDEX XI_DDL_Events_EvenData_PROPERTY ON SQLLighthouse.DDL_Events ([eventdata]) USING XML INDEX XI_DDL_Events_EventData FOR PROPERTY;
TBD, stay tuned!