Migrating SQL Database from 2012 to 2016/2019 guidelines

Products

CA Release Automation - Release Operations Center (Nolio) CA Release Automation - DataManagement Server (Nolio)

Issue/Introduction

We're looking at migrating our CA Release Automation SQL Database.

Was looking at migrating to 2016

Can you please confirm if they'd be any issues backing up database in 2012 and restoring in 2016

Environment

Release : 6.6, 6.7

Component : CA RELEASE AUTOMATION RELEASE OPERATIONS CENTER

Resolution

We cant provide an affirmative yes/no if the upgrade of DB from lower version of MSSQL to higher version as we can't ascertain any issue may arise due to compatibility. If you are looking for more affirmative answer we request to consult your MSSQL Database Administration or MSSQL Support.

In general MSSQL DB is backward compatible so restoring data from old version dump should not be an issue however you need to check that you keep the compatibility level and some DB level configuration in the newer MSSQL version for smooth performance of applications(whose database is migrated).

Some common Problem post migration faced by applications, whose DB migrated from old version MSSQL to newer versions.

When users upgraded to SQL 2014 or 2016 they found that some specific queries were running much slower than they ran in the previous version. The differences are not trivial. It is not uncommon to see a query that runs in 2 seconds in the earlier version taking 10 minutes in the upgraded version.

Microsoft Explanation: Microsoft has acknowledged the problem but not the severity of the problem for users that are affected. Here is what they said when the problem was first recognized: “Cardinality Estimator updates for compatibility levels 120 and 130 incorporate assumptions and algorithms that work well on modern data warehousing workloads and on OLTP workloads.”

Microsoft Statement: Finally Microsoft stepped in with a fix that is much simpler and still allows you to access the new features in the upgraded version. The name of this fix is Legacy Cardinality Estimation. It gives you the option to use the new CE or use the legacy CE as it existed before the changes were made in 2014. Here is the syntax for enabling or disabling Legacy Cardinality Estimation:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON

As per Microsoft documentation, Cardinality Estimation can miscalculate

In the below mentioned case cases, SQL Server cannot accurately calculate cardinalities. This causes inaccurate cost calculations that may cause suboptimal query plans. Avoiding these constructs in queries may improve query performance. Sometimes, alternative query formulations or other measures are possible and these are pointed out:

- - Queries with predicates that use comparison operators between different columns of the same table.
  - Queries with predicates that use operators, and any one of the following are true:
    - There are no statistics on the columns involved on either side of the operators.
    - The distribution of values in the statistics is not uniform, but the query seeks a highly selective value set. This situation can be especially true if the operator is anything other than the equality (=) operator.
    - The predicate uses the not equal to (!=) comparison operator or the NOT logical operator.
  - Queries that use any of the SQL Server built-in functions or a scalar-valued, user-defined function whose argument is not a constant value.
  - Queries that involve joining columns through arithmetic or string concatenation operators.
  - Queries that compare variables whose values are not known when the query is compiled and optimized.

Check the compatibility_level of database before and post migration(restoration of DB)

SELECT d.name, d.compatibility_level 
FROM sys.databases AS d 
WHERE d.name = 'yourDatabase'; 
GO

Check on QUERY_OPTIMIZER_HOTFIXES & LEGACY_CARDINALITY_ESTIMATION post migration. Please refer Microsoft Documentation for more details.

Additional Information

In case if you are looking for more details and information around How to? please get in touch with Microsoft Technical Support or MSSQL Communities