Tag: unfinished

Hacking DLM Dashboard, Part 2

Another teaser, just to keep you waiting… I promise I’ll finish it soon!

This hack is about change buildup/bloat.  When you ignore a particular DB’s schema drifts for a while, the amount of changes that the “Review” page needs to show gets larger and larger.  Until, eventually, there’s simply too much content for it to handle, and it dies.  This is a bummer, because you want to ack those changes, but you don’t necessarily need to see them all in detail, as long as you have a general idea of what they were about.

So, this will be a tale of how I purged a set of un-acknowledged changes from DLM Dashboard for a specific Server/Database.  RG support, bless their heart, wanted me to go to the document repo (which, if you’re not aware, uses a RavenDB back-end stored on your local file system) and purge everything.  But I said no, I think we can do better than that.

Plus, if you really really care about those changes, you can use the RavenDB Studio (GUI) to dump the documents to CSV — i.e. if this tool is part of your audit/compliance toolbox and you don’t want to lose those changes, but you need to be able to “move on with life” by acking the changes to the DB so that you can resume checking it on a regular basis without having a page-hang/crash.

This will be screenshot-heavy, censorship-heavy (to avoid divulging company secrets!), and meme-free (I know, I know, some of you will thank me and others will be a sad panda).

More to come, stay tuned!

Hacking RedGate DLM Dashboard

This is a work-in-progress, but I thought I’d put it out there because it may be interesting to others who use the tool.

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:

  1. Basic architecture
    1. RavenDB back-end
    2. SQL db, tables, procs, & triggers (“installation”)
  2. How it does event tracking
    1. Jessica = awesome support agent
  3. Modifying the event-reader to exclude app-names and other things
    1. XML event data
    2. XML indexes to help filter queries
    3. https://www.simple-talk.com/sql/database-administration/getting-started-with-xml-indexes/
  4. Schema-compare files to filter results
  5. 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!