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. |
