Here is the in place upgrade checklist I created for migrating from SQL 2008R2
Pre upgrade Checklist
- Make sure the hardware requirements are satisfied; use System Configuration Checker (SCC) that comes part of sql server setup to verify
- SQL Server 2012 requires Windows Server 2008 R2 SP1 or later
- Analyze the legacy sql server using SQL server 2012 Upgrade advisor
- Ensure that SQL Server 2012 installation binaries and SP1 are available locally
- Ensure SQL Server 2008R2 installation is available (for rollback )
- Install pre-requisite files
- .NET Framework 4.0
- Windows PowerShell 2.0
- .NET 3.5 with Service Pack 1
- Script logins and users
- Script all the sql jobs
- Make a note of all the maintenance plans
- Make sure that backups are taken
- Run CHECKDB on all databases to ensure that they are in a consistent state.
- Change all the ‘read only’ databases to ‘read write’
- Ensure that all the databases (especially system databases) are configured for ‘AutoGrow’
- Set the AUTO_UPDATE_STATISTICS option to ON for each database
- Make a note of Service start up accounts and passwords.
- Note the Memory settings for the SQL Server Instance
- Kill all the current connections and prevent the application from connecting to the database by disabling the application specific logins )
During Upgrade
- Start the SQL Server 2012 Setup program.
- Select required SQL Server 2012 components.
- Select SQL Server Database Services and any other desired components, such as Workstation Components, SQL Server Books Online, and Development Tools.
- no need to select SSAS or SSRS as these are installed on any of our environments
- Select the default or named instance of SQL Server 2005/2008/2008 R2 to be upgraded. (You can only upgrade one instance at a time in-place.)
- Follow the instructions shown in the screen
Note: The in-place upgrade process automatically upgrades all system and user databases.
Post-upgrade check list
- Install SQL Server 2012 Tools as in place upgrade won’t upgrade the tools.
- Change the compatibility level of the databases (including master db)
- Run the results of the following query on a new window
- SELECT 'ALTER DATABASE '+NAME+' SET COMPATIBILITY_LEVEL = 110' FROM sys.databases WHERE compatibility_level <> 110
- Set the SQL Server memory back to the old settings, as upgrade process resets these values.
- Run DBCC CHECKDB WITH DATA_PURITY;
- EXEC sp_MSForeachDB @command1 = 'Use ?;DBCC CHECKDB WITH DATA_PURITY;'
- For each user database run DBCC UPDATEUSAGE(db_name)
- EXEC sp_MSForeachDB @command1 = 'Use ?; DBCC UPDATEUSAGE(0);'
- For each user database update stats
- EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';
- Check for application connectivity
- Make sure that the scheduled tasks / maintenance plans are migrated
- Verify Linked servers if any
- Verify SPNs if any
- Enable user logins
- Run the application and test.
- Run the backup job
- Uninstall SSMS 2008
Rollback plan
- Run ‘setup’ from sql server 2008 installation folder
- Select the components
- Choose named instance and enter the appropriate instance name
- Enter the service account details
- After the installation restart the server in single user mode to restore master db
- In SQL Server Configuration Manager, click SQL Server Services.
- In the right pane, right-click SQL Server (<instance_name>), and then click Properties.
- On the Advanced tab, in the Startup Parameters box, include ‘-m;’ as shown below
- Click OK.
- Restart the Database Engine.
- Restore master and msdb databases
- C:\> sqlcmd –S<ServerName>\<instanceName>
- 1> RESTORE DATABASE master FROM DISK = 'x:\Backups\SystemDatabases\master\master.bak' WITH REPLACE;
- 2> GO
- In order to restore msdb, stop sql server agent and restore the database using
- RESTORE DATABASE msdb FROM DISK = 'x:\Backups\SystemDatabases\msdb\msdb.bak' WITH REPLACE;
- Restart SQL server Service in multi user mode by removing ‘-m;’
- Make sure that all the logins are restored
- Ensure that the sql server agent jobs and maintenance plans are in place
- attach / restore all the user databases
- Start the application and test.
