SQL2008 Migration: Hints and Tips SQL 2008

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 2008 Forums

SQL Server Administration (2008)

SQL2008 Migration: Hints and Tips SQL 2008

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 11:29:55

Edits: Added link to article on SQL Login migration
Recent changes are in Orange

Please chip in if you've got any other suggestions, or Gotchas - any suggestions for migrating DTS packages?

If coming from SQL 2000 then the SQL Team post on Migrating to SQL 2005 Hints and Tips may be useful, however most of the information here relates to upgrading to SQl2005 and has more benefit of "hindsight" than the earlier post - e.g. suggestions to use CHECKSUM and READ_COMMITED_SNAPSHOT which also existed in SQL2005

I have not found a post on SQL Team relating to SQL7 migration to SQL2000, but if you are coming from that far back you can NOT restore a SQL7 backup directly on to SQL2008, you will have to first restore to either SQL2000 or SQL2005, backup again, and then restore that to SQL2008.

Microsoft's "What's New in SQL 2008" List
Behavior Changes to Database Engine Features in SQL Server 2008

Use "SQL 2008 Database Upgrade Advisor" (Download here) to check for incompatibilities

Upgrade DOCs:
SQL 2008 Technical upgrade reference guide
If you have not got the SQL 2008 client tools installed on your PC you might want to install the SQL2008 Documentation (Books Online or "BOL") so that you have a ready-reference on your desktop

Also if coming from SQL 2000 consider also the changes between SQL2000 and SQL2005:
SQL Server 2005 upgrade handbook
SQL 2005 Technical upgrade reference guide
Behavior Changes to Database Engine Features in SQL Server 2005
SQL Server 2005 Best Practices Analyzer (download) which can be run on SQL 2005 database before upgrading.
"SQL 2005 upgrade advisor" I don't know if all the logic in this is also repeated in the SQL 2008 Upgrade Advisor, so I advise using this in addition to the SQL 2008 one.
If you are only upgrading to SQL 2005 you may want to install the SQL2005 Documentation (Books Online or "BOL") so that you have a ready-reference on your desktop

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 SQL 2008 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

Check that the Collation on SQL2008 is the same as you had before


SELECT 'Collation', SERVERPROPERTY( 'Collation' )

Check that the Version of SQL2008 is the most recent Service Pack, and consider if you any of the fixes in Cumulative Updates are relevant to you
See: http://www.sqlteam.com/article/sql-server-versions

SELECT @@VERSION
EXEC Master..xp_msver

After restoring database to new SQL 2008 server:

If SQL 2008 is on a different server transfer the logins - see http://www.sqlmag.com/article/query-analyser/sql-server-login-transfer.aspx

Change the compatibility level to 100 (SQL 2008 mode)
Change Options : Recovery : Page verify = CHECKSUM
(make sure you do this before rebuilding all indexes)

USE master
GO
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 100
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 choose 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

Use DBCC CHECKDB to check that there are no problems in the data

DBCC CHECKDB WITH NO_INFOMSGS, DATA_PURITY

Comment from Gail Shaw: "Other important reason to run CheckDb after an upgrade is because, on SQL 2000, CheckDB did not pick up all issues. Main thing here is that on SQL 2000 CheckDB did not run checkCatalog. Hence there could be schema corruption (often cause by direct modifications to the system catalogs) and you'd never know. On SQL 2005, CheckDB does run checkcatalog, hence those problems will be immediately picked up.

Orphaned records cause by direct modifications to the system catalog are easy (relatively) to fix on SQL 2000. They're near-impossible to fix on SQL 2005. Hence you want to find those as early as possible so that you can restore the pre-upgrade backup to SQL 2000, fix the errors there, then upgrade again."

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 realtime maintenance of usage is supposed to be fixed in SQL2008), 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

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-01-12 : 12:01:49

In addition to your tips, there is a document out there called "SQL 2008 Technical upgrade reference guide". It is very comprehensive in terms of different upgrade paths, covers cluster upgrades etc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 12:15:34

Thanks, URL = "SQL 2008 Technical upgrade reference guide"

There seems to also be a "SQL 2005 Technical upgrade reference guide"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-16 : 07:46:50

quote:
Originally posted by Kristen

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

And DBCC CheckCatalog. On 2000 CheckDB does not run checkcatalog. Most common problems picked up by checkDB after upgrading are catalog errors. If you can detect them before starting the upgrade, it's the best time to fix.

quote:
Update usage
Will this do?
DBCC UPDATEUSAGE (MyDatabase) WITH COUNT_ROWS -- , NO_INFOMSGS

Yup.

quote:

Reindex ALL the tables / Indexes
Will this do?
Will it Update Statistics on all tables with a full scan?.
SELECT 'ALTER INDEX ALL ON dbo.[' + [name] + '] REBUILD '
+ 'WITH (ONLINE=OFF, STATISTICS_NORECOMPUTE = OFF)'
FROM sys.tables
ORDER BY [name]

Not enough. That'll update all the index statistics with fullscan, but won't touch the column statistics (stats not associated with an index). I rather recommend this

SELECT 'UPDATE STATISTICS dbo.[' + [name] + '] WITH FULLSCAN' 
FROM sys.tables 
ORDER BY [name]

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 08:23:54

So I should do both Index Rebuild and Update Statistics.

Index Rebuild will rebuild all indexes and their stats, that leaves the stats on non-indexes.

On that basis what's the best command for Index Rebuild - can I turn off the Update Stats if I'm going to do that immediately afterwards anyway?

(I could just take the viewpoint that for a migration I don't care to be THAT efficient and just do both, in full?)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-16 : 10:03:30

An index rebuild always updates that index's statistics with fullscan and that cannot be turned off. If you're thinking ALTER INDEX ... WITH STATISTICS_NORECOMPUTE ON, that means that the stats on that index will never be automatically updated ever again (well, at least until someone turns NoRecompute off again). Not necessarily a good thing....

If you really want to be efficient, then use sys.stats to drive the statistics update and just update stats that aren't part of indexes. But you probably won't be saving much, stats updates are generally fairly quick, even on big tables.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 10:19:33

I went and read up on STATISTICS_NORECOMPUTE and understand it better now. I'll leave it in my code to turn it back on for anything that accidentally got turned off!!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-16 : 13:23:39

Ummm.... maybe go read it again. ;-)

There's a double-negative involved here. The setting controls whether the stats will NOT be auto updated
NORECOMPUTE ON = statistics will NOT be updated automatically
NORECOMPUTE OFF = statistics WILL be updated automatically

Bad naming. Very bad naming in fact. I had a go at MS during a presentation at PASS Summit about this and the more I work with it, the more I dislike it.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 13:31:38

I've got "STATISTICS_NORECOMPUTE = OFF" - so I reckon that will turn Stats ON for anything that has accidentally been turned OFF - if I've got that right I had spotted the double negative, and agree with you that (even taking this conversation as an example) its open to total confusion!

SET NOCOUNT ON ... ditto!

Next time you are having a rant at them (if you haven't already!) please tell then I'm not happy with the recent datatype names:

tinyint, smallint, int, bigint CHECK!
smalldatetime, datetime CHECK!
varchar / text CHECK!

datetime2 Huh?

varchar(MAX) Huh?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-16 : 13:48:27

quote:
Originally posted by Kristen

I've got "STATISTICS_NORECOMPUTE = OFF" - so I reckon that will turn Stats ON for anything that has accidentally been turned OFF

Yeah, that's perfect.

--
Gail Shaw
SQL Server MVP

David Singleton
Starting Member

35 Posts

Posted - 2010-02-24 : 12:00:48

Excellent post, just helped me a lot. Thanks. :thumbsup:

David Singleton

Microsoft MVP Dynamics NAV

chillidog
Starting Member

1 Post

Posted - 2011-11-17 : 05:32:11

I'm seeying another difference in the options between a native SQL 2008 database and a migrated one. The migrated ones have "Service Broker" set to enabled. Some of our most complex pieces of cross-database über-synced-transaction software are working perfectly without it. Seems useless to be left enabled when it's not necessary. Something to check with development, though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources