Welp, it’s all done. The final countdown came and went… SQL Server 2016 is as dead as the parrot purported to be pining the for the fjords. I mean actually, it’s a bit more like that parrot than you’d imagine. But if you’ve done nothing about it, it’s definitely dead for you. And in the song that Jeff and I decided to use as the theme song for the post series, Europe posited, “I guess there is no one to blame” a couple lines before asking “Will things ever b the same again?”. They’re wrong, if you’re still on SQL Server 2016, someone somewhere in your organization is to blame. And no, if you stay there, things will not be the same. I mean they literally will be, but when the compliance team or lawyers (or the CFO) come looking, they’ll want to find that one who is to blame. SQL Server 2016s resilience out there is some sort of a strange testament to how well it’s built, maybe we’ll all miss her so. But anyway it was the final countdown. Yeah.

SQL Server 2016 is Dead – What do you do now?

Jeff summed this up in the post he wrote when he kicked off the series. You have three options:

Choose Not To Decide – Neil Peart wrote it best – if you choose not to decide you still have made a choice… You can choose to stay on SQL Server 2016 and do nothing for the extended updates. If you’ve chosen that (or put of the decision) this is what you’ve decided:

• No more feature updates or bug fixes
• No more security updates – This is the one that should scare the heck out of your compliance folks and any executives in the path to that decision. You’re signing up for security risks just when the AI tools are making security patches get more and more serious. Vulnerabilities are being discovered left and right, vulnerability hunters are getting mad about missed payouts and threatening to release the things they are finding, bad actors are using just as many tokens as folks are for their homegrown tools. This is the kind of stuff that comes up in the lawsuits.
• No new features/improvements/updates – If you are a software vendor and you aren’t supporting the latest versions of SQL Server, you are waving the white flag. If you are hosting data that anyone anywhere cares about and you aren’t on at least SQL Server 2019, it’s some sort of a strange message you are sending to your customers and partners about what you think of security. And if you aren’t paying through the nose for the potential to at least maybe get some security updates, you should just rip up any of your compliance attestations. You are more living on prayers.

Help Microsoft Get Richer – You can use the sort of complicated and expensive Extended Security Update (ESU) process Microsoft has created to let you pay through the nose for security updates. You can read about the options to pay for it here. But this is what this approach means:

• You pay an expensive “license price” equivalent for this “privilege” – This is priced by 2 core pack (like licenses) and you are paying somewhere around $3,336 to $13,000 per two core pack per year from what I’ve seen. And I’ve seen some confusion around if it will be a stable price over the 3 years or an increasing one – it seems like it will now be a stable price – just shy of new licenses but I am not even 100% sure there because I’ve seen a few different articles published.
• You are only getting Critical Security Updates – And it’s not you and I who decide what critical looks like. And if you look at the pace of vulnerabilities and even a recent argument between an exploit hunter and Microsoft that has spilled over into the public sphere, you can see that not all agree on what is critical and what isn’t. Microsoft is busy, they are obsessed with Fabric and Cloud and new products, I wouldn’t bet my clients’ data on SQL Server 2016 getting a lot of attention for critical updates.
• You are kicking the can and getting complacent – For a tiny bit less than the cost of upgrading and getting modernized, you are purposefully deciding to stay on a ten year old product.

Upgrade/Migrate – You can upgrade to a more modern version of SQL Server (don’t go to SQL Server 2017, you’ll be reading these same posts next year and all we have to do is republish with a find and replace for 2016 to 2017…) Yeah upgrading sounds really scary (and if you do an in-place upgrade it will be) but in the grand scheme of things upgrading SQL Server isn’t really that big of a task for most folks. We have a ton of posts and an entire video series (it’s a bit older but much of it still applies) on upgrading SQL Server. Check out these tags on the blog (tell me you have some tag cleanup left to do without telling me you do…). You can modernize, eliminate the compliance and finance risk of the above two options and for a lot of clients, you can look to license efficiency, consolidation, and even look at if you still need Enterprise Edition (SQL Server 2025 gives you 256GB of RAM now… We’ve helped a lot of clients move from Enterprise to Standard and modernize to the cloud or their own data centers in the process of upgrades.)

A well-planned migration to SQL Server 2025 or SQL Server 2022 doesn’t have to be as scary as the prospect sounds. With some simple planning, simple build out, testing, and compatibility mode settings – we’ve helped many shops move to a supported version of SQL Server in as little as 20 hours of consulting time for simple environments and a bit more as complexity (sprawl, availability options, etc.) dictate. If you’d like to talk about this – reach out – we’re happy to have a consulting chat with you and you’ll probably walk away with some thoughts on how this can and should look even if you wind up doing the work yourself. I’m happy to point someone in the right direction especially if it means one more SQL Server 2016 (or 2017) instance is placed out of service on purpose.

Check Out the Rest of the Posts In this Series

• What End of Support Means and Why You Should Care
• Features Deprecated and Discontinued Since SQL Server 2016
• Key Features from SQL Server versions 2017, 2019, 2022, 2025
• SQL Server 2016 Is Becoming a Serious Security Risk (Note – I say “has become/is”)
• Assessing your SQL Server 2016 Estate

Thanks to Jeff Iannucci for a lot of heavy lifting in this series and to Mike Lynn for the security take also. At the end of the day – we live in a litigious, risky world with lots of threat actors, tools that make exploiting holes easier, and if you are running SQL Server 2016 or SQL Server 2017 (because the SQL Server 2017 end of life is coming in 15 months.) and not paying scores to hundreds of thousands of dollars a year to Microsoft for the hope of maybe getting a security update, you are gambling with your customers’ data. It’s that simple. And I’m telling you – upgrading a SQL Server is a lot less risk and work than you’d imagine. We do dozens of them a year for our 130+ clients under management with us, so maybe it’s just become second nature. If you have any questions leave a comment or send an e-mail or contact us. I’m happy to point you in the right direction whether you become a customer or not.

The post SQL Server 2016 – But still, it’s farewell… appeared first on SQL Server Consulting - Straight Path Solutions.

And, without giving too much away, at the end of the movie the lead character shouts “Don’t touch it! It’s evil!” Which is exactly how you should think about some of the configuration options in SQL Server.

SQL Server has quite a few instance and database configuration options, which is great if you need to make changes for different business workloads. But some of these configurations can do more harm than good, especially with modern version of the product.

As a consultant, I’ve had the opportunity to work with many clients who have a diverse range of configurations for their instances. And every now and then I see some that have been configured for what I can only presume is a predilection for danger. I mean, little to no good can come of them.

So today I wanted to share with you a few that I have seen used or changed, and to recommend to you with all the influence that I may have, that you DON’T TOUCH THEM – THEY’RE EVIL!

In plain words, here are some things in SQL Server you should never touch (or at least should have a very, very specific reason for even thinking about touching) in 2026. And I’m only mentioning them because I’ve seen them changed somewhere and had to clean up the mess.

Database-level settings

Auto Close.  Enabling this closes the database when the last user disconnects, requiring it to reopen (with all the overhead of recovery, cache rebuilding, etc.) on the next connection. This made some sense on resource-constrained 1990s desktops; today it just causes inexplicable latency spikes and clutters the error log. Always leave it OFF.

Auto Shrink. Enabling this periodically shrinks data and log files. And when is “periodically”? Like Monty Python’s Spanish Inquisition, it’s when you least expect it. These shrink events cause massive index fragmentation, generates huge amounts of I/O, and best of all, the files usually just grow right back. It’s a textbook example of a setting that sounds helpful but actively harms performance. Always leave it OFF.

Auto Create Statistics. Leave it ON, which is the default. Disabling this is almost always a mistake, because the optimizer needs statistics to make good plans. Sometimes clients turn this off because they “manage statistics manually” and of course that never ends well.

Auto UPDATE Statistics. Same story, leave it ON. If you’re worried about updates happening at bad times, enable AUTO_UPDATE_STATISTICS_ASYNC as a reasonable middle ground for OLTP workloads.

Delayed Durability. Setting this to FORCED allows transactions to commit before the log record is hardened to disk. This means you’re trading durability for throughput, which means a power loss or crash can lose committed transactions. In plain English, that means lost data. There are narrow OLTP-with-log-bottleneck scenarios where it’s defensible, but even those should use ALLOWED instead of enabling it database-wide. It’s best to leave it DISABLED unless you have a clearly defined use case, and the business has explicitly accepted data loss risk.

Page Verify = NONE or TORN_PAGE_DETECTION. CHECKSUM has been the best answer for page verification since SQL Server 2005. Unfortunately, some folks keep copying databases from before Y2K from instance to instance (or worse, installing in-place upgrades) without ever changing this setting. The other older options leave you blind to certain forms of corruption, and corruption is also evil.

Recursive Triggers. Enabling this allows for a trigger to call another trigger, which could potentially call another trigger, which could potentially call another trigger, which could…see, infinite loops are no fun. Leave this OFF.

Trustworthy. For security reasons, please, please, please do NOT turn this on. Enabling this allows code in the database to access resources outside it under the database owner’s context. If the owner is a high-privilege account (like sa or some other sysadmin), then this is an easy privilege escalation path for malicious actors. That said, you might be told this setting is required for some specific scenarios, but if that’s the case then you should audit the activity of any database with this on.

DB Chaining. Enabling this allows for cross-database ownership chaining. This is also a security risk, for similar reasons to TRUSTWORTHY. Leave it OFF unless specifically required by someone who has signed in blood (metaphorically speaking, of course) that they know what this does.

Instance-level settings

Common criteria compliance enabled. Off by default, so only enable if you require Common Criteria certification compliance. This setting changes login auditing, residual information protection, and column GRANT behavior in ways that can… let’s just say surprises people, and in a bad way.

C2 audit mode. Also OFF by default. If you turn this ON, it generates massive trace files and will shut down the instance if it can’t write audit data. Sounds like fun, right? SQL Server Audit is a better option for modern versions.

Default trace enabled. This is ON by default. Turning this OFF blinds you to a lot of auditing information for negligible performance benefit. Only an anarchist would change this.

Fill factor (%). Server-wide fill factor default should be left at 0 (which means 100% of your data pages are filled with delicious data). Setting a server-wide non-zero value applies to every index that doesn’t specify its own, which is almost always wrong. Only set fill factor per index based on the index’s update pattern.

Lightweight pooling (also called “fiber mode”).  This sounds modern, but it solves a problem that effectively doesn’t exist on modern hardware. And by modern I mean your CPUs are from the 21^st Century. Almost no workload benefits, and it breaks CLR, OLE DB/linked servers, and various other features that rely on thread affinity, so Microsoft recommends never enabling it unless explicitly told to by Microsoft Support.

Locks. Manually setting a lock count was a thing in the SQL 7/2000 era, but you don’t need to do that now. Leave it at the default of 0 (dynamic) and let SQL Server manage the locks.

Min server memory. Leave it at the default is 0. Setting this aggressively high is a common mistake, where people think it “reserves” memory for SQL Server, but SQL Server doesn’t actually allocate at this value at startup. The setting actually sets a floor below which SQL Server won’t release memory under pressure, and that can make the OS cranky with memory limitations. If Windows ain’t happy, ain’t nobody happy.

Ole Automation Procedures. This is OFF by default. In 2008 you may have thought using sp_OACreate and other related procs was cool, but now this is significant a security concern and a sign of legacy code that should be rewritten.

Priority Boost. It’s 2026 and this is still in the product, and I still every now and again see instances with this enabled. Because it sounds magical, right? Sure, magical like Voldemort, because it raises SQL Server’s process priority above the OS itself, which can starve critical OS threads (including the ones SQL Server depends on) and cause cluster failures. Always leave it OFF, because Microsoft has deprecated it and explicitly recommends never enabling it.

User connections. You can set this up to 32,767 but leave it at 0 (which means no manual limit). Setting a manual cap creates a weird ceiling that can cause connection failures during traffic spikes.

Summary

I know that’s quite a few settings to consider, but think of all these as things you never need to know about…unless you see someone has turned them on. And at that point, you should start asking questions.

The post Settings and configurations you should never touch in SQL Server appeared first on SQL Server Consulting - Straight Path Solutions.

Hopefully you don’t have to try to figure out if your database is in a bad way or not because of an unusual state, but I presume when you do you will need to know as soon as possible. So, I’ve put this handy list of possible database states together to help you in your moment of need.

And before we begin, I know Microsoft has a page about database states, but let’s be honest, it’s a bit sparse with the information. Hopefully this post can add to that, and answer some of your common questions about different database states, and do so in a hurry.

ONLINE

What does this mean? All files are online and the database is available for access. Your database is up and running. People can connect and execute queries of varying quality. This is the state you want every database to be in pretty much always.

Is this bad? No, this is the desired state for a functioning database.

Can I change this with a restart? No. Well, not unless you are an anarchist and want to live dangerously by moving data files around or changing the permissions for the SQL Server service.

Any other notes? This is the best state for a database with the primary filegroup online, but if you just restarted the instance then the undo phase of recovery may not be complete yet. The database is available, but a few rows might still be locked while uncommitted transactions get rolled back. If users complain about weird blocking right after a failover or restart, this could be why.

Also, you could still have corruption, deadlocks, or runaway transaction log growth in this state, so be ready for anything.

OFFLINE

What does this mean? Someone explicitly set the database to this unavailable state.

Is this bad? It depends entirely on whether you meant to do it. If you took it offline to move a file to a different drive, then no, that’s normal maintenance. If you show up Monday morning and a production database is OFFLINE and nobody knows why, yes, that might be bad. Check the error log and ask around.

Can I change this with a restart? No. Restarting SQL Server won’t bring an OFFLINE database back. But you should be able to bring it to ONLINE with ALTER DATABASE [YourDB] SET ONLINE to get it back.

Any other notes? 99% of the time OFFLINE is a deliberate choice, not an accident. We recommend you find out who and why before you bring it back ONLINE, especially if it wasn’t you.

RESTORING

What does this mean? A restore is in progress for one or more files of the primary filegroup, or a restore was started with NORECOVERY and is waiting for more log backups before being recovered. Either way, nobody can use it right now.

Is this bad? Not by itself. It’s normal during a restore. It’s only bad if it’s stuck there when you didn’t expect it to be, or if a restore failed partway through and now you can’t get it out of the state.

Can I change this with a restart? No, and please DO NOT try. Restarting SQL Server while a database is RESTORING does nothing helpful and can leave you with a bigger mess. To finish the restore and bring the database online, run RESTORE DATABASE [YourDB] WITH RECOVERY. That tells SQL Server “I’m done restoring, finalize it.”

Any other notes? You cannot set a RESTORING database to OFFLINE, ONLINE, or EMERGENCY. The only way out is to finish the restore (…WITH RECOVERY) or drop the database and start over. If your restore failed because the backup file is corrupt, you may have to drop and restart from a different backup.

Also, this is the expected state for your databases that are being copied via Log Shipping.

RECOVERING

What does this mean? SQL Server is currently running recovery on the database, replaying the transaction log to bring everything to a consistent state. You’ll typically see this right after a restart, a failover, or bringing a database online.

Is this bad? No, this is normal. It’s a transient state, so sit tight and let it finish. If recovery succeeds, the database moves to ONLINE on its own. If it fails, you end up in SUSPECT, which is a different conversation.

Can I change this with a restart? No, and restarting in the middle of recovery just makes recovery start over from the beginning when SQL Server comes back up. You’re not helping, you’re prolonging the wait.

Any other notes? If recovery is taking an exceptionally long time on a big database, that may be expected. A database with a giant transaction log or a long-running uncommitted transaction at the time of shutdown can take a while.

Check the error log, since SQL Server reports recovery progress there as percentages. Also, read about and consider Accelerated Database Recovery if you are using SQL Server 2019 or later.

RECOVERY PENDING

What does this mean? SQL Server tried to start recovery but bailed out because of some resource problem. The database isn’t necessarily damaged, but something is in the way. Common reasons include a missing data file, a missing log file, a permissions issue on the file, or the drive isn’t mounted.

Is this bad? It’s not good, but it might not be serious. The data is probably fine, but you just need to fix whatever is hindering recovery.

Can I change this with a restart? Often, yes, if the underlying problem was due to a drive being offline when SQL Server tried to start, but now the drive is available. In those cases, restarting the SQL Server service might let recovery finish. But if a file is genuinely missing, a restart accomplishes nothing.

Any other notes? Look at the error log first. SQL Server tells you exactly why it couldn’t recover the database. Most of the time it’s a missing file or a permissions issue, and once you fix that, it’s easy to get back in business. Don’t panic and don’t reach for repair commands until you’ve actually read the error message.

SUSPECT

What does this mean? SQL Server tried recovery but it failed. The primary filegroup appears damaged, and SQL Server can’t bring the database to a consistent state. The database is unavailable.

Is this bad? Yes. Most definitely. This is the state where you take a deep breath and start checking when your last good backup ran.

Can I change this with a restart? No. Restarting won’t fix it. Recovery already failed once. It will fail again.

Any other notes? The most common root causes are related to storage or I/O failures, so you can blame your system admins or network admins, or maybe some vendor. Not that it would solve the problem though. Other causes include recovery failures where the data and log files aren’t in sync, logical consistency errors detected, and even availability group issues. As ever, start by reviewing the error log.

At this state your options are:

1. Restore from your last known good backup. This is most frequently the right answer if you have backups that are both recent and restorable. However, if you haven’t completed a DR test in a while, this could be a resume generating event.
2. If you have no recent and restorable backup (and shame shame shame on whoever let that happen), you can set the database to EMERGENCY state noted below, and then try some database surgery, which means likely data amputation.
3. If repair fails too, keep the database in EMERGENCY and try to copy data out to a fresh database. But be aware that if this was caused by a storage or an I/O error, you may have a hardware problem that will corrupt the next database too if you don’t address it.

EMERGENCY

What does this mean? Somebody with sysadmin rights manually and explicitly set this state, presumably to try database surgery. The database is now marked as READ_ONLY with logging disabled. Only sysadmins can connect.

Is this bad? Well, a human (or maybe your new AI Agent?) did this, and that’s almost always because something else already went wrong. The EMERGENCY state itself isn’t the problem, since the database was already broken.

Can I change this with a restart? No, restarting won’t get you out of EMERGENCY. You have to either run a successful repair, restore from backup, or try to manually set it back with ALTER DATABASE.

Any other notes? EMERGENCY exists for two reasons: to let you read data out of an otherwise dead database, and to allow DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS to run. The name of that option tells you everything you need to know about how it works, so that option should be a last resort. Microsoft explicitly says it can lose more data than restoring from a known good backup.

If you find yourself reaching for EMERGENCY mode in production, the real problem is that you didn’t have a usable backup, and that’s the lesson to take away. Now might be a good time to try out our free sp_CheckBackup tool to find out what’s up with your database backups.

The post SQL Server database states for people in a panic appeared first on SQL Server Consulting - Straight Path Solutions.

If you are reading this and you still have SQL Server 2016 instances kicking around, let this be yet another post reminding you that extended support for that version ends on July 14, 2026. If this is news to you, Microsoft has laid out the details here: SQL Server end of support options.

Now, trying to figure out what SQL Server 2016 instances you actually have running sounds like it should be the easy part of a migration project, but it may not be. If you start looking you might find at least one instance nobody remembers owning, one database that three different apps depend on, or one linked server pointing at something that was decommissioned…when? Before you can plan a migration from SQL Server 2016, you need an honest inventory, and by that I mean not the inventory in a spreadsheet from the year 2016.

So now, let’s walk through tools you can use to build that inventory.

MAP Toolkit

For years the go-to answer here was the Microsoft Assessment and Planning Toolkit, better known as MAP Toolkit. It’s still around, and you can still download it directly from Microsoft today at the MAP Toolkit download page, and it will still run.

However, Microsoft has stopped maintaining and supporting it. That same download page states plainly that there is no support available, and it explicitly recommends using Azure Migrate instead. There have been no updates to the MAP Toolkit in about two years, so if a scan comes back wrong, you’re pretty much on your own.

To be clear: the MAP Toolkit isn’t discontinued (yet), but it is orphaned. It’s still there and still installable, but there’s nobody answering the phone if you need help, and it’s not getting updated for anything ever again.

Azure Migrate (or whatever it’s called this week)

As for Azure Migrate, or as the web page calls it this week “Azure Copilot migration agent built on the Azure Migrate platform,” handles discovery, dependency mapping, and assessment, and as noted earlier, it is the tool Microsoft actively points people toward now.

Before you can assess anything, you need a complete and current list of every SQL Server instance in the estate. This is where the forgotten instance problem shows up the most. For example: Developer edition installs on someone’s old laptop that somehow became load bearing, or a reporting instance spun up during a project that never got decommissioned, or an Express edition got included inside a business app server.

If your ultimate target is Azure, Azure Migrate’s SQL assessment feature will take a lot of this data and translate it directly into a sizing and readiness recommendation. You can read more about that in the Azure Migrate documentation.

dbatools

While Active Directory and network scans can surface instances that nobody remembers registering anywhere, these can be tedious and you as a “Database [whatever title you have]” may not be able to use them.

For us at Straight Path, dbatools is what we often use, and the good news is it is very much alive and actively developed. This is a free, open source PowerShell module built specifically for SQL Server administration and migrations, and it is updated regularly.

Once you have a list of servers, or even a subnet to scan, dbatools commands like Find-DbaInstance can help identify SQL Server instances that are reachable on the network, and Test-DbaConnection will confirm what you can actually get into. From there, Get-DbaService will show you every SQL related service on a box, engine, agent, browser, and full text, which tells you a lot about what that instance is actually doing.

If you already maintain a Central Management Server or a list of registered servers, Get-DbaRegServer (renamed a while back from Get-DbaRegisteredServer, in case you have old scripts lying around) will pull that list so you have a starting point to expand from rather than starting cold.

All these require a bit of PowerShell knowledge, but the tools make it very easy for you with excellent documentation and examples. You can even dig deep into the source code at the dbatools GitHub repository.

Using dbatools for a database-level inventory

Once you know your instances, the next layer is what is actually living on them. Get-DbaDatabase across your instance list will give you names, sizes, compatibility levels, recovery models, and owner information in one pass instead of clicking through SSMS Object Explorer eleventeen zillion times.

I suggest that you pay close attention to compatibility levels. A database sitting at compatibility level 100 on a SQL Server 2016 instance is a completely different migration conversation than one already bumped up, and it tells you something about how carefully that database has been maintained.

This is also a good moment to run Get-DbaLastBackup or our own sp_CheckBackup across the estate. It is not directly a migration assessment task, but I presume that discovering a database has not had a successful backup in eight months is the kind of thing you want to know in week one of a project, not the week before cutover.

Finding dependencies

Export-DbaInstance in dbatools is fantastic for this part of the exercise. It scripts out logins, jobs, linked servers, credentials, database mail configuration, and a long list of other server level objects in one shot, which gives you both a dependency map and a disaster recovery artifact at the same time.

For dependencies inside the databases themselves, cross referencing sys.dm_sql_referencing_entities, sys.sql_expression_dependencies, and sys.sql_expression_dependencies will help you find cross database queries, synonyms pointing elsewhere, and views built on top of views built on top of something in a completely different database than you expected. Application dependencies rarely respect the tidy boundaries we drew in the architecture diagram three years ago.

Performing a workload assessment

Inventory tells you what exists. Workload assessment tells you how hard it is working and whether your target platform can actually handle it.

At a minimum you want: CPU, memory, and IO utilization patterns over a real business cycle, not just a Friday afternoon. Get-DbaTopResourceUsage in dbatools is a nice quick way to see which databases are actually driving load on an instance.

Query Store, if it is enabled anywhere in your 2016 estate, is a goldmine for understanding your actual workload shape rather than guessing. If it is not enabled, this is a good excuse to turn it on now, even briefly, before you finalize a migration plan.

Wait statistics over time to understand whether you are CPU bound, IO bound, or something else entirely. This matters enormously for sizing an Azure SQL Managed Instance tier or picking VM specs, and guessing wrong here is an expensive mistake to correct later.

Here’s something you can do this week: clear the counters with Clear-DbaWaitStatistics at the start of a representative business day, let a full cycle run, then capture a clean snapshot with Get-DbaWaitStatistic. That gives you wait stats tied to an actual dated window instead of numbers that have been quietly accumulating since the last unplanned restart, which is what most people are looking at without realizing it. If you haven’t restarted your instance in years (yes, this happens) the current statistics may not be as relevant.

SSMS 22 and its built-in Migration Assessment

I noted that there was a migration assessment built in to Azure Migrate, but the latest version of SQL Server Management Studio 22 includes real assessment capabilities that older SSMS versions never had. Microsoft retired the standalone Data Migration Assistant on July 16, 2025, but SSMS is now positioned as a useful tool for upgrade assessments and even Azure migration assessments.

Here’s how you use this feature. In SSMS 22, right click an instance in Object Explorer, choose Migrate SQL Server, and you get two useful paths.

• Upgrade Assessment checks a source instance against a target SQL Server version and flags breaking changes, discontinued features, and deprecated functionality, with remediation guidance included.
• Azure Migration Readiness does the same kind of check pointed at Azure, evaluating your databases against Azure SQL Managed Instance, Azure SQL Database, and SQL Server on Azure VMs, then recommending which target works best and why. You can read more here if you want to Migrate SQL Server to Azure SQL.

As of SSMS 22.5 this all lives on a single Migrations page, so assessment, target selection, migration, and monitoring are not four different places that you have to remember to check. It is not a replacement for the workload analysis dbatools gives you, but as a fast, metadata-based readiness check before you commit to a target, it is worth running early rather than after you have already picked a direction.

Choose wisely

By the time you finish this exercise with whichever tools you choose, you should have an honest, current picture of every instance, every database, every service dependency, and a real sense of how hard each workload is actually running. That is the foundation the rest of the migration plan gets built on. Skip it, or half do it, and you will find out about the gaps at the worst possible time, usually during a cutover weekend, and usually around two in the morning.

My guidance for you: consider not using MAP Toolkit anymore, since orphaned tools have a way of missing exactly the thing you needed them to catch. Get comfortable with PowerShell and use dbatools since it is free, actively maintained, and built (and used) by people who migrate SQL Server instances all the time. Run the SSMS 22 migration assessment early for a fast readiness read, and lean on Azure Migrate if Azure is anywhere in your future. And try not to wait too long after July 14^th to finish your assessment.

Sources and further reading

• SQL Server end of support overview, Microsoft Learn
• MAP Toolkit download page, Microsoft (confirms it’s still downloadable but states no support is available)
• Azure Migrate overview, Microsoft
• Azure Migrate documentation, Microsoft Learn
• SQL Server Migration Component (SSMS 22), Microsoft Learn
• Simplify SQL Server Migration with SSMS 22.5 Features, John Deardurff
• Migrate SQL Server to Azure SQL, Microsoft Learn
• sys.dm_sql_referencing_entities (Transact-SQL), Microsoft Learn
• sys.sql_expression_dependencies (Transact-SQL), Microsoft Learn
• dbatools official site
• dbatools GitHub repository
• Export-DbaInstance documentation, dbatools

The post SQL Server 2016 – the Final Countdown: Assessing Your SQL Server 2016 Estate appeared first on SQL Server Consulting - Straight Path Solutions.

If you have ever done an in-place upgrade of SQL Server, the master database probably has a lower compatibility level than the other system databases.

Context

We were attempting to install a troubleshooting stored procedure in the master database of a SQL Server 2016 instance when we received the following error.

Msg 195, Level 15, State 10, Procedure sp_ShootTheTrouble, Line 227 [Batch Start Line 7]

‘TRY_CONVERT’ is not a recognized built-in function name.

This was unexpected, as TRY_CONVERT has been a command since SQL Server 2012. As a consequence, we were unable to install the stored procedure.

The Investigation

We did a quick check of the compatibility level of the master database using this query.

SELECT [name], compatibility_level
FROM sys.databases
WHERE name = 'master';

The results of the query indicated that the master database was set to compatibility level 100, which is SQL Server 2008.

A quick check of the Microsoft SQL Server folder showed this instance had experienced several in-place upgrades, but was likely to have originally been SQL Server 2008 R2, as indicated by the folder “MSSQL_10_50.MSSQLSERVER”.

One thing many folks don’t realize is that the master database retains the compatibility level it had before the upgrade if you upgrade SQL Server in-place. All other system databases (tempdb, model, msdb, and Resource) will upgrade to the compatibility level of the new version, although user database maintain the compatibility level of the database if it is a supported level.

So, for this instance, despite multiple in-place upgrades, the master database still retained the compatibility level of the original installation of SQL Server 2008 R2.

The fix – manually change the compatibility level

There is usually little to no risk in manually raising the compatibility level of the master database in this case. The biggest consideration would be for any stored procedures or other objects with dependencies on features or commands that have been removed in the current version.

There were no other user objects, so we ran this to bring the compatibility level of the master database to that of the version.

USE [master];
GO
ALTER DATABASE [master] SET COMPATIBILITY_LEVEL = 130;
GO

The Straight Path Team and Skills

This case shows one of many reasons why in-place SQL Server upgrades are problematic and generally not recommended. If you are considering upgrading your version of SQL Server, the Straight Path team can help make the migration as painless and cost-effective as possible.

Straight Path Solutions is a team of nearly 20 SQL Server experts trusted by 120+ clients. We treat our clients’ environments as if they were our own because our job depends on your success. Making your life pain-free in the land of SQL is the rewarding part of our work. If you’re planning a migration, dealing with backup concerns, or just want to sleep better knowing someone’s watching your environment, reach out to us.

This post is part of our Case of the Week series, real SQL Server issues and lessons from the field.

The post SQL Server Case of the Week: Why the master database can have a lower compatibility level than the instance appeared first on SQL Server Consulting - Straight Path Solutions.

