January 17, 2013 at 4:00 am
For SQL 2000 to 2005 Inplace Upgrade, after upgrade, what should we do as next steps. Some are
run checkdb
change compatility
backup db
January 17, 2013 at 4:03 am
DBCC UPDATEUSAGE
January 17, 2013 at 4:20 am
Do we need to run Data Purity check?
January 17, 2013 at 7:18 am
-- set compatibility level
-- change dbowner to same as it was under SQL 2000, it is usually 'sa', but check
-- update all statistics, needed for performance, SQL 2005 interprets these differently
-- update usage stats (correct sp_spaceused values) can be incorrect after SQL 2005 upgrade
dbcc updateusage(0)
-- ensure torn page detection set.
alter database dbname set page_verify checksum
-- upgrade creates a schema for all users and sets this as default, use following to create SQL to
-- modify users default schema to dbo and then run in a new window
-- if SQL 2000 objects were not owned by dbo modify script accordingly
-- SQL2000 always checks objects by username.object first, then checks dbo.object if not found,
-- so this check can be avoided and improve performance by setting default_schema to dbo so it is checked first,
-- however if all objects are owned by the user or all or some are owned by user and object not qualified with
-- owner in SQL then default_schema should be left at user. Only testing may prove this.
set nocount on
select 'alter user '+ name+ ' with default_schema = dbo' from sys.sysusers
where uid > 4 and isntgroup = 0 and issqlrole = 0 and isapprole = 0
order by name
-- now drop all the user schemas created
select 'drop schema ['+ name+ '] ' from sys.sysusers
where uid > 4 and issqlrole = 0 and isapprole = 0
order by name
-- if not done before backup used in migration, check database integrity, if comes up clean, data_purity will be enabled for all future checkdbs
dbcc checkdb with data_purity
-- if database reasonable size, take this opportunity to reindex it (run dbcc dbreindex maint plan job)
-- if a large no of ad-hoc queries run on this database, consider forced parameterization option
-- could reduce cpu usage significantly
--alter database dbname set parameterization forced
-- now back the database up
---------------------------------------------------------------------
January 18, 2013 at 6:12 am
TEST, TEST, TEST... hope you have a test environment and just aren't upgrading production.
Biggest things are:
Rebuild Indexes so optimizer sees the indexes
Update Stats 100%
CHECK Integrity, this makes sure there is no corruption
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply