In this article, We will explain step by step how to prepare Plan Of Action(POA) for In-Place upgrading SQL Instance from SQL Server 2008 R2 to SQL Server 2016.
POA for upgrading instance from SQL Server 2008 R2 to SQL Server 2016 | |
Pre-requisites to be considered and to take action | |
1 | Check if any database HA is enabled or not. |
3 | Run SQL Server 2016 Upgrade Advisory to find out possible errors during up-gradation and fix the OS\SQL\DB related errors if any. |
Implementation Plan | |
Sl.No | Activity details |
1 | Stop the applications which are pointing to the databases on prod DB server. |
2 | Take the snap shot SQL server version and Edition details. |
3 | Take the backup of all the databases to disk. |
4 | Take the backup of all the linked servers, credentials, proxy and SSRS encryption key(if any). |
5 | Take the backup of all the logins, jobs and SSIS packages(if any). |
6 | Check the collation level of the instance(Take snap). |
7 | Note\take snapshot of memory configuration,LPM accounts,services,ports etc for the instance. |
8 | Take backup of sys.configurations,sp_helpdb,sys.master_files,sys.databases to an Excel file. |
9 | Take the snap shot of SQL server instance properties, Configuration Manager, SSRS configurations etc. |
10 | Run the setup and select the option ‘Upgrade from sql server 2005,2008 and 2008 R2 to 2012’ and perform instance upgrade. |
11 | Restart the server post up-gradation completes. |
12 | Check the services,configurations, DB accessibility and verify error logs. |
13 | Start the application and check the functionality. |
Roll back plan | |
1 | Uninstall the SQL server 2016 and all its components from control panel and restart server. |
2 | Install the SQL sever 2008 R2 SP2 Enterprise Edition and restart the server. |
3 | compare the collation level and version details. |
4 | configure the memory settings. |
5 | Restore all the user database. |
6 | Restore all the logins and jobs. |
7 | Restore all the Linked server. |
8 | Configure the LPM settings as per the snap shot taken. |