SQL server Upgrade

 

When you upgrade to a new version of SQL Server, there are some critical things you should do to help avoid any surprise SQL Server performance issues. I mean new version  of SQL Server is 2016,2017 or 2019, Because Microsoft  closed its support up to some extent to older version

Why need the Requirement to upgrade the Version?

Basically it is because Microsoft ended Its support of some versions.

There are two type of Support

1- MainStream Support

2- Extended Support

SQL Server Versions	MainStream Support	Extended Support
SQL Server 2005	April 12, 2011	April 12, 2016
SQL Server 2008 or R2	July 8, 2014	July 9,2019
SQL Server 2012	July 11, 2017	July 12, 2022
SQL Server 2014	July 9, 2019	July 9, 2024
SQL Server 2016	July 13, 2021	July 14, 2026
SQL Server 2017	Oct 11, 2022	Oct 12, 2027
SQL Server 2019	July 1, 2025	Aug 1, 2030

For Upgrading any Version there are some preparations which need to be taken care.

1- Find the Upgrade Blockers. 

2- Clear Understanding of Version and Editions.

3- Clear Understanding of Features introduced and obsolete

3- Make a List of objects which use the features which  are obsolete from the newer version and currently in use.

4- Clear Hardware Requirement for newer Versions. 

For this Activity i am upgrading the Server from 2012 to 2017.

Process to Upgrade the SQL Server Version.

There are 3 ways to upgrade

1. - In-Place Upgrade

2. - Side-by-Side Upgrade

3. - Rolling Upgrade

Steps for an In-Place Upgrade 

In-place upgrades are the easiest to perform, but the most difficult to rollback should there be any issues. The steps involved in an in-place upgrade are as follows:

1. Verify that backups exist for all databases (user and system). 

2. Review the list of requirements for SQL server 2017 and install whatever is needed.

3. Install SQL Server 2017 

4. Restore the Backup

Side-by-Side Upgrade

• 1- Backup all the database

• 2-Script out any and all necessary system objects.

            3-Review the list of requirements for SQL server 2017 and install whatever is needed.

• 4- Run the script to create the Object

• 5-Select database(s) to migrate and it take offline. 

• 6-Migrate database to new instance. Repeat for each database.

•  

 

 

 

Rolling Upgrade

1- Select any High-Availability 

2- Install new Server instance 2017 and make it  as a Secondary node

3- Fail the Primary Node now New Instance will be active and will be Primary Node

 

Checklist need to Perform Before Planning to Upgrade about version of Server

1-Checking of Deprecated Features  in new version

SELECT * FROM sys.dm_os_performance_counters   WHERE object_name  = 'MSSQL$JFT:Deprecated Features   

2- Get the List top indexes which are in use maximum times in the current version.

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name

      ,IX.name                     AS Index_Name

      ,IX.type_desc                 AS Index_Type

      ,SUM(PS.[used_page_count]) * 8     IndexSizeKB

      ,IXUS.user_seeks AS             NumOfSeeks

      ,IXUS.user_scans AS             NumOfScans

      ,IXUS.user_lookups AS             NumOfLookups

      ,IXUS.user_updates AS             NumOfUpdates

      ,IXUS.last_user_seek AS         LastSeek

      ,IXUS.last_user_scan AS         LastScan

      ,IXUS.last_user_lookup AS         LastLookup

      ,IXUS.last_user_update AS         LastUpdate

FROM sys.indexes IX

INNER JOIN sys.dm_db_index_usage_stats IXUS 

ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID

INNER JOIN sys.dm_db_partition_stats PS 

on PS.object_id=IX.object_id

WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1

GROUP BY 

OBJECT_NAME(IX.OBJECT_ID) ,

IX.name ,

IX.type_desc ,

IXUS.user_seeks ,

IXUS.user_scans ,

IXUS.user_lookups,

IXUS.user_updates ,

IXUS.last_user_seek ,

IXUS.last_user_scan ,

IXUS.last_user_lookup ,

IXUS.last_user_update

3- Current Used Space at current instance and Available Space in the new instance.

4- Current OS Hardware Configuration.

5- Scripting of all user defined Objects.