March 20, 2013 at 10:48 am
I am migrating SQL databases from 2005 to 2008 R2 Enterprise edition. There are only 5 databases but they all are 500 GB or more. I would like to know what checks to make on the dbs after they are moved to the new server. Great hardware on the new server.
We have a very small maintenance window and most of the time will be going towards moving databases, testing jobs etc. I am considering running following things in this order, but worried about the time it takes on such a large databases and performance impact.
DBCC UPDATEUSAGE
DBCC CHECKDB
REBUILD/REORGANIZE INDEXES
UPDATE STATISTICS (for Indexes that were ReOrganized)
RECOMPILE All the procs.
Can anyone please provide expert comments if we really need all these or not? Thanks in advance...
March 20, 2013 at 2:22 pm
Why do you need DBCC UPDATEUSAGE ? You are upgrading not from v. 2000.
How you will recompile all procs ?
You are upgrading in-place or move to another server ? If the latter, check all linkages between users and logins, and don't forget to move SSIS packages, Linked servers, Maint jobs.
Make full backup before and after migration.
March 20, 2013 at 3:17 pm
during the migration window I would just run dbcc updateusage() and sp_updatestats, thats enough to get the database performing under the new version..
I presume you run checkdbs on a regular basis anyway, so that should be enough, no reason why the migration itself should corrupt the database.
ReIndexing not necessary except as part of your normal maintenance outside the migration.
The procs are going to get recompiled anyway, whatever way you migrate these databases.
Have you considered changing the compatibility mode?
---------------------------------------------------------------------
March 20, 2013 at 3:52 pm
Yes we'll be changing the compatibility mode too.
March 22, 2013 at 7:15 am
Thanks George. Off topic, but one question here -
New server is not live yet. No one except me is using it and like I said its a great piece of hardware. 60 CPU 100+ GB RAM. When I run DBCC CHECKDB on a 20GB db, it takes long time and CPU spikes to 60-80 constantly. No other processes are running. As soon as I cancel dbcc CPU comes down to 0. I know dbcc is an intensive operation but nothing else running, I dont think it should take this much CPU. Any idea?
March 22, 2013 at 8:36 am
no, sorry. Check your server level settings.
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply