Migration

SQL Server Versions: Out With the Old, In With the Supported

October 29, 2025 by Kevin3NF Leave a Comment

If your production SQL Servers are still running 2016 (or older) you’re basically banking on inertia. Sure, it’s been stable. But that doesn’t guarantee it’ll stay safe or compliant.

Microsoft shut off mainstream support for 2016 back in July 2022, and extended support ends in July 2026. Beyond that? You’re on your own for bug fixes, security updates, or emergency patches.

What You’re Missing

It’s easy to view upgrades as optional enhancements; in truth, staying current is about maintaining resilience. What you gain with 2019/2022 isn’t just bells and whistles. It’s reliability, defensive tools, and measurable performance.

Smarter Engines Under the Hood

“Better defaults” are no marketing fluff. From improvements in memory grants, parallelism, and hash joins, newer SQL Servers are tuned to make your workload more efficient out-of-the-box.

Adaptive Behavior Without Rewrites

Here’s where SQL Server 2019 and 2022 quietly earn their keep. Microsoft invested heavily in the Intelligent Query Processing (IQP) stack – features that make your existing code run better without touching a line of T-SQL (most of the time).

Older versions execute queries based on a single snapshot of estimated data volume, join paths, and parameter values. If those estimates are off (and they often are), the engine makes bad choices and never looks back. The newer engines don’t do that anymore.

Adaptive joins can switch between nested loop and hash join strategies while the query runs, based on how much data actually flows through. That means fewer “query plans from hell” when parameter values swing wildly between executions.

Interleaved execution gives the optimizer a second chance – especially for multi-statement table-valued functions. Instead of assuming a generic row count of “1,” SQL Server now runs the first statement, learns the real cardinality, and uses that for the rest of the plan.

Table variable deferred compilation fixes one of the longest-standing developer pain points. Instead of guessing that a table variable has exactly one row (which breaks most real-world queries), the engine waits until the table is populated, measures it, and builds an informed plan.

And if your code uses scalar user-defined functions , SQL Server 2019+ can inline them, turning what used to be a loop into a set-based operation. That alone can turn a 5-minute report into a 5-second one.

The beauty here is that you may not need to rewrite or refactor anything. You just get smarter plans, more consistent performance, and less time spent chasing parameter sniffing ghosts. All of the above have limitations. Do your homework and proper testing.

Faster Recovery & Safer Rollbacks

Ever had a long-running rollback or crash recovery hang your system? Accelerated Database Recovery (ADR) changes the game—making rollbacks and crash recoveries significantly faster, which is a safety net when things go sideways.

Query Store on Steroids

In older versions, you’d turn on Query Store, fiddle with settings, maybe capture plan regressions. In newer versions, it’s more mature, more integrated, and more automatic. You get insights, forced plan control, and regression protection with minimal overhead.

Security That Doesn’t Feel Optional

TDE, always encrypted, ledger capabilities (in 2022), granular auditing – these aren’t checkboxes anymore, they’re baseline expectations. Newer versions make it less painful to stay compliant and secure.

Hybrid & Cloud-Aware by Design

Backup to URL, cross-environment DR, and more. The newer SQL Server versions are built from the ground up to span on-prem, cloud, or hybrid without the constant “lift and re-architect” panic.

Predictability & Fewer Surprises

2019 and 2022 have been battle-tested at this point. Most of the early-stage regressions, bugs, or inconsistent behaviors have been discovered and addressed in the Cumulative Updates. That predictability is worth its weight in gold when you’re managing risk.

The Bottom Line

Stable isn’t the same as safe. SQL Server 2016 had a great run, but it’s time to let it retire gracefully.
Plan your move to 2019 or 2022 this quarter. Skip 2025 for now. You’ll sleep better.

———————————————————–

Need Migration Help?

Reach out to Dallas DBAs with code “Newsletter”

———————————————————–

Thanks for reading!

SQL Server Migration Overview

October 22, 2025 by Kevin3NF Leave a Comment

It’s Not Just Backup / Restore

At some point every company faces it: the SQL Server that’s been quietly running for years is due for retirement. Maybe the hardware is old, the lease is ending, or your CIO wants to move workloads to the cloud.

From the outside, a SQL Server “migration” can sound like a simple lift-and-shift. Just copy the databases over, right? The reality is closer to moving offices. You don’t just grab every box and throw it into a new building. You measure the space, update the wiring, decide what gets upgraded, and make sure everyone can find their desk again on Monday.

The Big Picture

Predict & Provision

The new environment needs to handle both today’s workload and tomorrow’s growth. Simply matching your old CPU, RAM, and storage can be a mistake if your business has grown since the last server was purchased. In the cloud, it’s even more important to right-size. Too small and you’ll choke performance, too large and you’ll bleed money. Planning capacity up front avoids both. For cloud VMs, provision low during testing and bump up the size as needed.

Install & Configure

SQL Server isn’t plug-and-play. A fresh installation with updated patches and best-practice settings sets the stage for stability. This is where you decide things like where to place (and separate) data and log files, how many tempdb files to allocate, and which default settings to avoid. A solid foundation here can prevent countless problems later.

Tune the Source Before the Move

One of the biggest mistakes in any migration is bringing old baggage (technical debt) into a new system. Giant log files, bloated indexes, and unnecessary jobs can cause just as much trouble on shiny new hardware as they did before. Tuning the source first is similar to decluttering your house before moving – you start fresh without dragging the junk along. Or cleaning the bathroom before your housekeeper shows up 😉

Move the Data

Databases aren’t the only things that need to come across. Logins, SQL Agent jobs, linked server definitions, and security settings are just as important. If you miss these, users may not be able to connect, backups may not run, or nightly jobs could fail. Successful migrations treat this as a holistic move, not just a database restore. There are multiple approaches to this, depending on your data size and cutover window.

Test, Test, Test

Once the new server is up, applications need to prove they can connect and perform. Something as small as a changed network name or a forgotten firewall rule can cause chaos. Testing gives you a safe window to discover what doesn’t carry over cleanly. It’s also a chance to capture new performance baselines so you can measure improvement.

Final Cutover

The actual “move day” should be planned, short, and closely monitored. Typically this means scheduling downtime, running one last backup and restore, and redirecting applications or DNS. The next 48 hours are critical: you’re confirming not only that the server is online, but that backups succeed, jobs run, and performance holds steady. With good prep, the cutover feels more like flipping a switch than rolling the dice.

For large databases in the TB+ range, a full backup/restore during the week with only a Differential needed on cutover day can reduce the amount of time dramatically.

Why Preparation Matters

Here’s the piece many companies miss: migrations are an opportunity to fix what wasn’t working. If you had jobs that failed silently, indexes that were never used, or security shortcuts, they’ll follow you into the new system unless you address them first. Treating the migration as a reset, or a chance to leave bad habits behind, means the business not only gets a new server, but a more reliable platform for the future.

The Bottom Line

A SQL Server migration is less about moving bits and more about moving confidence. With the right planning, you don’t just get a new server – you get a healthier, more reliable foundation for your business applications.

Free Disaster Readiness Quiz

I’ll trade you an email address for an honest assessment

DR Quiz – Are you ready?

Thanks for reading!

SQL Server Migration and Upgrade

March 22, 2022 by Kevin3NF 2 Comments

(This post written by Jon Biggs (t), one of our Senior DBAs)

We are currently performing migrations with upgrade of multiple-instance SQL Servers to new servers. The migrations are going smoothly (knock on wood), and I wanted to relay some information about the migration process. There are four phases you need to perform when migrating a SQL instance to a new server. These phases are Review, Prepare, Test, and Migrate. It sounds simple enough but let’s take a deeper look into what goes into each phase.

In this case, the Test server and instances are migrated, but the matching Production server and instances have not been. A followup blog is expected with any lessons learned.

Review

During the review phase we check the existing environment for best practices, the SQL databases for upgrade readiness and the target SQL server for appropriate sizing.

When it comes to best practices there are Microsoft SQL Server best practices, vendor best practices and industry best practices that need to be considered. Here are two new best practices that I dealt with during the migrations:

AWS EC2 best practice is to utilize Lock Pages in Memory (LPM). While LPM was not a new best practice to me, it was surprising to learn that AWS recommends this configuration when running SQL Server on EC2 VMs.
Microsoft SQL Server best practice when using FILESTREAM is to disable “last access time” and to disable “8 dot 3 naming” on the volumes where FILESTREAM data is stored. Disabling 8 dot 3 naming led me to the options of disabling this setting globally or per volume. It turns out that per volume is the default configuration for Windows Server.

All best practices need to be reviewed and discussed on whether they will be implemented or not. Check the current environment and determine why a best practice is not followed and if it should be followed.

If you are upgrading SQL Server to a new version during the migration, Microsoft provides a tool named the Data Migration Assistant to help determine database upgrade readiness. This tool will assess databases for compatibility issues by SQL Compatibility Level. You may find that you have issues that exist in your databases at your current compatibility level.

screenshot of Data Migration Assistant — (click to enlarge)

When migrating to a new SQL server this is a great time to determine if you need to modify the resources on the server. Do you need more or faster CPUs? Do you need more memory? Do you need larger storage volumes? This needs to be discussed and decided during the review step. Microsoft provides an advanced tool named the Database Experimentation Assistant which can be used to determine how your workload will perform on the target SQL Server. It will also provide query compatibility issues and degraded queries and query plans.

Prepare

During the prepare phase we are determining the migration method, making changes to service accounts and permissions, deciding on shared feature migration such as SQL Server Integration Services and SQL Server Reporting Services, configuring the target SQL Servers, and using Query Store for query regression review.

There are multiple methods of migrating the databases to a new SQL Server instance. These include:

Backup/Restore. You can simply backup the databases, copy the backup files to the new server and restore the databases. This can be a time-consuming process which you may not have the downtime for. A few things to help speed this up:
- Backup the databases directly to the new server, eliminating the copy step. This requires the network share to allow the source SQL Server service account write permissions. This is the method I am using for the migrations as we have the time to do this, and we are using SQL Server Standard.
- If you have a SAN, move the volume with the backup files to the target SQL Server, also eliminating the copy step.
- You can perform a full backup of the databases ahead of time and restore them with NO RECOVERY. When the migration window starts you can perform a differential backup of the databases and restore them with RECOVERY. A caveat here is that you must perform Copy-Only backups if a full backup is required before the migration starts, otherwise you will break the backup chain.

There are a couple of tools to help make the backup/restore method easier.

DBATools.io is a set of Powershell commands for migrating databases, logins, database mail profiles/accounts, credentials, SQL Agent objects, linked servers, etc. A caveat with using dbatools to migrate databases with the Start-DbaMigration command is that it does not move Full Text Index Catalog(s) to a new location if you are migrating to different paths on the target SQL Server. If the source path does not exist on the target SQL Server, the restore of the database will fail.
- If this is the case, you can manually backup/restore the database using with MOVE commands and it will be successful.
- There are other dbatools commands for migration that can be used to avoid this scenario as well.
Microsoft Database Migration Assistant can be used to migrate databases using the backup/restore method.

Detach/Copy/Attach. You can simply detach the databases from the source SQL server instance, copy the database files to the target SQL Server and attach them to the target SQL Server instance. This is faster than backup/restore but does require time to copy the files over the network. There is also the risk of data corruption occurring during the copy process.
- If you have a SAN, move the volume(s) with the database files to the target SQL Server.
- If you have a SAN, replicate or snapshot the volume(s) with the database files to new volumes. Shut down the source SQL Server services so the databases are in a clean, shutdown state prior to final replication or snapshot.
Log Shipping. With log shipping you can have the databases restored on the target SQL Server ahead of migration with NO RECOVERY and continuously restore the Transaction Log backups to the target SQL Server to keep the databases synchronized. When it is time to migrate, the final Transaction Log restore is done with RECOVERY and the databases are brought online. This is also an older Disaster Recovery method for SQL Server and the databases can be brought online without a restore of the last set of Transaction Log backups.
Always On Availability Groups. If you are using the Enterprise version of SQL Server and upgrading from 2012 or later, you can utilize Always-On Availability Groups to migrate and upgrade. Configure a target AG Secondary replica that is a higher version of SQL Server than your source SQL Server and failover to the target AG Secondary replica to finalize your migration with upgrade.

During install and configuration of SQL Server you can choose Windows accounts to use for the various SQL Services. Some of the common services that need Windows accounts are:

SQL Server database engine service
SQL Server Agent service
SQL Full-text Filter Daemon Launcher service
SSIS service
SSAS service
SSRS service

For these services it is an industry best practice that you utilize domain accounts. When setting the services to utilize domain accounts this can be done during installation or using the SQL Server Configuration Manager utilities after installation. It is highly recommended that accounts are set using either method above as the minimum required permissions should be set automatically for the domain accounts. If you set service accounts manually through the services control panel, you will have to manually set permissions for the accounts. It is also an industry best practice that you allow SQL Server to set permissions for domain accounts and do not elevate the permissions for those accounts without an acceptable reason.

Prior to migration you need to determine which shared features will be migrated to the target SQL server. Not all SQL shared features have to run on the same server as the SQL Server database engine service. These include SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS). Plan out the migration of these shared features as they will be done separately from the database migrations. Starting with SQL Server version 2017, SQL Server Reporting Services is a stand-alone installation that is no longer part of the SQL Server installation. This means:

You can only have one SSRS installation per Windows Server now. You can no longer have an installation of SSRS per SQL Server instance.
If you are migrating multiple SQL instances to a new server and you have multiple installations of SSRS on your source server(s) you will have to plan out how to combine them into one installation or choose to migrate them to separate Windows Servers.
You can use the rs.exe utility to migrate reports from one SSRS install to another SSRS install. This can be done at various folder levels. In our migration we migrated one SSRS install over to the target server as-is and then migrated a second SSRS install into a sub-folder on the target server.

When planning out the target SQL Server configuration is the time to make changes to the SQL Server environment. Aside from the best practice configurations that have been reviewed and agreed upon, now is the time to clean up the database file structure if necessary. According to industry best practices you should separate your database files as follows:

System database files
TempDB database files
User Database files (.mdf, .ndf)
User Database log files (.ldf)

Each type of file above has different requirements on the storage sub-system. Depending on your environment you can have separate I/O queues for each volume or you can have physical drives that meet different read and write patterns. Plan this out appropriately when configuring the target SQL Server(s). Logically separating the files can make future infrastructure changes easier.

An option you can use during migration is the Query Store. You can enable Query Store in the databases once they are running on the target SQL Server (SQL 2016 and higher) and leave the database compatibility level to the same level it was on the source SQL Server. After a few weeks of running and collecting data in Query Store you can update the compatibility level of the databases to the new level of the target SQL Server. Query Store provides reports on “Regressed Queries” and “Query Wait Statistics” that can be used to determine which queries are performing worse on the newer version. Query Plans can be forced to quickly revert queries to their previous plan prior to the compatibility level change and can then be evaluated for tuning.

Test

During the test phase we are testing our migration plan. There are always unforeseen issues during a migration regardless of how well you plan. Here are things testing can help with:

Migration method timing can be gathered during testing. You might have looked at your backup times and doubled it for migration but restore could take longer than anticipated. Getting an accurate length of downtime for the migration will help meet expectations.
Tool or script usage can be tested. Did you have any unexpected failures during the migration?
- For me I had issues with dbatools and full text index catalogs that I had to work through.
SQL feature migration can be tested. Was it straightforward migrating SSIS or SSRS?
- For me I had issues restoring the SSIS database master key that I had to work through.
- I also had issues with getting SSRS Subscriptions working again on the target server due to a stored procedure referencing the SQL server by name.

Aside from just testing the migration plan and working through the timing and unforeseen issues you should plan for end user acceptance testing. Here are things end user acceptance testing can help with:

Once the SQL server has been migrated, do the applications still work correctly?
Did you have trouble changing DNS or connection strings?
Did you forget about a server that was part of the application that needed to be changed?
Were any settings on the SQL Server that affect the application missed?
- For me I missed setting trace flags in SQL on the target SQL server.
- I also missed static ports that were set on the source SQL server for the named instances.
- These are things I know should be done but when juggling moving parts, they can get dropped and the testing phase is the appropriate time to catch them.
Is all functionality of the application still working when using the target SQL server?
Is your testing plan comprehensive of all the components affected by the migration?

If you have a test environment for your application, then test phase can also be used to migrate the test environment permanently to the target SQL Server(s). If you do not have a test environment, then this is a good time to consider implementing one. If you are unable to implement a test environment, then use the production target SQL Server for your test migration and consider it a dress-rehearsal for the final production migration.

Migrate

Once you have reviewed, prepared, and tested your migration then it is time to migrate. You should have a solid idea of how long you will need for downtime and how long it will take to test the new environment. Plan for rollback to the source environment in the event of unforeseen issues:

If issues come up during migration or testing, then determine the window of time allowed to resolve those issues before rolling back to the source environment. If you cannot be down for more than X hours and the testing has not been completed by that time you need a plan to get the source environment back online to maintain business continuity.
When planning for rollback, define steps that are go/no go for rollback. If you reach a point where it is more beneficial to resolve issues than roll back or where live data is being inserted into the target databases, then you can define a point where rollback is no longer an option.
- You can mandate that all parties involved in the migration give consent to proceed past a point where rollback is no longer an option.

Define and agree on success criteria for the migration. Success criteria will help define when a migration is considered successful. If this is defined ahead of the migration and is acceptable to all parties involved, when the criteria are all met you can send out communication calling the migration a success. This will help with future communication claiming the migration was a failure or the project stretching out for weeks and having to discuss whether an issue is a “migration” issue or a normal operating issue. Examples of success criteria can be:

End user acceptance testing is completed with all tests passed.
Applications Y and Z come online.
If application X is not online, it will not affect the success of the migration.
SSIS packages run successfully.
Reports and schedules run successfully.
48 hours of production run time will determine a successful migration regardless of new issues.
Issues opened during the first 24 hours of production run time need to be resolved to determine a successful migration.

A SQL Server migration is fundamentally the same as any system migration. You may have some considerations which are specific to SQL server but overall, the phases of the migration will be the same. If you properly follow the four phases of Review, Prepare, Test, and Migrate then you will not just have a successful migration of your SQL Server, you will have a successful migration of your SQL Server in the eyes of your client or manager.