If you fail to plan, then you plan to fail. But sometimes your plan sucks and you fail anyway. In these situations, a plan B is nice to have up your sleeve.

Glenn Berry has invited us to talk about our favourite feature in SQL 2022 for TSQL Tuesday this month and for me it’s all about the extra information in Query Plans and the prospect of getting some of my clients busier workloads experiencing the gains from Built-In Query Intelligence.

The query plan is the critical secret SQL sauce in delivering data back to a user as quickly as efficiently as possible. Well, nearly as quickly and efficiently. Before SQL 2022 various forms of black magic were involved in forcing compiled plans to perform consistently. The query optimizer didn’t plan to fail, but if the parameters passed weren’t what it expected it often would, sometimes horribly. In SQL 2022 we have the addition of a Plan B.

Built in query intelligence is a real gamechanger for consistent performance of query plans. And it all starts with a very little change that I haven’t actually heard mentioned too much:

Query store turned on by default

The Query store is incredibly powerful, and simultaneously surprisingly underused in the wild. This is mostly because by default it’s not on, but that changes in SQL 2022. The simple fact that, as a consultant I can come to a new server and already have Query Store available means that the information I can feed back to a client before making any change in the environment is greatly enhanced.

And then the planning got better.

The classic parameter sniffing scenario starts with either a cached plan for an atypical portion of a data set, or of a dataset that represents very disparate usage scenario’s. A classic example would be a user data set with a mix of users from large and small towns and cities. The typical usage may reference users in Capital-City but the first execution is run against a user from Little-Shite-On-The-Mire and we get a seek cached instead of a scan. All subsequent queries run with that plan and server performance is abysmal.

The ability to have a plan cached for both scenario’s is huge and is an example of my favourite type of performance enhancement in SQL Server. You don’t need to turn stuff on or off, you don’t need to change code, you don’t need to modify user behaviour and the overhead is small\insignificant. Just upgrade to SQL 2022, set your compatibility mode correctly and stuff will run faster and more consistently. I love when discussing SQL upgrades with clients being able to discuss enhancements in the way queries are processed because it really does give a compelling reason for them to stay current when they don’t have huge resource to push into configuring and maintaining new features, but just want to know SQL will keep doing what it does, only better.

Add in enhancements to reading from Availability group replicas, hints in query stores, Cardinality Estimator Feedback in plans, and feedback and learnings being persisted between failovers and restarts and I’m really looking forward to getting some of the heavier workloads running against SQL 2022 and using the new toys we’ve been given.

The prospect of Microsofts upcoming deprecation of basic authentication has lead to a bunch of questions around what impact it will have for people using database mail. I thought I’d take a moment to record my understanding in the hope it saves some people out there some stress.

Database Mail should not be effected by this change. It uses SMTP authentication which Microsoft has explicitly stated will not be turned off.

Microsofts exact wording is “Please note this change does not affect SMTP AUTH – we will continue supporting Basic Authentication for the time being.  There is a huge number of devices and appliances that use SMTP for sending mail, and so we’re not including SMTP in this change – though we are working on ways to further secure SMTP AUTH and we’ll share more on that in due course.” which is pretty clear. The confusion I think comes from people who work with databases having no idea how to differentiate what is SMTP auth(Using basic authentication) and what is Basic Authentication.

Another thing to be aware of is that some of this work has already happened and not all of it will happen all at once on 1st October. By default basic authentication is not available in new tenancies and SMTP authentication is disabled. For existing tenancies basic authentication has been disabled if it is not detected as in use, and finally the change is slated to happen “from October 1st”. What that means is that October 1st is not necessarily a drop dead date and things might continue to operate after that depending how far down the change list you are. This is an important distinction for people planning to check that everything is working on October 1st-2nd(A weekend) to judge whether they have covered all the bases. Unfortunately that will not work as a strategy, you should have everything sorted by October 1st, but changes may not hit your tenancy until later in the month.

Many, many years ago, I started a blog. It was mostly so I could write some bits of code and findings down because I have a memory like a gold fish and I figured having a record of some of the issues I encountered, and the ways they were overcome might be useful. I thought maybe it was worth sharing this with the world rather than keeping it to myself too, because you know….the SQL community is awesome about telling me the answers to my questions, and if jotting down a few things helps someone else out there solve the problem they are baffled by then…mission accomplished. Because I was a junior DBA when I started my blog, and because I had a habit of asking questions looking for a black and white answer and never received it, I also called my blog “It Depends”.

Many many years later Debra Melkin happened to choose “It Depends” as the title for her TSQL Tuesday blog invitation, and while I was a couple of weeks late to the party, I thought it was a good chance to revisit what is all to often a standard response to any question in the DBA world.

So way back in 2011 Paul Randal wrote a post that stated something along the lines of “The answer to every question in SQL consulting is ‘It Depends’, unless the question is ‘should I enable autoshrink?’ in which case the answer is ‘no‘.” What is so powerful about that post (and to be honest the SQLSkills approach to consulting in general) is that they don’t use “it depends” as a conversation ender – it’s a conversation starter. It turns a question searching for a black and white answer into a list of more targetted questions to find out what the right answer is for you, and for your particular base.

Despite actually naming my blog “It Depends” I try hard not to use the phrase itself, but I do find myself answering questions that could be answered with “It Depends” with 4-5 questions which allow me to give a targeted answer that fits the clients specific environment and acknowledges the business drivers rather than focusing on the specific technical answers to questions.

And with this in mind I (finally) get to my response to Debra’s invitation. She asks specifically about times someone asks a question about SQL which started off with an “It depends” redefinition of the question to find out what the thing they are actually asking about or trying to achieve is, and leads into a rant that may or may not be related. So here’s my rant.

“Your technical skills are not the point.”

This is both my biggest realization and biggest disappointment in my technical career. I think all people who reach the highest level in technical roles have to cross this bridge at some point. In general we cross the bridge with regret and more than a little reluctance, but the sooner we can cross it and get on with our career growth the better. You see for the first part of your career there is a massive wall of technical knowledge that we find ourselves battering against, constantly trying to get technically more and more proficient. But at some point, either independently or because someone higher up the food chain kindly takes us aside and explains it to us, we come to a realization that being technically brilliant is far less important than working our what it is that the business is trying to achieve.

Let’s use a question about availability groups as an example. “Would this work with availability groups?” is the type of question I see asked from time to time. The technical answer can be “yes” or “no” and too often that’s the answer I see given by junior technical people. And they are right, but the problem is they’ve answered the question presented to them instead of working out what question they should have been asked. Why are you asking me about availability groups? Do you want to report off this? Do you want it to be highly available? What is it that you actually want to achieve?

I see business people frustrated with IT slowing them down instead of empowering them. I see technical people frustrated with Salespeople selling things that can’t exist because there are technical limitations and I see developers cobbling together a solution that meets an immediate requirement but then hamstrings the performance of key business systems for years to come. And the reason for all these problems is usually the same. A business question is asked as a technical question, and the technical answer is misinterpreted as a business answer. In other words, people are speaking the language of their role, and no-one is translating.

That is why when you ask a junior if you can do something they will often say “Sure” and they’ll make a change and suddenly you can do the thing you wanted, but when you ask a more senior person the same thing you get met with a seeming wall of questions back.

IT systems are complex, and throwing one switch can often have a string of unexpected consequences. That’s why the more senior a person the more questions you’ll likely get asked before they take any action taken. But business systems can be even more complicated, and technical people need to be aware that ultimately they will not be judged on how perfectly a system is set up, but on how perfectly it meets business requirements and expectations.

I wish it wasn’t so. I wish when a business person asks me something they understood the technologies they were talking about and asked a correctly structured technical question. But the reality is that being the ‘translator’ is often the most critical role that can filled in any project. Getting the tech people to understand the business need and the business people to understand the technical possibilities is a skill that is both rare, and in the end very lucrative. Even if we wish it wasn’t so.

I struck an interesting problem this week when migrating a database. Apparently the database was my users default, which immediately kicked me out of the server and prevented me logging back in. This is the story of how it happened and the steps I needed to take in order to resolve it.

It wasn’t a dark and stormy night, but considering what was about to go down it probably should have been. Typical weather really.

I was migrating the final database off and old SQL 2012 server. It was the final database on this server which we began migrating databases off over 18 months ago, so I was pleased to see it go. The database itself was tiny, so my part of the migration was just to perform a backup restore migration to the new server. The full backup ran fine in a couple of seconds, but when I ran the tail-log backup it completed and I immediately got disconnected.

I thought that was odd, but proceeded with the restore on the new server which went fine. We were using a CNAME redirect to catch any connection strings we didn’t know about, so the old server was renamed as part of the process. I asked the server engineer to hold off his rename and CNAME steps while I investigated the disconnect.

I quickly found I couldn’t log in with either my own account or the service account our application connects to SQL. Both received the same error.

Cannot open user default database. Login Failed. Login failed for user ‘xx’.

So, we have provided support for this client for a while, but the server predates our companies support contract. However what it looked like to me was the group containing our logins had it’s default database set to the database I’d just taken offline. Annoying, but not insurmountable. I asked one of the client testers I knew had security permissions on the server to log in and flip our groups default database back to master. But she got the same error.

Things were suddenly looking dark. I quickly completed the migration steps on the target server and we did the server rename and CNAME switch to the testers could test the successful migration, but what should have been me sitting quietly for an hour while they completed testing quickly became a mad flurry of trying everything to get access into the old server should we require a rollback. Now I wasn’t too concerned, we did have a snapshot taken prior to the change that was our sledgehammer rollback, but I didn’t know what was going on and wanted to understand.

There was a simple fix I should have done at this point, which is just to change the database to master in my connection string. When I did that it failed. I don’t know why, and haven’t been able to recreate that failure, which leads me to think that when I changed the database it didn’t ‘stick’ and I only tried it once, assumed it failed and moved on to find another solution. A lot of time should have been saved here by doing that correctly. For the record that is on the options->connection properties screen in SSMS. All you need to do is type in ‘master’ as per below:

That should allow you to log in and update the default database of users who can’t log in. It also shows why trying the same thing twice is sometimes NOT the definition of insanity, because you may have done it wrong the first time, particularly if you are restoring databases in the other window.

At any rate – assuming the direct connect with the correct default database was still failing we tried and failed the following steps:

1. Access the server via “sa” – only to find the “sa” password was not recorded in the client password vault.
2. Access the server via various other Windows accounts – All failed with the same default database error.
3. Access the server via other SQL accounts – These succeeded but none had the credentials to add much to the troubleshooting or remediation.
4. Force admin access via single user restart – This also failed with the default database issue.
5. Force sysadmin access via DBATools. This is a really cool feature of DBATools, but in this case failed with an error that didn’t provide much context on the problem(I suspect it was the same default database login failure causing the issue).

So what we knew at this point was that all windows logins failed, while all SQL logins succeeded. I was beginning to suspect I knew the nature of what had happened. Some account that windows logins were a member of had been added with the default database set to the one I had just dropped. But how to prove it? And how to fix it?

I took the following steps:

1. Create a local windows user.
2. Add that user to the servers local administrator group
3. Restart SQL in single user mode with access limited to SQLCMD connections
4. Run my SQLCMD script to create a SQL login in the context of the new local windows account
5. Let out a huge sign of relief when that worked.
6. Restart the server in multi user
7. Log in with my shiny new SQL sysadmin account and start the troubleshooting.

Once I had sysadmin access back troubleshooting took only a few seconds. Sure enough the DOMAIN\USERS account had been added to the server. It had no specific rights on anything but had the default database set to the database we’d just taken offline. I flicked that across to “master” and everyone was immediately able to log back in again.

All of this took around 1/2 hour to work through, and ultimately didn’t have any impact on the migration except for a couple of minutes delay before I handed the database over for the rename. I was able to do my troubleshooting in parallel with the application testing, so ‘no harm, no foul’ right? Well, yes. But it could have been so much worse if this wasn’t the last database we shifted off the server. This was an incredibly dangerous setting. If we had moved this database first instead of last the simple act of taking it offline would have taken every other application on the server offline.

Hopefully, if you find yourself in the same situation the information in this post will save you some time both diagnosing and resolving the problem. I’ve definitely added checks for Domain\Users accounts into my pre-migration checks, and while I don’t ever expect to see this scenario again, if I do it’s nice to know the fix is 10 seconds and 2 clicks away on the login screen.

1. Be aware of the Domain Users group when making changes in a production environment.
2. When troubleshooting – keep notes of all the bits you tried – because you may make a solution way more complicated than it actually needs to be

I was patching an old SQL 2016 instance this afternoon and post patch the SQL Agent didn’t start. I took a config screenshot and flipped it over to local system and it started fine. Shouldn’t have been a problem right, because the NT SERVICE\SQLSERVERAGENT account is a managed account so you just leave the password blank? Of course it was not going to be so easy.

The SQL configuration manager would not accept a blank password – even though the account doesn’t need to authenticate(It’s a service!) the SQL configuration manager requires a password to start the sql agent and finalise the change. When I try to enter that it fails. Repeatedly.

This certainly sounds like a bug, but luckily it’s a SQL Server bug. Starting up services.msc I was able to change the account in the normal way – entering NT SERVICE\SQLSERVERAGENT as the user and leaving the password blank.

2021 has been a tough year and it’s been one where I have heard SQL Server, delivered as an on premise platform, referred to several times as a legacy technology. I guess nobody likes to hear the product they work on all day referred to as old, but I thought I’d use this post to get my thoughts in order around this so I have a clear response next time it gets casually dropped into a conversation.

The argument for SQL Server being dead?

The argument goes something like this. NoSQL databases started becoming popular in the late 2000’s and the promotion of them was around faster queries, huge ability to scale and querying vast pools of data a more simple exercise for developers. SQL databases were created last millennium with a focus on reducing duplication and adding structure to IT real estates that were already beginning to sprawl as computing systems were in their infancy and adoption was driven by immediate inhouse needs rather than a centrally planned and implemented strategy. SQL databases solved a number of problems, but the primary one was data-sprawl, or more specifically data being duplicated in multiple places, and the inefficiency and storage costs associated with that.

NoSQL databases rose to prominence in a different time in the IT world, a time when IT systems were already critical in the day to day jobs of most office staff and importantly, when the cost of storing data itself had substantially decreased. This meant that there was a shift in what was costing money to businesses when it came to data driven applications. The cost was not so much the infrastructure/storage anymore, so much as the server license fees and the direct development cost of the application(ie/ the developers). Born in the age of DevOps NoSQL developers were keen for a system that allowed them the flexibility of not having to define structure upfront and then be locked into that structure for the life of the application. They wanted, in short, a database system that could be modified as often as the requirements they were fed were. And at the same time cloud computing was on the rise, giving the developers an ability to distribute their code rapidly to multiple different datacenters, housed in multiple different regions, provided by multiple different providers. The infrastructure now existed to make their applications more flexible, reliable and resilient, but a central database server became a huge bottleneck.

NoSQL addressed all of these problems. Typically a NoSQL database will have the following attributes:

• Ease of use for developers
• Flexible Schemas
• Horizontal Scaling
• Fast Queries due to the data model

In short, the argument goes, when you are working with NoSQL you are working with DATABASES 2.0 – the new and improved way of managing data; the evolution of databases; the purpose built solution for data management in this millennium. Ergo, NoSQL is better. SQL is Legacy. SQL Server is dead.

Technology changes

And all that’s nice, NoSQL database systems have been developed to meet a need that many application developers, and specifically web application developers needed. The initial versions coped a lot of crap about things like reliance on ‘eventual consistency’, not being ACID compliant, not supporting transactions, being insecure and losing data. Some of this is founded, some of this appears to just be completely made up. In current NoSQL versions, these concerns are all addressed and to my mind resolved (If they did actually exist that is, the data loss thing is hard to find any actual evidence on). Where they remain it is as a design choice – transactions are optional, security is something that must be configured to meet requirements (in any product) and options like sharding come with pros and cons.

So NoSQL databases have evolved to meet the objections thrown at them. The mistake made by many NoSQL advocates is thinking that SQL databases haven’t also evolved. Here’s a handful of examples of SQL Server implementing NoSQL, or non relational features:

• Polybase – introduced in SQL 2016 Polybase allows direct integrations with NoSQL databases and Hadoop data lakes.
• JSON support – from 2016 JSON can be stored and queried within SQL Server.
• XML support – this column type has been available since SQL 2005 and can serve a similar purpose to JSON support.
• Scalability – SQL Server has always offered a range of options here. Availability groups have evolved greatly and are now available in all paid SQL Server editions, and with software assurance licensing is provided for multiple replica servers as part of the primary servers license. But the only technology that offers multiple writable replicas is replication, which is still hugely flexible and…with that it has to be said…hugely complicated.

On top of this Microsoft as a company has focussed most of it’s non-relational development effort into it’s cloud products, and the integration between relational and non-relational cloud products. Non-relational cloud products from Microsoft include:

• Azure Table Storage
• Azure Blob Storage
• Azure File Storage
• Azure Cosmos DB

So even if NoSQL databases were becoming more popular, that doesn’t necessarily have much effect on Microsofts bottom line as they have plenty of non-relational offerings both inside SQL Server itself and in the wider Microsoft family.

I say even if, because the evidence that non-relational databases are cutting into the relational database market share is flimsy at best. Numbers on DB-engines.com as at December 2021 show only one NoSQL database in the top 10 databases(MongoDB at number 5).

Now let’s talk about Legacy

What’s interesting on that ranking to me is that MS Access is sitting in the top 10. If you want to talk to me about Legacy systems it’s mind boggling that MS Access is still considered one of the top 10 databases in the world. Why is that?

Well, the thing with these charts it’s it isn’t about a direct feature by feature comparison of different database systems, or a fancy cost-benefit analysis of one against the other. It’s about who uses stuff. A lot of the noise about NoSQL databases was, and still is coming from sectors which have to be very agile and dynamic. To tie it down further I would say Startups and greenfields developments. In this scenarios you have an open choice of technologies and can pick the one that is best suited to your needs. Let’s accept all the arguments and say that NoSQL databases were always the right choice for a new data project – and that everything not developed in the past 12 months is ‘Legacy’.

What do you think businesses are running on? It is a hugely expensive exercise to port the backend of an application from one database engine to another, let alone change the underlying assumptions of the database engine itself. I’ve had half a dozen calls this year from people looking to use SQL Server as a backend to Access front-ends and the performance results are horrible. It’s not just a case of cramming the data from one system into another, the underlying methodologies just don’t work – and that is between two ‘relational’ databases!

I don’t have personal experience with moving SQL data to MongoDB data, but I’ve heard some rather horrifying stories from those who have done it, or have had to pick up the pieces after it was done. I’m not saying it can’t or won’t be successful, I’m just saying that I’ve heard enough examples of where it wasn’t to think that the percentage option for a business is to invest in tweaking poor performance in a relational database engine rather than redeveloping it in a non-relational one (or in a different relational one for that matter).

So, even if the correct choice for every new development is a NoSQL database(Hands down, it’s not, but I’m conceding that to make my point) the majority of businesses are not going to redevelop their current functional IT systems which will mean that relational databases will still continue to be the dominant player in data management for a long time to come.

This is mirrored by what I see today in my customer base. Servers become more powerful and can comfortably hold larger and larger numbers of databases, but while we consolidate systems as much as possible new ones arise and because SQL Server is already dominant in the clients environment new applications are often back ended in SQL Server. Even the move to cloud computing hasn’t largely changed this paradigm. Businesses have to support their applications, and this will often swing the choice in favor of technologies where they already have existing skillsets and\or support contracts.

So don’t be alarmed SQL DBA’s. There is plenty of life left in your skillset. Even more importantly there are many exciting paths open to you to expand that skillset.