Upgrading SQL Server

This article will discuss upgrading SQL Server from your previous version of SQL Server to SQL Server 2000. I will begin by giving a brief description of SQL Server upgrade from SQL 7 and then briefly discuss the options available to you when upgrading from version 6.5 and version 6.

Upgrading from Version 7

If you have SQL Server 7 installed you can install an instance of SQL Server 2000 as either a default or named instance. By following this procedure you will overwrite your installation of SQL 7(by selecting the default instance). You can keep you install of SQL Server 7 by installing 2000 as a named instance.

Close all applications that may be open (inc. any anti-virus software)

Before setup attempts to run the updating SQL Server scripts, certain application processes and services need to be stopped: - In Control Panel, double-click services. Stop all services except the following on Windows 2000:

• Alerter
• Computer Browser
• Distributed File System
• Distributed Link Tracking Client
• Distributed Link Tracking Server
• DNS Client
• Event Log
• IPSEC Policy Agent
• License Logging Service
• Logical Disk Manager
• Messenger
• Net Logon
• NT LM Security Support Provider
• Network Connectors
• Plug and Play
• Process Control
• Remote Procedure Call (RPC) Locator
• Remote Procedure Call (RPC) Service
• Remote Registry Service
• Removable Storage
• RunAs Service
• Security Accounts Manager
• Server
• Spooler
• TCP/IP NetBIOS Helper
• Time Service
• Windows Management Instrumentation Driver Extensions
• Windows Time
• Workstation

You do not need to reboot your machine: This will likely start some of the services you just stopped.

Insert the Microsoft SQL Server 2000 compact disc for the edition to which you want to upgrade into your CD-ROM drive. If the compact disc does not autorun, double-click Autorun.exe in the root directory of the compact disc.

Select SQL Server 2000 Components, select Install Database Server, and then setup prepares the SQL Server Installation Wizard. At the Welcome screen, click Next.

In Computer Name dialog box, Local Computer is the default option and the local computer name appears in the edit box. Click Next.

In the Installation Selection dialog box, click upgrade, remove, or add components to an existing instance of SQL Server, and then click Next.

In the Instance Name dialog box, Default will be selected. Click Next.

In the Existing Installation dialog box, click Upgrade your existing installation, and then click Next.

In the Upgrade dialog box, you are prompted as to whether you want to proceed with the requested upgrade. Click Yes, upgrade my <text specific to the upgrade> to start the upgrade process, and then click Next. The upgrade runs until finished.

In the Connect to Server dialog box, select an authentication mode, and then click Next.

If you are not sure which mode to use, accept the default: The Windows account information I use to log on to my computer with (Windows).

In Start Copying Files dialog box, click Next.

In the Setup Complete dialog box, click Yes; I want to restart my computer now, and then click Finish.

Upon restart ensure SQL Server Agent has been started.

Now SQL Server has been upgraded it is recommended that you update all the SQL Server 7.0 statistics (using SQL 7 statistics in user-defined tables in SQL 2000 may result in poor performance!)

In Query Analyser: Choose appropriate database and run sp_updatestats (this may take a while on large databases)

SQL Server is now upgraded and you can test the user connections etc.

Upgrading SQL Server 6.5

If you are running SQL Server 6.5 you can run SQL Server 2000 as either a default or named instance. In either case the SQL Server 6.5 instance will remain intact, which means that it can be used in a version switch configuration with SQL Server 2000. When SQL Server 2000 has been installed you can upgrade your SQL Server 6.5 configuration and databases by using the SQL Server upgrade wizard.

If SQL Server 6.5 and SQL Server 7 are installed on the same machine they cannot run simultaneously however they can both run simultaneously with SQL Server 2000.

SQL Server 6

It is not possible to upgrade straight to SQL Server 2000 from an installation of SQL Server 6. If you wish to upgrade from a SQL Server 6 version to SQL Server 2000 then you have two options:

1. Use the SQL Server upgrade wizard that is provided with SQL Server 7 to upgrade your SQL Server 6 version to SQL Server 7 and then use the SQL Server setup to upgrade from 7 to 2000.
2. Convert SQL Server 6 to SQL Server 6.5 and then convert the 6.5 instance to SQL Server 2000.

Conclusion

This article has discussed upgrading SQL Server from previous versions. I don’t think there are too many instances of SQL Server 6 or SQL Server 6.5 still in a production environment. Although I am sure there will be many of you out there who will be able to prove me wrong SQL Server 7 I still in use, although Microsoft has moved this from mainstream support to extended support at the end of last year (2005).

As the functionality of the latest versions improves and SQL Server moves towards a truly competitive enterprise database solution many of you want to consider upgrading SQL Server from your existing versions, I hope this article provides you with suitable options and upgrade paths for upgrading.

Return to installing SQL Server database