Step-by-Step Process to Upgrade SQL 2008 to SQL 2016
There are generally two manual methods to upgrade to SQL 2016, which involve:
Performing side-by-side upgrade – It involves creating a full backup of existing SQL Server database and a clean installation of SQL Server on a new operating system
In-place upgrade of the earlier SQL database
Both of these methods have their own advantages and disadvantages. The side-by-side upgrade helps in eliminating the registry and system corruption that often sneaks into Windows installations. It offers a more stable installation of newer SQL Server. Also, you can avail the database on the original system while installing on a new computer.
But, side-by-side upgrade of SQL 2008 is safest and takes little time to upgrade the Server but eliminate long downtime if any unwanted issue occurred. In the side-by-side upgrade, you’ll have to backup and restore all the database. Many times, you’ll have to implement a new version of Windows Server, which often requires a new license for the operating system. Also, you’ll have to restore the customized settings and properties of SQL server that are changed during migration, and then reapply them to the new SQL server.
1. New server should build with latest OS
2. Document environment pre-requisite
3. Study deprecated features and discontinued features
3.Run upgrade advisor to know Breaking changes and Behavior Changes because some of the keywords\functionality or other things are deprecate in the upper versions you have to make sure your application should run after migrate to higher version
4.Identify SQL Server services - Database engine, SSIS, SSAS, SSRS, etc if any
5.Database collation upgrade considerations
6.Application connection requirement
7.Plan to manage other technology features - log shipping, clustering, database mirroring, replication, full-text search, DQS, etc, if any
8.Manage Linked Server availability if any
9.Plan database backups – Full backup, differential backup and transactional log backup
10.Manage required disk size
11.Manage separate service accounts
12.Check data consistency
13.Pre upgrade - performance metrics to compare after upgrade performance
14.Estimate downtime
15.Finalize upgrade approach
16.Upgrade validation criteria
17.Final acceptance criteria
18.Rollback plan and testing
19.Notification to all involved and impacted stakeholders
20.Post deployment steps
21.Ready with new/modified database maintenance plan
Based on your requirement you can add the steps..
