Shawn Meyers (@1dizzygoose), Principal Architect 

On July 9, 2019 Microsoft will officially end extended support for SQL Server 2008 and SQL Server 2008 R2.  This means that no further security updates will be available and support for issues will also end.  You can pay to extend support with Microsoft, but I’ve heard that the contracts are very expensive. However, it is past time to start your upgrade project if you plan on upgrading.  Brent Ozar recently did a poll as part of his blog and 15% of the SQL Servers in production were still running 2008 or 2008R2, based upon his respondents.  If you have many servers left running 2008 or 2008 R2, you will most likely miss the July deadline.  This blog will not describe how to properly complete an upgrade, as there are probably hundreds of articles out there on that topic, but rather will focus on compromises you can make to minimize the impact of not upgrading in time.  There are multiple options, and while most of them should not be at the top of your list, they are options you can consider for making the upgrade project smaller, because you have procrastinated thus far.

Windows Server 2008 will also be going out of support six months later in January of 2020.  Don’t scramble to finish one upgrade and then have to worry about the next one.  Make sure you are moving to a new OS, at least Windows Server 2016 if possible.  Start your list of SQL Servers running newer versions of SQL Server on Windows Server 2008 as well, since that will be your next project.

Do I need support?

Can I survive without support? This question comes up all the time.  It really depends on the risk factors your organization has and what sort of data is stored on these servers.  If your business runs on a database sitting on a 2008 R2 server, can you risk not being able to get it back up?  If you have nine years of experience running on this version, you can be pretty confident your organization knows how it runs, and finding a new bug now in your application is unlikely.  However, the other aspect of support is security patches.  Microsoft will stop issuing security patches for SQL Server 2008, and if some new threat is uncovered, you may face exposure.  If the data is running IT management applications like your AV system, you may not care, but if it has PII or trade secrets you should be a lot more concerned.  If you have regulatory compliance rules, like PCI, HIPAA, or HiTRUST, anything running on 2008 will fail an audit.  Upgrading is a business decision, not a technical decision, so make the managers who don’t have to be up at 3am fixing a bad ETL process earn their keep and make their decisions based on accepting risk. Your job is to document potential risks.

Inventory current systems

Now that your back is against the wall, inventory what you need to upgrade and what can stay on 2008.  Recently, I was working with a client trying to push the upgrade of 65 systems to 2016 as fast as possible, and started with the oldest servers first.  When I say the oldest servers, I am talking their SQL Server 2005 servers, and I told them stop being silly.  Since these systems were already way out of support, I suggested they focus on their critical systems instead.  We took their list and changed it up and went from 65 systems to 22 key systems.  They still have 65 systems to upgrade, but 22 of them are the most critical.  So, they are going to focus their efforts where they get the most return on effort.  Who knows when they will upgrade the SQL Server 2005 servers (if ever). After all, once you have been out of support for long enough, does it really matter?  I still see some SQL Server 2000 boxes in production with no plans to upgrade them, since they just work.  It is good thing they are virtual machines and not 18-year old hardware.

Use the free Microsoft tools

Microsoft has replaced Upgrade Advisor (UA) with SQL Server 2016, but SQL Server 2012 and 2014 still use Upgrade Advisor.  The new tool included with SL Server 2016 is called Data Migration Assistant (DMA) and has some new features that help with server level settings, making things easier than Upgrade Advisor did.  But one great feature from the UA was that you could run a sample workload through the tool to see if there were issues, but in DMA that feature is missing.  If your target will be SQL Server 2012 or 2014, use both tools, but if you are going to SQL Server 2016 or 2017, then use DMA.  DMA does a good job and letting you know if your database can run in Azure DB or Azure Managed Instance.  If you don’t currently have experience running databases in these places, you don’t have time to learn and upgrade your systems properly.  So, stick with what you know.  Microsoft has done a great job showing you how to lock into their ecosystem, and for many it will make sense long term.

In-place upgrades

In-Place upgrades actually work well, and I haven’t had any issues with them in years.  Granted, I hate doing an in-place upgrade, and I will do everything I can to build new server and migrate the workload instead.  Of the few times that I was forced to upgrade in place, I experienced very few, if any, issues.  Microsoft has made the process much less painful over the years.  With the lack of service packs going forward, it may be okay to consider more in-place upgrades if your rollback plan can handle a revert from snapshot.

