Are you still running SQL Server 2012?

Just in case you didn’t see any news on this, Microsoft ENDED extended support for SQL Server 2012 on July 12, 2022 – https://docs.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-2012.  Do you still have critical databases running on this 10 year old version of SQL Server? Dare I ask, are they running on the ancient Windows Server 2012 operating system which will be unsupported by Microsoft since next October???  It’s time to upgrade!! Here is an overview of the process to get that done.

Know the Options

1. SQL Server 2019
   1. Easiest upgrade
   2. Download the Developer edition here https://www.microsoft.com/en-us/sql-server/sql-server-downloads
2.  SQL Server 2022
   1. CTP 2.0 recently released, full release later this year!
   2. https://www.microsoft.com/en-us/sql-server/sql-server-2022
   3. Lots of new features: business continuity to Azure, analytics, SECURITY, and multiple performance improvements
3. Run in the Azure Cloud
   1. Azure SQL Database
   2. Azure SQL Managed Instance
   3. SQL Server on an Azure VM (can still run 2012 and get security updates)

Questions to Ask

• What is this database for? (Accounting, Human Resources, Critical Application…)
• Is my vendor still supporting it?
• What versions of SQL Server are on the vendor’s application compatibility list?
• What does it take to upgrade that application to be compatible with the latest version of SQL?
• Does that effort/cost outweigh the risk of not being able to call Microsoft with any SQL Server issue?
• What costs do I have (server, licensing, and professional services) to upgrade to SQL Server 2019? Or do I already have licensing for a not-so-recent version?
• Are we running any other SQL Server services like Reporting Services, Integration Services, MDS, DQS, SSAS, replication and more!
• Do I need additional resiliency and high availability with the upgrade process?

Process

1. Make sure you know exactly how many servers/databases you have running SQL Server 2012
   1. Recommend running the Microsoft Assessment and Planning Toolkit to get a full inventory of your database servers
   2. https://www.microsoft.com/en-us/download/details.aspx?id=7826
2. Install the Data Migration Assistant on any machine on the same network as the identified SQL Servers
   1. https://www.microsoft.com/en-us/download/details.aspx?id=53595
   2. View the report on compatibility issues identified for each database, resolve or ignore at your discretion
   3. The report will have a list of detected issues identified by Importance
   4. Review the entire report carefully!!
      1. This report just tells you what could be wrong
      2. It does nothing to actually fix anything
3. At this point we need to decide if the new SQL Server will be a new virtual machine, a new physical host, or a cloud VM
   1. The operating system should be Windows Server 2019 or Windows Server 2022 – please nothing older!
   2. If you choose a physical host, be well aware of how Microsoft licenses SQL Server by core count and purchase the CPU with the highest clock rate you can afford based on core count (no hyperthreading)
   3. Know that licensing will by far be the most expensive line item on this project!
   4. Provision your server, according to SQL Server best practices and you will save a TON of headache later!
4. Time to test
   1. Backup production database(s)
   2. Restore databases to new, SQL Server (2019, I hope) DEVELOPER instance
   3. Change database compatibility level to highest level supported
      1. This will make sure that the new database will be able to use all the query performance improvements and new features.
      2. If there are any breaking changes, we’ll know fast and CAN downgrade in the event the situation cannot be resolved
   4. Purchase, license, and install your chosen version of SQL Server on your new server
   5. Backup the 2012 database and restore to the new SQL Server instance
   6. Run these commands:
      • USE MyImportantDB DBCC CHECKDB WITH DATA_PURITY;
      • DBCC UPDATEUSAGE(MyImportantDB);
      • USE MyImportantDB EXEC sp_updatestats;
   7. Take a full backup
   8. Point a non-production instance of your application, if available, to the new SQL Server instance and run through a few simple tasks
      1. There are several places where you may get a failure…
      2. Definitely view each screen, add a few records, and run a few reports to see how it is performing
      3. Record your results and re-point your application back to the original server
   9. Review your results with stakeholders/management
5. Prepare your users for a cutover
   1. Or be sneaky and don’t tell them!
   1. “Let’s see if they can tell the difference?”
6. Cutover and party like its 2022!!!
   1. This is not an exhaustive list of a thoroughly planned cutover
   1. There are some snarky comments inserted just for your enjoyment, and actual cutover will be assuredly professional

Benefits

By upgrading your organization’s database servers, you not only get your databases onto a fully supported platform, you are also taking advantage of 10+ years of innovation and improvements in Microsoft technology. SQL Server has been battle-tested in the cloud for several years now and Microsoft is using knowledge gained in that endeavor to make incredible improvements in SQL Server. SQL Server 2022 recently hit CTP 2.0 and promises another assortment of improvements, particularly in the server performance space.

Need help getting started on a SQL Server 2022 assessment and upgrade? Contact PBITPro at paul @ pbitpro.com or call 972.987.0637.