Shop Talk: 2021-01-18

The Recording

The Panelists

• Kevin Feasel
• Mala Mahadevan

Notes: Questions and Topics

Resource Groups in Azure

The first topic of discussion was around the proper scope of resource groups. Here’s my thought on it:

My general philosophy is that a single resource group contains the whole set of things you’d destroy when it’s time to decommission something.  From that perspective, it’s easy to think of one application per resource group:  as it’s time to tear down an application, you delete the resource group and you’re done.  Even with a hundred applications, I don’t think I’d be too upset about one resource group per set of application services if they’re independent applications.
That said, there are services which go across several applications.  For example, a data warehouse or data lake might be used in several applications as a data source.  Those core services could also get their own resource groups, but that comes with the downside risk that if all of the dependent users of the service get destroyed, the service might still be around, at which point it would be wasting money.  So there’d have to be some sort of architectural mapping across application/resource group boundaries to keep track of dependencies.  The list of resource groups itself would no longer be sufficient to provide all of this information.
Another thought would be, how independent are these applications?  If they’re effectively independent microservices which communicate via expected protocols (like HTTP or gRPC) rather than shared infrastructure, then one resource group per application can sound really viable.  But if some of the applications are tightly coupled together, it may make more sense to put them all into the same resource group, especially if there is a considerable overlap.  Suppose for example that five applications all use the same SQL Server VM to store data, that there are cross-database queries galore, and that each app is dependent on the others for survival–that if you got rid of the other four, any individual one would not be particularly useful.  In that case, it could make more sense to put that cluster of applications into a single resource group.
A related thought to the prior is, how much resource overlap is there?  Do all of the apps use the same Azure App Services?  Are you sharing one Azure Container Repository for everything?  Is there one single Azure Synapse Analytics workspace across all of the applications?  Or maybe one Azure Function app?  The more interconnected services exist, the less useful “one resource group per application” works in practice.  That said, there will probably be some overlap automatically–they may all use the same Azure DevOps implementation and store all of their code in the same GitHub account (though different repositories).  The fact that there is some overlap isn’t necessarily contradictory; it’s the level of overlap which counts.

Solarwinds and Security

@lisanke chimed in with a really good question:

does anyone have or work for an organization that bought Solarwinds and caught a sunburst? Or does anyone buy a single source security mechanism for their Org? Do you now worry that u’ve put all your security eggs into one basket? np if this is too big for an add-on discussion tonight… maybe something to ask for a future talk. Or more general, supply-chain bugs.

This led to a lengthy discussion around risk.

Availability Group Failover Causes

@vijayb5 has a good question as well:

what approach you would think will guide us to determine root cause to find failover of alwayson availability group i have used Failover Detection Utility most of the time it never give proper reason other than showing the possible error around that time. i am interested to find what caused the error.

This is one that I tried to answer, but we’re bouncing it off of Tracy for a better answer in the next episode.

Upgrades and Server Names

Chris Wood hits us with a question, too:

My upgrade problem. I have a SQL2019 CU4 server running on Windows 2019 that had the server renamed. The appropriate drop servername and add servername was run but the registry wasn’t changed to indicate the current servername

When I ran the CU8 upgrade it throw a 574 error running msdb110_upgrade.sql against master and master wasn’t recoverable. We had disk snapshots and recovered to another server. I had heard of using Trace Flag 902 to stop the master being upgraded. I would like to try again sometime but I am now unsure what to do. This is the first major failure in about 20 years of SQL upgrades

In this case, it sounds like there’s an issue with running an upgrade script for SQL Agent. I was working from the assumption that it’s related to the server name change, though Solomon cautions that this isn’t necessarily the case. In any event, Mala’s recommendation on this is to contact Microsoft support. Mine is to try to convince the customer to install on a new server rather than upgrading in-place.

PolyBase and Pushdown

I ended the episode with an issue that a person e-mailed me about. It shows just how difficult it can be to get PolyBase to perform well when running SQL Server to SQL Server. The short version is that a particular query can work fine when returning a result set, but as soon as we try to save the result as a variable, performance tanks due to a predicate not being pushed down to the remote server. It’s a weird one and I’ll have a proper blog post about it someday.