Installing SQL Server

Now that you understand all the preliminary considerations of installing and using SQL Server, you're ready to install the software. The actual mechanics of installation are simple—the installation program starts automatically when you insert the CD into the drive; if it doesn't, you can initiate the installation by executing the autorun.exe program at the root of the SQL Server CD.

SQL Server 2000 is installed using the InstallShield program, which is common to many Microsoft products. One advantage of using InstallShield instead of a specific SQL Server Setup program is that you can use the Add/Remove Programs options in Control Panel to add SQL Server components or to completely remove a SQL Server instance. In addition, InstallShield makes the process of running an unattended installation much easier than in previous versions.

The lengthiest part of installation, along with the mechanics of copying the files, is building and configuring SQL Server's master database, which stores configuration information and information about all other databases as well as many system stored procedures. If you accept the default collation, the installation program simply copies a mostly prebuilt master database from the files master.mdf and mastlog.ldf on the CD. These files are in a subfolder called Data in the x86 folder. Then the installation program does a small amount of additional configuration. A new installation using the default character set can typically take less than 15 minutes from start to finish (sometimes as little as 5 minutes, depending on the speed of your hardware and how much "think time" you need for the questions). If you do not accept the default collation, the installation program must reindex the entire master database. This reindexing is a totally automatic operation, but it can add 5 to 10 minutes to the installation time. The total installation time is still usually under 30 minutes.

Upgrading from a Previous Version

The installation program will detect if a version of SQL Server is already installed on your machine. Depending on whether the previous installation is SQL Server 6 or SQL Server 7, you will be presented with some choices to make when installing SQL Server 2000.

Upgrading from SQL Server 7

If your machine has SQL Server 7 installed, you will be given a choice of installing a new, named instance or upgrading the existing SQL Server 7 to SQL Server 2000. If you choose to upgrade the previous installation, the installation program rebuilds all the system stored procedures to ensure that the most recent versions are installed. The database files for each database are modified slightly to conform to the structure of SQL Server 2000 database files (which I'll discuss in detail in Chapter 5). The actual table data isn't touched, and after the upgrade is complete, all your SQL Server 7 databases will be available on your SQL Server 2000 server.

Alternatively, you can upgrade an individual database from SQL Server 7 to SQL Server 2000 if you install a separate named instance of SQL Server 2000 alongside your original SQL Server 7 server. You can load database backups from SQL Server 7 into SQL Server 2000, you can use the sp_attach_db procedure to connect database files from a SQL Server 7 database to your SQL Server 2000, or you can use a feature called the Copy Database Wizard. This wizard is based on the sp_attach_db functionality but is available as a GUI wizard to make the process a bit more painless. Moving a database from SQL Server 7 to SQL Server 2000 is a one-way operation, partly because of the change to the database files that takes place. You cannot load a SQL Server 2000 backup into a SQL Server 7 database, and you cannot use sp_attach_db with SQL Server 7 to connect to files from a SQL Server 2000 database.

Upgrading from SQL Server 6.5

If the installation process detects that you have SQL Server 6.5 installed (and you don't have SQL Server 7 available for version switching), you are given the choice of making the new SQL Server 2000 server a separate named instance or setting up a version switch with your SQL Server 6.5 server. In either case, your SQL Server 6.5 is not affected and the installation is treated as a new installation rather than as an upgrade. Because of tremendous structural changes between versions 6.5 and 7, all your version 6.5 data must be completely rebuilt in order to be used with SQL Server 2000. The rebuilding process is called a database upgrade and can be accomplished using the SQL Server Upgrade Wizard, which is available from a program group called Microsoft SQL Server - Switch. You can run this wizard while the installation of SQL Server 2000 is taking place, or you can run it at a later time. The only way to accomplish the upgrade is by using the wizard.

If you choose not to install SQL Server 2000 as a separate named instance, your new SQL Server 2000 will be the default instance. Since your SQL Server 6.5 can only be a default instance and you can only have one default instance running, you must decide whether you want to run SQL Server 6.5 or the new SQL Server 2000 instance at any given time. You can version-switch between the two by choosing Programs/Microsoft SQL Server - Switch from the Start menu and then choosing the option to switch to the desired version. Only the default instance of SQL Server can be involved in a version switch.

For more details on installation, see the online documentation and the whitepaper titled "Installing Microsoft SQL Server 2000"on the companion CD.