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
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
- In-Place Upgrade
- Side-by-Side Upgrade
- 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:
Verify that backups exist for all databases (user and system).
Review the list of requirements for SQL server 2017 and install whatever is needed.
Install SQL Server 2017
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.
