Lesson 2: Running SQL Server Setup and Testing the Installation | Microsoft SQL Server 7.0 System Administration Training Kit

[Previous] [Next]

The Setup program runs from the SQL Server CD-ROM or from a shared network folder. Before you can install SQL Server or any component, you must log on to the computer on which you plan to install SQL Server, using an account that is a member of the Administrators local group.

After this lesson, you will be able to

Identify different types of SQL Server installation
Install SQL Server and SQL Server management tools using the SQL Server Setup program
Test the installation of SQL Server

Estimated lesson time: 90 minutes

Types of SQL Server Installation

SQL Server supports three installation types to accommodate different levels of users and different default installation configurations. The following table describes the types of installations offered by the SQL Server Setup program.

Installation type	Description
Typical	Installs SQL Server with default installation options and includes SQL Server management tools and online documentation. A Typical installation does not include full-text search, development tools, and code samples. You can choose the SQL Server services account for all SQL Server services and the destination folder for program and data files.
Compact	Installs SQL Server with default installation options, but without any management tools. You can choose the SQL Server services account for all SQL Server services and the destination folder for program and data files.
Custom	Installs any or all components and presents all server installation options. You can choose components and server installation options. You also can choose the SQL Server services account for all SQL Server services and the destination folder for program and data files. Select a Custom installation to install management tools only. This allows you to manage SQL Server from another computer on a network.

The Setup program selects a Typical installation as the default. If you want to change any installation defaults, perform a Custom installation. You can install the management utilities on other computers on your network to make it possible to manage SQL Server without going to the SQL Server computer. To install the management utilities only, perform a Custom installation.

Exercise: Installing SQL Server

In this exercise, you will run the Setup program and install SQL Server and all management tools on your local computer.

To install SQL Server

Log on to your Windows NT computer as Administrator or another user that is a member of the local Administrators group.

Insert the SQL Server 7 CD-ROM. When the Microsoft SQL Server dialog box appears, click Install SQL Server 7.0 Components.

Click Database Server — Standard Edition.

Use the information in the following table to complete the installation.

Option	Value
Install method	Local Install - Install to the Local Machine
Name	Your name
Company	Your company name
Serial	Serial number of your copy of SQL Server
Setup type	Custom
Program files	C:\Mssql7
Data files	C:\Mssql7
Components and subcomponents	Accept all defaults
Character set	ISO character set
Sort order	Dictionary order, case-insensitive
Unicode collation	General Unicode, Case-insensitive, Width-insensitive, Kana-insensitive
Network libraries	Named Pipes TCP/IP Sockets Multiprotocol
SQL Server service account	Same account for all services and auto start SQL Server service
Service settings	Domain user account
User name	SQLService
Password	Type password (all lowercase)
Domain	STUDYSQL
Licensing mode	Per Server
Number of licenses	50

If prompted with the Convert Existing SQL Server Data dialog box, do not select the Yes, Run The SQL Server Upgrade Wizard check box. You can use this wizard after the install if you have data to convert. We will discuss how to upgrade in Chapter 3, "Upgrading to SQL Server 7.0"

Note
If you need to install either the program files or the data files in a different location than those suggested below, remember to substitute the locations you selected in subsequent exercises. Even if you install these files in a different location and substitute the correct location for those suggested offered within this book, some of the exercises within this book will not work.

Testing the Installation

Testing to verify proper SQL Server installation involves reviewing the installation results, starting the SQL Server services, and connecting to SQL Server.

Reviewing Installation Results

After you run the Setup program and install SQL Server, it is important to understand what has been installed. The following table summarizes what is typically installed by the SQL Server Setup program. The exact list of what is installed depends on the options selected during installation.

