Azure SQL DB Extended Events and Cross-Tenancy

Creating an Extended Events session (as well as viewing events) in Azure SQL Database is slightly different than a typical SQL Server. Since you don’t have access to the file system of the server where your database live, you need to configure a storage account target for persistence of your extended event sessions. You can write them to the ring buffer, but since you do not have the ability to “view live events” in SQL Server Management Studio, this is of limited benefit. You read about what you need to do in docs here, but in a nutshell it’s create a storage account (or use an existing one) create a database scoped credential so you can use the storage account, and then create the xEvents session.

The reason why I’m writing this post is that there is a bit of a bug here that’s not fully documented. Many of us (especially those of us who are consultants) work across the scope of Azure Active Directory tenants. What that means is joeyd@dcac.com might manage a database in the contoso.com Azure AD tenant while still being logged in with the joeyd@dcac.com identity. Normally, this isn’t an issue but there are a couple of places where some odd things happen with cross-tenancy. When you try to create a credential in your database, you will receive the following error, even if you are the database owner.

blue white orange and brown container van
Photo by Pixabay on Pexels.com


Started executing query at Line 1

Msg 2760, Level 16, State 1, Line 1
The specified schema name "joeyd@dcac.com" either does not exist or you do not have permission to use it.

Total execution time: 00:00:00.195

You should note the rapid execution time of that error–this isn’t failing when going out to a storage account to validate the credential, the code is failing in the database. I posted something about this to the Microsoft MVP DL and the ever brilliant Simon Sabin emailed me and suggested that I try to create a schema called joeyd@dcac.com and then create the credential. Sure enough–that worked fine and I could proceed. In the customer system where this happened, we were fortunate enough to have global admin rights in AAD, and just created a new user in their subscription, and used it.

Contact the Author | Contact DCAC

A Viable Future for PASS

I have received feedback that some folks think I just want to burn PASS down, or that I don’t want a for profit company involved with a community organization. Neither of those things are remotely what I’m thinking—I’ve only been loud and writing about it here, because I want PASS to survive, which is going to be near impossible with a loss of its main revenue source (in-person PASS Summit) and its expenses (C&C) which haven’t dropped nearly enough in the face of the aforementioned revenue loss. What do I see as a future for PASS?

Summit

Virtual Summit is going to happen in 2020, it’s also probably going to lose money. It’s effectively a sunk cost at this point, so I’m not going to waste any time talking about that. In 2021, PASS has a tough decision to make—large international conferences are unlikely to be a thing until 2022, when the covid vaccine has been broadly distributed. Planning a virtual conference in 2021 is risky as well, given that most of the competition is free. I think doing a low cost (and lower overhead) smaller scale event using a much cheaper platform like Microsoft Teams or even GoToWebinar would be a good small bet, without much risk. I also think a small conference the size of the old SQL Rally (a few hundred people and run in a hotel, not a conference center) could be viable for Q4 of 2021.

The reason for doing this would be an effort to try to keep the fundamental networking aspect of PASS going, while reducing financial risk. The original SQL Rally was a community organized event—by keeping it small, you not only reduce costs, but you also reduce the time to plan, which allows you to have a better assessment of the pandemic situation. PASS could also think about leveraging larger SQL Saturdays like Atlanta and Dallas, amongst others to be candidates for a Rally, as these events have community organizers who are very experienced at running larger scale events.

The Managing Organization

I’ve said what I’m going to say about C&C, but it’s very clear that PASS as an organization is untenable with its current cashflow situation. This means costs need to be drastically cut wherever possible. PASS won’t be in the business of planning large in person conferences until 2022, and therefore doesn’t require a large event management firm dedicated to its management. I would recommend hiring a full-time executive director (yes, I know I said we need to reduce spending) to manage the organization and manage vendor relationships. C&C currently has a seat on the on the PASS exec board with a title of Executive Director, which is a conflict of interest, and I would propose ending that immediately. The Executive Director role needs to be someone who understands both data and analytics and building communities. Finding this person will be a challenge, but I believe they are out there. I would also move to stop using the custom developed platforms PASS is using and move to using Software as a Service platforms where possible. Sessionize is probably the most obvious solution here, but there are others.

