Installation Tips | ppk on JavaScript, 1/e

Ninety-five percent of SQL Server installations go flawlessly, and you can just click your way through the wizard and install the software successfully. It's the remaining five percent that can make a DBA want to quit his day job and take a different career path. In this section, I'm assuming that you know how to install SQL Server. With that in mind, let's go through some of the more advanced topics concerning your installation, including some problems that may occur with the installation and upgrade. We'll cover installation of SQL Server 2000 in a clustered environment in Chapter 11.

SQL Server is highly dependent on network connectivity. If you are installing it on a server that doesn't have a network card, you can install the MS Loopback Adapter to 'fake' a network adapter. If you're installing SQL Server on a Windows 95/98 machine, make sure you install Client for Microsoft Networks.

In the Trenches

It is possible for certain programs to interfere with the setup procedure. Typically, these programs may intercept certain Named Pipe connectivity. You may receive the error 'unable to write to mailslot.' This is easily fixed by stopping the following services and restarting your installation:

Oracle
DBWeb
Personal Web Server
Internet Information Server
Microsoft SMTP
Microsoft NNTP
Exchange Server
SNA Server
Backup software such as BackupExec
Disk Keeper
SNMP Service(s) and related monitoring services such as Tivoli
Compaq proprietary services

If you do not have access to the registry or the registry doesn't have sufficient room for the new entries, you'll receive the following error:

Unable to create the registry set: PreBuild Server.

Make sure you're signed in as a user with administrator privileges to the server. Also check that there is enough room in the registry and adjust if necessary by going to the System Properties dialog box and clicking Performance Options on the Advanced tab. Click Change under the Virtual Memory section and increase the number of megabytes in Maximum Registry Size.

Note

If an error occurs during your installation, you can view sqlstp.log in the %SystemRoot% directory to determine the cause of the error. During installation, Cnfgsvr.exe is executed to configure your server. The file connects to your SQL Server and executes a series of scripts. If any errors occur, they are written to the sqlstp.log file. Lastly, you can check the SQL Server error log, which is named Errorlog with no extension, in the MSSQL\logs directory to determine if any problems occurred while the scripts were running.

If there is any chance that your SQL Server could receive connections during the installation, unplug it from the network. Finally, make sure you're logged in as an administrator on the server and have proper access to the registry. Essentially, the setup consists of a few basic steps (there are more depending on the version):

Gathering of data and requirements
Installing MDAC 2.6
Installing MSDTC
Installing SQL Server
Running configuration scripts
Registering ActiveX components

If your setup fails, the setup program (InstallShield) will partially roll back the installation. The primary place your setup will fail is during the configuration step. This is where SQL Server places your database server in single-user mode and runs scripts to configure the system catalog. Make sure that no other connections are using your SQL Server while this process is occurring.

Before you click OK on an error message, ALT-TAB out of the setup screen and see if the files were copied over. See also if the service was created. If so, then drop to DOS and run the following command:

ISQL /Usa /P /S.

Once you see a 1>, run any SQL command like this:

1>Select * from Sysobjects 2>GO

If you see results, you can determine that your service is functioning and you're probably failing during the configuration step.

SQL Server 2000 Editions

With the release of SQL Server 2000, the line in the sand has become clearer on the differences between Enterprise Edition and the other editions in the SQL Server family. Focus has been placed on scalability and reliability in Enterprise Edition. This focus comes with a premium price tag. Table 1-1 shows you some of the differences between the core SQL Server editions.

Table 1-1: Basic Feature Differences Between SQL Server Editions
Feature	Enterprise	Standard	Personal
Number of processors	Up to 32 in Windows 2000 DataCenter Up to 8 on Windows NT 4.0 Enterprise and Windows 2000 Advanced Server 4 on Windows NT 4.0 Server and Windows 2000 Server	Up to 4 on all platforms except for Windows NT 4.0 Enterprise Edition, which supports 8	Up to 2 on all platforms except for Windows 98, which supports 1
Amount of RAM	Up to 64GB of RAM on Windows 2000 DataCenter Up to 8GB on Windows 2000 Advanced Server Up to 4GB on Windows NT 4.0 Server and Windows 2000 Server	2GB	2GB
XML support	Yes	Yes	Yes
AWE support	Yes	No	No
SQL Server failover support	Yes 4-node in DataCenter 2-node in Enterprise Edition	No	No
Maximum database size	1,048,516TB	1,048,516TB	2GB
Indexed views	Yes	No	No
Data mining	Yes	Yes	Yes
Distributed partitioned views	Yes	No	No
Metadata services	Yes	Yes	Yes
Data transformation services	Yes	Yes	Yes
OLAP	Yes	Yes, but lacks features	Yes, but lacks features
English query	Yes	Yes	Yes
Virtual Interface System Area Network (VI SAN)	Yes	No	No
Per-processor costs	$19,999	$4,999	Included with other editions