Installed components	Description
SQL Server Services	MSSQLServer SQLServerAgent MS DTC (Microsoft Distributed Transaction Coordinator) Microsoft Search
Management tools	Group of tools used to administer SQL Server.
Databases	master, model, msdb, pubs, Northwind, tempdb
Folders and files	Relational database engine and all tools in C:\Mssql7\Binn, databases in C:\Mssql7\Data, various other files and folders in C:\Mssql7.
Default startup options	A set of default startup options that are written to the Windows NT registry.
Default security mode	Mixed allows users to connect with Windows NT authentication or SQL Server authentication.
SQL Server sa login account	A built-in SQL Server administrator login account without account a password.
SqlAgentCmdExec Account	A local Windows NT user account that xp_cmdshell can use account when nonadministrators execute commands via xp_cmdshell. By default, the SQL Server Agent service uses this account to execute specific types of jobs that are executed by nonadministrators.

Starting SQL Server Services

After you run the Setup program, you must start the MSSQLServer service to use SQL Server.

Automatically Starting SQL Server

If, during the installation process, you configure SQL Server to start automatically, SQL Server starts each time Windows NT starts.

If you want the SQL Server services to start automatically but you did not choose the Autostart option during installation, you can configure the services to start automatically by using SQL Server Enterprise Manager or Services in Control Panel. Figure 2.1 shows the dialog box you use in SQL Server Enterprise Manager to set the Autostart options.

Figure 2.1 The SQL Server Properties dialog box Autostart options

Exercise: Starting the SQL Server Service and the SQL Server Agent Service

In this exercise, you will use the SQL Server Service Manager to test and verify that the MSSQLServer and the SQLServerAgent services are started.

To verify that the SQL Server service has started and to start the SQL Server Agent service

On the taskbar, double-click the SQL Server Service Manager icon.

Verify that the MSSQLServer service is running.

Select SQLServerAgent in the Services list box. Click the Start/Continue button to start the SQLServerAgent service. Notice that the icon on the taskbar changes when the service starts.

Select MSSQLServer in the Services list box.

Close SQL Server Service Manager.

Notice that the SQL Server Service Manager icon remains on the taskbar.

Manually Starting, Pausing, and Stopping a Service

You can start, pause, and stop the SQL Server services manually by using

SQL Server Service Manager

SQL Server Enterprise Manager

Services in Control Panel

A net command at the command prompt, such as net start mssqlserver, net pause mssqlserver, or net stop SQLServerAgent

Because SQL Server is integrated with Windows NT, you can start, pause, and stop a SQL Server service locally or remotely. Starting, pausing, and stopping SQL Server services have the following effects on SQL Server:

Starting the MSSQLServer service allows users to establish new connections. You can automate activities and activate alerts after you start the SQLServerAgent service.

Pausing the MSSQLServer service prevents new connections. Users who are already connected are unaffected. You may want to pause the MSSQLServer service in preparation for server maintenance. Pausing prevents new users from logging on. It also allows enough time for you to send a message that asks current users to log off. Pausing the SQLServerAgent service prevents automatic activities and alerts from occurring.

Stopping the MSSQLServer service prevents new connections and disconnects current users. Stopping the SQLServerAgent service prevents automatic activities and alerts from occurring.

Note
It is recommended that you do not stop the SQL Server service using Services in Control Panel or the net stop command. These methods do not perform checkpoints in each database prior to shutdown, and therefore, using them may increase recovery time the next time that the SQL Server service is started.

Overriding the Default Startup Options

The MSSQLServer service starts with a number of default startup options. You can change these default startup options, or you can start SQL Server using nondefault startup options.

To change the default startup options, click the Startup Parameters button on the General tab of the SQL Server Properties dialog box in SQL Server Enterprise Manager. A dialog box appears that allows you to add and remove the startup parameters, as shown in Figure 2.2.

click to view at full size.

Figure 2.2 The Startup Parameters dialog box

Start SQL Server from the command prompt or from Services in Control Panel to use nondefault startup options.

For example, you may want to start SQL Server in single-user or minimal configuration mode as follows:

To change server configuration options or recover a damaged database, use single-user mode. To use single-user mode, type sqlservr —m at the command prompt.