Trade-offs

We all want to do a project once, and do it right.  The quote by John Wooden goes, “If you don’t have time to do it right, when will you have time to do it again?”. I often see things that were meant to be temporary fixes in production still in use years later.

Unfortunately, you’re now out of time for doing it right, and need to focus on getting it done.  The longest phase of any project should be the planning stage.  The planning should already be done by now, but if it’s not, you really don’t have time to plan anymore.  The challenge now is that you need to figure out how to manage a project where plans are being made, while migration tasks are moving forward.  This tends to be like managed chaos, but can be accomplished with a strong team.

Testing and QA are typically the largest bottlenecks during an upgrade.  Proper testing of all your applications in all scenarios may not be possible with the time you have available.  Trying to determine what levels of testing can be skipped to get all of the databases migrated comes down to prioritizing the work. Work to fix existing bugs and to add new features does not stop while you’re trying to complete the upgrade.

For compatibility mode, go ahead and use this setting and don’t try to update it.  Especially if you’re moving to SQL Server 2014 or higher, the new cardinality estimator can cause some issues.  So, to get the upgrades done, set rules in place to use the old cardinality estimator, and all databases will be left in 2008 compatibility mode.  Completing the smallest possible upgrade to get more time, might be your best answer.  After all, moving from 2008 to 2012 is easier to than moving to 2017.  Granted it means you have to do another upgrade sooner rather than later, but it makes things easier right now. But keep in mind that July 2022, when SQL Server 2012 will be going out of support, will be here before you know it, and this process may start all over again fairly soon.  I like to say make many mistakes once, but many organizations make the same mistakes over and over again.

Migrating to Azure is another option to consider.  I know I said earlier in this article that you don’t have time to learn Azure DB or Managed Instance.  However, running your SQL Server 2008 as a stand-alone Azure VM is an option.  Microsoft will provide three years of extended support for SQL Server 2008 for customers who decide to go this route.  You can read more about this possibility here.  Azure has some great tools for migrating an on-premises workload to Azure.  With this method, you will be running the same OS and SQL Server version, and have another three years to complete your upgrade.  You will be locked into Azure using the lift and shift method however, which means this is likely the most expensive option.  If you plan your cloud migrations, perform sizing and consolidation, and complete edition downgrades, you can control costs.  But, if you’re scrambling and don’t plan, and instead just move to Azure to buy more time, your eyes may pop at the bills that start showing up every month.  Not planning will cost you, but this option is valid if you need to maintain support and you can’t find another way to upgrade.

Automation is key

At House of Brick, we have helped many customers upgrade over the years and we have lot of experience automating as much of the manual tasks as possible. We have automated a variety of tasks for our clients including inventory features installed, server and database level settings, backup and restore, provisioning of virtual machines, using templates, as well as moving logins, jobs, linked servers and everything else needed for an upgrade.  These are tried and true methods that can be utilized to lower the labor involved in the migration process.  The planning and communications processes will still need to be the same, but automation can free up the DBA to do higher level work instead of watching a log backup being moved and applied to a test system.

Aliases
Using aliases for SQL Server and having the clients/web server/middle tier use an alias to connect to the SQL Server can help squeeze out changes and make the upgrade cycles easier.  Now you can just do a DNS redirect during the next migration cycle.  Or if you are upgrading to SQL Server 2016 Enterprise Edition, make sure your plan allows you to utilize Distributed Availability Groups, so your next version upgrade can be a rolling process where you just failover.

Recapitulation
The clock hasn’t run out, but if you are behind on your SQL Server upgrade project, there are things you can do to get as much done as possible.  The total amount of servers upgraded is not as important as updating the critical systems, so now is the time to focus your efforts on the truly important parts.  Taking some shortcuts, minimizing validation, and using old compatibility modes may not be the best answers, but they may be necessary to helping you stay complaint, especially for those of you with regulatory rules breathing down your neck.  If you have to upgrade and are working against a deadline, feel free to give House of Brick a call for some burst capacity for your teams.