{"id":113942,"date":"2026-10-23T12:00:00","date_gmt":"2026-10-23T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=113942"},"modified":"2026-10-22T13:39:21","modified_gmt":"2026-10-22T13:39:21","slug":"sql-server-2016-end-of-support-upgrade-options","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/cloud\/sql-server-2016-end-of-support-upgrade-options\/","title":{"rendered":"SQL Server 2016 end of support: how to choose your upgrade path"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><strong>SQL Server 2016 reached the <a href=\"https:\/\/techcommunity.microsoft.com\/blog\/sqlserver\/sql-server-2016-extended-security-updates-stay-protected-while-you-modernize\/4529478\" target=\"_blank\" rel=\"noreferrer noopener\">end of extended support in July 2026<\/a>, which means it no longer receives regular security updates unless you pay for <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/end-of-support\/sql-server-extended-security-updates?view=sql-server-ver17&amp;tabs=sql-vm\" target=\"_blank\" rel=\"noreferrer noopener\">Extended Security Updates (ESUs)<\/a>. <\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Your realistic options are to upgrade to <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/what-s-new-in-sql-server-2025?view=sql-server-ver17\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server 2025<\/a>, upgrade to SQL Server 2022, or buy ESUs for up to three more years (to July 2029), whether you run SQL Server <a href=\"https:\/\/www.red-gate.com\/simple-talk\/other\/on-premises-vs-cloud\/\" target=\"_blank\" rel=\"noreferrer noopener\">on-premises<\/a> or in <a href=\"https:\/\/www.red-gate.com\/simple-talk\/cloud\/azure\/\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Azure<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For most production systems, <strong>I recommend a side-by-side migration to SQL Server 2025<\/strong> unless an application vendor or a removed feature rules it out. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The right approach ultimately depends on application compatibility, infrastructure constraints, acceptable downtime, licensing costs, and how long the new environment needs to remain supported.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In this article, I\u2019ll cover the key decisions (in-place vs side-by-side, SQL Server 2022 vs SQL Server 2025, on-premises vs Microsoft Azure, and ESUs), plus a pre-upgrade checklist and rollback advice to keep risk low.<\/p>\n\n\n\n<h2 id=\"h-what-are-you-trying-to-achieve-with-your-sql-server-2016-upgrade\" class=\"wp-block-heading\">What are you trying to achieve with your SQL Server 2016 upgrade?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before making an upgrade decision, you need to work out what exactly you&#8217;re trying to achieve. This might seem obvious but, in many organizations I&#8217;ve worked in, it&#8217;s actually <em>not<\/em>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Some just see the support lifetime and want to get onto a supported version as fast and as disruption-free as possible, while others take advantage of the interruption by consolidating their servers, replacing old systems (or improving their availability) &#8211; or even moving their workloads to Azure.<\/p>\n\n\n\n<h2 id=\"h-what-to-find-out-before-choosing-a-sql-server-2016-upgrade-strategy\" class=\"wp-block-heading\">What to find out <em>before<\/em> choosing a SQL Server 2016 upgrade strategy<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Whenever I&#8217;m discussing this type of upgrade with clients, there are several things I want to know:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>How many SQL Server instances are being considered for upgrade?<br><br><\/li>\n\n\n\n<li>Which non-database features are being used (e.g., <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/reporting-services\/reporting-services-consolidation-faq?view=sql-server-ver17#:~:text=General%20questions-,What%20is%20SQL%20Server%20Reporting%20Services%20(SSRS)%3F,-SSRS%20is%20a\" target=\"_blank\" rel=\"noreferrer noopener\">SSRS<\/a>, <a href=\"https:\/\/learn.microsoft.com\/en-us\/analysis-services\/ssas-overview?view=sql-analysis-services-2025\" target=\"_blank\" rel=\"noreferrer noopener\">SSAS<\/a>, or <a href=\"https:\/\/www.red-gate.com\/simple-talk\/podcasts\/why-ssis-will-never-die-with-tim-mitchell\/\" target=\"_blank\" rel=\"noreferrer noopener\">SSIS<\/a>)?<br><br><\/li>\n\n\n\n<li>Which applications depend upon these systems?<br><br><\/li>\n\n\n\n<li>What constraints are there around changing the servers?<\/li>\n<\/ul>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">It&#8217;s critical to really check out what&#8217;s being used in terms of third-party applications, since vendors often certify their software for specific SQL Server versions. I know that Microsoft has been telling them for years to target database compatibility levels instead of database server versions, but the message still isn&#8217;t getting through to them. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Even if an application works perfectly on SQL Server 2025, there may be a roadblock if the vendor will <em>only<\/em> support it on SQL Server 2022. It&#8217;s unlikely that the client organization will be happy to move to a setup that breaks their vendor support.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You must also consider the level of operating system (OS) support. It was common to install SQL Server 2016 on <a href=\"https:\/\/www.microsoft.com\/en-us\/evalcenter\/evaluate-windows-server-2016\" target=\"_blank\" rel=\"noreferrer noopener\">Windows Server 2016<\/a>, for example, but that&#8217;s not possible with SQL Server 2025. We usually find that those servers need an infrastructure refresh anyway.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">What you find here should determine the strategy you use for upgrading from SQL Server 2016, and there are two core ways of doing this.<\/p>\n\n\n\n<h2 id=\"h-in-place-upgrade-vs-side-by-side-migration-which-should-you-choose\" class=\"wp-block-heading\">In-place upgrade vs side-by-side migration: which should you choose?<\/h2>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">There are two core ways to upgrade from SQL Server 2016: install the new version over the existing instance (in-place), or build a new instance and move your databases to it (side-by-side).<\/p>\n<\/blockquote>\n\n\n\n<h3 id=\"h-in-place-upgrade-pros-cons-and-risks\" class=\"wp-block-heading\">In-place upgrade (pros, cons, and risks)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">One way of upgrading from SQL Server 2016 is by an <strong>in-place upgrade<\/strong>, which installs the newer SQL Server version over the existing instance. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The main advantage of this approach is simplicity &#8211; there&#8217;s no need to migrate every database, recreate logins, or redirect applications to another server. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">However, there are also some downsides to this approach. The fact that you are modifying the existing instance, for example, can make rollback difficult. I generally won&#8217;t ever do this unless I know that a full image backup of the server and all associated drives has been made.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Additionally, the upgrade itself requires downtime, and you need to allow for a rollback time window in case that&#8217;s necessary. I can&#8217;t tell you how often I see clients who have not allowed sufficient time for potential rollback situations.<\/p>\n\n\n\n<h3 id=\"h-can-you-upgrade-sql-server-2016-directly-to-sql-server-2025\" class=\"wp-block-heading\">Can you upgrade SQL Server 2016 directly to SQL Server 2025?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;ve applied <a href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=103440\" target=\"_blank\" rel=\"noreferrer noopener\">Service Pack 3 (SP3)<\/a> for SQL Server 2016 (as you should have by now), then <strong>a direct in-place upgrade to SQL Server 2025 is supported<\/strong>, presuming the edition and OS requirements are met. There&#8217;s no need to make a transition step through SQL Server 2019 or 2022 first.<\/p>\n\n\n\n<h2 id=\"h-side-by-side-migration-pros-cons-and-how-it-works\" class=\"wp-block-heading\">Side-by-side migration (pros, cons, and how it works)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Side-by-side migration<\/strong> involves building a new SQL Server instance and migrating databases and associated objects to it. While this <em>can<\/em> of course be more work, there are many advantages to doing this. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The new environment can be built, configured, and tested independently &#8211; and both the hardware <em>and <\/em>operating systems can be upgraded at the same time. You also might be able to migrate applications one by one rather than all at once. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">And lastly, the original setup might remain available for testing.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">One important lesson I can offer from the school of hard knocks: use different service accounts, logins, etc. for the upgraded system. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I&#8217;ve seen clients who had both the old and new systems available on a single network, and applications talked to a mixture of the systems. <em>What a mess!<\/em><\/p>\n\n\n\n<h3 id=\"h-how-to-run-a-side-by-side-migration-backup-and-restore-log-shipping-or-availability-groups\" class=\"wp-block-heading\">How to run a side-by-side migration: backup and restore, log shipping, or availability groups<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">There are many options for how to do the actual migration. You can use backup and restore, log shipping, and availability-group-based approaches where supported.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>I recommend log shipping for large database migrations<\/strong>. How log shipping works is simple: a final tail log is taken on the old system and applied to the new system when you&#8217;re ready to migrate. I&#8217;ve seen that take only a few seconds.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It&#8217;s for these reasons that I prefer carrying out a side-by-side migration for most production systems.<\/p>\n\n\n\n<h3 id=\"h-in-place-vs-side-by-side-at-a-glance\" class=\"wp-block-heading\">In-place vs side-by-side at a glance<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th><\/th><th>In-place upgrade<\/th><th>Side-by-side migration<\/th><\/tr><\/thead><tbody><tr><td>How it works<\/td><td>Installs the new SQL Server version over the existing instance<\/td><td>Builds a new instance and moves databases and objects to it<\/td><\/tr><tr><td>Main advantage<\/td><td>Simplicity: no need to migrate every database, recreate logins or redirect applications<\/td><td>New environment can be built and tested independently; hardware and OS can be upgraded at the same time<\/td><\/tr><tr><td>Main risk<\/td><td>Rollback is difficult because you modify the existing instance<\/td><td>More work up front<\/td><\/tr><tr><td>Downtime<\/td><td>Required, plus a rollback window<\/td><td>Can be as little as seconds with log shipping<\/td><\/tr><tr><td>Application cutover<\/td><td>All at once<\/td><td>Can move applications one by one<\/td><\/tr><tr><td>Original system afterwards<\/td><td>Overwritten<\/td><td>Remains available for testing<\/td><\/tr><tr><td>Best for<\/td><td>Smaller or lower-risk systems, with a full image backup in place<\/td><td>Most production systems<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 id=\"h-sql-server-2022-vs-sql-server-2025-which-should-you-upgrade-to\" class=\"wp-block-heading\">SQL Server 2022 vs SQL Server 2025: which should you upgrade to?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Clients used to be hesitant about upgrading to a new version of SQL Server, instead choosing to wait until at least one service pack had been released, but the approach has been different since the <a href=\"https:\/\/support.microsoft.com\/en-us\/servicing\/sql\/sql-server-2017\/cumulative-update\/kb4041553-sql-server-service-packs-are-discontinued-starting-from-sql-server-2017\" target=\"_blank\" rel=\"noreferrer noopener\">discontinuation<\/a> of service packs nearly a decade ago.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">As I mentioned earlier, I see customers choosing SQL Server 2022 simply because it&#8217;s been available for longer and they think that means it&#8217;ll be more stable. That&#8217;s not quite how I see it, though. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ever since the team moved to using a common code base (many years ago), the best patched version of SQL Server 2022 is SQL Server 2025. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re using brand new features in SQL Server 2025, that&#8217;s a different story &#8211; but if you&#8217;re using the same features that you were using on SQL Server 2016, <strong>SQL Server 2025 is the best patched version of SQL Server you can use<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Given <strong>SQL Server 2022 reaches the end of extended support in January 2033, and SQL Server 2025 extends that to January 2036<\/strong>, moving to SQL Server 2025 gives you another three years of supported lifecycle. That&#8217;s an important difference if you&#8217;re undertaking a substantial migration. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">After all, why invest heavily in an upgrade only to face another one sooner than you need to?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In my experience, SQL Server 2025 just runs SQL Server 2016 applications <em>better<\/em>. It&#8217;s the result of various improvements such as intelligent query processing, security, developer capabilities, and integration with modern data platforms. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">At my company, we&#8217;ve been particularly impressed with the adaptive query plan changes that have happened over the years.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">However, SQL Server 2025 is not better for <em>every<\/em> workload. There are many things you need to consider, like changes to linked-server connectivity and requirements around encryption.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Also, some components have been removed, such as <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/master-data-services\/master-data-services-overview-mds?view=sql-server-ver16\" target=\"_blank\" rel=\"noreferrer noopener\">Master Data Services (MDS)<\/a> and <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/data-quality-services\/data-quality-services?view=sql-server-ver16\" target=\"_blank\" rel=\"noreferrer noopener\">Data Quality Services (DQS)<\/a>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That hasn&#8217;t affected us and our clients yet, but you need to check your own situation. If you rely on MDS and\/or DQS, or a software vendor hasn&#8217;t certified SQL Server 2025, SQL Server 2022 is probably better for you in the interim.<\/p>\n\n\n\n<h2 id=\"h-verdict-choose-sql-server-2025-unless-there-s-a-clear-reason-not-to\" class=\"wp-block-heading\">Verdict: choose SQL Server 2025 unless there&#8217;s a clear reason <em>not<\/em> to<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>My preference would be SQL Server 2025 for new migrations, unless there is a demonstrated technical or commercial reason to remain on SQL Server 2022.<\/strong><\/p>\n\n\n\n<h3 id=\"h-sql-server-2022-vs-sql-server-2025-at-a-glance\" class=\"wp-block-heading\">SQL Server 2022 vs SQL Server 2025 at a glance<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th><\/th><th>SQL Server 2022<\/th><th>SQL Server 2025<\/th><\/tr><\/thead><tbody><tr><td>End of extended support<\/td><td>January 2033<\/td><td>January 2036<\/td><\/tr><tr><td>Patch level for features you already use on 2016<\/td><td>Earlier release<\/td><td>Best-patched version, thanks to the common code base<\/td><\/tr><tr><td>Master Data Services (MDS) and Data Quality Services (DQS)<\/td><td>Still available<\/td><td>Removed<\/td><\/tr><tr><td>Vendor certification<\/td><td>May be the only version some vendors support<\/td><td>Check your vendors; some haven\u2019t certified it yet<\/td><\/tr><tr><td>Watch out for<\/td><td>Shorter support lifecycle<\/td><td>Linked-server connectivity changes and encryption requirements<\/td><\/tr><tr><td>Choose it when<\/td><td>You rely on MDS\/DQS or a vendor requires it<\/td><td>You have no demonstrated reason to stay on 2022<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 id=\"h-keep-sql-server-on-premises-or-move-to-azure\" class=\"wp-block-heading\">Keep SQL Server on-premises or move to Azure?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Upgrading SQL Server versions may also be a good time to consider exactly <em>where<\/em> you want SQL Server to be run. On-premises or in the cloud?<\/p>\n\n\n\n<div id=\"callout-block_ea1e045aab60bc5d33e110b352ff01fb\" class=\"callout alignnone\">\n    <div class=\"child-last:mb-0 child-first:mt-0 bg-gray-50 dark:bg-gray-950 p-4xl my-3xl\">\n\n<p class=\"wp-block-paragraph\"><strong>You may also be interested in&#8230;<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/other\/on-premises-vs-cloud\/\" target=\"_blank\" rel=\"noreferrer noopener\">On-Premises vs Cloud<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n<p class=\"wp-block-paragraph\">Keeping SQL Server on-premises can make sense where workloads depend on local applications, low-latency connectivity, particular hardware configurations, or existing infrastructure investments.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">On the second point, never underestimate the impact of latency differences! Last year, I did work for a bank who were moving their systems to AWS-based cloud infrastructure. In theory, they had provisioned resources that were about eight times the size of what they had on-premises. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The result? An overnight process that used to take two hours, now took eight (and that&#8217;s why they got us involved!) As it turned out, most of the issue was latency &#8211; the applications were simply too chatty.<\/p>\n\n\n\n<section id=\"my-first-block-block_57385f32d89e00ac559e9454a1578d66\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Cloud adoption is accelerating, but database migrations aren\u2019t keeping pace. Find out why.<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            The Cloud Migration Divide explores why complex, business-critical databases remain on-premises \u2013 and what\u2019s holding organizations back as confidence fails to scale with complexity.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/solutions\/state-of-database-landscape\/2026\/cloud-migration-divide\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Download the free report: Cloud adoption is accelerating, but database migrations aren\u2019t keeping pace. Find out why.\">Download the free report<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<h2 id=\"h-how-to-migrate-your-sql-server-to-azure-the-three-options-compared\" class=\"wp-block-heading\">How to migrate your SQL Server to Azure: the three options compared<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If <a href=\"https:\/\/www.red-gate.com\/simple-talk\/cloud\/azure\/\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Azure<\/a> is your target for a SQL Server migration, you have three options. Below, I&#8217;ll explain each one to help you determine which is best for your use case.<\/p>\n\n\n\n<h3 id=\"h-azure-sql-database-most-change-best-for-new-applications\" class=\"wp-block-heading\">Azure SQL Database: most change, best for new applications<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-sql\/database\/?view=azuresql\" target=\"_blank\" rel=\"noreferrer noopener\">Azure SQL Database<\/a><\/strong> is my preferred choice for a SQL Server migration, but it&#8217;s more complex than just migrating an existing on-premises SQL Server database. You need to be prepared for redevelopment work. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Migrating to Azure SQL Database makes most sense for greenfield applications (software built entirely from scratch).<\/p>\n\n\n\n<h3 id=\"h-sql-server-on-an-azure-vm-closest-to-what-you-run-today\" class=\"wp-block-heading\">SQL Server on an Azure VM: closest to what you run today<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">SQL Server is highly compatible with<strong> <\/strong><a href=\"https:\/\/azure.microsoft.com\/en-gb\/products\/virtual-machines\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>Azure Virtual Machines (VMs<\/strong><\/a>). Chances are, when running an environment on an Azure VM, it&#8217;ll be very close to what you&#8217;re already running (or have run in the past).<\/p>\n\n\n\n<h3 id=\"h-azure-sql-managed-instance-the-middle-ground\" class=\"wp-block-heading\">Azure SQL Managed Instance: the middle ground<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/sql-mi-configuration-gotchas\/\" target=\"_blank\" rel=\"noreferrer noopener\">Azure SQL Managed Instance<\/a><\/strong> is, in many ways, the middle ground. You don&#8217;t need to manage the platform beneath it, and you can take advantage of automated patching and built-in high availability. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It&#8217;s not the same as SQL Server, though. If you&#8217;re using features like <a href=\"https:\/\/www.red-gate.com\/simple-talk\/other\/filetables-great-for-business-great-for-hobbyist\/\" target=\"_blank\" rel=\"noreferrer noopener\">FILESTREAM or FileTable<\/a>, you need to consider alternatives.<\/p>\n\n\n\n<h2 id=\"h-azure-migration-options-at-a-glance\" class=\"wp-block-heading\">Azure migration options at a glance<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th><\/th><th>Azure SQL Database<\/th><th>SQL Server on Azure VM<\/th><th>Azure SQL Managed Instance<\/th><\/tr><\/thead><tbody><tr><td>Compatibility with on-premises SQL Server<\/td><td>Lowest: expect redevelopment work<\/td><td>Highest: very close to what you run today<\/td><td>High, but not identical<\/td><\/tr><tr><td>You manage<\/td><td>Least<\/td><td>Most (OS and SQL Server)<\/td><td>Less: no platform management<\/td><\/tr><tr><td>Built-in features<\/td><td>Fully managed<\/td><td>Whatever you configure<\/td><td>Automated patching, built-in high availability<\/td><\/tr><tr><td>Watch out for<\/td><td>Redevelopment effort<\/td><td>Running the platform yourself<\/td><td>FILESTREAM and FileTable need alternatives<\/td><\/tr><tr><td>Best for<\/td><td>New (greenfield) applications<\/td><td>Lift-and-shift of existing workloads<\/td><td>A middle ground<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 id=\"h-what-else-to-consider-before-a-potential-azure-migration\" class=\"wp-block-heading\">What else to consider before a potential Azure migration<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">You also need to consider the cost implications of a potential Azure migration, but don&#8217;t just focus on the price of the database service. Make sure you compare the complete cost of compute, storage, licensing, backup, availability, and administration.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A potential move to Azure is often a separate architectural decision and, with most of my clients, SQL Server 2016 reaching end of support would rarely trigger the move. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It <em>might<\/em> be a good idea, though, and preparing for an upgrade is as good a time as any time to consider it.<\/p>\n\n\n\n<h2 id=\"h-sql-server-2016-extended-security-updates-esus-length-cost-and-limits\" class=\"wp-block-heading\">SQL Server 2016 Extended Security Updates (ESUs): length, cost, and limits<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">You might run into a situation where you simply need to keep running SQL Server 2016. If you don&#8217;t want to be unsupported during this time, <strong><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/end-of-support\/sql-server-extended-security-updates?view=sql-server-ver17&amp;tabs=sql-vm\" target=\"_blank\" rel=\"noreferrer noopener\">Extended Security Updates (ESUs)<\/a> provide critical security updates for three additional years, ending in July 2029<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">However, ESUs aren&#8217;t the equivalent of &#8216;normal&#8217; product support, so don&#8217;t expect new functionality or routine bug fixes. You also need to consider the cost. I&#8217;m always nervous about mentioning prices, but I&#8217;m told by customers that Microsoft have quoted around 75% of the on-premises license cost each year. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Note: I only mention that as a guide. Actual charges depend on many things.<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There was a period when moving an unsupported SQL Server workload to an Azure VM automatically unlocked free ESU support, but I&#8217;m told that&#8217;s no longer the case and SQL Server 2016 VM ESUs must be paid for. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Again, though, talk to Microsoft for further information specific to your use case.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">ESUs might be useful if you <em>know<\/em> an application vendor needs more time before upgrading, and they&#8217;ve already committed to development of a &#8216;replacement&#8217; version. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Otherwise, it&#8217;s probably time to find a new application vendor instead.<\/p>\n\n\n\n<h2 id=\"h-database-compatibility-levels-why-they-matter-when-upgrading\" class=\"wp-block-heading\">Database compatibility levels: why they matter when upgrading<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Originally introduced to avoid upgrade blockers, database compatibility levels have been useful tools over the years. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine you had a database object called &#8216;XYZ&#8217;, but &#8216;XYZ&#8217; has suddenly just become a <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/language-elements\/reserved-keywords-transact-sql?view=sql-server-ver17\" target=\"_blank\" rel=\"noreferrer noopener\">reserved word<\/a> in the latest version of SQL Server. That could stop you dead in your tracks, but with <a href=\"https:\/\/www.brentozar.com\/archive\/2023\/04\/what-does-setting-the-sql-server-compatibility-level-do\/\" target=\"_blank\" rel=\"noreferrer noopener\">database compatibility levels<\/a> you could run the code with the previous level <em>while<\/em> upgrading your code to avoid the issue.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Nowadays, however, they&#8217;re much more than that. Your database doesn&#8217;t have to be at the same level as your server, for example. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A database running at compatibility level 130 on SQL Server 2016 can <em>stay<\/em> on that level after moving to either SQL Server 2022 or 2025 &#8211; allowing the engine upgrade and compatibility-level change to be treated as separate activities.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I&#8217;d still recommend that you <em>initially<\/em> keep the existing compatibility level to make sure the apps work as expected. You can then raise the compatibility level as a separate, planned change. Try to avoid running at the lower compatibility level indefinitely.<\/p>\n\n\n\n<h2 id=\"h-sql-server-upgrade-checklist-7-things-to-do-before-you-start\" class=\"wp-block-heading\">SQL Server upgrade checklist: 7 things to do before you start<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before scheduling a SQL Server version upgrade, I&#8217;d suggest you do the following (and it&#8217;s more than just running SQL Server Setup).<\/p>\n\n\n\n<h3 id=\"h-take-inventory\" class=\"wp-block-heading\">Take inventory<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Record databases, SQL Server versions and editions, operating systems, service accounts, linked servers, SQL Agent jobs, SSIS packages, encryption keys, and high-availability configurations.<\/p>\n\n\n\n<h3 id=\"h-check-application-support\" class=\"wp-block-heading\">Check application support<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Confirm that applications, drivers, and third-party components support the proposed SQL Server version.<\/p>\n\n\n\n<h3 id=\"h-check-version-compatibility\" class=\"wp-block-heading\">Check version compatibility<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Identify any discontinued features, deprecated syntax, or configuration changes that could block the migration<\/p>\n\n\n\n<h3 id=\"h-establish-performance-baselines\" class=\"wp-block-heading\">Establish performance baselines<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Before starting a SQL Server version upgrade, make sure you capture representative CPU, memory, <a href=\"https:\/\/en.wikipedia.org\/wiki\/Input\/output\" target=\"_blank\" rel=\"noreferrer noopener\">I\/O (input\/output)<\/a>, <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/long-running-query-detection-in-sql-server\/\" target=\"_blank\" rel=\"noreferrer noopener\">query-duration<\/a>, and <a href=\"https:\/\/www.red-gate.com\/simple-talk\/free-stuff\/sql-server-performance-tuning-using-wait-statistics-a-beginners-guide\/\" target=\"_blank\" rel=\"noreferrer noopener\">wait-statistics<\/a>. If it makes sense for you, enable and configure <a href=\"https:\/\/www.red-gate.com\/simple-talk\/other\/t-sql-tuesday-181-query-store-and-its-evolution\/\" target=\"_blank\" rel=\"noreferrer noopener\">Query Store<\/a> (which I know is not for everyone!)<\/p>\n\n\n\n<h3 id=\"h-validate-backups\" class=\"wp-block-heading\">Validate backups<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Take current full backups, ensure the necessary log-backup chain is available, and perform test restores on other servers. Make sure that encryption certificates and keys really are both available <em>and<\/em> recoverable.<\/p>\n\n\n\n<h3 id=\"h-rehearse-the-migration\" class=\"wp-block-heading\">Rehearse the migration<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Where possible, rehearse the complete migration using a representative workload, including application connectivity, security, scheduled jobs, and maintenance operations.<\/p>\n\n\n\n<h3 id=\"h-document-the-cutover-process\" class=\"wp-block-heading\">Document the cutover process<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Define the sequence of activities, responsibilities, expected downtime, validation checks, and the point at which <a href=\"https:\/\/www.red-gate.com\/simple-talk\/podcasts\/rollback-vs-roll-forward-in-databases\/\" target=\"_blank\" rel=\"noreferrer noopener\">rollback<\/a> will be required.<\/p>\n\n\n\n<h2 id=\"h-sql-server-upgrade-rollback-why-you-need-a-plan-before-you-start\" class=\"wp-block-heading\">SQL Server upgrade rollback: why you need a plan before you start<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">I often find that rollback is the weakest part of the upgrade plan for many organizations. They tend to forget that <strong>SQL Server database upgrades are usually one-way operations<\/strong>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is because, once a database has been upgraded to a newer engine version, its files or newer-version backups can&#8217;t simply be restored onto SQL Server 2016 &#8211; even if the database compatibility level stays the same.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">While an in-place upgrade might seem simple, recovery might require you to rebuild the whole environment and restore backups.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Even though you still have the original system with a side-by-side upgrade, you might have a data issue if any work was attempted on the upgraded system. That would potentially be lost.<\/p>\n\n\n\n<div id=\"callout-block_ea1e045aab60bc5d33e110b352ff01fb\" class=\"callout alignnone\">\n    <div class=\"child-last:mb-0 child-first:mt-0 bg-gray-50 dark:bg-gray-950 p-4xl my-3xl\">\n\n<p class=\"wp-block-paragraph\"><strong>You may also be interested in&#8230;<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/podcasts\/rollback-vs-roll-forward-in-databases\/\" target=\"_blank\" rel=\"noreferrer noopener\">Rollback vs Roll Forward in Databases<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n<h2 id=\"h-summary-how-to-upgrade-from-sql-server-2016\" class=\"wp-block-heading\">Summary: how to upgrade from SQL Server 2016<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>If you&#8217;re using SQL Server 2016, you probably don&#8217;t need to move away from it &#8211; but the support deadline shouldn&#8217;t be the only decision point.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Generally, I&#8217;d recommend a side-by-side migration to SQL Server 2025 and that will be the strongest long-term option, but only if your application support is available. If not, SQL Server 2022 might still be a valid choice, but I&#8217;d suggest only where application compatibility or vendor certification demands it.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you are upgrading, you should consider whether Azure SQL Managed Instance or SQL Server on Azure VMs make sense.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Finally, if more time is genuinely needed, Extended Security Updates can provide some limited breathing space.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In the end, you don&#8217;t just want to end up with a new version of SQL Server. You need to have an environment that&#8217;s supported, tested, recoverable, and better positioned for the years ahead.<\/p>\n\n\n\n<section id=\"my-first-block-block_f2e7a693d4813d11c12931277e9c72cd\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Fast, reliable and consistent SQL Server development&#8230;<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            &#8230;with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/products\/sql-toolbelt-essentials\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Learn more &amp; try for free: Fast, reliable and consistent SQL Server development...\">Learn more &amp; try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: upgrading from SQL Server 2016<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. Is SQL Server 2016 still supported?<\/h3>\n            <div class=\"faq-answer\">\n                <p>No. SQL Server 2016 reached the end of extended support in July 2026, so it no longer receives regular security updates or bug fixes. Organizations that need more time can buy Extended Security Updates (ESUs), which provide critical security updates for three additional years, ending in July 2029.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. Can I upgrade directly from SQL Server 2016 to SQL Server 2025?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Yes, if SQL Server 2016 has Service Pack 3 (SP3) applied, you can upgrade in place directly to SQL Server 2025, provided your edition and operating system meet the requirements. You don\u2019t need to step through SQL Server 2019 or 2022 first. Check the OS carefully: SQL Server 2025 can\u2019t be installed on Windows Server 2016.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. Should I upgrade to SQL Server 2022 or SQL Server 2025?<\/h3>\n            <div class=\"faq-answer\">\n                <p>For most migrations, SQL Server 2025 is the better choice. It\u2019s the best-patched version, and its extended support runs to January 2036 versus January 2033 for SQL Server 2022. Choose SQL Server 2022 only if a vendor hasn\u2019t certified SQL Server 2025, or you rely on removed components such as Master Data Services or Data Quality Services.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. How long do SQL Server 2016 Extended Security Updates last?<\/h3>\n            <div class=\"faq-answer\">\n                <p dir=\"ltr\">ESUs for SQL Server 2016 provide critical security updates for three additional years, ending in July 2029. They don\u2019t include new features or routine bug fixes, and they\u2019re an extra cost that varies by situation, so confirm pricing with Microsoft. They suit organizations waiting on an application vendor to certify a newer version.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">5. Can I roll back a SQL Server upgrade?<\/h3>\n            <div class=\"faq-answer\">\n                <p dir=\"ltr\">Not easily. Upgrades are usually one-way: a database upgraded to a newer version, or its newer-version backups, can\u2019t be restored onto SQL Server 2016. After an in-place upgrade, rollback often means rebuilding the server and restoring backups. A side-by-side migration keeps the original system, but data changed on the new system could be lost.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server 2016 support has ended. Compare SQL Server 2022 vs 2025, in-place vs side-by-side upgrades, Azure and ESUs, with a pre-upgrade checklist.&hellip;<\/p>\n","protected":false},"author":346483,"featured_media":104584,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[137091,10,143523,53,143524],"tags":[5364,5336,4168,4170,4150,4151],"coauthors":[159368],"class_list":["post-113942","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","category-cloud","category-databases","category-featured","category-sql-server","tag-azure","tag-cloud","tag-database","tag-database-administration","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/113942","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/346483"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=113942"}],"version-history":[{"count":24,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/113942\/revisions"}],"predecessor-version":[{"id":113997,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/113942\/revisions\/113997"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104584"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=113942"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=113942"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=113942"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=113942"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}