What are the impact of existing application and steps need to be followed while upgrading SQL server 2012 to 2016

Question

My company informed to development team to upgrade of SQL server from 2012 to 2016.

What are the impact of existing application and steps need to be followed while upgrading SQL server 2012 to 2016. ?

What are the main points needs to be noted for the applications (web and Windows).?

Answer 1

There are 2 migration approaches

1. Side by side: In which you get new VM, install new version of SQL Server, move databases by backup restore, move other logins and jobs.
2. In-place Upgrade: You run the SQL Server 2016, then select upgrade to SQL Server 2016 and it will upgrade.

The approach you choose depends on downtime you get and complexity of database. Before you start you need to run DMA tool on SQL Server 2012 to find out all the breaking changes when moved to SQL Server 2016. You also have official document stating Breaking Changes in SQL Server 2016 when moving from lower version. The DMA will generate report and you can share it will all the stakeholders, get the approval, and then upgrade.

You also need to test your application to new version of database. Post migration update the stats of complete databases and change the compatibility level of databases to that corresponding to SQL Server 2016. You also have option to continue with old compatibility level.

Now for impact it depends on features and deprecated features you are using in SQL Server 2012. A main concern is "CE regression" which mostly comes when you upgrade to SQL Server 2014 and above due to new CE. This can easily be solved by using old compatibility level or using query hint and force database to use old CE. See example below

ALTER DATABASE SCOPED CONFIGURATION--forcing while DB to use old CE  
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
      
USE db_name  
GO  
SELECT PurchaseOrderID, OrderDate  
FROM Purchasing.PurchaseOrders  
WHERE OrderDate = '2016-05-30'  
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));--forcing only this query to use old CE  

Answer 2

We don't know you application & database code, so I can only say: Test it.

And you can use the tool DMA = Data Migration Assistant to check your database code: Overview of Data Migration Assistant

Answer 3

If you are going to upgrade, I highly suggest you upgrade to a newer version of SQL Server. SQL 2016 is going to be out of mainstream support in 7/2021.

Answer 4

The recommended workflow for upgrading is to leave the old compatibility level after upgrade, and use Query Store to build a baseline of query performance before testing with the increased compatibility level. See

https://learn.microsoft.com/en-us/sql/relational-databases/performance/upgrade-dbcompat-using-qta?view=sql-server-ver15