The Role of the Board

As I was reading the by-laws and guidance for the PASS BoD, I came across this paragraph.

Role of PASS Board Members

PASS Board members are the fiduciaries who steer the organization towards a sustainable future by adopting sound, ethical, and legal governance and financial management policies, as well as by making sure the organization has adequate resources to advance its mission. They are responsible for the short- and long-term strategic direction of the PASS organization.

Beyond this, what PASS needs from the Board is perspective and expertise. Perspective and expertise as a data professional, PASS community member, from their region, or from drawing on other relevant experience (i.e. speaker, business leader, other Board experience).

What PASS does not need from the Board is tactical execution or day to day management of organizational activities.

The challenge is to do this while also providing focus for the organization. Focus to drive the evolution of the organization and to prevent distraction from impeding momentum.

“What PASS does not need from the Board is tactical execution or day to day management of organizational activities”—I can’t imagine running a SQL Saturday and completely outsourcing everything to a third party—I feel the same way about our community organization, especially in this time of crisis. I think this is completely wrong, and the main reason why PASS is in the situation it is right now. The Board of Directors needs to take an active role in managing the organization, period. We, as a community organization are in a situation where the organization might go bankrupt and die, and while this is largely due to a black swan event (the pandemic), it is also due to decisions made that are in the interest of the managing firm, and not the community. When I was heavily involved in running my PASS chapter, I had a board member, who’s portfolio was chapters, who took an active interest in the chapters, and their needs and worked his tail off to make things better. Unfortunately, he was not re-elected, and things never got any better from there.

The board needs to take an active role—while the day to day operations of the org, would be managed by the executive director, and eventually some administrative staff, in a time where the organization needs to be austere with its spending, being on the board should require you to get your hands dirty. I would also try to involve the community—there are lots of projects, that over time could have been open sourced, but there has always been push back from the board. Given the success of community managed projects like DBATools, I see no reason to not engage volunteers who are willing to help, especially on community facing projects.

I’ve been involved in PASS for nearly 15 years now—I want it to survive, because having a centralized community organization is a good thing and makes the community stronger. The central organization also provides governance and helps with sponsors. PASS cannot survive financially in its current state, and we as a community must band together to help it survive and foster the changes to make it a sustainable organization. While we are doing that, we can make it a better community org.

Contact the Author | Contact DCAC

Why I’m Not Speaking at PASS Summit and You Shouldn’t Either

If you saw any of my angry tweets last night, it’s not just because the Saints weren’t good. I’ve been writing a lot about PASS and C&C the for-profit event management firm that runs virtually all of PASS’ operation. I personally think C&C imposes a financial burden on the Microsoft Data Platform community that will ultimately kill PASS. I want to run for the board of directors (once you agree to run for the board you have to agree not to speak or write poorly of PASS, but it doesn’t say anything about C&C) to try to return PASS to being a community oriented organization. PASS has been a great organization and the connections I have made have been a great foundation for the career success that myself and many others have achieved. The reason I agreed to speak at PASS Summit this year was to help enable the organization’s survival, despite my lasting frustrations with C&C.

PASS had a couple of options for doing PASS Summit virtually, and they’ve failed at every turn. The best option would have been to do a super low-cost virtual summit, using Microsoft Teams, and tried to keep the pricing at level the average DBA could pay out of pocket. This big reduction in revenue is bad for C&C’s business, but frankly given that there likely won’t be a big conference until 2022, C&C should be operating on an austerity budget, since PASS’ main income source has been severely constrained.

The Burden on Speakers

I’ve lost count of how many webinars I’ve done this year—it’s been a lot. 98% have been live—in some cases with some really dicey demos, like I did at Eight KB.  Doing a webinar or a user group meeting is a decent amount of effort, but no more than doing in-person session. However, PASS Summit has asked speakers to record their sessions—recording a session takes me at a minimum 2-3x the amount of time to execute than to simply deliver a session. Setting up cameras, lighting, and doing small amounts of editing all add up to considerable amounts of time. Additionally, you have to render the video and then upload it to the site. I say this with experience, because I just recorded three sessions for SQLBits.