Note

If you install SQL Server Personal Edition on a Windows 98 machine, you have additional limitations. For example, your Personal Edition server on Windows 98 won't be able to support Windows Authentication mode.

Take Command

Another troubleshooting tactic is to rerun the installation in debug mode after uninstalling and rebooting SQL Server. You can do this through the hidden command k=dbg. The full command-line statement would read:

\x86\setup\setupsql.exe k=dbg

Once your setup process is in debug mode, the InstallShield will prompt you when it starts each new step of the setup. From this, you can determine exactly where your process is breaking.

There is also a Developer Edition that has all the features of the Enterprise Edition, but cannot be licensed for production. The Desktop Engine (previously known as MSDE) is a freely redistributable 'trimmed-down' version of SQL Server that you can package with your applications. The Desktop Engine doesn't install any of the tools that ship with the full editions. You can, however, use the tools from a full installation to manage a Desktop Engine server.

The final edition is the CE Edition. The CE Edition is a fraction of the size of SQL Server, but retains the core functionality of its SQL Server parent, including replication. The CE Edition is about a megabyte in size and is compatible with PocketPC devices.

An ideal situation for a CE Edition server is for an inventory inspection. In that scenario, portions of the inventory could be dispersed to a number of handheld devices. Then each auditor would perform his inspection and use the merge replication feature to collect the data back to one source.

It is important to note that CE has a stripped-down version of Query Optimizer. Some queries that work on your SQL Server may not work on the CE Edition.

Preinstallation Decisions

You have to do a great deal of planning before you even open the SQL Server shrink- wrap. If you're installing SQL Server on a Windows NT 4.0 or Windows 2000 server, you must decide whether to install SQL Server on a primary domain controller (PDC), backup domain controller (BDC), or a member server. A great deal of traffic is generated on PDCs and BDCs as accounts are synchronized, so I recommend a member server.

Server Hardware

One of the largest considerations for your database server is the hardware. Although under-powering the server doesn't slow down performance as much as a bad query, it can cause a major bottleneck. For an administrator, these decisions are a constant battle among the priorities of cost, performance, and reliability. The trick is to find a compromise among the three.

As is true for most hardware decisions, the less you spend, the less performance and reliability you have. You must also ask yourself how much you value your weekends, when you might receive a page at midnight about a server that's down.

Scalability Considerations

I'll refer to two types of scalability in this book: scaling up and scaling out. Scaling up simply means adding more processors and RAM for performance gains. Scaling out is adding more servers into a 'server farm.' Microsoft solutions favor scaling out for reasons explained in this section.

If you work for a company with individuals who are familiar with the host (mainframe) world, you may find a company scalability strategy that favors scaling up. This is because on hosts you typically have very large systems that can handle every client your company supports. The systems can be logically partitioned and have the capability to run multiple operating systems at the same time.

With the release of SQL Server 2000 and Windows 2000, the Microsoft solutions have grown up to better support for a scaling-up solution. SQL Server 2000 Enterprise Edition has the capability, in parallel with Windows 2000 DataCenter, to scale up to 32 processors and 64GB of RAM.

However, certain problems become apparent when scaling up. The foremost problem with scaling up is its cost. Windows 2000 DataCenter is configured out of the box at the manufacturer before you even receive the machine. This is the same philosophy that the host world took years ago, which is 'it's not the hardware that's unstable, but the operator who is tweaking it to the point of instability.'

All applications that are installed on the server must undergo thorough stability testing by Microsoft to make sure they can coexist efficiently with Windows. Because so many people have a hand in making this solution stable and reliable, it is not unheard of to pay $1,000,000 for a single server.

As you can imagine, having a single server for all of your company's applications could have a cost benefit as you leverage the same equipment across multiple applications. This is also an advantage because maintenance is done all on one server. As you scale up, however, there is a point of diminishing returns for each processor you add. Each processor tacks on overhead for Windows, as the operating system tries to utilize each processor appropriately. Another problem with only having one server is that it becomes a single point of failure. You need to ensure that the server is somehow clustered.

Scaling out means you add servers to handle your growing traffic needs. What's nice about scaling out is that you can theoretically scale out infinitely. There is never a single point of failure, so if you need to add a service pack on one server, you can take a server down without affecting your other products. This is also a kinder budgetary solution because you can start with a smaller server and then continually add servers as you need them. SQL Server 2000 also utilizes the scale-out method with its distributed partitioned views (DPVs).

Now that you're familiar with the different types of hardware scalability, let's look at some other hardware decisions you'll have to make.

RAID

The best way to implement fault tolerance for your hard drives is to use RAID, which stands for 'Redundant Array of Inexpensive Disks.' You can configure RAID to take apart your data and spread it across several drives. This way, several of your drives can work in parallel but appear as a single drive. In other cases, you can use RAID simply to mirror your hard drive.

RAID 0

RAID 0 is the fastest type of RAID, but it has a serious limitation in that it has no fault tolerance. However, RAID 0 has the best read/write performance. If one of the drives in your RAID 0 system fails, all of your data is lost.

RAID 1

RAID 1 is one of the best ways to provide a fault-tolerant hard drive and still provide good performance. It is also one of the most costly types of RAID, since each drive has a duplicate mirrored on another drive. SQL Server stores data in the transaction logs and in the TempDB in a sequential manner. It is for that reason that transaction logs and the TempDB should be placed on a RAID 1 array if you can afford it.

If one of your drives fails, the requests are switched over to the mirrored drive. Since the drives are mirrored, you could theoretically lose half of your disks in the array as long as they were the right disks.

RAID 5

RAID 5 is the least expensive way to implement fault tolerance. Data is broken up and placed on each drive in the array. This is costly on performance because each time you write to the disk, you must have one read I/O and four write I/Os. RAID 5 is the most common form you'll find in the enterprise because it is so cost effective, but it only allows for a single drive to fail. It is also the most common place to store your data files.

Caution

If you can avoid using software RAID in favor of hardware RAID, do so. You receive nearly twice the throughput with a hardware-based RAID system compared to a software-based system.

RAID 10 (0+1)

One of the RAID configurations I'm beginning to see much more of is RAID 10, also known as 0+1. This is the most expensive type of configuration that I've mentioned in this section, but it provides the best redundancy and performance. Essentially, RAID 10 mirrors two or more RAID 0 drives. By doing this, you do lose a lot of disk space, but you can then have the benefits of RAID 0 and 1.

NTFS vs. FAT

SQL Server supports installations on both NTFS and FAT. Although FAT is nominally faster, NTFS is more robust and easier to restore if your server is powered down improperly. I recommend using NTFS for all of your SQL Server installations.

Caution

Don't install SQL Server on compressed drives. This is not supported by Microsoft and can lead to a performance degradation of up to 50 percent.

Licensing Models

There are two top licensing models with SQL Server: processor and Client Access license (CAL). The processor model is a descendant of the Internet Connector license and allows you to have unlimited connections for each processor that is licensed. The CAL model lets you license the client (not the server) and each connection (whether a PC, Internet connection, or handheld device). Once you've purchased the license for the server, you can access any number of instances, making it the least expensive method of licensing for a small to medium environment.

When installing multiple instances of SQL Server on the same machine in the CAL model, you may have to license each instance separately. If you are running Enterprise Edition, you will not need additional server licenses, and you can install up to 16 instances per server and remain supported by Microsoft.

Note

The question often arises about using Microsoft Transaction Server (MTS) to reduce licensing cost. Even if you are using MTS to reduce the number of direct connections, you still need to license each person making the request. For example, if 50 people are making requests through one MTS component that is requesting data to SQL Server, you will need 50 CALs. Also, Microsoft states that if you have anonymous users connecting (for example, from the Internet), then you must license SQL Server based on the per-processor model.

Removing SQL Server

As with most applications, SQL Server is removed in Control Panel under Add/Remove Programs. However, the uninstall procedure does leave some details by the wayside. Microsoft intentionally leaves the directories intact with your user-defined database and log files still there. You can reattach the databases if the need arises.

I have often had installations initially fail that were successfully installed after removing the old registry keys. If you want to completely remove SQL Server and its databases, you must delete the following:

C:\Program Files\Microsoft SQL Server\80\Tools
C:\Program Files\Microsoft SQL Server\MSSQL

Remove the following SQL registry entries also (depending on how the installation failed, some keys may be deleted):

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server

Note

If you've installed a second instance of SQL Server, it will reside under the HKEY_LOCAL_ MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server key.

When you remove SQL Server and reinstall it, you will see that configured items such as registered servers and client network utilities aliases are kept. This is done at the individual domain user level. If you would like that removed as well, you can optionally remove the HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server and HKEY_CURRENT_USER\Software\Microsoft\MSSQLServer registry entries.

Starting SQL Server and Agent

One of the important questions you must answer during the installation is what Windows account to start SQL Server with. The domain user account you use to start SQL Server Agent must have the following permissions:

Read/Write to registry key HKEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer or HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server
Read/Write to registry key HKEY_LOCAL_MACHINE\System\CurrentControlset\ Services\MSSQLServer or HKEY_LOCAL_MACHINE\System\ CurrentControlset\Services\MSSQL$Instancename
Read/Write to registry key HKEY_LOCAL_MACHINE\Software\Microsoft\ Windows NT\CurrentVersion\Perflib
Change permission for the \Program Files\Microsoft SQL Server\Mssql directory (this directory will be different if you changed it during the installation)
Log on with service privileges
Change permissions to the data and log files

In the Trenches

An easily correctable problem may arise if you start your SQL Server Agent with the system account. Any time your SQL Server must go on the network as part of a job, it will by default use the security context of the system account, which has only local access. Replication will also not work when using the system account.

If you care about either of these features, create a domain account and start the SQL Server Agent with that account. If you've already installed SQL Server, you can go into Enterprise Manager and open the SQL Server Agent properties to change the startup account.

If you change the user who starts the SQL Server instance, make sure the new user is a part of the Administrators or Power Users group. Otherwise, the user will not be able to add SQL Server objects to Active Directory in Windows 2000. If you don't care about SQL Server interacting with the Active Directory, there is no need to assign the user to one of those groups. I'll discuss the minimum permissions for this account in Chapter 3.

Upgrading from SQL Server 4.2

This is one of the most difficult of upgrade scenarios. Chances are, if you have SQL Server 4.2 in a production environment, you're still using Windows NT 3.51. If this scenario applies to you, there is no direct upgrade path. Following are some of the problems that arise when trying to upgrade data from SQL Server 4.2:

You can't upgrade SQL Server 4.2 to SQL Server 2000 using the Upgrade Wizard.
The DTS Transfer SQL Server task will not transfer 4.2 objects.
SQL Server 4.2 will not run on Windows NT 4.0 or greater.
SQL Server 2000 must use Windows NT 4.0 or greater.

So how do you work around such a challenge? If you want to keep the data on the same server, the upgrade process must take place in the following order:

Check the installation requirements.
Run CHKUPG.EXE to check the database status to make sure it is acceptable for upgrade.
Upgrade SQL Server 4.2 to 6.5.
Install SQL Server Service Pack 4 or greater.
Validate the data.
Upgrade the operating system to at least Windows NT 4.0.
Apply at least Windows NT 4.0 Service Pack 5 or greater for Y2K compliance.
Remove SQL Server 4.2.
Install Internet Explorer 4.0 or greater.
Install SQL Server 2000.
Run the SQL Server Upgrade Wizard to upgrade the 6.5 databases and users.
Validate the data.
Remove SQL Server 6.5.

Often, you have to upgrade your 4.2 server to a parallel server and not touch the existing OS or DBMS. If this case arises, you'll have to create the databases manually, then use BCP to transfer the schema and data into the new databases. Stored procedures will also need to be converted manually.

You will have to script the stored procedures in 4.2 using sp_helptext for each stored procedure, and then execute the create statements on the new server running 2000. If needed, you can also run SQL Server 4.2 alongside SQL Server 6.5 on the same server, although it is not recommended.

Upgrading from SQL Server 6.0 or 6.5

Upgrading from SQL Server 6.5 is a much easier process. To upgrade SQL Server 6.x to SQL Server 2000, perform the following steps in order:

Check the hardware installation requirements.
Install SQL Server 6.5 Service Pack 4 or greater or SQL Server 6.0 Service Pack 3.
Make sure that your TempDB has at least 25MB of available space. The master database will need at least 15MB and there should be 1.5 times the hard drive space of the databases you're upgrading.
Install Internet Explorer 4.0 or greater.
Install SQL Server 2000.
Run the SQL Server Upgrade Wizard to upgrade the 6.5 databases and users.
Validate the data.
Remove SQL Server 6.5 when you feel comfortable with the data.

Pay special attention to the step where you validate the data. Check random tables for mission-critical data. What's handy with this conversion is that you can use the SQL Server switch to toggle between SQL Server 6.x and 2000. The SQL Server Upgrade Wizard can upgrade a single database or the entire server. After running the Upgrade Wizard, you can upgrade individual databases again by dropping the SQL Server 2000 database and running the Upgrade Wizard again.

Some administrators need to keep the current version of SQL Server for production use until a new application is created around SQL Server 2000. You can use the SQL Server Upgrade Wizard to export the data to a different SQL Server, running both servers in parallel until the application is complete.

Caution

The data begins to become old as soon as you export it, as it is not kept in synch automatically.

Upgrading from SQL Server 7.0

Since the SQL Server 7.0 core engine has not changed much in SQL Server 2000, the upgrade process is relatively simple. Before starting the upgrade, make sure that your current SQL Server is started. After that is confirmed, run Setup.

In SQL Server 2000, you can run multiple instances of SQL Server. You can also upgrade your data and program files. If you choose to upgrade the current data rather than create a new instance, the SQL Server instance name will be the same as the server's network name. To upgrade your server, select the upgrade option in Setup as shown in Figure 1-1.

click to expand
Figure 1-1: Upgrading to SQL Server 2000.

Selecting this option starts the process of rebuilding the system databases in SQL Server 2000 format.

Note

The upgrade option does not upgrade your user-defined databases nor does it transfer the data from the system databases. To upgrade the data, select that option on the next screen.

The setup program runs through extensive scripts to perform the upgrade. This process also upgrades users and stored procedures. After a reboot, you're finished. Microsoft has not made any major changes in the T-SQL language in 2000 or system tables, so your application should operate fine after the upgrade, requiring no programmatic changes. The main problems I generally encounter are with the connectivity components such as MDAC. Even the database compatibility mode is upgraded for you to 80 when you upgrade the server.

In the Trenches

SQL Server 7.0 may be more 'forgiving' with bad syntax. For example, I've had instances where queries like:

SELECT * FROM Northwind.dbo..categories

May work in SQL Server 7.0 and not work in SQL Server 2000.

You can also use the Copy Database Wizard (CDW) to upgrade your SQL Server 7.0 server to 2000. The Copy Database Wizard is an excellent way to copy or move databases on a 7.0 server to a 2000 server, or from a 2000 server to another 2000 server. It uses DTS on the backend to copy users, messages, jobs, history, and database objects to the new server. The new server can't have a database with the same name as the database you're trying to transfer.

Another option you have for upgrading your 7.0 databases is to back up your databases and simply restore them to the 2000 server. Similarly, you can detach a database from a 7.0 server and attach it to a 2000 server.

Upgrading Stored Procedures

Microsoft has an excellent strategy regarding backward compatibility. Most, if not all, of your stored procedures should work in SQL Server 2000. However, stored procedures created in SQL 4.2 or 6.5 may not work if the following circumstances exist:

The stored procedure references a retired DDL statement. For example, issuing the DISK INIT command is no longer valid in 2000.
The stored procedure references another stored procedure or maintenance script that has been retired (such as DBCC FIX_AL).
The stored procedure references a system table that has been changed or retired.
Use of undocumented stored procedures of functions such as the encrypt() function.

Tricks and Tips for Upgrading

This section mentions a few things to consider, and some circumstances to worry about, when you upgrade an earlier SQL Server version to SQL Server 2000.

Upgrade Wizard May Take a Long Time

Table 1-2: Time Required to Convert from SQL Server 6.5
Size of Database	Time to Convert
1GB	1 hour
10GB	Approximately 4 hours
50GB	Approximately 12 hours
100GB	Approximately 24 hours

Double the preceding times if you are upgrading using the tape data transfer option. The time needed to upgrade presents an interesting challenge to a production environment where the database server must be operational 24 hours a day, 7 days a week. If you have this problem, it is better to upgrade one section of the data at a time.

Avoiding Replication Problems

If you have any type of replication occurring in your network, there are special considerations for ensuring a safe upgrade. You must upgrade the distributor prior to upgrading any subscriber or publisher. After upgrading the distributor, you can upgrade any subscriber or publisher in any order.

By upgrading the distributor first, you can phase in the SQL Server upgrade one server at a time. Keep in mind that you will not be able to use the full set of features of SQL Server 2000 replication until you upgrade all participating servers. I've found that replication performs much faster and more reliably under SQL Server 2000.

Special Upgrade/Downgrade Scenarios

A common question is how to upgrade from either a beta version of SQL Server or an evaluation copy. This problem often arises also for those who install the MSDN copy of SQL Server or want to downgrade from Enterprise Edition to Standard Edition. If you start a server running SQL Server Evaluation Edition after the 120-day evaluation has expired, you would receive the following error:

SQL Server evaluation period has expired.

To upgrade your server, use the following steps:

Insert the retail CD of SQL Server and begin the installation.
Select 'Upgrade, remove, or add components to an existing instance of SQL Server.'
Select the instance to upgrade.
Select the 'Yes, upgrade my programs' option.
If you want to keep the existing components, select No in the 'Do you want to install additional components?' option.
Click Finish and then restart your computer.

If you want to downgrade your server from Enterprise Edition to Standard Edition, perform the following steps:

Detach your user-defined databases using the sp_detach_db stored procedure.
Back up your databases just in case.
Script out your logins.
Stop SQL Server.
Back up the database and log files you just detached (they have the extensions .mdf, .ndf, and .ldf).
Uninstall the version of SQL Server that you want to downgrade or upgrade from. Do not remove SQL Server directories.
Install the target edition of SQL Server in the same path you used for the previous version.
Run the script you created earlier to create the logins.
After you removed the last edition of SQL Server, the data and log files should still be intact. If they are not, restore them from the backup.
Attach the user-defined databases using sp_attach_db. This creates the databases with the users.

Files and Directories

Installation has two parts: installing the tools and installing the server components. The tools are installed on your C drive under C:\Program Files\Microsoft SQL Server\80. No matter how many instances you have on a server, you still use this shared tools directory. You cannot easily change the location of the tools, but I cover a brute force method in the next section.

The server components are installed by default in \Program Files\Microsoft SQL Server\MSSQL. The server directory, MSSQL, changes based on whether you have a named instance of the SQL Server installed on the machine. For example, if you have an additional instance named INSTANCE1, your files would be placed in \Program Files\Microsoft SQL Server\MSSQL$INSTANCE1. Table 1-3 shows you what is placed in some of the core server directories. The server directories are not shared between instances.

Table 1-3: Purposes of the Various SQL Server Subdirectories
Directory	Description
Backup	The default location for backups to be deposited. If you're using a maintenance plan, there may be several subdirectories for each database in this directory.
Binn	The main executables for SQL Server are stored here.
Data	The default location for your SQL Server use databases and logs and location for system databases and logs.
Ftdata	Used for full-text catalogs
Install	The install scripts that are run when first installing SQL server.
Jobs	Workspace for job output files.
Log	Where the logs for both SQL Server and the SQL Server Agent are stored.
Repldata	Workspace for replication files.

Tip

The Install directory holds a gem. If you happen to corrupt your pubs or Northwind database, you can restore it from scripts located in this directory. The Northwind database is created, and its data is loaded, using instnwnd.sql. The pubs database is created and loaded using instpubs.sql. If you lose any one of these databases, open Query Analyzer and run the associated command while attached to the master database.

Moving the Tools Directory

Most developers and administrators install the SQL Server tools on their own workstations. Developers use the tools to modify stored procedures or to work with the schema on the development servers. Administrators usually prefer to administer the servers locally, using Enterprise Manager, and perform performance tests with SQL Server Profiler.

If your workstation has less than the needed space on C drive or the installation will fill too much space, you'll find it's not easy to change the location of the tools. An unfortunate design by Microsoft installs the tools on the C drive unless you move them with a workaround.

SQL Server 2000 provides no options. It installs the tools into C:\Program Files\ Microsoft SQL Server\80\Tools. During installation, you are not even presented an option to change the target directory.

The SQL Server installation routine installs the application to the directory noted in the registry data item named ProgramFilesDir in the registry subkey HKEY_ LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion. Change the data of the ProgramFilesDir to reflect the target path you prefer.

Caution

After you install the SQL Server tools (or server files), set the ProgramFilesDir data item back to its original value before you reboot. The path in this data item is used by other functions, such as the System File Protection feature.

Note

After installing the tools into a custom directory, you may notice that your Query Analyzer templates no longer work. If this happens, you can adjust where Query Analyzer is looking for the templates under Tools and Options. Then go to the General tab and change the Template File Directory option. We'll discuss Query Analyzer in depth in Chapter 6.