SQL 2000 to 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQL 2000 to 2005 - Upgrade

SQL 2000 to 2005 - Upgrade

Rate Topic

Display Mode

Topic Options

Author

Message

balasach82

Posted Thursday, January 17, 2013 4:00 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

For SQL 2000 to 2005 Inplace Upgrade, after upgrade, what should we do as next steps. Some are
run checkdb
change compatility
backup db

Post #1408306

Suresh B.

Posted Thursday, January 17, 2013 4:03 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075, Visits: 5,114

DBCC UPDATEUSAGE

Post #1408308

balasach82

Posted Thursday, January 17, 2013 4:20 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

Do we need to run Data Purity check?

Post #1408321

george sibbald

Posted Thursday, January 17, 2013 7:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:24 AM
Points: 5,265, Visits: 11,194

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

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

Post #1408413

Markus

Posted Friday, January 18, 2013 6:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:37 AM
Points: 1,046, Visits: 2,208

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

Post #1408862

« Prev Topic | Next Topic »

Permissions