Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2000 to 2005 - Upgrade Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 4:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:54 AM
Points: 393, Visits: 973
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
Posted Thursday, January 17, 2013 4:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 2:55 AM
Points: 1,101, Visits: 5,280
DBCC UPDATEUSAGE
Post #1408308
Posted Thursday, January 17, 2013 4:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:54 AM
Points: 393, Visits: 973
Do we need to run Data Purity check?

Post #1408321
Posted Thursday, January 17, 2013 7:18 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:40 AM
Points: 5,989, Visits: 12,925
-- 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
Posted Friday, January 18, 2013 6:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:16 AM
Points: 1,270, Visits: 2,784
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 »

Add to briefcase

Permissions Expand / Collapse