Appendix A - Installation and Setup of the Server and Client Software

• Installing the SQL Server software at the server

• Starting and stopping the server

• Installing SQL Server client utilities

• Obtaining help for SQL Server commands

Understanding Server Hardware and Software Requirements

Also according to the documentation, you must have a minimum of 16M of RAM—although additional memory is recommended—for x86-based systems. A minimum of 16M is suggested for a Windows NT Server system, but 32M is more appropriate. Most RISC-based systems are usually configured with a minimum of 64M and often are configured with 128M of memory.

These stated minimums are just that: minimums. As you bring up systems, there are a number of factors that will impact these numbers. Be sure to pay special attention to any replication tasks you may wish to run and to the number of client systems that you'll be allowing to access the server during peak periods.

One of the biggest performance boosts you can offer in the SQL Server world is the addition of memory. This simple enhancement can improve performance by two-fold or more in many cases.

These recommendations aren't unreasonable for a server system. Memory has become very inexpensive in recent years. In 1977, a quarter of a megabyte of memory for at least one manufacturer's system was priced at $17,000. In recent years, you can buy 1M of memory for less than $50.

Although you might be using an Intel x86-based processor in your server system, its processing power and speed far exceeds the large minicomputer systems of 10 to 15 years ago. Physically, your server might be only as large as a client system, but don't be deceived by that.

You need to have at least 70M of disk space to complete the installation, and if you install the books online feature of SQL Server, you'll need to add 15M to this requirement. However, if you have only 70M, you don't have any space to create additional logical devices on which to create your databases. You should count on a minimal SQL Server installation requiring approximately 150M to start. Note that although you can create your database tables and other objects within the master database, you shouldn't. Try to keep your core objects—those installed when you first bring up SQL Server—on a common logical device. As you'll see later, when you create additional devices, databases and tables, you can designate different storage devices for those (if your primary location does not have the disk space available). You also need a floppy-disk drive for 3 1/2-inch, high-density disks or a CD-ROM drive to read the installation media.

For more information on creating new devices, see Chapter 4, "Creating Devices, Databases and Transaction Logs"

Load the SQL Server software from your installation media onto a Microsoft Windows NT workstation or Windows NT Server system. You don't need any additional network software because the Windows NT system contains built-in network software. You need a network interface card (NIC) that's supported by Windows NT. If you'll access SQL Server directly only from the server system, without using a network for access, you don't need a NIC.

You can install SQL Server on a partition that uses either the FAT or NTFS file systems. You'll probably want to take advantage of the recovery and security features of a NTFS disk partition rather than the older and simpler FAT disk system, although your installation might have other considerations for other installed software that dictate this installation parameter.

For more information on the installation of NT Server, see Que's Special Edition Using Microsoft Windows NT Server by Roger Jennings.

Running Setup

To perform the installation, follow these steps:

1. Choose Run from the Start menu.

2. Type the drive letter of your floppy or CD drive, followed by setup in the run combo list box.

   The setup program displays one or more message boxes after checking whether you're working from an Administrative account and whether SQL Server already is installed. Figure A.1 shows the Welcome dialog box that appears when the SQL Server setup program is invoked.

   Fig. A.1 - You can change a characteristic such as the security mode only by running SQL Setup.

3. Click Continue.

4. Enter a Name, Company, and Product ID in the Enter Name and Organization dialog box. Then click Continue.

5. A second dialog box appears, asking you to confirm the name and company. If you want to change the name, click Change. Otherwise, click Continue.

6. The Microsoft SQL Server 6.5 – Options dialog box appears. The Install SQL Server and Utilities option is automatically selected.

   When you first install SQL Server, only the Install SQL Server and Utilities, Upgrade SQL Server, and Install Utilities Only options are available. Install SQL Server and Utilities installs all the SQL Server software, including the 32-bit version of the client utilities on the server. Click Continue.

7. In the Choose Licensing Mode dialog box, you must enter the way in which you've bought licenses for the use of SQL Server, as well as the number of clients that may connect to the server. Figure A.2 shows the entry of a server-based license that will permit a maximum of 20 connections. After you click Continue, an additional dialog box appears that requires you to confirm that you've bought the number of client licenses that you've entered.

   Fig. A.2 - Client licensing permits you to buy the exact number of client connections that you need.

8. In the SQL Server Installation Path dialog box that appears, confirm or change the drive and directory path for SQL Server files and click Continue. Drive C is the default.

9. The MASTER Device Creation dialog box that appears allows you to confirm or change the entries for the MASTER device drive, directory, and the size of the master device.

   The default size of 25M for the MASTER Device Size may be inadequate. If you anticipate creating many devices, databases, or other objects, you can initially allocate a master device of perhaps 40M to 60M.

   The master device contains the master database and transaction log, which holds several system tables. You should avoid creating any objects in the master database, unless you want the objects to be available throughout the server system to all databases. Rows are added to the system tables by SQL Server to reference objects that you create. Click Continue.

