Upgrading to SQL2012 / SQL2014

I've copied this thread over from the old forum so I can add to it over time

Are there any recommended documents / BLOG articles, on upgrading (in my case to SQL 2014)?

We will be installing new hardware, so I will be restoring onto the new hardware, rather than upgrading in-situ.

Some of the things I've been thinking of include:

Pre-create databases in order that I can the most optimal VLFs (before restoring any backups)

I have some databases with Compatibility Level = 80. There is no good reason why they are set to that, so I can change them to 100. Presumably I need to do this before I BACKUP to then RESTORE? It would be better if I could do it after RESTORE because that would mean that (Belt & Braces!!) the new compatibility level was tested on the new hardware before actually going live, whereas if I change them on the current system (before Backup) there is a small risk that I then encounter a problem on the [old] Production server.

1 Like

OK, here's how far I have got:

DOCUMENTS

What's New: https://msdn.microsoft.com/en-us/library/bb500435.aspx
Breaking changes: https://msdn.microsoft.com/en-us/library/ms143179.aspx
(Check for earlier versions too [lower down the page], back to the version you are upgrading FROM)
Behaviour changes (Engine): https://msdn.microsoft.com/en-us/library/ms143359.aspx
Behaviour changes (SQL): https://msdn.microsoft.com/en-us/library/cc707785.aspx
Native client changes: https://msdn.microsoft.com/en-us/library/bb964722.aspx
Check Hardware requirements: https://msdn.microsoft.com/en-us/library/ms143506.aspx
Choose installation features https://msdn.microsoft.com/en-us/library/ms143786.aspx

PRE UPGRADE CHECKS

Upgrade advisor: https://msdn.microsoft.com/en-us/library/ms144256.aspx

Check no existing databases have Compatibility levels too old / low

Check each database to make sure it has NO data corruption

DBCC CHECKDB WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY

INSTALL NEW SQL VERSION

Ensure that O/S Service Packs are in place (on target server) before start of SQL install. (Visual Studio may otherwise, require SPacks before its install can complete)

Check that Windows Management Instrumentation service is running
Configure firewall to allow SQL Server access
SQL Server Setup requires user to have administrative privileges

POST INSTALL ACTIONS / CHECKS

Install any Service Packs (Looks like SQL2014 does this during Install)

Double Check that Version is as expected!!

-- Select Version / Service Pack
SELECT @@VERSION
SELECT CONVERT(varchar(1000), SERVERPROPERTY('edition')) 
	+ ' - ' + CONVERT(varchar(1000), SERVERPROPERTY('productlevel'))
	+ ' - ' + CONVERT(varchar(1000), + SERVERPROPERTY('productversion'))
EXEC Master..xp_msver

Upgrading from prior to:

SQL 2008: Change TEMPDB from PAGE_VERIFY to CHECKSUM (default for new installs, required for in-situ upgrades)

SQL 2008 & 2012: For SQL Server Agent scripts manually remove @schedule_uid parameter (last parameter of job schedule) (to prevent DUPs)

Checks for each Database (these are my preferred defaults, but SQL2014 might offer more/different, in which case I'll add to this post)

EXEC sp_dboption 'MyDatabase'

PRINT 'Should :
torn page detection
auto create statistics
auto update statistics'
GO

DBCC useroptions
PRINT 'Should be:
textsize		64512
language		British
dateformat		dmy
datefirst		1
lock_timeout		-1
quoted_identifier	SET
arithabort		SET
ansi_null_dflt_on	SET
ansi_warnings		SET
ansi_padding		SET
ansi_nulls		SET
concat_null_yields_null	SET
isolation level		read committed'
GO

Perform checks for Compatibility Level, Collation, Auto CLose/Shrink, AutoUpdate of Stats etc etc etc

-- Set to new level (assuming APP tested and safe to do so)
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = xxx

DBCC UPDATEUSAGE (MyDatabase) WITH COUNT_ROWS -- , NO_INFOMSGS

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY

Rebuild all indexes and update STATS
BACKUP LOG FREQUENTLY DURING INDEX REBUILD !!!

Refresh Meta data for all views

EXEC sp_refreshview [i]MyViewName[/i]

SQL 2012: Rebuild any columns computed with SOUNDEX

Take a Full Backup before handing the DB over to the users

Also:

Given that I will be installing on a new machine I need to figure out how to migrate:

Logins
Scheduled tasks
Linked Servers
??? Other stuff ???

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

Migrating to a new server

Easier than an in-place upgrade, as the new hardware provides a test platform prior to the actual upgrade, and if anything goes wrong during the upgrade then the original hardware, and database(s) are still available to back-out to.

New hardware also provides a much faster cut-over process too. Forget about Detaching databases and copying them to the new server; you will be offline for the total time that it takes to copy the database files, and database files are considerable larger than backup files.

There are some steps to make prior to the actual cut-over; at this time users can still connect to and work on the Old Server.

Take a full backup on Old Server
Copy the backup files to, and Restore on, the New Server using NORECOVERY option

This can be done considerably in advance of the cut-over (you can use the previous night's Full backup file, rather than making a fresh one).

Do NOT take any additional FULL backups after this point (if you do, or anyone else does!, then repeat the restore)

When you are ready to start the cut-over:

Take a Transaction Backup on Old Server (you don't need to restore this, but it will help keep the subsequent DIFF and TLog backup files as small as possible to reduce copying time)

Take a DIFF backup on Old Server, copy to New Server and restore (with NORECOVERY again)

Once the DIFF restore is completed (just in case it "takes a while") take a TLog backup on Old Server and restore it to New Server (there might have been additional, scheduled, Tlog backups in the meantime - you need to restore all of them, since the DIFF backup was made, in chronological order. Keep using NORECOVERY

OK, once all that is done you are ready to cut-over

The Cut-Over:

Put up holding page on the website / alert users that the system is now down for maintenance

Set the database on Old Server to READ_ONLY to prevent any further changes. Use the ROLLBACK IMMEDIATE option to terminate any existing connections

Take a final TLog backup on Old Server, copy to New Server server and restore. (Make sure to also restore any other TLog backups which have been made by scheduled jobs)

If you are happy that everything went well you can use the RECOVERY option on New Server (you do NOT have to do this as part of the final TLog restore, you can just use the RECOVERY option on its own (i.e. as part of the RESTORE command syntax) without specifying any actual file to restore.)

The database will now upgrade from to latest version. This will take however long it takes ...

(Note that your database will be in READ_ONLY mode at this point, as per the Old Server's setting, so you will need to ALTER it back to READ_WRITE, MULTI_USER on the New Server)

You still need to Update Statistics (query performance may be poor until that is done), but you may feel that you can allow users to connect again whilst that is running.

The database on the Old Server is still set to READ_ONLY / SINGLE_USER so users cannot accidentally connect to it and make changes. In the event that you have a problem with the New Server, during the Migration, then just set the Old Server back to READ_WRITE / MULTI_USER which will re-allow connections.

If everything is OK on the New Server either swap its IP Address / Server Name with the Old Server, or update the Client Connection Strings to point to the new server, or change the DNS Server so that the original name now points to the new server.

Following discussion in some other threads - it seems that folk upgrading from one version to the next may not implement COMPRESSED BACKUPS. This used to be an Enterprise-only feature, but was added to Standard in SQL2008R2.

From my tests using COMPRESS in the Backup command reduces Backup (AND Restore) time by 40% and reduces disk file size by 80%. Even if the disk space is not an issue the saving on Backup / Restore time is well worthwhile.

It is possible to make Compressed Backups the default - so no need to change anything else :slight_smile:

USE master;
GO
EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;

Is it or It is ?

1 Like

It isn't is it, it is it is :slight_smile:

Reminds me of the owner of the "Dog and Fox" complaining to his sign writer that there was too much space between Dog and and and and and Fox"! Please don't complain about the spacing in THIS post ... :yak:

Ali of Mind Your Language would probably reply
Oh blimey ! you are confusing me.

1 Like