Search This Blog

SQL server 2012 upgrade Check list (Non Clustered environment)

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.

No comments: