Introduction to SQL Server

This section is intended for the experienced Microsoft Access user who has never explored the features of Microsoft SQL Server. If you are familiar with SQL Server and SQL Enterprise Manager, skip this section. For more information about any of the features discussed here, see SQL Server version 6.5 Books Online.

SQL Server Setup

The SQL Server Setup program performs the following actions for you:

The Setup program copies the SQL Server files to the disk. The default directory is C:\Mssql. There will be a variety of subdirectories, the most important ones being:
- C:\Mssql\Binn holds the main executable file sqlservr.exe and other important executables and files.
- C:\Mssql\Data stores the data files with the extension .dat. These Windows NT files are called "devices" in SQL Server. When SQL Server is running, it creates an exclusive lock on these files. You cannot copy these files while SQL Server is running and Windows NT backup cannot back up the files in this directory. SQL Server's backup program can copy this data.
- C:\Mssql\Log contains the error logs as files named Errorlog, Errorlog.1, Errorlog.2, and so on, up to and including Errorlog.6. The most current error log is always Errorlog. When the server stops and starts again, it deletes Errorlog.6, renames Errorlog.5 as Errorlog.6, renames Errorlog.4 as Errorlog.5, and so on, ultimately renaming Errorlog as Errorlog.1. Then, it creates the current error log as Errorlog. The error logs are opened from SQL Enterprise Manager but can also be opened with Notepad or Wordpad.
The Setup program completes the required registry entries. There are more than 50 of these but the most interesting entries are in:
```
\HKEY_LOCAL_MACHINE
  \SOFTWARE
    \Microsoft
      \MSSQLServer
        \MSSQLServer
          \Parameters
              SQLArg0 = -dC:\MSSQL\DATA\MASTER.DAT
```
This is the path to the Master.dat file. This must be accurate or SQL Server will not start.
```
SQLArg1 = -eC\MSSQL\LOG\ERRORLOG
```
This is the path to the error log.
```
\Setup
SetupStatus = Installed
```
If the Setup program did not complete properly, the SetupStatus will say Upgrade.
The Setup program starts the server and runs the setup scripts in the Install subdirectory consistent with the options you have selected.
The Setup program creates your program group icons and Start menu entries.

Windows NT Services

SQL Server creates and uses two Windows NT Services. To see these, use the Services application in Windows NT Control Panel. The MSSQLServer Service is the basic server. The SQLExecutive Service is the scheduling manager for SQL Server. Each of these services can be started with the local System Account or with a Windows NT domain account. The System Account is usually sufficient unless your network has several servers that need to talk to one another. Replication from one computer to another computer requires using a domain account that is recognized by both servers.

Security

If you are installing SQL Server for the first time, select mixed security to give you the maximum connection options.

Network Protocols

The Setup program selects Named Pipes by default. If you are new to SQL Server, start with at least Named Pipes and perhaps TCP\IP. The server can easily listen on several protocols and the resource cost of doing so is minimal. When you understand the network issues, you can run Setup again and remove the unneeded protocols. If you plan to use Windows NT integrated security, you must select either Named Pipes or Multi-Protocol.

SQL Enterprise Manager

SQL Enterprise Manager is the workhorse of SQL Server. You use it to manipulate devices, databases, tables, and indexes. SQL Server does not have a user interface. SQL Enterprise Manager is a client, as is Microsoft Access. True, it is constructed to give you tools specifically for dealing with SQL Server, but it connects as a client and has no special status. SQL Enterprise Manager can connect to several SQL Servers at the same time. The first time you start SQL Enterprise Manager you are asked to register a server. Provide the Windows NT computer name in the server box, click Use Standard Security, and provide "sa" as the Login ID with no password.

Exploring Server Manager

Once the server is registered, you can start exploring the Server Manager. Click the plus signs (+) to expand lists of objects, drilling down to greater detail. A normal installation creates five databases:

master contains the system tables for the server.
model is a database that is copied to create new databases.
msdb is a database used by SQL Executive to store information about automatic task scheduling.
pubs is a sample database.
tempdb is a database used to hold temporary tables. It is recreated by copying the model database each time you start SQL Server.

SQL Query Tool

This tool enables you to send SQL statements to the server. Select a server or database in the Server Manager window. On the Tools menu, click SQL Query Tool. (SQL Enterprise Manager does not have a tool like the Microsoft Access query by example grid.) In the Query dialog box, click the DB list to expand it and select the pubs database. Enter a query:

SELECT * FROM titles

To run the query, click the green arrow at the upper right of the dialog box. You can execute the query by typing ctrl+e or by selecting Execute from the Query menu of SQL Enterprise Manager.

ISQL/w

This program is similar to the SQL Query Tool in SQL Enterprise Manager. You can more directly control the login for troubleshooting connection problems. It also excels at passing error messages to the users. This makes troubleshooting easy to perform with the simple ISQL\w client program.

Client Configuration Utility

When you install SQL Server, you select one or several network protocols. Before you connect from SQL Enterprise Manager or ISQL/w program, you can change the protocol you use with the Client Configuration Utility. To do so, on the Start menu, select Programs, click Microsoft SQL Server 6.5, then click SQL Client Configuration Utility. Next, click the Net Library tab. Select the desired protocol from the Default Network box and click Done.