SQL Server Performance Forum – Threads Archive
Upgrade questionsHi experts, I have SQL 7.0 running on Winnt. We are planning to
upgrade OS(from Winnt to Wondows 2000) as well as SQL 7.0
(to SQL 2000 SP3). We have the following two options: Option 1:
1. Install the OS (windows 2000) after re-formatting the
disks on the existing box.
2. Install SQL 2000 & SP3, and load the database backups
taken from SQL 7.0 (prior to OS install) Option 2:
1. Buy a new machine and install Windows 2000 on it.
2. Install SQL 2000 & SP3, and load the database backups
taken from SQL 7.0 (from the other machine). Questions on both the above two options: 1. Will the normal database load take care of the database
upgrade or do I need to run any sps?
2. Can I use attach/detach db options for the above
3. If I use "copy database wizard" to copy all the databases including system databases will it take care of the upgrade?
4. Any other issues to watch out for? Thanks a lot for your help,
SQL 7 data files can be detached and reattached to an sql 2000 server. However, always take a backup of all databases in addition to this. Personally I wouldnt use copy database wizard for the simple reason its slow and comprised of multiple operations, whereas reattaching is very simple from a user point of view. If youre planning on buying a new machine in the future anyway, then now could be a good time to do it. This means you can install OS and SQL 2000, reattach sql7 databases, and you are guaranteed not to lose any config settings from your server; if you forget to transfer anything (logins/jobs etc), they are still present on the old server. To answer your question on upgrading 7 to 2000, lots of people might recommend this, I dont know. I can only speak from my experience, in which after upgrading 3 servers from 7 to 2000, the final server didnt quite work out. When upgrading, the setup will run lots of upgrade scripts on the system tables. One of the upgrade scripts consistently crashed halfway through, leaving the server somewhere between 7 and 2000. This created a whole load of knock on problems as you can imagine, and in the end I reformatted and started afresh. Ive not heard this happen to many people to be fair, but its clearly feasible that it could be you.
As you’ve got most of it from Chappy, I addup to adopt the Option 1 where once you reinstall SQL from scratch. Restore SQL 7 databases on SQL 2000 to work with. Also you can use COPY DATABASE WIZARD to accomplish the task from 7 to 2000. _________
I would go by Satya and Chappy in installing clean SQL Server 2K on new machine so that incase things go wrong, you always have a failover server. Attaching a database should be fine. Copy database wizard should also be fine. Gaurav
If you have a choice, don’t upgrade in place, instead install onto a new server. That is our policy at my company, as upgrades sometimes (but not always) have problems. We don’t want to have to face troubleshooting these issues, so we avoid them in the first place. Once you upgrade, don’t forget to run UPDATE STATISTICS or to rebuild your indexes, otherwise your performace may suffer for a while after the conversion. —————————–
Brad M. McGehee, MVP
We’re in the process of doing this as well and we’re going the detach/attach road. Things to think about: 1.
The attached database will keep its current SQL7 sortorder/collation. In other words if you install SQL 2000 with another sortorder/collation than your SQL 7 databases you might break applications that do text compare operations in tempDB and you would have to rewrite them or change collation on all columns in the database. 2.
The database will be upgraded to SQL 2000 and then be running in sql 2000 compatibility mode. Applications could break because of this (new reserved keywords etc) and you might have to set the database in compatibility mode to SQL 7 instead. 3.
Easiest way to transfer logins is with Microsoft SPs like sp_help_revlogin and then run sp_change_users_login with Auto_Fix option to sync logins. Check for null passwords afterwards. /Argyle
Hi all, Thanks a lot for your inputs. I have few more questions. If I decide to go for a fresh install of SQL 2000 (existing or new machine), 1. What are the system databases do I need to load from the SQL 7 backup? (Master, model, msdb…). 2. If I load master from SQL 7 backup, how do I attach the user databases as the names of the user databases and their stuctures will be there in master database after the master database load? 3. If I don’t load master database, what are the system tables I need to BCP in? (Syslogins for sure, anything else)? Thanks again
I would not attempt to restore any system databases from 7 to 2000. (Master, model, msdb and tempdb). I recommend installing your new server, and then using import/export wizard, to transfer objects such as logins across to the new server.
True, just note down any collation settings defined specifically for any databases from old server and input same while rebuilding new server. Also restore MSDB database if you have many jobs to maintain and if any time you specify MODEL database settings. Other than this there wouldn’t be any difference in managaing system databases. If the logins count is numerous then you can think of restoring Master db. _________
Hi all, Finally the time has come for me to upgrade the server. Here is the sequence of events, I’m planning for. 1. Setup a test SQL 7 on a test machine.
2. Dump/load all the databases including master, model, msdb from production to test.
3. Upgrade the test SQL 7 to SQL 2000.
4. Dump all the databases in test machine after the successful upgrade including master, model, msdb.
5. Backup all the databases (system and user) and filesystem where SQL 7 binaries are installed in production.
6. Un-install SQL 7 in production.
7. Install a fresh SQL 2000 in production.
8. Load all the database backup images (system and user) to production (from test) from step 4. Questions :
1. Is there anything I am missing in the above sequence ? 2. There will be some time difference (say 15 days) between step 4 and step 5. That is, the installation of SQL 2000 in production will happen after 15 days of successful test upgrade. The user database in production is dynamically growing and lot of SqlAgent jobs are running every day.. I am worried, if I will have to install a fresh SQL 2000 in production and load the master, msdb from test (which is 15 days old image of production), will I not miss some details in sysdatabases and other system tables (ex jobshistory info. in msdb ?) How do I overcome this problem ? Thanks & regards,
2) If the jobs are numerous and getting updated, then better to restore MSDB which will save the work to reassign jobs. Other than I don’t see anything is missed in the process, just document the process on test environment which will ease your work when its done on production. HTH _________