TexCopy Sql Server 2005

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

1. Start the SQL Server 2012 Setup program.
2. 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
3. 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.)
4. 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.