Edits: Added link to article on SQL Login migrationRecent 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.

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

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

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)

(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 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 usageWill this do?

DBCC UPDATEUSAGE (MyDatabase) WITH COUNT_ROWS -- , NO_INFOMSGS

Yup.

quote:Reindex ALL the tables / IndexesWill this do?Will it Update Statistics on all tables with a full scan?.

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.

There's a double-negative involved here. The setting controls whether the stats will NOT be auto updatedNORECOMPUTE ON = statistics will NOT be updated automaticallyNORECOMPUTE 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.

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:

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.