Anyhow, we have a few months’ worth of updates for our FREE sp_Check stored procedures, so here are the June updates. And not only do we have more updates and checks for you, our repos now feature enhanced README files that have even more helpful information as well as a listing of every check in each stored procedure. Yes, really!

We hope these new updates can help make your job even easier, especially at proactively identifying issues. Here are the updates for June 2026, with links to the GitHub repositories where you can download the latest versions.

sp_CheckAG

• CHANGE documentation to say SQL Server 2016 and higher
• ADD servername mismatch check (CheckID 617)
• CORRECT offline listener URL (CheckID 417)
• CORRECT various typos
• ADD check for database not synchronized on a synchronous replica (430)
• ADD check for disconnected replicas (431)
• ADD Connected State to Replica result set
• ADD check for suspect pages detected (504)
• ADD check for automatic page repair activity (506)
• CORRECT check for Seeding Mode
• CORRECT check for Database Owner
• CORRECT check for Database Health Level
• CORRECT check for Physical Memory (114)
• REMOVED some dead parameters

sp_CheckBackup

• ADD SQL Agent Offline (CheckID 628)
• ADD URL for VLF check (CheckID 217)
• CHANGE documentation to say SQL Server 2016 and higher
• ADD snapshots with differentials for latency check (CheckID 739)
• EDIT wording used for I/O freeze detection (739)
• CHANGE Importance of I/O freeze detection from 1 to 2 (739)
• CORRECT broken links for Check IDs 210, 211, 212, 213
• CORRECT values for replace and recovery in results of @Mode = 2
• UPDATE to work with Azure MI
• UPDATE include Azure MI backup history for user databases
• UPDATE VLF check to only check online databases (217)
• CORRECT typos in Parameters of @Help = 1
• CORRECT check for failed backups to be more robust (207)
• CORRECT typo in Check ID 206
• REMOVED commented debug code

sp_CheckSecurity

• UPDATE versions for May 2026 updates
• CORRECT URL for local Administrators (315)
• CORRECT typos in CheckIDs 119, 321, 332, 340
• CORRECT typos in @Help = 1
• CORRECT check for number of error log files (622)
• ADD check for proxies for sysadmin (352)
• ADD check for xp_cmdshell proxy account (353)
• ADD check for logins with IMPERSONATE ANY LOGIN (354)
• ADD check for logins with ALTER ANY LOGIN (355)

sp_CheckTempdb

• ADD check for Resource Governor used for tempdb in SQL Server 2025 (CheckID 727)
• CORRECT check for @SQLMajorVersion
• ADD check for Engine Edition
• ADD check for Accelerated Database Recovery enabled in tempdb (739)
• ADD constraints for @Size, defaulting to MB
• CORRECT check for Resource Governor used for tempdb in SQL Server 2025 (727)
• CHANGE check for file growth for log file to account for default max (619)
• CHANGE @Mode = 3 to use dynamic SQL and work with Azure Managed Instance
• CHANGE Trace Flag checks to exclude Azure MI (715, 716)

The post Updates for all sp_Check tools – June 2026 appeared first on SQL Server Consulting - Straight Path Solutions.

For many companies SQL Server 2016 is a reliable database management system.  It has worked well for several years and let’s be honest; it isn’t broken, so why fix it?

We all have several high-priority tasks; everything from updating a report for management to troubleshooting the same report for slow performance. We all know the end of life for SQL Server 2016 is right around the corner, but not everyone has the time to research why newer versions are a safer choice.  In this article we will explore one of the security enhancements supported by newer releases of SQL Server; TLS encryption.

TLS encryption is something you’ve likely heard from your system administrators and security team.  TLS stands for Transport Layer Security, and it is used to provide secure communication over a network.  To relate that to the DBA world, TLS is used when a request is made to connect to your SQL Server.  The server operating system will negotiate with the client to determine a common protocol to use when encrypting traffic between the two systems.   To give an example; if your server operating system supports TLS 1.0 and TLS 1.1 and the application server supports TLS 1.1 and TLS 1.2; the common protocol is TLS 1.1 and that will be used to encrypt the connection between the systems.

When SQL Server 2016 was released, Microsoft recommended using TLS 1.2, which was the most advanced encryption protocol at the time. SQL Server 2016 also supported other common protocols like TLS 1.0 and 1.1. Since the operating system handles negotiating with clients on which protocol to use, most systems have all versions of TLS enabled. 

At the time it worked but vulnerabilities were discovered in TLS 1.0 and 1.1 and both were marked as deprecated in 2021.  Having said that, just because they were marked as deprecated doesn’t mean they were disabled.  I still see systems from time to time with TLS 1.0 and 1.1 enabled. 

Currently though, this leaves TLS 1.2 as the only supported encryption protocol for SQL Server 2016 to use for secure communication.  And while it is still supported, TLS 1.2 has its own security issues and is being replaced by TLS 1.3. The security industry currently recommends moving towards TLS 1.3 because it fixes some weaknesses outlined below:

Currently the only versions of SQL Server that support TLS 1.3 are SQL Server 2022 and SQL Server 2025.  SQL Server 2022 only supports TLS 1.3 when TDS 8.0 is in use while SQL 2025 fully supports TLS 1.3.

What does this all mean and why does it matter?  So glad you asked. This matters because new TLS vulnerabilities are discovered constantly, and an end‑of‑support database server becomes the weakest encrypted link in the environment. Attackers don’t target what’s newest, they target what is unpatched.  With SQL Server 2016 no longer receiving updates after July 2026, and TLS 1.2 no longer receiving updates either; these servers will be very enticing to a bad actor when one gets into your environment. 

The questions I think most people aren’t asking themselves are:

“Am I okay with being the weakest link in the company chain when defending against hackers?” 

“Am I okay with admitting the servers I manage were taken over because I put off upgrading them to newer and more secure software?” 

If your answer to these questions is “No!”, my recommendation for you is to start planning and pushing for a migration to SQL Server 2022 or SQL Server 2025. 

The post SQL Server 2016 – the Final Countdown: SQL Server 2016 Is Becoming a Serious Security Risk appeared first on SQL Server Consulting - Straight Path Solutions.

What’s Changing and Why

The change Microsoft is making is related to an information disclosure security vulnerability found in CVE‑2026‑20833. To fix this vulnerability Microsoft is stopping the default use of the RC4 encryption algorithm in Kerberos authentication. RC4 encryption has been around since the 80s, has several known vulnerabilities associated with it, and is no longer recommended for use in production environments. The specific risk is “Kerberoasting” an attacker requesting service tickets and cracking the weaker RC4 encryption offline to recover service account passwords.

How Microsoft Has Rolled This Out

Microsoft has been preparing for these changes in phases since January 2026. The first step was an Audit phase, which started recording messages in domain controller event logs. In the System event log, watch for the new events created specifically for this change:

• Event ID 201, 202, 206, 207 – RC4 default disablement / at-risk interactions and service accounts
• Event ID 205 – logged when an explicit configuration still allows RC4

In the Security event log, you can also review:

• Event ID 4768 – Kerberos Authentication Service (AS request / Ticket Granting Ticket)
• Event ID 4769 – Kerberos Service Ticket Operations (Ticket Granting Service – TGS)

Inside these events, an encryption type of 0x17 indicates RC4 is being used.

The next change came with the Windows update in April 2026, which moved domain controllers into enforcement mode by default . This blocked RC4 connections from non-compliant devices for accounts using the default encryption configuration. If you started encountering issues after the April update, you could modify the Active Directory account and set the attribute msDS-SupportedEncryptionTypes to include RC4 (0x24). This was meant to be a workaround, not a permanent fix.

The final phase arrives in July 2026, when the temporary registry escape hatch goes away… Microsoft removes support for the RC4DefaultDisablementPhase registry key that some organizations used to postpone the change. After July, that delay option is gone, and you’ll no longer have the choice of putting off tomorrow what you now get to fix today. (Note: explicitly configured per-account overrides are still honored even after July but relying on them keeps you exposed to the very vulnerability this change exists to close, so they should be a controlled last resort, and a plan to open up vulnerabilities, not succeed…)

What to Review in Your Environment

The key parts of your environment that should be reviewed are service accounts, applications using Kerberos or Windows Authentication, older systems, and with SQL Servers: linked servers and the accounts being used. Have a sysadmin review your domain controller event logs for the event IDs above and check whether RC4 (0x17) is in use. A bigger lift will be verifying your application libraries and drivers are up to date and checking whether older software in your environment supports AES encryption.

“We Deployed April and Had No Problems… Do I Still Need to Check?”

You may be asking yourself, “We’ve deployed the April update and haven’t had any problems, do I really need to check all these things?” The impact of not checking means that in July you may start to experience one, or all, of the following:

• Failed database connections (Windows-authenticated SQL Server)
• Linked server authentication failures
• Application login issues
• Service startup failures
• Kerberos delegation failures

If your environment is up to date on Windows updates and your system administrators have kept up with this change, your chances of experiencing any of these issues are much lower. As we all know, that chance is almost never zero, and taking one last look isn’t going to hurt. If anything, you may find the one thing that was overlooked which could save the company from coming to a grinding halt when RC4’s default support ends for good.

Where to Learn More

If you would like to read more about the change, you can look up CVE‑2026‑20833 or review Microsoft’s official guidance. Reviewing your environment now can help ensure a smooth transition and avoid unexpected disruptions.

• How to manage Kerberos KDC usage of RC4 (CVE-2026-20833) — Microsoft Support (KB5073381)
• Detect and Remediate RC4 Usage in Kerberos — Microsoft Learn
• Kerberos and the End of RC4: Protocol Hardening and Preparing for CVE‑2026‑20833 — Microsoft Community Hub

Check Your SQL Server Security and Upgrade!

While you are at it – don’t forget to check your own SQL Server security. We can definitely help with our health checks, but you can also just download our free SP_CheckSecurity proc and check your own security. And if you are on an unsupported SQL Server (which will include SQL Server 2016 soon) Check out some of our posts on SQL Server 2016’s final countdown and upgrade best practices.

The post RC4 Is Going Away in July 2026: What CVE-2026-20833 Means for Your SQL Server and Windows Environment appeared first on SQL Server Consulting - Straight Path Solutions.

SQL Server 2016 was the version where Microsoft really started giving us the good stuff for free. Query Store, Columnstore for everyone, Always Encrypted, Availability Groups that worked, and more. Plus, the Service Pack 1 feature additions made Standard Edition folks feel like they weren’t second-class citizens anymore.

But that was a decade ago, and as of July 14^th, 2026, SQL Server 2016 is out of support. No more updates or hotfixes, so it’s well past the time to upgrade to a newer version. I know upgrades can seem painful, and we can help with that, but on the other side of that upgrade are many features added since SQL Server 2016 that could make your life easier.

Let’s walk through some of my favorite new features from each version, in the style of an executive summary in case you want to copy/paste to your supervisors to convince them to upgrade. I’ll keep the descriptions short, tell you why you should care, and link to pages with more info should you want to learn more.

SQL Server 2017

SQL Server 2017 shipped only 16 months after 2016, which by Microsoft’s historical standards is practically a patch. Still, they managed to slip in some real gems.

SQL Server on Linux

We were all assured for decades would be an icy day in a place hotter than the desert here in Arizona before Microsoft ever supported Linux, but starting with 2017 you can run the database engine on Red Hat, Ubuntu, SUSE, or in a Docker container. If you work somewhere that has a Linux-first policy, SQL Server is no longer a non-starter, and if you’re a DBA, containerized SQL Server means you can spin up a test instance in roughly the time it takes to refill your coffee. Read more from Microsoft.

New String Functions

Developers, rejoice! STRING_AGG, TRIM, TRANSLATE, and CONCAT_WS may seem like cute new functions, but STRING_AGG alone eliminates the XML PATH abomination we’ve been using since SQL Server 2005. If you’ve never written FOR XML PATH(”) followed by STUFF to concatenate rows, count yourself lucky because now you’ll never have to. Read more from Aaron Bertrand.

SQL Server 2019

This version has been very stable for years, and I would guess as of this writing it is the most popular version with our clients. And it had a few neat feature additions too (and no, I’m not talking about the troublesome scalar UDF inlining and all its requirements.)

Accelerated Database Recovery (ADR)

Before ADR, if a long-running transaction got killed or the server crashed mid-flight, SQL Server had to chew through every log record from the oldest active transaction before the database came back online. For a well-behaved OLTP system that’s fine, but for a system where somebody just ran a three-hour ‘UPDATE’ with no ‘WHERE’ clause, recovery could take hours. That’s real pain.

ADR uses a persisted version store (similar to RCSI) so rollbacks and recovery are nearly instantaneous regardless of transaction size. Crash recovery can go from minutes to seconds. Read more from Microsoft.

Memory-Optimized Tempdb Metadata

If you’ve ever worked on a busy OLTP system where everybody and their uncle is creating and dropping #temp tables, you’ve seen the PAGELATCH_* waits on the tempdb system tables. It’s a classic contention point that no amount of “add more tempdb files” will ever fully solve.

SQL Server 2019 lets you shove the tempdb system metadata tables into memory-optimized (Hekaton) tables, eliminating that latch contention entirely. Doing this requires a server-level setting and a restart, but for the right workload it’s night and day. Note, this feature seemed to have gotten some big improvements in SQL Server 2022. (Watch Haripriya Naidu’s video with more)

SQL Server 2022

The marketing pitch for 2022 was “the most Azure-connected SQL Server ever!” which is great if you’re in Azure and slightly less exciting if you’re not. But even setting aside the cloud stuff, there are some cool features here.

Query Store Is On By Default (Finally)

New databases created in 2022 have Query Store turned on out of the box. It took them six years since the 2016 release, but hey, these things take time. Query Store is also now supported on readable secondary replicas in Availability Groups, which means your read-only reporting workload is finally observable. Read more from Microsoft.

Query Store Hints

You can now apply query hints *without changing application code*. Specifically, you use sys.sp_query_store_set_hints to attach hints like RECOMPILE to a specific query_id in the Query Store. For anyone who’s ever had to performance tune a vendor-supplied application where you can’t change the SQL, this is a gift. Read more from Microsoft.

S3-Compatible Object Storage for Backup/Restore

You can now BACKUP TO URL and RESTORE FROM URL against S3-compatible object storage, and not just Amazon S3 but also Dell, HPE, Pure Storage, NetApp, MinIO, and others. For anyone running SQL Server in a hybrid shop, this is a much simpler backup target than wrestling with file shares or Azure-only URLs. Read more from Microsoft.

Contained Availability Groups

In every pre-2022 Availability Group (AG), you had to manually keep logins, SQL Agent jobs, permissions, and linked servers in sync across replicas. (Did you ever forget to add logins on the secondary, fail over, and hear the lamentations of the users?) With contained AGs, users/logins/permissions/jobs live inside the AG itself using contained master and msdb databases so across replicas these consistencies are handled for you. Read more from Microsoft.

SQL Server 2025

SQL Server 2025 went General Availability (GA) on November 18, 2025. This is the first release in a long time where there are several situations to push clients to upgrade faster rather than slower.

Standard Edition Finally Gets Some Love

For the first time since 2016 SP1, Standard Edition got a real upgrade, including:

• 32 cores (up from 24)
• 256 GB RAM (up from 128 GB)
• Resource Governor is now included in Standard (it was Enterprise-only forever)
• There’s a new Standard Developer Edition so you can build dev/test environments that actually match your Standard production limits

If you’re a shop that has been running Standard Edition for years and feeling the squeeze, this alone is a reason to skip 2022 and jump straight to 2025. More from Microsoft.

(Related: Express Edition gets some more love too, which now supports 50 GB databases, up from 10 GB.)

Native Vector Type and Vector Search

Yes, this is the “AI” feature, and yes, it’s real. SQL Server 2025 has a native VECTOR data type, a vector index powered by DiskANN, and functions like VECTOR_DISTANCE, VECTOR_SEARCH, and AI_GENERATE_EMBEDDINGS that let you call out to an embedding model from T-SQL. Whether or not you personally need to build a RAG chatbot, the broader implication is big: your database can now do semantic (meaning-based) searches, not just keyword matching. Read more from Andy Yun.

Optimized Locking

Optimized Locking uses Transaction ID (TID) locking and Lock After Qualification (LAQ) to dramatically reduce lock memory use and lock escalation. For high-concurrency OLTP workloads, Microsoft has published telemetry showing 80% of locks being skipped in some cases. It requires ADR to be enabled, but no application changes. Read more from Microsoft.

Native JSON Type + JSON Indexes

Since 2016 we’ve stored JSON as NVARCHAR(MAX) and lived with it. 2025 introduces a real native JSON data type (binary-stored, up to 2 GB per document) and lets you create JSON indexes on specific path expressions. There are still some quirks with JSON_VALUE not always using the index, so test carefully. Read more from Microsoft.  

But wait, there’s more

Just so you know, that was not a comprehensive list. There have been many new features added since SQL Server 2016 that, depending on your situation, could benefit your organization. These other features are all included in SQL Server 2025:

• Fabric Mirroring if you have Microsoft Fabric reasons.
• Graph Database Support for those modeling hierarchies or social networks.
• Ledger to give you a tamper-evident (cryptographically verifiable) record of every change made to specific tables.
• Optional Parameter Plan Optimization (OPPO) can now generate different execution plans adapted to the runtime parameter values for “kitchen sink” queries.
• Regex Support with a bunch of new T-SQL commands.
• TDS 8.0 / TLS 1.3 support for the security-minded folks.
• ZSTD Compression allows for faster, small backups than the default compression.

Thanks for reading. Now go forth and plan to upgrade your SQL Server 2016 instances.

The post SQL Server 2016 – the Final Countdown: Key Features from SQL Server versions 2017–2025 appeared first on SQL Server Consulting - Straight Path Solutions.

The post Session: Defending Your SQL Server: Practical Strategies against Ransomware by Jeff Iannucci appeared first on SQL Server Consulting - Straight Path Solutions.