To correct configuration problems that prevent a server from starting, use minimal configuration mode. To use minimal configuration mode, type sqlservr —f at the command prompt.

Connecting to SQL Server

Connecting to SQL Server is the final test to verify the SQL Server installation. You can connect to SQL Server using one of the SQL Server graphical administration tools or a command prompt utility.

When you connect to SQL Server, you must use your Windows NT user account or a SQL Server login account. The first time you connect to SQL Server, you must use a Windows NT administrator account or use the SQL Server sa login account. If you connect to SQL Server successfully, you can then configure and use SQL Server.

Practice: Using a Graphical Utility to Connect to SQL Server

To connect to SQL Server, you should use one of the following graphical utilities:

SQL Server Query Analyzer

SQL Server Enterprise Manager

Exercise 1: Connecting to a Local SQL Server and Executing a Query

In this exercise, you will use SQL Server Query Analyzer to connect to your SQL Server and execute a query to verify the version of your installation.

To connect to your local SQL Server and execute a query

Log on to your computer as Administrator or using an account that is a member of the local Administrators group.

On the taskbar, click the Start button, point to Programs, point to Microsoft SQL Server 7.0, and then click Query Analyzer.

Log on to your SQL Server for SQL Server, type SQLServer (or the computer name of your Windows NT computer if it is not SQLServer). For Connection Information, select Use Windows NT authentication.

Your account is a member of the Windows NT Administrators local group, which is automatically mapped to the SQL Server sysadmin role.

Verify that your database is master. To do this, make sure that master is selected in the DB list box.

Type and execute the following query:

 SELECT @@VERSION

@@VERSION is a global system variable that returns current product version information.

The results pane displays the installed version of SQL Server.

Exercise 2: Verifying Installed Databases

In this exercise, you will execute a query to verify the installed databases.

To verify the installed databases

Type and execute the following query:

 SELECT * FROM sysdatabases

The results pane displays the names and other information about the installed databases, which include master, model, msdb, Northwind, pubs, and tempdb.

On the Query menu, click Results in Grid.

Note that the information is displayed in a grid, which is easier to read than the simple text output.

Exercise 3: Verifying Installed Files

In this exercise, you will verify the installed files.

To verify the installed files

Open Windows NT Explorer and expand the C:\Mssql7 folder.

Open each subfolder and review the installed files.

Note the files in the C:\Mssql7\Binn, C:\Mssql7\Log, and C:\Mssql7\Data folders.

Exercise 4: Using the osql Command-Prompt Utility

The osql utility uses Open Database Connectivity (ODBC) to communicate with SQL Server.

Note
In SQL Server 7, osql replaces the SQL Server 6.5 utility called isql. The isql utility, which uses DB-Library to communicate with SQL Server, is still available. DB-Library remains at the SQL Server version 6.5 level of functionality. DB-Library applications such as isql do not support some SQL Server 7 features. For example, they cannot retrieve Unicode ntext data. The osql utility has a user interface modeled on isql and supports the full set of SQL Server 7 features.

To connect to SQL Server using a command-prompt utility

At a command prompt, type and execute the following command. This command connects to the SQL Server called SQLServer using a trusted connection.

 osql –E -SSQLServer

Note
The osql utility and its predecessor, isql, use case-sensitive command-line arguments. For example, the switches —q and —Q have slightly different effects. For a complete list of switches, execute osql -?.

At the prompt, type the following statements, pressing Enter after each line.

 SELECT CONVERT(varchar(30), name) FROM sysdatabases  GO

You should see a list of databases similar to the one you saw when using SQL Server Query Analyzer, as shown in Figure 2.3.

At the prompt, type the following command and press Enter to quit osql.

 QUIT

click to view at full size.

Figure 2.3 Using the osql utility to display a list of databases

Lesson Summary

Three types of installation are available: Typical, Compact, and Custom. The installation types make it possible for users with varying experience to install all or some of the components of SQL Server. After installing SQL Server 7, test the installation by starting and connecting to the server.