February 13, 2012 at 12:54 pm
We are planning to move a single huge database from SQL 2000 server to SQL 2008 server by switching a Lun. Could someone please explain the process that i need to follow to accomplish this?
February 13, 2012 at 8:55 pm
That won't actually switch the database from SQL Server 2000 to 2008. Your server may be 2008 but your databases will still be SQL Server 2000. Don't just try to switch the compatability mode either. Check the "Upgrade Advisor" for things you need to fix before making the switch.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2012 at 5:08 am
Absolutely use the Upgrade advisor as Jeff says. That has to be step 1. If it identifies issues, address them first.
Run a backup. You want to be absolutely safe during this operation. I'd use checksum and I'd run VERIFYONLY (although that still doesn't completely test the backup, it's close).
After that, you have to DETACH the files that define your database from the 2000 server. Then you can switch the LUN. Once the 2008 server can see the files you can use ATTACH to upgrade to 2008. From there, as Jeff said, make sure you're not running in compatibility mode.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2012 at 5:55 am
Having verified the database is SQL2008 compatible and the app still works and attached the database to the SQL2008 instance you should run the following, mainly for performance reasons
-- set compatibility level to 100, allows use of all SQL 2008 features. Code needs to be compatible with SQL2008 restrictions
-- change dbowner to same as it was under SQL 2000, it is often 'sa', but check
-- update all statistics, needed for performance, SQL 2008 interprets these differently
-- update usage stats (correct sp_spaceused values) can be incorrect after SQL 2008 upgrade (run dbcc updateusage(0))
-- ensure torn page detection set. use checksum
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
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 not too large take opportunity to reindex
-- only 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
---------------------------------------------------------------------
February 14, 2012 at 8:29 am
Thanks guys.
Would it be a problem if I move the database to SQL 2005 server using switch Lun method?
February 14, 2012 at 8:36 am
EXACTLY the same rules and caveats apply as SQL2008, but just as doable
compatibilty level would be 90
---------------------------------------------------------------------
February 14, 2012 at 10:04 am
Thanks a lot guys.
February 14, 2012 at 12:36 pm
One last question, should i perform the following steps before migration or after migration?
-- 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
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
February 14, 2012 at 12:43 pm
I wouldn't modify anything until the migration was done and successfully implemented.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2012 at 3:49 pm
that should be done AFTER the migration.
If all objects were owned by dbo in SQL2000 (and they very probably were) you want the dbo schema to be the first schema checked for objects in SQL2008.
---------------------------------------------------------------------
February 15, 2012 at 9:44 am
Describing the process of migrating a SQL Server database from one version to another as "switching a LUN" is definately a reductionist view of things.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply