My teammate Meagan (b|t) messaged me in Teams yesterday afternoon to say “Joey, the client created a new database using your automated process, and my ETL user (which is a AAD user) didn’t get created, can you fix it?” Well, after a quick perusal of emails I remembered that I had the asked the client to add the create user process to their initial population process which hadn’t occurred yet. The reason why I did this was that creating an Azure Active Directory user in an Azure SQL Database from Azure Automation was painful and maybe not even possible. However, I pinged Rob Sewell (b|t) about the best way to do that. This sounded not that bad to do, but I managed to hit land mines around every corner.

The First Problem

Azure Automation is mostly PowerShell only—there is a Python option, but I’ve never used it, and I’m not going to start now. The trick with PowerShell is that it’s great for things you have to do to Azure Resources, it’s far less good for things you have to do inside of databases (think creating a user). I typically use the invoke-sqlcmd cmdlet, however we have a chicken and egg problem—I can’t create an AAD user from a SQL connection (a connection made using a SQL login) and invoke-sqlcmd doesn’t support authenticating with AAD. The Azure Automation service allows you to import 3^rd party soluitons from the PowerShell gallery, so you can use DBATools which I did here. Rob has an excellent blog post here that describes this process.

The code, which I happily stole from Rob’s blog allows me to connect as a service principal. To easily facilitate this I made my automation account part of my DBA group (the Azure AD Admin group for the Azure SQL Server), which you can assign without this ridiculous process. I threatened to add Meagan’s ETL user to that group, but she was going to out me on Twitter.

After running that code I could connect to Automation run as account to my Azure SQL DB, but my query was failing with the following error:

I’m logged as a service principal there—hence the weird GUID, you can see that I have basically every privilege in SQL Server, but I can’t create a user from an external provider. PowerShell (and automation) say that the user could not be resolved.

The Next Land Mine

So I DMed Rob, and asked him WTF? It turns out for this to work, you need to create a service principal for your Azure SQL Database. If you aren’t familiar with service principals they are analogous to service accounts in an on-premises world. Doing this was the easiest step in the process—I have a PoSH script to hit every server in my subscription, and it was trivial to add a service principal as well as add to my database runbook. However, that was just the first part.

You have to give the service principal the “directory reader” permission in Azure AD, and the effective way to do this with Automation is to assign that privilege to a group. Well, it turns out adding AAD roles to group is a relatively new feature (it’s in preview) and more importantly requires P1 or P2 Azure Active Directory which has a per user cost. Which meant I needed to get approval. After much chatter on a DCAC teams channel I discovered since this feature was not user-assigned (e.g. it’s enabled for the entire AAD tenant once it’s enabled) I only had to have one AAD license in the tenant (I assigned it to Meagan). Once that was in place, I could grant the directory permission to the SQL Server Service Principals group.

Are We Done Yet?

I should have noticed in the documentation provided by the SQL team assigning groups with PowerShell, that there was a reference to the preview PowerShell module for Azure AD (I did, but I didn’t think it mattered because I was just assigning a user to a group). So I thought I had everything wired up when I started getting the following error:

I have Global Admin and Subscription owner in the two environments I was testing in, so clearly this wasn’t a permissions issue. To further prove that point, I was able to add the service accounts I had created to the group through the Azure portal. So after writing like three emails with my further discoveries to the Azure MVP distribution list (I could add the service principal to a regular group, just not one with a role assigned to it). I went back and decided to play with that preview module.

Everything up to this point is me being an idiot, but I’m going to yell at Microsoft for a second. I couldn’t install the azureadpreview on my Mac because its dependent on Winforms—I thought Az modules were all supposed to be built on .NET core. I also couldn’t get it to run in cloud shell, which may be related to the Winforms thing, or not.

I do have a Windows VM, so I installed the module there, and it successfully worked on the DCAC tenant.  I went to Azure Automation to install the module. If you’ve never imported a module into Azure Automation, you should know that the portal notification about a module import being complete is meaningless, because Azure Automation is a lying liar who lies.

Look on the modules page and hit refresh a lot. It usually takes 1-2 minutes for a module to import. I messaged Kerry in Teams.

And what do you know? It worked. I was concerned and about ready to murder someone, but it worked. Rob’s code is really helpful and he covers key vault in his post. I did have some open GUIDs in some of my code pictures, it’s cool those aren’t sensitive. However, you should store all your secrets in Key Vault as it’s fast and easy.

The other thing I learned in this process is that you can now make a guest user you Azure Active Directory Admin (this means I could make joey@dcac.com or jdanton1@yahoo.com an admin in the joeydantoni.com tenant), which you weren’t able to do before. Prior to this you could use a group and add a guest user to that group as I mentioned above. (Note: you should still use a group and not a single user as it’s best practice)

I wanted to write a post about the things I think PASS does well and are foundational to the future with or without the PASS organization. Before I address that however, I want to speak to an ethical matter related to the new PASS Pro service offering. Every year for PASS Summit, speakers sign an agreement (which if you sign, print it out—they don’t email you a copy, nor can you access prior year’s agreements in the speaker portal) that includes giving PASS the right to the recording of your session. This has always been there, so that PASS could sell online or USB access to session recordings. A few years ago, PASS started selling this access to people who didn’t attend the PASS Summit, which I was fine with—the context of that is still the conference.

With the PASS Pro offering, PASS took all of the sessions that community and Microsoft speakers did in Summit 2019 and bundled it as part of a paywalled offering. Speakers were not asked for their permission to reuse this content as part of a for-profit subscription model, nor were they compensated. Which in both cases is probably legal? However, I strongly question the ethics of this—when a community event morphs into a paid service, most speakers (myself included) want a cut of take, which is only fair as it’s a lot of work to speak at a conference. PASS will say that “all of this money is going back to the community”, which is frankly bullshit. First, the platform they are hosting PASS Pro on is not free, and then there is whatever C&C is charging PASS for this project, which is likely significant. As I’ve mentioned before, the community typically sees about 5% of PASS’ revenue, and the revenue numbers for PASS Pro make that an absolute pittance, while potentially alienating community members. This makes me think hard about speaking at future PASS events.

Stop Bitching, What Did You Really Want to Write About?

The SQL Server community has been part of my life for last 12 plus years—its why I have my current job, have most of my professional network, and why I’ve spoken all over the world. PASS motto is “Connect, Share, Learn” and I think it is a good one and should be the goal of any community organizations.  Let’s talk about the things that make up that community:

• Conferences
• SQL Saturday Events
• Virtual Chapters
• User Groups

Conferences

Having a centralized community organization like PASS has some benefits. The biggest benefit is the ability to have PASS Summit, which is a mostly independent community organized conference that allows us to have a deep dive on Microsoft Data Platform (sorry to use a marketing phrase, but it’s a good description of what the topics covered are) over a week’s time. If you haven’t attended a vendor conference like Microsoft Ignite, it’s a very different experience compared to PASS Summit. The sessions are more marketing driven, and while you have excellent access to Microsoft engineering staff, you aren’t going to have precons on deep dives into topics like high availability and query optimization, and you won’t see nearly as many community speakers offering real-world perspective.

Having a big conference is a good thing, and it’s something PASS has done fairly well and would be a major loss if PASS were to fail. Running a big conference is expensive and hard, and would likely only happen with vendor support, or over the period of several years from a smaller conference. This is a US centric view, as SQLBits and Data Platform Summit in India have been running pretty large scale conferences for several years. However,

SQL Saturday Events

SQL Saturdays are awesome—they provide a great starting point for a new speaker. There’s even less pressure than a user group, because your attendees are typically divided between a few tracks. I also have a fondness in my heart for them, as they are where I grew my career and gained organization skills by running a successful event for several years. However, they don’t need PASS to be a success. PASS in recent years has deemphasized SQL Saturday because of a flawed notion that they were cannibalizing Summit attendance (this may be true on the margins, to which I would say, make Summit better). While having a centralized infrastructure for these events is nice, the website is an absolute trainwreck, and should probably be refactored. Numerous SQL Saturdays in Europe have become independent events, without issue—sites like Sessionize make it really easy to run an event. I foresee a little bit of a lull, but these events can run well without a centralized org—just look at Code Camp.

User Groups

Even moreso than SQL Saturdays I do not see the loss of a centralized org having any impact on user group meetings. In recent years, the only service PASS has offered user groups is web hosting (and some funding tied to Summit discount codes, a program which has gotten more limited over time). User Groups, by their nature are fairly independent entities. I look forward to speaking at any UG post covid—having free pizza and meeting a bunch of data pros is always a good time.

Virtual Chapters

As you may have noted in reading my blog, I tend to be cynical about PASS C&C. However, in researching for this post, I noted that (as of Monday) PASS Virtual Chapters have provided 173 hours of free training in 2020. Gathering data is a bit arduous, so I didn’t capture previous year’s data, but I was really impressed at the amount of training in one place. There are other virtual events (especially this year) but having a central place and organization is real benefit to an organization.

What Does All of This Mean?

This means PASS’ core competencies are running Summit, and Virtual Chapters. (wow, I feel like that was the most MBA thing I’ve ever written). Any organization going forward needs to have those structures in place. Summit provides a revenue source, that can allow everything else to proceed.  It also means trying to provide a paid online training and certification service lies outside of its competencies and shouldn’t continue.

However, the challenge PASS faces (and is ultimately tied to its survival) is that Summit is not going to have the same level of revenue for at least the next two years, and expenses haven’t dropped significantly. In recent years I’ve heard a common refrain from board members—PASS Summit was “too big for a small event management company, and not big enough for a large event company”. Since PASS Summit is going to be smaller at least in the medium term, perhaps now is the time for change to save the organization.

I’d welcome comments on anything I missed or your thoughts on what your most meaningful community experiences have been.

One of the only things platform as a service databases like Azure SQL Database do not do for you is actively manage column and index statistics. While backups, patches, and even integrity checks are built into the platform services, managing your metadata is not. Since Azure SQL Database lacks a SQL Sever Agent for scheduling, you have to use an alternative for job scheduling. One of the more common approaches is to use Azure Automation runbooks which are PowerShell (or Python) jobs that are run from within Azure. I like to call Automation “cron for Azure”. My good friend Erin Stellato (b|t) just recorded a YouTube video that walks through the process of setting up an Azure Automation runbook for stats maintenance. There are a lot of steps to get your runbook up and running, and if I had a very small environment, I might just recommend using an Azure Logic App, with a schedule trigger–for a handful of databases, you could be up and running in a few minutes.

However in my case I’m working on a customer subscription, and I need to have my solution automatically deal with all of the databases in their subscription. My customer has a data as a service model, and has been using the “serverless” tier of Azure SQL Database in order to reduce their compute costs. The serverless tier is effectively auto-close (and auto-scale) for Azure SQL Database, which means the first time you attempt to connect to the gateway (the gateway is what you actually connect to–yourdatabase.database.windows.net is a public IP that in turn connects to your actual database(s). When you connect to that gateway, Azure will begin the process of turning on your database–this can take up to 45 seconds, which means the first connection will most likely fail.

I was running into failures, so I looked at my code, and made some minor modifications.

{
$svr=(get-AzSqlServer -ResourceGroupName $rgs).ServerName
#write-host 'rg:'$rgs
foreach ($svrs in $svr)
{
$sql=$svrs+'.database.windows.net'
write-host $sql
$d =Get-azSqlDatabase -ResourceGroupName $rgs -ServerName ` $svrs|Where-Object {$_.DatabaseName -NE 'master'}
$db = $d.DatabaseName
$servicetier = $d.RequestedServiceObjectiveName
$size=$d.maxSizeBytes

if ([string]::IsNullOrEmpty($db) -eq $false)
{
$Params = @{
'ServerInstance' = "$sql";
'Database' = "$db";
'Username' = "$adminlogin";
'Password' = "$pwd";
'Query' = "$query";
}

# write-host $Params.Query
Invoke-Sqlcmd @params

Start-Sleep -Seconds 45
$query = "EXEC dbo.usp_AdaptiveIndexDefrag;"


$Params = @{
'ServerInstance' = "$sql";
'Database' = "$db";
'Username' = "$adminlogin";
'Password' = "$pwd";
'Query' = "$query";
}
#


Invoke-Sqlcmd @params

}

}

In this case, I’ve made my first query select @@servername, but you can have any query issued–it’s going to fail anyway. I then add a sleep command in PowerShell, I’m using 45 seconds, but you could probably drop that to 30 seconds if you have a tight time window. I’m then using the AdaptiveIndexDefrag script that Pedro Lopes (t) has written.

Let’s talk through the current situation PASS faces. Your business has had its main revenue source devastated by a raging pandemic. To raise revenue from other sources you plan to announce a new online learning and networking service. Except your website is down for five hours on the morning of the rollout, and even after that, there’s still no mention of the new service on the front page of the site. It seems like the term “botched rollout” is used quite frequently when it comes to our community organization, more often than not in fact. In normal times, we can laugh, but when the organization is facing financial peril, execution and decision making are absolutely critical. In this post, I’ll talk about PASS, their new service offering PASS Pro, and my vision for saving the organization.

Let’s Just Talk About Economics

Before I talk about my opinions on the Pro membership offering and how it could work, let’s talk about basic economics of the PASS organization. PASS’ major source of revenue is Summit—in 2018 revenue was about $9MM USD, and Summit made up about $8.9MM of that revenue (thanks to Steph Locke for her excellent research into the budget). Summit isn’t happening in 2020—the conference is going virtual. However, given the reality of a global vaccine distribution process and depressed business travel budgets, even if it happens in 2021, revenues will be likely be down. Which likely places PASS in the awkward situation of trying to run a conference that may have costs which significantly exceed revenue.

Focusing on 2020 for now, PASS Summit is virtual, as are VMWorld, AWS reInvent and Microsoft Ignite. Ignite and reInvent, which are typically around the same cost of the physical Summit, is free as in beer, and VMWare is using a freemium model which only costs $299. Virtual PASS Summit costs $599, or $999 with precons. I’m not pointing out these differences in cost to be petty—those other conferences are subsidized by the vendors who run them, and you won’t get the deep level of database content you’ll see at PASS Summit. However, those other conferences present headwinds that could slow the number of attendees that attend virtual PASS Summit. If PASS were to have 2000 attendees that attended precons, they would only have $2MM of revenue. I think having 2000 attendees is probably optimistic given Zoom fatigue, the fact that other events are free, and the depressed economy, but we’ll run with that number.

The Other Problem

As Steph mentions in her post, and I’ve mentioned before, C&C is a for-profit consulting firm that performs event management and community management services for PASS. There is no meaninful difference between PASS and C&C. PASS Board members have stated that C&C has hired staff just to work with PASS full-time (which brings into question labor laws about contractor status both in the US and Canada, but I’ll leave that to lawyers). In fact in that same post, C&C is referred to as PASS HQ. C&C, in a typical year, charges PASS around $4MM in services. You should also note that there are no PASS board meetings that happen without C&C representation. C&C has a non-voting seat on the PASS board, in the form of their CEO, Judy Christensen.

For those of you who are math majors, or are Business Intelligence specialists, you’ll note that $2MM Revenue <  $4MM Expenses. PASS does have some cash reserves, but they aren’t sustainable forever, and frankly why should our community continue to subsidize a private firm as we are losing money. The search for other revenue sources is a good idea, but is six months late, and millions of dollars short.

PASS Pro Membership

Yesterday PASS announced a new Pro Membership model. I know a lot of work went into this effort, over the course of many months and the mission is noble. I have colleagues who are developing excellent content for the platform that I think is quite good. However, there are a few problems. First, I know of a few community members who in recent months who were asked to host community networking events without being told these events were associated with a paid service. As someone who speaks and writes for part of his living, I’m fine with doing something for free to help a community organization (I’m receiving no compensation for speaking at PASS Summit), but when something is to purely support a for-profit effort (even community leaders have to pay for access to the new offering), to quote a wise man, “#$%@ you, pay me”. The bigger ethical issue is these community members were not informed that these “Community Connects” events were for a paid service. This changes a community organization into a for-profit business (which is mainly what it was before, however this makes it explicitly clear).

Beyond those issues, I just can’t see this working as a successful business (and I hope I’m wrong), PASS Pro is both on price and mostly business model competing with Pluralsight and LinkedIn Learning (formerly Lynda, disclosure: I have training videos on LinkedIn Learning and DCAC is compensated for them). PASS also faces competition from other folks in the DBA space who have their own successful private training services. However, those organizations have a wide base of training offerings, and a steady pipeline of content, and more importantly are both public companies that large coffers to help them pay content creators to build a continuous pipeline of content. While, PASS is paying content creators for the new service, this model is likely unsustainable over time without major capital investment to fund content creation.

With all that being said, let’s just run some numbers. While PASS always talks of the 350,000 people on their email list, that number is just a dream. The data quality is awful, and everyone who is involved with the community knows that the actual number is an order of magnitude less. I’m going to be generous as say PASS has 50,000 active members. (I personally think the number is closer to 10-15k). If they get a 20% adoption at $120/year, that represents revenue of $1.2 million/year. This would be a significant revenue enhancement, however in a crowded marketplace, I just don’t see 10,000 people signing up for the service, nor do I have faith in C&C to build a service that’s attractive to a broad audience. In my decade plus long of working with PASS, while Summit has been successfully run, nothing else has.

To the Botched Rollout

The website going down on the morning of the launch was bad luck. However, it’s part of many systemic failures that we’ve seen throughout the years of C&C’s leadership of PASS. Computers break, but not having monitoring and automation in place to repair your website is just poor form for an IT professional organization. However, the bigger failure is that PASS hasn’t clearly defined what the benefits of the Pro membership are. From the blog post the following benefits are defined:

1. Download access to PASS Summit 2019 Learning Pathways
2. Exclusive partner discounts
3. PASS Community Connects networking sessions
4. A PASS Pro Member badge

I received a copy of the email sent to community leaders announcing this service—there are a number of “we aren’t sure yet” or “this service is new and we’re figuring things out” in the email that tells me the business model and the service were not fully thought out.There’s also mention of new features including an educational series, but no clear definition of what that is going to look like, and a PASS Training Record (which is based on surveys I’ve taken, probably leading into a certification of some kind, which is exceptionally hard and expensive to execute well, especially in a cloud world where exams need revisions every few months). This is a pretty limited set of benefits compared to Pluralsight, which already has a fully featured offering, with certification preparation and an excellent website/mobile experience.

I like the idea of diversifying revenue streams, however this offering is not fully baked, and in my opinion is unlikely to be successful. (I hope I’m wrong). Additionally, this offering was only introduced in a blog post, is not on the front page of pass.org, and as of 24 hours after the announcement, has not been emailed to members.

You Talk a Lot, What Would You Do?

If I were on the PASS board (and I’m thinking about running for the PASS board) I would do everything I could to reduce costs dramatically over the medium term. I would pursue termination of the contract with C&C, and probably look to downscale Summit in 2021, to be more at the level of a SQL Rally event. I just don’t see conference attendance returning to pre pandemic levels in 12-14 months. At that point, you are reducing costs to the level of hosting the websites for SQL Saturday and Virtual Chapters, other properties. I start planning for a Summit in 2022, to be back at the level of 2019, and look at all other opportunities to reduce overhead. These are desperate times for the community org, and I care far more about preserving the organization than propping up a for-profit consulting firm.