December 11, 2008 at 9:53 am
Hi,
I have a current sql server 2000/sp4 (32 bit) named instance running (no default instance running on the server) on a following specification box:
os: windows 2003/sp1
cpu:3 ghz intel pentium 4 (hyper threaded)
memory: 8 G
the server has one important user database with a few DTS processes which I want to migrate to a SQL 2005 server (32 bit).
After investigatin the possibilities, I have decided to do a side by side installation:
my plans are as follows:
- script all the logins from sql 2000 instance and back up all the databases
- install a new sql 2005 named instance (with different name) on the same box
- restore a copy of the user database backup to the new 2005 instance / here I am not sure whether it's better to detach the user DB from 2000 and attach to 2005
also I read that once you attach to 2005 you can't reattach to 2000...
so in case anything goes wrong and I want to go back to 2000 then I have to restore from the backup if I have used attach/reattach method, so it may not be the the right way to do it, please give me some advise on that?
- recreate the logins from the script created above
- recreate the DTS packages in 2005 ssiss / or using the 'Execute DTS 2000 Package Tasks' to execute the DTS
- adjust the applications so that it uses the new 2005 instance
- after some time completely uninstall the sql 2000 instance
please let me know if I am missing anything and give me some advice..
thanks in advance
Awp
December 11, 2008 at 1:51 pm
awp (12/11/2008)
I have decided to do a side by side installation:
undoubtedley the best way to go
awp (12/11/2008)
my plans are as follows:- script all the logins from sql 2000 instance and back up all the databases
- install a new sql 2005 named instance (with different name) on the same box
- restore a copy of the user database backup to the new 2005 instance / here I am not sure whether it's better to detach the user DB from 2000 and attach to 2005
detach the db's on the 2000 server and copy the disk files over. Leave the original files in place. If anything goes wrong simply re attach the files on sql2000 to be up and running
awp (12/11/2008)
also I read that once you attach to 2005 you can't reattach to 2000...
correct, so see above
awp (12/11/2008)
- recreate the logins from the script created above- recreate the DTS packages in 2005 ssiss / or using the 'Execute DTS 2000 Package Tasks' to execute the DTS
- adjust the applications so that it uses the new 2005 instance
- after some time completely uninstall the sql 2000 instance
please let me know if I am missing anything and give me some advice..
thanks in advance
Awp
you can migrate the DTS packages using BI\VS2005
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 11, 2008 at 2:36 pm
Thanks Perry for your quick response, I have been gathering information for some time before I made my plans, but postponing the migration for sometime, however your approval gives me more confidence to go with . Thanks again.
December 11, 2008 at 3:56 pm
Hi Perry, Could you pls let me know whether I have to run restore the logins prior to restoring the user data bases or the order doesn't matter?
Also when I detach the user DB, should I update the statistics (my user DB has a 'simple' recovery model.
Do I have to do any post installation procedure after attaching the DB?
Thanks,
Awp
December 11, 2008 at 4:00 pm
attach the databases first as when the script is applied to add the logins it will fail as the users default database does not exist. Maybe it doesn't really matter but that's the method i prefer
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 11, 2008 at 4:07 pm
Thanks again, I hope to do tomorrow/ or monday, and let you know how it went.
December 11, 2008 at 5:01 pm
dont forget to carry out the post 2005 migration tasks.
run dbcc update usage and rebuild all indexes\stats
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 14, 2008 at 3:14 pm
If I use 'update statistics' when I detach the database, do I still have to run the update statistics after I attaching the DB.
Is there a way to create indexes for a DB as a whole, because create stats only work for tables.
Thanks,
Awp
December 14, 2008 at 5:07 pm
If you do the stats before detaching, you shouldn't have to do it again.
What do you mean stats for the entire db? Where else is there data other than in tables that need statistics?
December 14, 2008 at 5:28 pm
As I am detaching the DB to a new server (2000 to 2005 migration), Perry Instructed me to do the following tasks after the migration:
- dbcc updateusage
- create index
- create stats
I found out I can create the statistics using the 'sp_createstats' SP in 2005 for the Database (not just a table)
Is there a way to do that for index?
Please let me know is there any particular order that I have to run the three tasks.
Thanks,
Awp
December 14, 2008 at 5:34 pm
sorry that should read
'As I am attaching.......
just to answer your question... all my tables are in one DB that I am migrating..
December 15, 2008 at 6:22 am
update stats using
sp_updatestats
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 15, 2008 at 7:04 am
Do I have to run 'sp_createstats' as well? if so in what order please?
December 15, 2008 at 10:39 am
just use the update, dont rely on the updates being correct when done in the 2000 database. When moving from SQL2000 to 2005 always update stats and usage.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 15, 2008 at 12:52 pm
I have done the installation exactly as above and everything so far working fine, many thanks for your input on this.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply