Voice of the DBA

Category: Blog

  • Detecting Database Option Changes with DDL Triggers

    One of the keys to managing a large production SQL Server environment is being aware of changes that are taking place in the environment, and preventing potentially harmful changes. There are any number of ways to do this: triggers, auditing, PMB, third party monitoring, and more.

    In this post, I want to look at a quick way to detect issues with databases using DDL triggers. Specifically, I will build a quick trigger that responds to the ALTER DATABASE event.

    Tracking Changes

    In general, I try to avoid those options that severely limit my flexibility. I dislike trying to enforce every possible rule I create since I understand that IT environments often evolve and change, and the hard rules you have today may not apply tomorrow. I also realize that most of the time the hard rules have exceptions to them for various reasons and it’s much easier to manage a set of instances if you expect that the might not have the same requirements.

    I do want to be informed of changes, and one option is a DDL trigger that responds to a particular event. DDL triggers have a large list of events that will trigger them, of which the ALTER DATABASE is one.

    We build the trigger by giving it a name, scope and an event. In this case, we’ll start with this template

    CREATE TRIGGER [name]

    ON ALL [scope]

    FOR [event]

    You can change the scope and events as needed. For me, I need the Server level scope (database changes are a server instance event) and then the ALTER_DATABASE event. From there, it’s pretty much normal T-SQL coding.

    The data comes back from the EventData() function as an XML fragment, so in the trigger, I need to parse out the particulars that I care about.

    The code I’ll use is this:

    CREATE TRIGGER DBAAudit_ALTER_Database
ON ALL Server
FOR ALTER_Database  -- Captures a Create Database Event
AS
     
DECLARE
  @EventTime datetime
, @ServerName varchar(200)
, @LoginName varchar(200)
, @DatabaseName varchar(200)
, @TSQL varchar(2000)
, @event XML

select @event = EVENTDATA()

SELECT @ServerName = @event.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(200)')
SELECT @EventTime = @event.value('(/EVENT_INSTANCE/PostTime)[1]','datetime' )
SELECT @LoginName = @event.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(200)' )
SELECT @DatabaseName = @event.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(200)' )
SELECT @TSQL = @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(2000)' )

Print 'Database ' + @Servername + '.' + @DatabaseName + ' was altered by ' + @LoginName
Print 'Command: ' + @TSQL

    When I create this on my instance, it is stored in the Server Objects \ Triggers area on my server. Just like any other object, I can right click and perform all kinds of actions in SSMS.

    ddltrigger_a

    When I execute a change on a database, such as setting a database to read only with this:

    alter database dba_admin set READ_ONLY

    I get this in the messages tab (from the Print statement)

    Database DKRSQL2012.dba_admin was altered by DKRSQL2012\Steve

    Command: alter database dba_admin set READ_ONLY

    If I set the database back to read_write, I get this:

    Database DKRSQL2012.dba_admin was altered by DKRSQL2012\Steve

    Command: alter database dba_admin set READ_Write

    This is a nice example, but in a real system, I’d use a database for tracking these changes and store the information from the event in a table. Instead of a PRINT, I’d insert data into a table that tracks changes.

  • Exercise in the Margins

    This article about exercising when you can is really interesting. It contains a number of hints about exercising and fitting it in, but fitting it into your life, as you live it. I especially like the note about the fact that you will lose time in your life every month for reasons beyond your control. That’s certainly true.

    I run every day. As of Thur, that was 1353 days in a row that I’ve run at least a mile. I started the mile a day streak thinking that a mile, even jogging slowly, takes no more than about 15 minutes. Maybe 20 if you’re older. I can always find 15 minutes in my day.

    I know that’s not easy for everyone, but I’d think most people could find 15 minutes to walk every day, whether it’s 5am or 11pm. Time is not a great excuse when you set a low bar. Or maybe it’s not an excuse at all if you do things like taking the stairs, or walking out of your way on a flat surface, parking further from the door at the grocers, or any number of other ideas.

    In technology, it seems to be harder for many people to get into shape. We sit at desks, staring at screens, and often get lost in our work. I try to move fairly often, but I’ve certainly been lost writing code or words and found myself sitting at a desk for hours at a time.

    I think taking care of yourself is important, and it’s an investment worth making. My PSA for today is to get out and do a little more movement. You don’t have to run or play basketball or ski. Just go walk. You’d be surprised how beneficial that it.

    And have a short memory for sure. If you miss a few days for some reason, forget about it. Pick up some small exercise tomorrow and don’t try to “make up” for what you missed.

  • Always restore with NORECOVERY

    Always, always, ALWAYS restore a SQL Server database with the NORECOVERY option.

    It’s trivial to switch the database online.

    Not trivial to recover from an accidental restore with RECOVERY (the default)

    ‘nuff said.

  • Lifehacking – Not such a good idea?

    I read the confessions of a recovering Lifehacker and thought it was an interesting read. It seems that the author has started to realize that while you can make some differences in the small stuff in your life, it’s just small stuff. It’s also important not to let the organization, or hack, or change, overwhelm the benefit you get from it.

    I try to make some small changes here and there, and I do want to be more efficient, but not if the efficiency cost takes away too much of the rest of my life. It’s a balance, and one that can be hard to find.

    There’s also an interesting discussion on Hacker News about the post.