SQL 2000 to 2005 - Upgrade

  • For SQL 2000 to 2005 Inplace Upgrade, after upgrade, what should we do as next steps. Some are

    run checkdb

    change compatility

    backup db

  • DBCC UPDATEUSAGE

  • Do we need to run Data Purity check?

  • -- 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

    ---------------------------------------------------------------------

  • 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