SQL 7 --> 2000 upgrade plan recommendation
-
We have an existing MSSQL 7 installation with approximately 70 databases of varying size that I'd like to upgrade to SQL 2000. The OS is Windows 2000 Advanced Server. This existing SQL 7 install is configured for case-sensitivity and binary sort order but the particular database that necessitated these settings has been moved to another machine. I would like to move forward with SQL 2000 on this machine using the default install options (dictionary sort, case-insensitive). This is where my question comes in.
If I upgrade the existing SQL 7 install to 2000, will I have an opportunity to select the default settings in 2000? Or will I need to do a separate install of SQL 2000 and then migrate the SQL 7 databases over? And even if upgrading directly from 7 to 2000 is possible, would a separate install of SQL 2000 be a "cleaner" path... ie., which option would be less prone to potential problems in the future? Any recommendations on the best approach and/or potential speed bumps to watch for would be greatly appreciated.
Thanks,
Jeri
-
Hi Jeri,
Are you saying that you want to change the character set and collation for each of the 70 databases, or that you just want to change the default settings for databases that will be created on the new server?
Cheers,
Chris
-
Don't you want to wait until SQL2005 aka Yukon is coming out?
Bye
Gabor -
A new install will be cleaner than an upgrade.
It gives you an excuse to ask for a new server. (How old is that 7.0 server, anyway?)
The old server is still available in case something goes wrong. If you're planning on changing the collation sequence on the databases, you'll want to keep the old server running for a while so you can do extensive testing.
The downside of having to update client connections should not rise above the nuisance level. If there's something out there making connections you don't know about or can't maintain, it's about time you found out.
Given Microsoft's track record on timely releases, why wait for Yukon? If you've waited this long to upgrade, you're not the early-adopter type. Besides, you can ask for another server for Yukon. (Make sure the old 7.0 server has found a good home or been scrapped first.)
-
Hi Chris,
My first goal is to change the default settings for all new databases that will be created on this server. A worthwhile secondary goal would be to change the settings of the existing databases (making life just a wee bit simpler for our programmers), but that's not mandatory.
Jeri
-
Naaah. I'll load it in due time (and probably sooner than I'd like in production if my boss has his way), but for now, I wanna sleep nights:-)
-
Heh. Believe it or not, this box is only two years old. The primary application it was serving as backend for was not supported with a SQL 2000 database so I had to stay with SQL 7 when I built the box. As soon as the app was supported on 2000, I ported it to a new (read: repurposed) box, and set my sites on getting the original box upgraded and using default settings. That project has been in the works for nearly 10 months now. Too many fires and not enough firemen.
All of this is not to say I wouldn't dearly love to get a new machine, but I've used all my new machine brownie points over the summer addressing other needs.
J.
-
New box would be my first recommendation and second recommendation would be new installation on that box only because I don't like upgrade and just in case you can go back to old server. Still, upgrading from SQL7.0 to SQL2K wouldn't be really bigger deal though. A few things to rememer if you are going to upgrade.
- Need to repopulate Full-Text Categlogs
- Make sure update the statistics by using sp_updatestats
- Check the Backward compatiblility section of the BOL
- Think about replication upgrade. Distribution server must be upgrade first if you have one.
- if you have cluster, think about that as well.
Some folks that I know did install the instance of the SQL2K on the same box so you can have SQL7.0 and SQL2K running on one server and then migrate the database by using Copy Data Wizard and other methods and uninstall SQL7.0. Make sure you have enough disk space. Doesn't sounds clean but it is safe because you have choice of having old sql server running for a while and works.
Assume you already read this but I am just going to put this link anyway
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx
Good luck and make sure to have backup first
-
Well, I just did a project where we replaced SQL 7 with SQL 2000, and we did a clean install (onto a new server) and then backed up all the SQL 7 databases and restored them onto the new SQL2k instance. That's kind of interesting in and of itself, because ... well, it just works! The db is automatically upgraded to the current version at the end of the restore, as long as certain scripts are present on the server's hard disk (which they are, by default).
In our case, we had very, very few logins and jobs to move, so we did those manually (with assistance from scripts). We had to rename the local server and rebuild all the full-text indexes, as others have mentioned. Actually, it was pretty easy for somebody that knows SQL (it might have been a little tougher for somebody who was good at operating SQL Server but didn't understand it to much depth, though). From your posts, I think you'll do fine whichever way you try.
One point that I haven't seen anybody respond to is whether you can specify character set and collation information during an upgrade, or if you just automagically inherit those from the existing installation. I believe that you *can* specify this information during an upgrade, but I can't remember what the scope of the specification is. For example, I don't remember if master gets rebuilt with the new information or not. My advice here would be to build a toy SQL7 instance someplace and try it to see what happens, should you decide to upgrade rather than build new. There is one article in the BOL entitled "Upgrading Character Set, Sort Order, and Collation," which states "when you upgrade, SQL collations can be specified for compatibility with existing instances of SQL Server. This makes it look like the collation selection options that you see when you do a new install may also be available when doing an upgrade from 7 to 2K.
Like I said, I think that it looks like you can handle things no matter which way you go. I'll tell you one of the more superficial reasons that I decided to do a clean build instead of an upgrade: I wanted to get rid of the \MSSQL7 directory! Trite, I know, but I like having standardized installations as much as possible, and most of our other servers had come from "clean" SQL2K installs. I also empathize with the person who said something about starting clean - that appealed to me also.
Either way, have fun, and let us know how it goes!
Cheers,
Chris
-
Upgrade may not be a big deal but you need to pay more attention to the database/application perrformance after the upgrade.
-
Chris- Thanks for the advice, and for keying in on my base question. I've also done upgrades from 7 to 2000 by backing up and restoring onto another machine and you're right- it just works! I'll do some testing as you suggested, but I'll most likely opt for a fresh install of 2000 instead of an inplace upgrade for this project (alas, without new hardware though). As you and others have said here, and as I also know from my own experiences, a fresh install is generally the cleanest solution.
Thanks to *everyone* for sharing your thoughts. I'll let ya'll know how it goes.
Jeri
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply