These are the Upgrade Steps I posted when moving to SQL 2008, re-posted here for any comments, additions, alterations with respect to migration to newer versions
Upgrade Steps
[blue]DBCC CHECKDB[/blue] all databases (inc. system) before you start migration testing, and again before any final cut-over
[red]Are there any issues / methods for migrating DTS packages? (That was a pain from SQL 2000 to SQL 2005)[/red]
When installing new SQL version make sure you set the appropriate folder for installation. SQL Install generates a folder below this, and then a number of sub sub folders - thus choosing to install to the ROOT of a drive is probably sufficient. Also important to avoid SQL installing System Databases on C:\Program Files\ !
Check that the Collation in the new version is the same as you had before
[blue]
SELECT 'Collation', SERVERPROPERTY( 'Collation' )
[/blue]
Check that the new version has the most recent Service Pack applied, and consider if you any of the fixes in Cumulative Updates are relevant to you
[blue]SELECT @@VERSION
EXEC Master..xp_msver
[/blue]
After restoring database to a new server [i.e. running a newer SQL version]:
If new version is on a different server transfer the logins - see http://www.sqlmag.com/article/query-analyser/sql-server-login-transfer.aspx
[red]Is this still the best way of transferring Logins?[/red]
Change the compatibility level to:
100 for SQL 2008 & 2008-R2 compatibility mode
110 for SQL 2012
120 for SQL 2014
See also: https://msdn.microsoft.com/en-us/library/bb510680.aspx
Optionally change Options : Recovery : Page verify = CHECKSUM
(make sure you do this before rebuilding all indexes)
[code][blue]USE master
GO
[teal]-- Check compatibility level:[/teal]
SELECT compatibility_level
FROM sys.databases
WHERE name = 'MyDatabase'
GO
[teal]-- SET compatibility level:[/teal]
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = [red]120[/red]
GO
ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM WITH NO_WAIT
GO
[/blue][/code]
Note: It is advisable to also change the BACKUP commands to add the "CHECKSUM" option to the WITH clause. This will a) check any database file reads that have the Checksum set to ensure that it is correct, and b) add a Checksum too all pages in the backup file - which will provide reassurance and protection on any Restore. This will cause the Backup to abort on any error, which you may not want, so also consider the CONTINUE_AFTER_ERROR option.
Consider changing the database to turn READ_COMMITTED_SNAPSHOT on. If you are using NOLOCK liberally in your code remove it!! and use READ_COMMITTED_SNAPSHOT instead (if you are using NOLOCK frequently you probably have no idea how much damage Dirty Reads may be causing you, and READ_COMMITTED_SNAPSHOT is probably what your thought you wanted when you chose NOLOCK!)
[code][blue]
USE master
GO
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE MyDatabase SET MULTI_USER, READ_WRITE WITH ROLLBACK IMMEDIATE
GO
USE MyDatabase
GO[/blue][/code]
Update usage
[blue]DBCC UPDATEUSAGE ([i]MyDatabase[/i]) WITH COUNT_ROWS [teal]-- , NO_INFOMSGS[/teal]
[/blue]
At the time of the release of SQL 2005 Microsoft said that they had fixed issues with earlier versions that caused inaccurate USAGE figures, however Microsoft appear to still be recommending freshening-up the Usage on installation of new versions, so even if it makes no change probably still worth running DBCC UPDATEUSAGE "belt & braces"
Use DBCC CHECKDB to check that there are no problems in the data
[blue]DBCC CHECKDB WITH NO_INFOMSGS, DATA_PURITY
[/blue]
DBCC CHECKDB in newer versions may detect more issues than in previous versions, hence worth running with the full checking options (such as DATA_PURITY) to be sure that all currently detectable errors have been found. If this takes too long then it can be performed on a freshly made backup (i.e. from the NEW version's installation) restored onto another machine and then checked on that machine.
Reindex ALL the tables / Indexes and Update Statistics
(Note: having a Clustered Index on every table will help be a benefit at this point)
[blue]
USE [i]MyDatabase[/i]
GO
SELECT 'ALTER INDEX ALL ON dbo.[' + [name] + '] REBUILD '
+ 'WITH (ONLINE=OFF, STATISTICS_NORECOMPUTE = OFF)'
FROM sys.tables
ORDER BY [name]
SELECT 'UPDATE STATISTICS dbo.[' + [name] + '] WITH FULLSCAN'
FROM sys.tables
ORDER BY [name]
[/blue]
(Note: Rebuild Indexes will update their statistics, but it won't update the statistics that are not on indexes, so the Update Statistics will re-update the Statistics for Indexes (again!), but also rebuild them for non-indexes
You may want to run Update usage again (Belt&Braces, although real-time maintenance of usage is supposed to be fixed from SQL2008 onwards), and I would do a final DBCC CHECKDB to make sure there are no corruptions in the database
[blue]DBCC CHECKDB WITH NO_INFOMSGS, DATA_PURITY
[/blue]
Make a full set of Regression and Performance tests before going Live
