SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL2008 Migration: Hints and Tips SQL 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/12/2010 :  11:29:55  Show Profile  Reply with Quote
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

Edited by - Kristen on 08/27/2010 01:40:27

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 01/12/2010 :  12:01:49  Show Profile  Visit dinakar's Homepage  Reply with Quote
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/

Edited by - dinakar on 01/12/2010 17:52:17
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/12/2010 :  12:15:34  Show Profile  Reply with Quote
Thanks, URL = "SQL 2008 Technical upgrade reference guide"

There seems to also be a "SQL 2005 Technical upgrade reference guide"
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/16/2010 :  07:46:50  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/16/2010 :  08:23:54  Show Profile  Reply with Quote
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?)

Edited by - Kristen on 01/16/2010 08:47:42
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/16/2010 :  10:03:30  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/16/2010 :  10:19:33  Show Profile  Reply with Quote
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!!
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/16/2010 :  13:23:39  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/16/2010 :  13:31:38  Show Profile  Reply with Quote
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?
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/16/2010 :  13:48:27  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

David Singleton
Starting Member

35 Posts

Posted - 02/24/2010 :  12:00:48  Show Profile  Reply with Quote
Excellent post, just helped me a lot. Thanks. :thumbsup:

David Singleton

Microsoft MVP Dynamics NAV
Go to Top of Page

chillidog
Starting Member

1 Posts

Posted - 11/17/2011 :  05:32:11  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000