May 20, 2008 at 1:36 pm
Our current SQL Server 2000 SE server has about 33 GB of data in nine user databases. We have a new database server and plan on installing SS 2005 EE. One option is to install SS 2000 SE on the new server, backup and restore all DBs from the old server to the new server, then upgrade in place to SS 2005 EE. That should free us from having to script logins, agent jobs, and such. Once everything tests out OK, we would then turn off the old server and uninstall SS2000 from the new server. Has anyone done this successfully? I know that a fresh install is usually better, but my experience has only been with upgrading products like XP in place. What am I not considering here? Thanks.
May 20, 2008 at 3:05 pm
One important consideration is uptime and fail over to your old system in case issues pop-up. MS highly recommends using the Database transfer wizard to move db's from 7/2000 to 2005. This will bring everything over in a clean fashion.
Some things to keep in mind as you will have to recreate the logins and users on the new server under any method except the wizard which takes care of this for you, otherwise it's a manual process.
Being that you have two physically different servers you could take a backup of your old server during a planned down time, then use the wizard to migrate you db's to the new sql 2005 server. Update the configuration on any applications so they look to the new server and have a test user group run thru a checklist of functionality to make sure everything is working.
Few things to keep in mind..no matter what take a backup of the db's on the old server before starting the migration. Several other methods to migrate...see below
Detach/Re-Attach
1.) Can be ideal if the old database is on the same physical disks and your space limited..not the case with you. Keep in mind if you are using full-text indexing you will have to manually bring the catalog backup for each db..it won't do it automatically.
2.) Backup/Restore good option if the servers are local to one another and you have plenty of disk space for the .bak file and the database...advantage being that your old server can stay online and continue going while you take care of testing. Make sure to verify your full-text catalog came back for each db..might need to rebuild it. If everything tests good, do one more backup and restore (check your full-text catalog) and your off to the races.
3.) Wizard...simplifies process greatly reducing potential oversights. However, less granular control..it does it's thing and either succeedes or fails..never had it bomb on me personally.
4.) Script it all..longest/hardest approach and most likely to result in problems. However, you control the process from start to finish, it also affords you the chance to change db settings while it's being created (ie. You inital data file size is 100gb on the old server, but you can only let it be 50gb on the new server. You can accomplish via t-sql scripts. Also useful if you need to split your data file out among multiple files or disks.
Probably the most important thing to keep in mind is this..once you attach/restore a sql 7/2000 db file(s) to 2005 server it is upgraded and can't be re-attached to the old server. So make sure you have a good backup of the old server (personally I wouldn't trust anything but SQL server to perform this particular backup. I wouldn't use veritas,snap manager, etc. Both work great, but if an upgrade like this goes south the DBA is going to be one with out a chair when the music stops.)
Hope this helps...I just did a series of these. Some with the wizard and other's with the backup/restore method. Never did and upgrade with detach/reattach, but have used it many times to move db's between servers or just disks on the same box. I have also done the scripting route, but i can think of a lot better ways to spend my time then writing all the t-sql! It's really only worth your time if you going to be maching significant changes to the db settings that would otherwise result in the process failing...in that situation you are probably downgrading your server not upgrading!
Hope this helps!
Eric
May 20, 2008 at 4:23 pm
Thanks, Eric, that really helps.
I tried the copy database wizard to copy one DB from SS2000 to my laptop that has SS2005. I get this error:
While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80004005 (Client unable to establish connection).
(Copy Database Wizard)
80004005 is usually associated with a lack of permissions, but in my case I am using a domain login who is a member of sysadmin on both source and destination servers. Any ideas?
May 21, 2008 at 2:30 pm
I'm watching this one closely as I'm in pretty much the same boat.
Largely agree with Eric, however I am sceptical about the Copy-DB wizard, last time I used it (admittedly for 7.0 to 2k), it failed woefully with most of the DTS packages, which had to be recreated, PAINFULLY.
I don't know how on God's green Earth such a simple tool is supposed to ensure all that ActiveX works from the target !!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply