PASS Summit Day 1 – Keynote, MCM, SQLPASSTV, ServiceBroker, BI Power Hour, MDX, Who Dunnit?

Just a heads up. Today I’ve seen the most impressive BI demos – ever! *jaw drop*

Key Note

My takeaways:

Atlanta, Denali, Crescent

The next version of SQL Server (code names Project Atlanta, “Denali”, and “Crescent”) – mostly about teradatas, column stores, and reporting and diagnosing from the cloud – will change the landscape of SQL Server.

And man, PowerPivot is everywhere!

You can now download the Denali CTP1 version from here:

My iPad cannot compete with trusty laptops and MacBooks, and the wireless during the keynote was essentially nonexistent. So I have to refer you to – ta da – Brent Ozar’s posts – for the lowdown of events:

White Paper:
ColumnStore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0

Books Online:
What’s New in SQL Server Denali

By the way, Happy Birthday @BrentO!

MCM Changes

This was not announced at the keynote, although for me personally, this is keynote-worthy. For techs like me who might have dreamed of maybe, just maybe, having a shot at taking the MCM someday, this is definitely worth jumping up and down for. No the MCM does not become easier. But a couple big possible hurdles – like money invested, and time off needed to do training – have been made more manageable.

Read on from Brent Ozar and Paul Randal:

And to get you started:

MCM Certified Master Training Videos (from Technet)

SQL Server Service Broker with Denny Cherry

My takeaways:

  • Service Broker is an asynchronous messaging that happens all within SQL Server. No other messaging technologies involved
  • Messages are guaranteed within the scope of a conversation. Max 1GB per message.
  • There is no way to reprocess or re-queue a message. And if it’s not processed, it will stay in the queue.
  • There is also no way to purge the queue. What you can do is ALTER and turn RETENTION OFF, then turn back on.
  • Remember queues are database objects too, and fully logged.
  • Advantage of service broker: fire and forget. But it will get there, even if you have a network glitch. Your message stays in queue until you’re back online. (unlike replication)
  • Use service broker for things that are NOT time sensitive.
  • If you need to encrypt, especially from server to server to server you will need to set up certificate exchange. Talked to David Wei (from Vancouver as well), it seems it was quite complicated, and it took him a week to configure this!
  • When configuring service broker, remember that SERVICENAME is always case sensitive
  • Service Broker does not require the SQL Server Agent at all
  • DBMail use Service Broker to send email
  • You don’t have to have 2 queues in service broker, but highly recommended
  • Service broker goes up as soon as the engine starts
  • When you restore a database, service broker is off by default

My most interesting take away: real life application of Service Broker.

For some, they use it as a “Fake” replication. Replication is very sensitive to network glitches, but with a properly configured Service Broker, your “service-broker-enabled” replication can withstand network glitches.

However, the BIG con is the latency. Because it is asynchronous, you might get the data much much later than you wanted. So don’t use service broker for time-critical applications.

Some great service broker resources:


BI Power Hour – with the BI Power Hour team

Honestly, words escape me. I was very very impressed with all the demos, and these have to be the best BI demos I have ever seen. Period.

Although the BI PowerHour team had made the atmosphere very fun and entertaining, what they’ve demo’d are pretty serious stuff.

Here’s the gist, if you haven’t seen BI Power Hour before (I cant believe this is the first time I’ve seen this! How did I miss it last year?)

An hour and 15 minutes. Eight people on stage. Seven demos. Seven impressive demos :

(disclaimer: I apologize I cannot remember exactly which technologies were used in the demos, so what I’ve listed below may not be very accurate. Everything’s a blur, it was just a whirlwind of demos after demos. What Im sure of is overall they used PowerPivot, Excel Services, PerformancePoint, SSIS, SSRS, SQL Azure)

  • Julie – PowerPivot and Path to Happiness
  • Lynn Langit (@llangit) – PowerPivot, Reporting Services on the cloud (aka Azure) about tweet trends

    Lynn Langit Quotable “Did I mention I have no fear?” re: all live demos
  • Dan Parish (@danparish) – wearing the striking Azure blue shirt – Excel Services Chess Game, Excel Services, SharePoint demo on drilling down stats on what “living things” airplanes hit while in flight, all plotted in Bing
  • PerformancePoint demo on reporting trolling on YouTube
  • Reporting Services transforms into a PacMan game. “SSRS will be the next gaming platform” – epic!
  • Matt Masson (@mattmasson) – SSIS custom source from FaceBook, custom destination back to Facebook, and custom transform task that integrates AI to generate you “comment” friends
  • Reporting Services transforms into a PacMan game. “SSRS will be the next gaming platform” – epic!
  • Another mind blowing demo on how to impressively drilldown on data using PowerPivot, Reports, XML data, (I cant even describe it); the presenter (apparently 2 weeks on the job at Microsoft) used his data to “decide” why he chose to move to Seattle.

Here’s the official blog about it (from Pej Javaheri):

Overall note to self: dont miss BI PowerHour in next PASS! I have to admit though, it’s very tough. If you’re at PASS choosing sessions, you know what I mean. I practically have at least 5 sessions I want to go to – for every time slot.

Demystifying MDX with Stacia Misner

Have to admit I’m an MDX newbie, and mostly attended to get a little bit more MDX exposure. It’s a little bit over my head, but I’m glad I did because Stacia Misner (blog | twitter) showed some great tips that I’m sure I will be able to remember and use, when the need comes.

Some notes:

  • Can only put measures on columns
  • Can change .Value to .FormattedValue
  • When using aggregate, must have group
  • Can manipulate to get a date selector; use expression instead of getting just the fieldname in the dataset (this returns a string)
  • You can only do asymmetrical query results with MDX
  • Use CONSTRAINED used in STRSET to avert SQL injection attacks

Check out StaciaMisner’s blog:

Change Tracking with Adam Machanic

Excellent presentation from Adam Machanic (blog | twitter)

  • As a DBA, you always need to answer the question “What happened”?
  • How not to audit: sql trace (why? no way of telling who accessed what; common criteria and c2 are close, but not quite)
  • How not to audit: ddl triggers (why? remember, data gets materialized in tempdb, and you might run into the “timestamp” problem, ie when your application depends on the maximum timestamp. In your concurrent transactions, an “older” transaction might finish later than a “younger” transaction, so your app will end up getting the “older” transaction, wiping out the updates of the “younger” transaction.
  • Big caution on Change Tracking (CT), Change Data Capture (CDC) and SQL Audit – these are v1.0 MS versions (dont be burned)
  • Change Tracking can tell you which row changed. It’s all about net changes. This can be enabled on the table level. It can’t tell you: history of the change, who made the change
  • When querying Change Tracking, can use CROSS APPLY and CHANGETABLE function. Use VERSION (1st arg) when querying baseline, or VERSION for succeeding changes. Watch your JOINs when working with CHANGETABLE
  • Change Tracking is intended for cache synchronization.
  • Need to enable snapshot isolation if you plan to use change tracking
  • Change Tracking uses an internal table that stores transaction ids (xact_id) and commit timestamps (commit_ts).
  • Change tracking will definitely affect performance. Clustered index insert to change tracking tables
  • Change Data Capture can show you history of what happened. It still does not capture who made the changes.
  • Change data capture is asynchronous and uses replication log reader
  • “CDC can help you win arguments”
  • Change Data Capture requires snapshot isolation to be turned on
  • Change Data Capture (CDC) does not scale as nicely as Change Tracking (CT)
  • BIG CAUTION: you might get the error “insufficient number of parameters …” even when your actual error is that the LSN does not exist ..
  • If you enable change data capture, put a DDL trigger so nobody can “accidentally” drop it.
  • If you add a new column – CDC does not do anything. 2 solutions: the MS solution, and the @AdamMachanic solution (ie, manipulate the CDC tables)
  • CDC or CT? Considerations are synchronicity, transactional impact, database impact
  • SQL audit is based on extended events. This can answer the question “Who did it?”
  • “An audit is a package where audit specifications are stored”. Inception?
  • TIP: Make sure you enable BOTH audit AND audit specification
  • Just because you can does not mean you should. ‘Nuff said.
  • SQL audit pitfall – auto parameterization of the engine. You dont see the original values anymore – already replaced with parameters
  • SQL audit pitfall – max 4000 characters. Original queries might get truncated.

It was a great, very informative session. Adam was inundated with questions all the way through, and he impressively was able to answer most of them!

By the way, you (yes, you the reader) should visit Adam’s blog and introduce yourself 🙂


My 10 seconds of fame, err, shameless plug. But honestly, I had stage fright 🙂

One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s