10. The SQL Server Books Online dialog box appears, enabling you to define whether you want to install the online documentation to run from the hard disk or the CD (see fig. A.3). You should install the documentation on a hard drive of the server, unless you're short on disk space.

    Fig. A.3 - You can share the online documentation from the server, saving disk space on the client systems. You can also install copies on client systems.

11. The Installation Options dialog box appears, enabling you to define the Character Set, Sort Order, and Additional Network Support (see fig. A.4). Click Sets.

    Fig. A.4 - Select Auto Start SQL Server at Boot Time to automatically start SQL Server, and Auto Start SQL Executive at Boot Time to automatically start SQL Server when the Windows NT server system is booted.

12. In the Select Character Set dialog box, select a language. If you're working only in English, you can choose 437 US English in the Select Character Set list box. If you're working in another language or storing information in multiple languages, you should select 850 Multilingual or ISO Character Set (Default). Click OK.

13. You're returned to the Installation Options dialog box. Click Orders.

14. In the Sort Order list box, confirm the default sort order or change it. If, for example, you select Binary Order, Order By clauses in Select statements are sorted by the direct binary representation of the column values. You can also select Dictionary Order, Case-Insensitive (the default) to treat corresponding upper- and lowercase letters as identical values.

    
    If you later must change your character set or sort order, you'll have to rebuild the master database.
    

15. Click OK. You're again returned to the Installation Options dialog box. Click Networks.

16. You can install additional Net-Libraries to Named Pipes in the Select Network Protocols dialog box. For example, you can enter one or more additional Net-Libraries, such as the IPX/SPX or TCP/IP communication mechanisms. If you don't select additional Net-Libraries during installation, you can install them later.

    
    If you have the desktop version of Microsoft SQL Server, you can't select alternate Net-Libraries; only Named Pipes may be installed.
    

17. Click OK to return to the Installation Options dialog box.

18. Click Continue. Enter a Windows NT account and password to be used by SQL Executive Service in the SQL Executive Log On Account dialog box (see fig. A.5).

    Fig. A.5 - You should enter a password for the SQL Executive Service account that contains at least eight characters.

    
    It's a good idea to use a separate NT account for this service rather than share the NT Administrator account. If you later change the NT Administrator account without realizing it's being used by SQL Executive Service, SQL Server may not work correctly.
    

19. Click Continue. A File Copy in Progress dialog box appears, showing the progress of server files as they're copied from the distribution. As the installation proceeds, additional feedback is displayed on your monitor telling you that SQL Server is installing SQL Server.

If you successfully install SQL Server from a CD, you won't see any more dialog boxes. If you install SQL Server from floppy disks, you'll be prompted to change disks. When the installation is completed successfully. Click Reboot if you want to begin using SQL Server.

Remember to manually start the SQL Server services after you reboot if you haven't defined automatic SQL Server startup.

Starting the Server

Using Automatic Service Startup

If you don't define SQL Server processes to automatically start up, you can later change it to automatic by using the Control Panel's Services option.

1. Open the Control Panel.

2. Double-click Services.

3. In the Services dialog box, scroll down the Service list box to find MSSQLServer.

4. Select MSSQLServer. The Startup should be Manual. A manual service isn't automatically started.

5. Click Startup to open the Service dialog box (see fig. A.6).

   Fig. A.6 - Change the Windows NT account that a service uses through the Service dialog box.

6. In the Startup Type section, select Automatic.

   You can also specify that the MSSQLServer process created by the automatic startup of the service use an account other than the Administrator system account. If you use another account, you must specify the Password Never Expires characteristic in the NT Server's User Manager utility. You don't need to specify a different account from the system account for SQL Server.

   
   It's very important that you consider your future uses for SQL Server as you install it with regards to user identities for the SQL processes. If you're using, or will be using in the future, the e-mail capabilities of SQL Server and the post office resides on a Novell server, you won't be able to access the mail system unless you establish a separate account for SQL Server.
   

7. Click OK in the Service dialog box. The MSSQLServer Startup column should have changed from Manual to Automatic.

8. If you want to immediately start SQL Server, click Start. A message box appears, telling you that SQL Server is starting. If the SQL Server service is successfully started, Started is added in the Status column of the Services dialog box for the SQL Server service.

9. Click Close.

Starting SQL Server with SQL Service Manager

1. Click the SQL Service Manager icon in the SQL Server for Windows NT group.

2. Click Start/Continue or click the green light.

   The status message at the bottom of the SQL Service Manager dialog box should change from The service is stopped to The service is starting. After SQL Server is started, the message changes to The service is running.

3. Either Close the SQL Service Manager or minimize it to an icon.

If you leave the SQL Server Service Manager running, you can easily Stop or Pause SQL Server as needed.

Starting SQL Server Through Windows Applications

Starting SQL Server Through a Command Line

1. Open a command prompt.

2. At the command prompt, enter this command line:

   sqlservr /d drive:\directory\data\master.dat

The drive is the drive letter you entered in the Drive text box of the SQL Server Installation Path dialog box. The directory is the directory you entered in the Directory text box of the same dialog box. The default for the directory is SQL and the default for the drive is C.

Master.dat is the name of the data file that's the SQL Server master device. The master database is located on the master device and contains the set of system tables that defines SQL Server. Master.dat is located in the subdirectory data of the SQL directory.

The sqlservr command line actually starts two system processes, both of which can have multiple threads.

Installing the Client Software

For the 16-bit Windows clients, the following utilities are installed:

• ISQL/W – the ISQL client for Windows. This application allows you to issue SQL statement to the server just as you can from the command line. This application uses a graphical user interface to present the query, results, and any optional trace and analysis settings you may wish to establish.

• SQL Client Configuration Utility. This allows you to set up the different configuration options for the client software. These options include network topology and any custom DLLs required to access the server. This utility is identical in function to the Client Configuration Utility installed on the server (although dialog boxes are slightly different in presentation).

• SQL Server Books Online. As the name implies, this option provides you with a fully searchable online version of the SQL Server book set. This item is also the same as the version installed on the client when the SQL Server is installed.

For the 32-bit client, the following utilities are installed:

• ISQL/W – the ISQL client for Windows. As with the server and 16-bit versions, this application allows you to issue SQL statements to the server just as you can from the command line. This application uses a graphical user interface to present the query, results, and any optional trace and analysis settings you may wish to establish.

• SQL Client Configuration Utility. This allows you to set up the different configuration options for the client software. These options include network topology and any custom DLLs required to access the server. This utility is identical in function to the Client Configuration Utility installed on the server (although dialog boxes are slightly different in presentation).

• SQL Server Books Online. As the name implies, this option provides you with a fully searchable online version of the SQL Server book set. This item is also the same as the version installed on the client when the SQL Server is installed.

• SQL Enterprise Manager. As you'll learn throughout this book, this application is the central command center for administering SQL Server. This includes managing the devices, databases, replication and other features and facets of SQL Server. Chances are very good that you'll become very well-versed in the use of the Enterprise Manager as you administer your SQL Server system.

• SQL Security Manager. As with the server-installed version, this application allows you to manage the users and groups that access the SQL Server system.

• MS Query. This tool, long included with the Office applications suite as the query tool for ODBC data sources, is not only a great tool to create queries against data sources, but also one of the best hands-on tools for learning the SQL language. You can graphically create a query, then review the SQL statements that are created to carry out the request.

• SQL Help. This help file contains the details of the Transact-SQL dialect used by SQL Server. You can use this help file to understand the different commands you can issue against the server—above and beyond the typical standard SQL commands.

• SQL Trace. This facility helps you manage the flow of transactions to and from your server. You'll be using this utility with the Distributed Transaction model in SQL Server 6.5.

• SQL Web Page Wizard. The Web Page Wizard allows you to create interactive, single-instance or trigger-oriented web pages to be accessed from your web site. The wizard will create the HTML and database interface code for you, saving time when you're developing database-centric pages for your web site.

Steps to Installing the Software for 32-bit Clients

Here are the steps to installing the software:

To avoid conflicts with system files, you should exit all other software applications prior to running the setup program. If you don't, you might receive an error message as the files are copied to your system. This error message will prevent you from completing the installation successfully. Pay special attention to less-obvious applications such as the Office toolbar or other applications that run in the background. When in doubt, use the Windows task manager to verify the processes that are active on your system.

• Start SETUP.EXE to begin.

• Click Continue at the prompt.

• If you are upgrading from a prior version of the client utilities, you'll be prompted to choose between installing the new utilities and removing the old version of the utilities on your system.

  
  If you're upgrading your client software, it's a good idea to remove prior versions of the utilities before you install the newer 6.5 versions on your system. If you do uninstall your old utilities, you'll need to restart the setup process to continue with the installation of the new utilities.
  

• You'll be prompted to specify the utilities you want to install. Your control over the installation is complete, allowing you to fine-tune your requirements based on disk space constraints or your need for a specific utility.

• The final phases of installation are automatic. The setup program will copy the routines to your system and update your system files.

• At the end of the routine, you'll be prompted to reboot the system, which will complete the process.

Once completed, you'll be able to use the client workstation to administer, inquire into, and manage the different aspects of your SQL Server system.

Installing the Software for 16-bit Clients

The first step is to open the Clients subdirectory on the CD containing SQL Server. Select your operating system (either DOS or Win16 for the 16-bit Windows client software). If you're running the Windows setup, select and run the SETUP program. Installation of the DOS client software simply requires copying the files to a directory of your choice.

The Windows setup program will prompt you for the destination of the files and will then ask you to confirm which utilities you want to install.

When you click Install, the utilities will be copied to your system. A program group will be created and you'll be ready to start using the utilities with your system.

SQL Server can be installed in an environment with many different chip sets and variations of the Windows operating system. All of the software needed to install and run the server, as well as to install and configure client machines, comes on the SQL Server CD. The process of installing software on the clients should be planned and consistent on clients running the same version of Windows to make maintenance and troubleshooting easier.

Table of Contents

22 - Accessing SQL Server Databases Through Front-End Products

Appendix B - SQL Trace