Microsoft SQL Server 7.0 System Administration Training Kit
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.
- 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"
| 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 |
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.
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.
- 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:
Your account is a member of the Windows NT Administrators local group, which is automatically mapped to the SQL Server sysadmin role.
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:
- On the Query menu, click Results in Grid.
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.
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.
- At the prompt, type the following statements, pressing Enter after each line.
- At the prompt, type the following command and press Enter to quit osql.
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 -?.
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.
QUIT |
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.