[Event Notifications] SQL Server 2016 CTP2 is out, what's new in EN-land?

Quickie post since I can't get Chrome or Firefox to download a VM so I can log onto work, and Azure is taking forever to start it up.

Why? SQL Server 2016 public CTP is out!  (Technically CTP2, but still...)

Naturally, the first things I want to know:
What's changed in Event Notifications?  Has SQL Trace actually (finally) been excised?  How do things look?  Did they rewrite SSMS again?

So, doing a quick looksee....

Profiler appears to be there.

select * from sys.event_notification_event_types
395

select * from sys.trigger_event_types

275

But what's new?

Okay, first up, sys.event_notification_event_types.

309 CREATE_AUDIT 10072
310 DROP_AUDIT 10072
311 ALTER_AUDIT 10072
312 CREATE_SECURITY_POLICY 10073
313 ALTER_SECURITY_POLICY 10073
314 DROP_SECURITY_POLICY 10073
315 CREATE_COLUMN_MASTER_KEY_DEFINITION 10074
316 DROP_COLUMN_MASTER_KEY_DEFINITION 10074
317 CREATE_COLUMN_ENCRYPTION_KEY 10075
318 ALTER_COLUMN_ENCRYPTION_KEY 10075
319 DROP_COLUMN_ENCRYPTION_KEY 10075

10072 DDL_DATABASE_AUDIT_EVENTS 10029
10073 DDL_SECURITY_POLICY_EVENTS 10016
10074 DDL_COLUMN_MASTER_KEY_EVENTS 10016
10075 DDL_COLUMN_ENCRYPTION_KEY_EVENTS 10016

Okay, so I made a mistake on the VM - did 2012 instead of 2014.  But what's obviously new?  Well, Column Encryption got 5 separate events.  Create/Drop/Alter_Audit (and DDL_DATABASE_AUDIT_EVENTS) were all added in 2014.  So we have the remaining three (and their "group") - CREATE_SECURITY_POLICY, ALTER_SECURITY_POLICY, and DROP_SECURITY_POLICY.  What are they?  *googles*  Hmmm

CREATE SECURITY POLICY (Azure SQL Database)

 Creates a security policy for Row-Level Security.

Okay, then! Looks like the only new DDL types are around the encryption.  Good to know.  

Any trace changes?  (you never know)

sys.trigger_event_types.

Huh!  260 vs 275.  

309 CREATE_AUDIT 10072

310 DROP_AUDIT 10072

311 ALTER_AUDIT 10072

312 CREATE_SECURITY_POLICY 10073

313 ALTER_SECURITY_POLICY 10073

314 DROP_SECURITY_POLICY 10073

315 CREATE_COLUMN_MASTER_KEY_DEFINITION 10074

316 DROP_COLUMN_MASTER_KEY_DEFINITION 10074

317 CREATE_COLUMN_ENCRYPTION_KEY 10075

318 ALTER_COLUMN_ENCRYPTION_KEY 10075

319 DROP_COLUMN_ENCRYPTION_KEY 10075

10072 DDL_DATABASE_AUDIT_EVENTS 10029

10073 DDL_SECURITY_POLICY_EVENTS 10016

10074 DDL_COLUMN_MASTER_KEY_EVENTS 10016

10075 DDL_COLUMN_ENCRYPTION_KEY_EVENTS 10016

I did choose the right tables, yeah?  (Yeah).  

Okay, what's the TLDR?  New features again supported, EN actually still works.  How about that nasty Hekaton bug?  If you remember, Hekaton in 2014, did not allow EN to be running when a memory-optimized table was created.  And now?

[long blobs of code deleted] 

Msg 10794, Level 16, State 124, Line 19

The feature 'EVENT NOTIFICATION' is not supported with memory optimized tables.

Ugh.  That's either the same message, or a less useful one.  Either way, they still haven't fixed it.

Please please vote on it!

https://connect.microsoft.com/SQLServer/feedback/details/874185/event-notification-please-make-in-memory-oltp-tables-natively-compiled-sps-work-with-en

[Presenting] 24 Hours of PASS - I'm speaking!

Too cool not to put here.



Yup, I'll be presenting, at 8am Central time, June 24th (2015), 13:00 GMT.

On what?  Event Notifications, of course, to track down Code(T-SQL) changes in your environment!  I'm still loving and using it relentlessly, and my former coworkers who used it all love it and run it in their environments.  You should too!  At this point, I can tell you what changes were made, anywhere from 2 years ago to 5 minutes ago.  And it's all checked into source control. 

The .Net Devs love it (they know what it actually looks like, not just what they think it looks like), the Ops people love it (ah, okay, so this person deployed/changed the code at this date), the auditors love it (oh hey, there's a trail of changes), and the SQL Devs love it (oh, that's right, we had that emergency fix for performance on that box since it could use feature X). 

 So, here's the iterations of the name:

• Master of all I Survey (grandiose title to get people to read the abstract - that's the hardest part!)
• SQL Watchdog (needed something shorter, but it was confusing because it sounds like a program name - which, ironically, was the intent.  Make it catchy and They Will Come)
• (Project) Overlord.  (I originally wanted to call it Evil Overlord, but apparently that's a cultural thing not everyone gets).  Plus, then I get the WW2 association, storming the beaches and all that.

Tickled pink to be picked, for several reasons.

1. I'm glad to see PASS trying to actively get more people to present.  I've been doing SQL work for over 15 years, but just decided the past 3 to start presenting. Now that I have, though, I'm encouraging everyone on our team(s) do do the same.  You wind up learning things REALLY WELL, it's fun, and you help others.  We have a crazy big & complex environment, so we have all the toys, all the tricks, and all the fun.  We do everything from clusters to compression to partitioning to filtered indexes to CTEs to Window Functions to Clustered Columnstores to... all in a high-transaction shop.  Most people don't have all that, but would like to learn.  And we've been doing it long enough we have some tidbits to make it easier for others.
2. Free training, and you don't have to leave your couch/desk/etc!  And it's cool stuff!  Encryption! Power BI! Error Log Repositories! Azure Machine Learning! BIML! Agile Development with SSIS!  And that's only 1/4th of it!
3. Holy cow, it's A Big Deal.  Traditionally it's been chock full of Big Names (and names that _should_ be big), there are a lot of good speakers, people I follow, that I've seen, spoken with at length, and the fact that I'm in it is very cool.

Save the date!  June 24-25!