You might ask why I was willing to record sessions for Bits, but not PASS Summit. That’s a good question—SQLBits is truly a community run event, for the community, by the community. Sure it can be rough around the edges, but it’s a great event, and in general the conference is great to work with. Additionally, SQLBits always pays for speaker’s hotel rooms, it’s nominal in the cost of an international trip, but it’s something that makes you feel wanted as a speaker and I remember it. PASS Summit, unless you have a preconference session (precon) doesn’t offer any renumeration at all to speakers, nor have they ever. All that being said, after recording my Bits sessions, I said “I’m never doing that for free again”. In addition after doing the work for your session, you have to show up and do Q&A for your session.

Why You Shouldn’t Speak at PASS Summit (and TimeZones are hard)

PASS has asked speakers to record their sessions just six weeks before the conference. These recordings will only ever be seen by paid attendees of the conference, and possibly PASS Pro members. Speakers received a highly confusing email informing them of this late last night, which included the time and date of their sessions. It wasn’t clear if “live sessions” still needed to be recorded—which is even more confusing to speakers. Speakers weren’t consulted about the need to record their sessions when the revised speaker agreement went out. This burden has been imposed at the last minute. I haven’t gotten any official communications since July when I received my speaker code. It’s not fair to impose this on speaker’s this late in the process, especially when you aren’t compensating them for their time.  Also, this is insignificant, but we were supposed to get the slide template in July, and it’s still not in my inbox. I’ve have no communications from PASS about Summit since July.

Precons are all starting in the speaker’s native time zone, which will limit the audience for many precon speakers—European speakers are starting at early a 3 AM EST, which means basically no one in North America (PASS’ main market). Most regular conference sessions are 8-5 PM EST—which probably is a decent compromise, but still greatly limits the west coast in the morning and other regions of the world like Asia. There are some evening and overnight sessions but those are extremely limited compared to EST business hour sessions. All schedules for a worldwide event are going to be a compromise, but I feel like some creativity could have been used to better support a virtual audience. For example, Ignite has replays of all its sessions available for broader time zone coverage.  As far as I know, no speakers were consulted during the making of this schedule.  

Doesn’t This Hurt the Community?

A successful PASS Summit is a good thing for the community. However, with the poor management of C&C, the marketing for the event has been poor, and with most other events either going to free or freemium models, PASS continues to charge a premium for the event. The platform that PASS is using hasn’t been demoed to speakers or attendees, to show how it would have value over a free conference like EightKB or Ignite.

I’m not going to speak at PASS Summit. I’m going to record my session, and put it on YouTube, so everyone can watch the session. And I’ll do a live Q&A to talk about it—it’s a really cool session about a project I’ve worked on to aggregate query store data across multiple databases. I challenge other speakers to follow me—the conference is so bad and so expensive, because C&C is trying to prop itself up on the back of the community. C&C needs to go away before we can move forward. I was frustrated before, but this Summit fiasco has really pushed me over the top.

Contact the Author | Contact DCAC

SQL Server Agent is Some Solid Code

SQL Server Agent is probably one of the least loved, but most used components in Microsoft SQL Server today.

Image by www.gotcredit.com

As applications go it hasn’t really gotten any sort of upgrade in probably 20 years, at least I can’t really think of any changes since SQL 2005 was released, maybe even SQL 2000. And that does show just how solid it really is.

The SQL Server Agent just sits there and does its job without any real love or attention. That really is a testament to the work that was done on the SQL Server Agent back in the day that nothing has really needed to be done to it in the last 15-20 years. So for this I say, “Well done Product Team, well done”.

It can be used for job scheduling, alert notification when resources utilization is high, etc.

Denny

Contact the Author | Contact DCAC
1 2 3 179

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers