Event Notifications were first introduced in SQL 2005, and, unlike some other features I could mention (I’m looking at you SQL Audit), it’s available in all editions.  So you don’t need to shell out beaucoup bucks to audit your instances.  You just need to do a little TSQL coding (nothing too scary, I promise).  I should mention here that Event Notifications are based on the SQL Trace architecture, and if you’ve been paying attention you’ll know that SQL Trace has been deprecated.  So the future of Event Notifications is a bit cloudy at the moment.  I really hope MS finds a way to keep it, because there’s no other feature that can take its place at this time.  So if you’re listening, Microsoft bigwigs, here’s my plea:  keep Event Notifications!!! Please?

Why I like Event Notifications

Why all the fuss?  Well, Event Notifications are kind of like SQL Trace and DDL/Logon Triggers had a baby and that kid got the best part of both parents.  Like SQL Trace, Event Notifications work asynchronously, meaning outside the scope of the transaction that caused the event.  This means that the event notification’s work doesn’t use the resources that transaction was using, and more importantly, it won’t impact that transaction if something goes horribly awry (think errors, blocking, etc.).  Unfortunately, this asynchronous-ness has its price.  Because it’s working outside the scope of the transaction, the event notification can’t be rolled back if the firing transaction rolls back.  And along those same lines, an event notification can’t roll back the firing event, like a trigger could.  (So if you’re looking for something that will prevent events from happening, Event Notifications aren’t the answer.)

Like triggers, however, Event Notifications can do more than just record an event, they can respond to it.  We’ll go more into this next time when I talk about how they work, but let’s just say that, since Event Notifications work hand in hand with Service Broker, they can be used to perform actions.  What kind of actions?  They can insert event information into a table, obviously, so no more messing with multiple trace files (whoohoo!).  But they can also do things like send an email.  Want to know the moment one of your developers modifies a stored procedure?  Event Notifications can do that.

Because they use the SQL Trace architecture, Event Notifications are very low impact.  They do incur some overhead due to their use of XML, but this is minimal and shouldn’t be noticeable.

What can Event Notifications audit?

It might be easier to ask that they can’t audit.  Really, what events you can audit will vary based on the scope of the event notification.  You can define it at the SERVER or DATABASE level, and obviously certain events only make sense at a certain scope, but other events are available at both scopes.

You can query the sys.event_notification_event_types DMV to see a full list of all events and event groups, but in a nutshell, you can use Event Notifications to audit:

• all DDL events – Things like CREATE TABLE, ALTER PROCEDURE, etc. are obvious candidates, but you can also audit CREATE STATISTICS to monitor SQL Server’s creation of auto stats, or what about linked server modifications using the ALTER_LINKED_SERVER event?
• some trace events – How about monitoring when a query is missing a join predicate or missing column stats?  What about auditing data or log file auto growth?  That might be information worth knowing about.
• security events – Monitor failed logins, or all logins, as needed.
• DML events – Not too many people know this, but you can also use Event Notifications to monitor object access with the AUDIT_SCHEMA_OBJECT_ACCESS_EVENT.  Like SQL Audit, this event is monitored at the time of the permission check, so you can audit not only successful attempts at access, but unsuccessful attempts, too.  It’s worth noting that this event is only available at the SERVER scope.  Which means it will fire for every object access event in the instance.

What can’t you audit with Event Notifications?  Temporary objects.  They won’t fire for local or global temporary tables or temporary stored procedures.  So no monitoring of TempDB usage here.

So what’s next?

That’s a basic overview of Event Notifications.  In the next post, I’ll go into how they work and creating a basic event notification. Stay tuned…

Session 1

Title: SQL Audit – Auditing doesn’t have to be boring

Abstract:

Let’s face it, auditing isn’t fun. But if you’re a DBA, you need to know what’s going on in your SQL Server instance. Fortunately, SQL Audit makes it easy. In this session, we’ll cover what you can and can’t audit using SQL Audit, enhancements in 2012, configuring database and server audits, ways to process output, and ideas for how to implement SQL Audit on a larger scale. Auditing doesn’t have to be hard. And with the help of a little custom coding, it can even be fun.

Level: Intermediate

Session 2

Title: So I started this blog…now what?

Abstract:

Ever thought about trying your hand at blogging? Or maybe you’ve started a blog but struggle with what to write about. We’ll take a beginner’s look at blogging and examine what makes a great blog, finding topics, finding your voice, building authority and credibility, and setting you on the road to blogging stardom.

Level: Beginner

Digging the new logo

Last week I was fortunate enough to attend SQLSkills IE1 class in Tampa. Fortunate to have an employer willing to send me to that kind of training, but also because Tampa in February is way better than Cleveland in February. Not that I really got to enjoy the sunshine and warmth much, because let me tell you, they call it an “immersion event” for a reason. 8+ hours of intense SQL server training, usually complemented with a couple more hours of SQL-related activities in the evening. It makes for 5 very long days, but it was so worth it.

A week of SQL Server

We started out the week with Paul (b | t) giving us a solid foundation of database structures and datafile internals. From the structure of a record, to how it’s placed on a page, allocation bitmaps, and compression, with demos using DBCC IND and DBCC PAGE. It’s very dense stuff, and to be honest, for me this isn’t the most exciting topic, but it’s important for truly understanding how SQL works. After that we moved on to datafile internals, talking about physical layout, storage considerations, file maintenance and tempdb. After that Kimberly (b | t) closed out the day talking about locking and blocking, how data modifications work under the covers, transactions and savepoints.

That was just day 1. On day 2 Kimberly continued with more on locking, then went into a discussion on isolation, focusing on snapshot isolation and how that works internally. Paul then covered logging and recovery, VLFs, how transactions are logged and rolled back, and the internals of a checkpoint. Another intense day.

Day 3 focused primarily on indexing and data access. Kimberly started out by explaining table and index structures, making sure we understood the importance of a good clustering key and its impact on performance and maintenance. From there she segued into data access internals: the tipping point for index usage, the benefits of covering indexes and filtered indexes. Paul closed out the day discussing the ins and outs of index fragmentation.

Thursday’s topic du jour? Statistics. And let me tell you: Kimberly loves to talk about statistics. She promised us in the beginning of the week that even if we expected the stats module to be the driest of the class, we would change our minds by the time she finished. And she was right. Stats are pretty darn interesting, and having a good understanding of how they’re gathered and used, both by SQL and by you, is critical to good performance. That’s one module I’ll be reviewing soon.

We closed out the week learning about indexing strategies, table design, and partitioning. My only “complaint” about the whole week was that I wish we’d had more time on partitioning. Though, Kimberly did acknowledge that there wasn’t enough time in this class to do partitioning justice and they’re talking about an IE5 that covers it more in depth. What we did cover, however, gave me ideas on how both partitioned tables and partitioned views could be used on large tables.

Brain overload

It’s a lot of information being thrown at you in 5 days, and even though Paul and Kimberly do a great job of presenting it in a very easy to understand manner, you need to keep in mind that you’re not going to absorb it all in one week. While I would have loved to stay for IE2, which is more directly applicable to my current job, I’m actually glad to have a chance for everything from this week to firm up in my head. That way when I do take IE2, I go in with a solid foundation.

Some tips if you’re planning on attending:

• Throughout the week I kept a separate list of resources I wanted to check out further once the class was over. I didn’t want them buried amongst the other module notes.
• Get plenty of sleep. This isn’t a conference! You’ll want to be rested to make the most of the class.
• You won’t “get” everything they cover during the day, so you’ll want to review the materials before the next day. That way you can ask questions if something’s still not clear. Personally, I found it more effective to get up a little earlier and review in the morning when I was fresh.
• Ask questions. There are no stupid questions. If something doesn’t make sense to you, ask!
• Disconnect at much as you can.   I realize that you’ll probably need to keep in touch with your job, but try to limit it to breaks and off hours.

Is it worth it?

Do you know what thought kept popping into my head throughout the week? “I wish I’d known that at my last job.” There were so my scenarios and problems that Paul and Kimberly talked about that I’d seen on a regular basis. And had I had this training then, I could have addressed them so much better.  That’s OK though, from here forward I’ll be able to work with SQL Server more effectively.

Is it expensive? Compared to other classes you could probably take locally, sure. Especially when you add in travel costs, since it’s not likely for the majority of us that these events will happen in our home town. But you’ll never get this level of training from one of those local classes. You just won’t.

We talk about training a lot, about whose responsibility it is: ours or our employer’s. I’m not going to debate that now, but I will say this: you don’t ask, you don’t get. Ask your manager. Make your argument as best you can. And if he/she still says no, find a way to send yourself. You won’t regret a single penny. And to you managers out there: absolutely send your DBAs, but send your developers, too. This isn’t a class just for admins. Developers will also benefit from a solid understanding of how SQL Server works.

Pluggable Databases

Up til now in Oracle, every database required its own memory structures and background processes.  You could have multiple schemas using the same database/instance, but if you wanted to have separate databases, you needed to spin up a new instance.  This meant managing memory, resources, patching, etc. between two separate environments.  Spin up a couple more, and you can see where the management nightmare begins.  Well, with version 12c, Oracle is introducing the concept of pluggable databases (PDBs).  The idea is that you install a single container database (CDB), which manages the memory and background processes, and then you “plug in” user databases (the PDBs) into the CDB.  All of the PDBs share the same pool of memory and processes.  So instead of multiple instances, each with a single database, you now have a single instance housing multiple databases.  Hmm, where have I heard that before…

Anyway, in addition to making resource management easier, it also makes patching easier, since you patch the container database and the patch is applied to all of the plugged in databases.  The obvious question then was:  what if you have an app that can’t be patched, like your ERP system?  In that case, you can spin up a new container database, unplug the ERP database from the first container and plug it into the new container.

There were still some unanswered questions surrounding the whole multitenancy concept.  Could you plug a PDB into a lower version CDB?  Does each PDB have its own redo logs, control files, UNDO and TEMP spaces, etc.  Details like that aren’t clear yet, or at least they weren’t clear at the meeting.  What is known is that you’ll get one CDB and one PDB out of the box.  Additional PDBs are a licensed feature ($).

But wait, there’s more!

While the pluggable database change was the big news of the afternoon, there are other new features in 12c that are also worth noting.

• Data Guard Far Sync – introduces the ability to asynchronously replicate your database to another geographically separate datacenter through the use of a Far Sync database.  The Far Sync database is a stripped down database composed of only control files, redo logs, and enough data space to house the redo data being sent to the remote standby database.  The idea is that the primary database synchronously sends redo data to the Far Sync database.  The Far Sync compresses the redo data and sends it asynchronously to the standby database.
• Information Lifecycle Management – tracks extent or block-level statistics on read and update activity.  This information can then be used to create a heat map of how data is being utilized (or not utilized) to better plan how to treat that data when it comes to storage.  Business rules can also be put in place to automate partition compression or movement based on usage.  It’s also smart enough to exclude DDL, statistics maintenance, and reads that result from full table scans.
• Data Redaction – dynamic data masking for queries based on rules created in the database.  I thought this one was pretty neat, since previously you had to code the redaction of sensitive data at the application level.  But this obviously left you open to other query tools.  Now the database handles the redaction, based on business rules you create.

And that’s all, folks.  If you’re interested in becoming more involved in the Oracle community here in the Cleveland area, check out the NEOOUG’s web site and consider becoming a member.

• the SQL Server feed:  Cleveland DBA – SQL Server
• the Oracle feed:  Cleveland DBA – Oracle

Of course, you can still stick with the main feed you’re using now and get it all.

So where have I been?  Well, back in October I left my job of 12 years to move on to a new challenge.  Why did I finally decide to leave after 12 years?  Well, when I started at that firm, I had no DBA experience.  I had done some tech support for Informix, mostly for their I4GL and New Era programming languages, and I’d done some development in I4GL, but I wanted to move into database administration.  The firm was looking for someone with DBA experience with Informix, as well as knowledge of the I4GL language.  They took a chance on me and it worked out well for both of us.  Informix was eventually phased out in favor of Oracle, and then SQL Server.  Each new phase gave me an opportunity to expand my skill set.  It kept me challenged.

The thing about working at one place for so long, especially a large shop, is that you really only get exposed to one way of doing things.  And while I appreciate everything I learned during my time there, I knew the only way to push my skillset to the next level was to move on.  So when this opportunity presented itself, I jumped on it.

I won’t go into details here regarding my new employer, but suffice it to say they’ve been keeping me busy.  I’ve already learned a lot of new stuff, and I’ve been brushing up on my somewhat rusty Oracle skills.  Since I use this blog in part as a way to record what I’ve learned for later reference, as well as to solidify it in my head, I’ve been thinking of how I can incorporate the Oracle stuff.  I know a lot of us out there support both Oracle and SQL Server, and there are quite a few reluctant “accidental Oracle DBAs” that may find the information useful, too.  And there just doesn’t seem to be the same number of good Oracle blogs out there like there is for SQL Server.  Maybe a separate blog is the way to go, or perhaps a separate page.  I’d prefer to keep it all on the same domain.  Perhaps I’m overthinking it, maybe it should all be mixed together and if you aren’t interested in the Oracle stuff, don’t read it.  I’d just need to update my syndication feeds.

And then there’s life.  My youngest brother got married and we had a fun trip up to Buffalo to celebrate with the family.  Two of my best friends moved across country, and there were numerous social activities in order to squeeze every last drop of fun out of them before they left.  Last week we spent an evening at the Severance Hall where they showed Charlie Chaplin’s Modern Times while the Cleveland Orchestra played the original score.  As much as I love old movies, I’d never seen any Charlie Chaplin films before.  It was so much fun.  We splurged on box seats, so now I’ll be spoiled for anything else.

The holiday season buys me some slack, I’m sure.  After all, I did bake 22 dozen cookies this weekend (8 dozen rugelach, 8 dozen pecan tassies, and 6 dozen sugar cookies, if you’re wondering).  Everyone is busy this time of year and I’m seeing a lot of blogs “go dark” until the new year.  So I’ll probably be quiet a bit longer, but hopefully you’ll still be here when I return in January.

Happy Holidays!

Here, have a cookie.

WIT Spotlight: Erin Stellato

This month we’re talking to the newest member of SQLSkills.com: Erin Stellato (b | t).  Erin and I are members of the same local PASS chapter, and her advice and support has been invaluable as I’ve worked to establish myself in the SQL Community.  She’s a stellar example of what #sqlfamily means.

There’s a lot of talk these days about getting girls and young women to enter – and stay – in technology careers.  As parents, what can we do to encourage more girls to enter STEM fields?

This question generates two thoughts…  First, I think it’s incredibly important to encourage females to enter STEM fields, but I think it’s also important to recognize that not every female will be interested in said fields.  As someone who loves technology, it’s naïve of me to think that my daughter will as well, just because I do.  I don’t mean to say that we shouldn’t be supportive and encouraging, but realize that 100% of young women will not want a technology career.

That said, for those that are interested, or haven’t made up their mind, I think the most important thing adults can do is engage in technology with young girls.  It reminds me of what Kevin Kline said at the Women in Technology lunch at the PASS Summit earlier this month.  “Come in and sit next to me, for I have known goodness.”  If young women see what we are excited and passionate about, because we sit down and show them, and help them, it carries tremendous weight.  In those moments we are not just teaching girls about technology, we are also showing them what we think of it, how it inspires us, and how it’s not something of which to be ashamed.   It’s not enough to buy my daughter a LeapPad or an InnoTab, Legos or some Tinker Toys, I need to sit down with her and build and play.

How has the transition been to working from home?  What advice can you share with someone looking to make that switch?

It’s hard to separate out the transition to working from home from the transition to the new job because they’re so intertwined!  I gave working from home a lot of thought.  I knew that I would need a dedicated space, that I would need to be disciplined about my time, and that I would be spending a lot of time alone.  Initially I did not have dedicated space and that was a struggle.  But once I got my desk set up, it completely fell into place.  For anyone who works from home, you absolutely need your own area – ideally one where you can close a door.

I am still sorting out my work time.  When my kids are at school the hours fly by, and I am very focused during that time.  At first I would work after my kids got home from school, but then I realized that I wasn’t spending time with them.  Now when they’re home, they are my focus.  When they go to bed, then I can work again.  But it took a while for me to reconcile no longer working the “normal” hours of 9-5.  However, I think I’m a lot more effective working in smaller chunks of time.

As for so much time alone…I really like it.  It is nice to have time in my house, by myself, even if I’m working.  Some days I don’t turn on the radio, it’s just complete silence.  For anyone who is looking to switch to working from home, I recommend thinking about the phrase, “working remotely.”  It has a different meaning, to me, than working from home.

Working remotely means that I don’t go into an office (ever) and I rarely see my co-workers.  I thought about this a little bit beforehand, but I underestimated how important it was to me to talk to my colleagues on a regular basis.  It’s great to see people face-to-face, but the real value is the conversation and dialog.  Once I realized this, it was easy to remedy.  I set up regular calls and WebEx sessions with different people on the team, I know that I can call if I ever need to, and now I’m in a much better place.  I value my time working alone, and I feel like I am extremely productive, but I still need to interact with people over more than just email.

So far, what’s been the best part of working at SQLSkills.com?  The toughest?

Do I have to pick one best thing?    I work with an amazing group of individuals.  That is the best thing about my job.  They are the best at what they do.  I appreciate that I can reach out to them as needed, asking for a review of an article or blog post, or an opinion on a client problem.  And they are people that I would like even if I didn’t work with them, which is even better.  Our team has a lot of fun, and I don’t take that for granted.  I think it’s a gift to work with people that you like as much as you much respect.

As for the toughest part…I would have to say it’s been the transition to working remotely, which I mentioned before and balancing my time.  When your office is in your house, it’s easy to spend many hours in front of a computer.  I’m still sorting out that balance.

You’ve come a long way since attending your first SQL Server conference in 2007. What do you hope to accomplish in the next 5 years?

I would like to present internationally.  I’ve presented at the PASS Summit the past two years and hope to present there again, but I would like to take it to the next level.

I’ve talked about getting my MCM before, and although that certification is changing slightly, it’s still a goal for me.

I haven’t defined any other goals yet, and I’m ok with that.  The last half of 2012 has brought great change for me, and I’m letting myself settle into where I am now before I decide what’s next.  I am very happy with where I am today.  It doesn’t mean I’m resting, or that I’m not always striving to improve, but deciding what’s next is big. I’m not quite there yet.

In the movie of your life, who would be cast in the title role?

This question makes me laugh   When I was in college I lived with seven friends in a huge house on Arbor street, and we often joked about “when they make a movie of our lives.”  So I’m sticking with who I picked back then, many years ago: Elizabeth Shue.

Must-see WIT TV

Some of you (hopefully most of you) just attended the PASS Summit and I’m sure you returned home inspired and full of ideas of how to do your job more effectively.  I know conferences have that effect on me; they recharge my battery and get me fired up again, like a SQL Server B-12 shot.  Hopefully the Summit inspired you to do something else, too: get up there and present.  There are some awesome speakers at the Summit, and they certainly know their stuff, don’t they?  But you know what?  They’re just like you.  Once upon a time they were brand new speakers and probably full of the same uncertainties you’re feeling.  It’s not easy to stand in front of a room of strangers and teach them.  They’ll ask questions you’re not prepared for.  They’ll challenge your knowledge of the topic.  That’s ok; it will help you learn.  And the next time you present, you’ll be better prepared.

So this month, in addition to a video, I’m featuring some sites to help you prospective speakers, you women especially, to get out there and start speaking.

• How I Got 50% Women Speakers at My Tech Conference
• We Are All Awesome!
• Technical Women – It’s conference submission season! (From Summit speaker Lynn Langit)
• Live Your Talk and the blog

The video is from Women Who Tech‘s 2010 Telesummit (skip forward to about 2:30).

Interested in supporting women in technology?

• Go to the WIT website and check out all the resources, blogs and coming events
• Follow @PASS_WIT and #passwit on twitter
• Email wit@sqlpass.org to be added to the WIT mailing list; you’ll also receive invitations to the monthly call
• Participate in the monthly call
• Attend WIT panels at SQLSaturday events
• Attend the WIT luncheon at the PASS Summit

WIT Spotlight: Christina Leo

This month’s PASS woman in the spotlight is Idera Ace Christina Leo (b | t).  Christina is a database developer/administrator, prolific speaker, intrepid traveler, fellow whisky-lover, and kettlebell-swinger from Nashville, TN Boston, MA.

You work for a large company now, but you began at a small software start-up. What did you learn from that experience?

I think the most important lesson that I learned is that there is no such thing as “comfort zone”. When there are only a handful of you, you must be willing to wear whatever hat needs wearing to get the job done and move forward. Having the courage and drive to try things that seem uncomfortable at first will open a lot of doors. I’m often asked how a special education teacher ended up as a SQL Server developer. The short answer is that I kept trying on hats until I found the one that fit.

How has being an Idera ACE impacted your career?

Community is the first word that comes to mind when considering how to answer this question. The ACE program was put together to help Idera connect with the SQL Server community, and I think that benefit goes both ways. Having the funds to speak at more events has allowed me to meet and connect with many more SQL folks. Having a bigger grapevine, so to speak, means I hear about job opportunities that others might not. Additionally, giving presentations is a bit like having a walking resume. It lets you get your foot a bit farther in the door when pursuing new challenges, simply because folks already know something about your skills. Want to move up to bigger and better things? Get out there and start connecting with the people who can make that happen. Start by volunteering with your local user group. Opportunities will abound from there.

What’s your next challenge?

In late August, an opportunity presented itself to do some contract work for a large investment fund management group in Boston. I was ready for a change, so I jumped at the chance. I absolutely loved the area when I flew up for my final interviews, so I decided to pick up stakes and relocate altogether. I started my first week on October 15th, and I’m thrilled with the decision to go this route. I’m getting to work with some super sharp folks, and the development work is well beyond what I had been getting to do before. The database and its usage requirements are very specialized. All sorts of tricks have been implemented to make things work efficiently. I’ve got a whole new industry to learn, along with bringing my T-SQL ninja skills up to par. We often talk about “drinking from the fire hose” when we describe some of the 500-level sessions at the PASS Summit. Working at this new gig feels very much like that. The next several months will be spent absorbing every bit that I can as fast as I can.

You travel quite a bit, can you share with us a memorable experience?

This is a tough one. I do love travelling to new places and have been blessed with the opportunity to go all over the U.S. as well as many places overseas. Two summers ago, I went on SQL Cruise Alaska. Besides getting to spend the week with some awfully smart SQL folks, I got to see some pretty amazing scenery. Glacier Bay was absolutely breathtaking; however, the highlight excursion for me was snorkeling in Ketchikan. While swimming through the middle of a kelp field, I suddenly remembered my teenage self, eagerly reading through dive magazines and hoping I would have a chance to visit some of the amazing places I was reading about. I instantly realized I was seeing a daydream come to life. That, combined with the sheer wealth of sea life to observe, then surfacing to see an enormous bald eagle sitting on a rock just feet away from me, definitely ranks high on the memorable experience list.

Whisky of choice?

BenRiach Authenticus 21YO – I first had this particular whisky when several of us SQLFoodies got together for an amazing meal at WD-50 after SQL Saturday #158 NYC. After that first sip, I knew I’d found my favorite. I tend to gravitate toward heavily peated whiskies, and this one has enough smokiness to keep my attention, but what I think makes it amazing is its balance. Not too smoky, not too sweet . . . but just right. Goldilocks would most definitely approve.

Must-see WIT TV

This month’s featured video is from NASA’s Aspire To Inspire program, aimed at encouraging and empowering young women and girls to enter STEM fields.

Interested in supporting women in technology?

• Go to the WIT website and check out all the resources, blogs and coming events
• Follow @PASS_WIT and #passwit on twitter
• Email wit@sqlpass.org to be added to the WIT mailing list; you’ll also receive invitations to the monthly call
• Participate in the monthly call
• Attend WIT panels at SQLSaturday events
• Attend the WIT luncheon at the PASS Summit

Why might you want to create a user-defined event audit in the first place? Well, we already know that we can use SQL Audit to audit access to certain objects. So let’s say we have a table with salary data. We can use the SCHEMA_OBJECT_ACCESS_GROUP to audit access to any object in that schema. We can also use SELECT, INSERT, UPDATE, and DELETE actions to audit those actions on specific objects. But let’s say even getting that granular will produce more audit output than we’d like. Suppose we only want to know when an employee’s salary is increased by more than 10%. We can’t do that with any of the canned actions. But we can do that with a custom event.

Configure the audit

The first step is to configure the audit object. Once we’ve got that configured we create the audit specification. This can be either a server audit spec or a database audit spec, depending on your needs. Just make sure to add the USER_DEFINED_AUDIT_GROUP action. And don’t forget to enable both the server audit and the audit spec.

 USE [master]
GO

CREATE SERVER AUDIT [TestingUserDefinedEvents]
TO FILE
(	FILEPATH = N'D:\SQL2012\Audits'
	,MAXSIZE = 5 MB
	,MAX_ROLLOVER_FILES = 5
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)
GO
ALTER SERVER AUDIT [TestingUserDefinedEvents] WITH (STATE = ON);
GO

USE [AdventureWorks2012]
GO

CREATE DATABASE AUDIT SPECIFICATION [UserDefinedEvents]
FOR SERVER AUDIT [TestingUserDefinedEvents]
ADD (USER_DEFINED_AUDIT_GROUP)
WITH (STATE = ON)
GO
 

Writing to the audit

To write to the audit log, we’ll use the sp_audit_write stored procedure. This built-in stored procedure accepts 3 parameters:

• @user_defined_event_id is a smallint used to identify the event
• @succeeded is a binary flag used to specify whether the action was successful or not
• @user_defined_information is an nvarchar string describing the event

So, to test our audit, let’s run the following:

 USE [AdventureWorks2012]
GO
EXEC sp_audit_write @user_defined_event_id =  27 ,
              @succeeded =  0
            , @user_defined_information = N'Testing a user defined event.' ;
 

If we check the audit log, we should see the event.

Putting it into practice

Back to our original purpose, we wanted to know whenever an employee’s salary was increased more than 10%. To do this, we can create a trigger. (As I’ve mentioned many times in the past, I’m not a developer, so no fair picking on my trigger code.)

 USE AdventureWorks2012
GO

CREATE TRIGGER [humanresources].[SalaryMonitor] ON [humanresources].[employeepayhistory]
AFTER UPDATE
AS
declare   @oldrate money
		, @newrate money
		, @empid integer
		, @msg nvarchar(4000)

select	@oldrate = d.rate
from deleted d

select @newrate = i.rate, @empid = i.BusinessEntityID
from inserted i

IF @oldrate*1.10 < @newrate
BEGIN
	SET @msg = 'Employee '+CAST(@empid as varchar(50))+' pay rate increased more than 10%'
	EXEC sp_audit_write @user_defined_event_id =  27 ,
              @succeeded =  1
            , @user_defined_information = @msg;
END
GO
 

Now if we test the trigger by virtually doubling employee 4′s rate and only increasing employee 8′s rate by a small amount.

 select * from HumanResources.EmployeePayHistory where BusinessEntityID= 4

Update HumanResources.EmployeePayHistory set rate = 59.8462
where BusinessEntityID=4 and RateChangeDate = '2006-01-15 00:00:00.000'

select * from HumanResources.EmployeePayHistory where BusinessEntityID= 8

Update HumanResources.EmployeePayHistory set rate = 41.8654
where BusinessEntityID=8 and RateChangeDate = '2003-01-30 00:00:00.000'
 

We should see an audit record for employee 4 in the output, and we do.

You might be wondering if you can configure an audit to only capture specific user-defined event IDs.  Great question, and you can by filtering on the user_defined_event_id field in the server audit.

So there you have it, another way to tailor SQL Audit a bit more to your specific needs. Happy auditing!

The Event

The weekend started with the speaker dinner, held inside the Rivers Casino.  It was an evening of good food and good conversation.  I met some folks I’d previously only “met” online, and got to see some now familiar faces again.  The drive back to the hotel was quite interesting.  This was my first visit to Pittsburgh and, based on this experience, I can say it’s full of winding, hilly streets, one way roads, and divided roads.  There was a moment when I was sure my phone’s GPS was just messing with me.

The next morning it was off to the event itself, held at La Roche College.  Let me just say now that Gina, Madhu, Matt and everyone else involved in the hours of planning that went into this event did a fantastic job.  Truly.  You would never have guessed that this was Pittsburgh’s first SQLSaturday.  Everything was laid out very well for good traffic flow.  Volunteers were always on hand to ensure that attendees and speakers had everything they needed.  Room proctors handled the distribution and collection of session evaluations and there was an interesting twist on the whole eval/raffle thing. Rather than lug books for each session to each room for raffle prizes, the raffle winners were given a ticket to redeem for the book of their choice back in the main hall.  I thought this was a smart time saver.

As will happen, there were a few last-minute schedule changes due to speaker cancellations, but Gina and Matt handled it well, finding speakers, including me, who were willing to fill in with an extra session.

My sessions

Because it’s all about me, isn’t it?  (I keed, I keed.)  I had originally planned to just give my talk on DDL auditing, but due to a speaker cancellation, I also gave my talk on getting started in blogging.  I had a small group of attendees for the blogging session, but they were a interactive bunch, which I’ll take over a large stoney-faced group any day.  This session isn’t really technical at all, it’s about getting past your hangups and just doing it, so I like it to have some energy.  And it’s just better when the energy isn’t all coming from me.

Right after that, it was on to my DDL auditing session.  Can I just say that talking for 2 hours straight is hard.  I’m not used to talking that much at a stretch, I don’t know how other speakers do full-day precons.  But the session went well, with the exception of a ZoomIt malfunction (someone in the room said other speakers had had problems with ZoomIt also), and I know I got at least one person thinking about what she could implement at work.  So… yay!

That’s all folks!

This event was my last SQL Saturday of the year.  With the holidays looming and other changes happening, life is just going to be too hectic for a while.  So I plan to spend the next few months developing new sessions and revamping my blogging session to make it more advanced.  Next year I hope to have all new material to talk about.