October 21, 2009 at 1:36 am
Hi All,
Can anyone share the check list for Migrating the sql 2000 databases to sql server 2005.
What are steps involved from scratch to migrate the 2000 databases to 2005.
Also, would like to know the Pre-requistes and Post - migration steps before actaully going to LIVE.
what kind of measures should be taken while migrating logins, jobs ,dts packages to 2005, Alerts, replication measures, service accounts etc...
Would appreciate sincere replies.
Thanks in Advance
October 21, 2009 at 3:27 am
You can perform a side by side upgrade as follows,
1. Take full backup of all the databases in SQL 7 or SQL 2000 for precautionary purpose
2. Script all the logins using http://support.microsoft.com/kb/246133 in SQL 7 or SQL 2000
3. Script all the jobs in SQL 7 or SQL 2000 using the below link http://msdn2.microsoft.com/en-us/library/aa177024(SQL.80).aspx
4. Install SQL Server 2005
5. Detach all the user databases in SQL 7 or SQL 2000 and copy the files
6. Attach all the user databases in SQL 2005
7. Run the script taken in Step 2 to recreate the logins
8. Run the script taken in Step 3 to recreate the jobs
9. Inorder to migrate DTS packages to SSIS you can refer this link, http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration
10. Once everything is completed login and check the application functionality
The below link is helpful for getting started with upgrading to SQL Server 2005, http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
October 21, 2009 at 4:42 am
A few things to remember:
Make a checklist. -- We can't give you a specific one because it depends on how your server is setup. The checklist should include EVERYTHING on your old server, what action your planning to take on those objects and a place to check off when you're done with that action item.
There is a Upgrade Advisor you'll want to check before hand. You'll also want to take any applications you have and test them thoroughly on an upgraded sandbox server before doing your migration. Odd things pop up when you're not expecting it and if you don't do this, you might be caught with your pants down.
DTS packages will need to be completely revamped as there are several items in DTS that don't exist in SSIS anymore. Several tasks will import (or upgrade) as stubs which will make your packages fail if you aren't careful.
Use the checklist.
Check your operators, linked servers, and every single server / database property. Have a list of on and off options that you can verify against the new server so that your settings are pretty much the same.
Go through EM's Object Explorer and check every single item for miscellaneous "custom" stuff. Don't forget replication (if you're using it).
Plan on increasing your HD space by a factor of 2 or 3 for the initial upgrade. Triple-check your hardware. 2005 takes a LOT of memory and CPU compared to 2000.
Take an entire weekend to do the upgrade itself. But try to do the server configurations, login setups, jobs (not scheduled to start until after the switch), etc., before the switchover. The more you can get done ahead of time, the better for your sanity.
Make a checklist!!!!! Use the checklist!!!!! Worship the checklist!!!!! @=)
Get everyone on your team involved. This is a good time to prune. Check for logins not needed anymore (and security perms), old jobs, old DTS packages, old operators and linked servers. Archive off (do not just delete) anything you don't plan on porting over. That way, if it turns out you do need it, you can just pull the objects off the archive and upgrade them to 2005.
And lastly, don't forget to update your checklist. @=)
October 21, 2009 at 6:25 am
Thanks so much.
That was very helpful.
One last question, i would like to know is there any way to generate scripts for the Maintenance plans so that i can move it to the new server.
October 21, 2009 at 6:47 am
Have you tried right-click to see if a menu with a script option pops up?
SSIS in 2005 supposedly allows for the migration of jobs, logins and maintenance stuff, but I've never been able to get it to work. The major issue is that 2005 tries to port stuff over to the new server out of order. So a login a job might need is ported after the job itself attempts to import.
The main issue is that maintenance tasks in 2000 are essentially DTS packages. So I don't think you can script them. You might be able to upgrade them through the SSIS tool. If you only have a couple, though, I advise rebuilding them from scratch. You're less likely to run into the stub problem that way.
October 23, 2009 at 6:50 am
Hi Brandie,
Thanks for the comments on the Maintenance plans and migrating jobs through SSIS. But still i need to check it out practically..
Would like to ask one more question on SQL 2000 to SQL 2008 Migration
Is that required to upgrade SQL 2000 first to SQL Server 2005 and then to SQL Server 2008?
Can't we do the migration directly from sql 2000 to sql 2008?
If not, any valid reasons!!!
October 23, 2009 at 7:27 am
I haven't done migration to 2008 yet. However, from what I understand, the engine hasn't changed very much and 2008 supposedly allows a direct upgrade from 2000.
Look into Microsoft's SQL website, MSDN, Technet, etc. These pages should have step by step details on this type of upgrade and a FAQ.
October 23, 2009 at 8:02 am
when ever u go for migration you should fix all comptability issues for download this tool
and go for migration
it will tell you all compilance and noncompilance issue that need to addres before migration
October 24, 2009 at 12:08 pm
Thank You.
October 24, 2009 at 1:27 pm
Couple more things
After upgrading to SQL 2005/2008, do the following:
Change the database's page verify to Checksum
Run a checkDB with the Data Purity option
rebuild all indexes
update all column statistics with fullscan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2009 at 1:50 pm
Hi Gila,
Thanks a bunch.
What is the use of the below
Change the database's page verify to Checksum
Also, i have replication set up on 2000.
What essential steps should be taken to migrate it to 2005?
October 24, 2009 at 1:51 pm
Forgot to mention the replication type. It is Transactional Replication.
October 24, 2009 at 2:01 pm
mahesh.vsp (10/24/2009)
What is the use of the belowChange the database's page verify to Checksum
SQL 2000 only had the option to use torn page verification. Checksum, new in 2005, picks up far more problems than torn page did.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2009 at 3:17 pm
Adding my 2cents
1)DBCC UPDATEUSAGE on each database.
2)change default schema to DBO
3)DBCC FREEPROCCACHE to clear out old query plans.
October 25, 2009 at 2:59 am
ravikanth. (10/24/2009)
3)DBCC FREEPROCCACHE to clear out old query plans.
Completely unnecessary. Plans are memory-only and don't survive a restart of the instance or a detach of the database. There's no way the old plans from SQL 2000 could come across to a SQL 2005/2008 instance
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply