December 10, 2002 at 4:16 pm
I have two servers that I need to do in January. One is a dev box, the other is one of our primary production SQL servers. All of our other SQL servers were built from scratch as W2K/2000 units.
I've done some searching here and at Microsoft, but have not found the prefered method to do this upgrade.
We'd prefer to do a complete rebuild, i.e. wipe the drives and start over. FWIW, all data and log files for the user db's are stored on a NAS, not on the local drives. Master, model, msdb and tempdb are local.
Thanks,
Don
December 10, 2002 at 4:28 pm
My experience is that a clean build is better than an upgrade. I upgraded from NT4 -> 2k -> XP and had some weirdness. After a reformat/reinstall, smooth sailing.
Just make sure you have backups. Test restoring them prior to wiping our your system. Nothing like finding your backup just has an empty file in it. 🙂
December 10, 2002 at 4:39 pm
I too prefer the clean install. The reason is when you do an upgrade there are files that just get left behind that have no use at all. Even just a SQL Server upgrade suffers the same thing. A clean install insures you don't have an out of date dll, that was updated during the install, still referenced in memory for some installed app you forgot about causing it to crash as well.
December 10, 2002 at 5:12 pm
How do you get all DTS packages, maint. plans, jobs, and make sure that all security issues are migrated? I'm comfortable with restoring from BU the user db's and reparing any orphaned users (actually probably quicker to detach and re-attach as they are not stored locally anyway), but doesn't master, and msdb also need to be restored as well?
Also, what happens to linked servers?
Sorry if this is like SQL 101, but I've never had to do this. At least not in the past couple of years.
December 10, 2002 at 7:30 pm
You are aware you can restore the system databases from backup. The only thing is that you cannot restore a 7 system DB to 2000. Since you need to do this I suggest backup everything first. Then do an upgrade to SQL 2000. After you get up to 2000 again backup everything when you are sure is accurate. Rebuild the box with Win2K and install SQL 2K, then restore the master from the SQL 2k backup you made, read "How to restore the master database (Transact-SQL)" in SQL BOL and search the threads on this site.
Of course you may have to install drivers and things for any non-sql linked servers. But it should be fairly easy. I have yet to do it this way but it works when just doing a regular rebuild of a server without doing the SQL upgrade to get the DBs the same revision.
December 10, 2002 at 8:17 pm
Oh, vey! Why do I see a long weekend fraught with cursing Bill and crew!
No chance of detaching the Master (as in a server move) and have 2000 reattach and upgrade at the same time?
Doesn't MS have a white paper on this type of upgrade? Seems as though it would be a *typical* senerio, yet my searches turned up little.
Edited by - oilbrnr on 12/10/2002 8:17:34 PM
December 10, 2002 at 8:39 pm
quote:
How do you get all DTS packages, maint. plans, jobs, and make sure that all security issues are migrated?
DTS packages can be stored in external files.
Jobs can be scripted using EM. (Just did this for a new system to move to PRODUCTION data base)
If you script your data base, you can have it script object security as well. Then apply the security to your new data base.
December 10, 2002 at 9:13 pm
Thanks for the replys.
Don, yeah I'm well aware of scripting and/or saving DTS packages. But in this particular case, on this prod server we're talking about many DTS packages, and a huge exposure factor. If I happen to miss something come Monday morning, my ass is going to be in the frier.
As I thought before posting this thread, it seems like the best way to do this is to do an in-place upgrade of 7 to 2k, (backup user db's for CYA) detach user db's, backup Master, msdb, restore, re-attach. Fix orphans (maybe).
Backup again.
Detach user db's.
Wipe drives and install W2K and 2000. Restore Master and msdb, then re-attach user db's and again fix orphans.
Thoughts?
December 11, 2002 at 3:44 am
Yes write out all the steps befoe you start into a checklist. Review it with others who will be involved. Then establish rough times you expect to be at each step. This will give you a good idea of where you should be before you hit the point where a backout may be in order. Plus it will give you a list to make sure you have completed specific things that if forgotten would be bad for you. Other than that the method I gave you is the only completely feasible option available. One note thou, Read Only DBs cannot be upgraded and once you upgrade they cannot be changed from read only to do so. Make sure you have those read only DBs you may have set for read/write capable.
December 12, 2002 at 3:06 pm
I have done this. And the worst part was to upgrade NT 4.0 to Win2k.
SQL 7 to SQL 2000 was easy.
Before you start from scratch, make sure you know the settings you have right now (sp_helpsort). Also don't forget that you can install a new instance of SQL2K without touching your SQL7 in the same box.
So, my recommendations:
1)backups !!!
2)WIN2K upgrade... once you know this is stable and with no problems
3)Upgrade SQL. your choice. either a)upgrade works fine. sure it leaves some stuff from SQL7, but it works. b) It's cleaner and more standard for future applications, but a little more work.
4)Be prepare to upgrade MDAC on clients. Unless you have a perfect scenario where clients are all with the latest & greatest.
🙂
Cheers &
Good luck
December 12, 2002 at 8:39 pm
I would also avoid any NT 4 to Win 2K. Just do the SQL2 Upgrade, then backup all, build new box, install SQL2K, restore master and databases. Also look for sp_helprevlogin on net for extracting sid's and passwords for SQL ID's for safety copy. They have saved me several times.
December 16, 2002 at 11:23 am
If at all possible, I'd recommend getting a new server for the upgrade. This forces you to really document the key system info such as linked servers, sp_configure, logins, DTS packages, etc. but it allows you to test the new server before it goes into production.
To document linked servers, see the script recently posted:
http://www.sqlservercentral.com/scripts/contributions/620.asp
If your budget is tight, find an older box, and set up to match your current O/S and SQL Server version & sp. This test server does not have to be anything special. An older desktop will do for this test.
Using system db backups, recover the master, MSDB & model onto this third server. Don't worry about the user databases at this point. (they will just show up as suspect.) But since the user db's are on a NAS, you may be able to get extra space to setup test version for this test.
Then, on this test server, do an inplace upgrade to SQL2K. You probably will not use these upgraded system databases, but it will tell you if there are any issues, and give you a bit of confidence that it will work.
(but you could use backups of these system databases, if it went well)
Finally, if the inplace upgrade fails, what are your recovery options? Make sure those are planned out. Again, with a new server, your biggest risk is that you did not take the time to understand, and document how your current configuration works.
Soapbox: This is perhaps the biggest problem with the easy admin of SQL Server 7 and higher. With 6.5 and earlier, you had to know the key configuration issues for each system device and database. With 7 & 2K, it often goes undocumented, until it is too late.
What's the business problem you're trying to solve?
December 19, 2002 at 6:26 am
I have just gone through this myself. I created a new server with windows 2K/SQL 2K. I set-up my SQL 7.0 box as a linked server. I then used DTS packages to move all of the databases, permissions, DTS packages, and jobs to the new server.
I wish I had the exact code for the DTS and jobs but I was able to find it on the web with little effort.
I have been testing the new system in parallel for 2 months and have found a single penny difference between the 2 systems.
However, please be aware of all the OTHER software i.e. Crystal reports, Visual Basic, etc... that is loaded on the machine to ensure a smooth transition.
Good Luck,
AJ
AJ Ahrens
SQL DBA
Custom Billing AT&T Labs
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply