Upgrading to SQL2012 / SQL2014

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Upgrading to SQL2012 / SQL2014

Author

Topic

Kristen
Test

22859 Posts

Posted - 2015-04-30 : 11:01:29

PLEASE NOTE: I have migrated this thread to the new forum, any changes / additional material will only be posted there. Please switch to the new forums using this link, rather than reading this outdated version. Thanks

http://forums.sqlteam.com/t/upgrading-to-sql2012-sql2014/1020

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.

Kristen
Test

22859 Posts

Posted - 2015-04-30 : 14:38:24

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 MyViewName

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 ???

Kristen
Test

22859 Posts

Posted - 2015-05-08 : 04:07:35

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

DBCC CHECKDB all databases (inc. system) before you start migration testing, and again before any final cut-over

Are there any issues / methods for migrating DTS packages? (That was a pain from SQL 2000 to SQL 2005)

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


SELECT 'Collation', SERVERPROPERTY( 'Collation' )

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

SELECT @@VERSION
EXEC Master..xp_msver

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
Is this still the best way of transferring Logins?

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)

USE master
GO
-- Check compatibility level:
SELECT compatibility_level
FROM sys.databases
WHERE name = 'MyDatabase'
GO

-- SET compatibility level:
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 120
GO
ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM WITH NO_WAIT
GO

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!)


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

Update usage

DBCC UPDATEUSAGE (MyDatabase) WITH COUNT_ROWS -- , NO_INFOMSGS

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

DBCC CHECKDB WITH NO_INFOMSGS, DATA_PURITY

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)


USE MyDatabase
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]

(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

DBCC CHECKDB WITH NO_INFOMSGS, DATA_PURITY

Make a full set of Regression and Performance tests before going Live

Kristen
Test

22859 Posts

Posted - 2015-05-08 : 04:08:39

Some questions:

Best way of migrating SQL Agent Jobs from an older version to a newer version?

Are there any recommended settings that were not available / tended not to be used in older versions? For example ASync statistics rebuild?

Kristen
Test

22859 Posts

Posted - 2015-05-11 : 16:33:51

I have migrated this thread to the new forum, any changes / additional material will only be posted there. The latest version, on the new forums, is here: http://forums.sqlteam.com/t/upgrading-to-sql2012-sql2014/1020

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources