Microsoft SQL Server 7.0 System Administration Training Kit
After you install SQL Server, you should configure SQL Server Enterprise Manager and SQL Server.
After this lesson, you will be able to
- Configure SQL Server Enterprise Manager to work with your server and other SQL Servers
- Configure SQL Server and understand dynamic configuration in SQL Server 7
Estimated lesson time: 45 minutes
Configuring SQL Server Enterprise Manager
To manage a local or remote server with SQL Server Enterprise Manager, you must register the server with SQL Server Enterprise Manager. The local server is registered automatically when you install SQL Server. To manage a remote server with SQL Server Enterprise Manager, you must register the remote server manually.
Registering Servers
Registering a server in Enterprise Manager configures Enterprise Manager to connect to SQL Server; it does not affect the server in any way, and the server has no record of the registration. The SQL Server registration information is maintained in the Microsoft Windows NT registry. SQL Server Enterprise Manager uses this information each time you connect to a registered SQL Server.
You must specify the server name, Windows NT authentication or SQL Server authentication, and a server group. When you register a server, Enterprise Manager attempts to connect to the server; if it cannot connect, a message is displayed and you are asked whether you want to register the server anyway.
Use the Register SQL Server Wizard to register multiple SQL Servers with Enterprise Manager. This allows you to administer all of the servers from one computer. You must be a member of the sysadmin fixed server role in order to administer a server. By default, the Windows NT Administrators local group on the computer on which SQL Server was installed is a member of the sysadmin role. Since members of the Windows NT Domain Admins global group are members of the Administrators local group, domain administrators are able to administer all SQL Servers in a domain.
The default network library that Enterprise Manager uses to connect to a server is Named Pipes. If a remote server is not using Named Pipes (Microsoft Windows 95 or Windows 98_based servers cannot use Named Pipes), use the Client Network Utility to change the network library that you use to connect to the remote SQL Server. This utility can be found in the Microsoft SQL Server 7.0 program group.
Exercise: Verifying and Editing Your Server Registration
In this exercise, you will verify and modify your SQL Server registration in SQL Server Enterprise Manager.
- To verify and edit your SQL Server registration in SQL Server Enterprise Manager
- Open SQL Server Enterprise Manager.
- In the console tree, expand Microsoft SQL Servers, and then expand SQL Server Group.
- Right-click your server, and then click Edit SQL Server Registration Properties.
- Check the Show System Databases And System Objects option. Click OK.
- In the console tree, expand your server to verify that you can connect to your SQL Server.
Notice that your SQL Server computer is registered automatically.
What type of authentication is used by default to connect to your SQL Server?
Answer
How can you tell whether your SQL Server is started and whether you are connected to your SQL Server?
Answer
Creating Server Groups
When you register a server, you can either place the server in the default SQL Server Group or create new server groups. Groups in Enterprise Manager provide a way to organize servers in a large organization with many servers. They allow you to group servers together in the Enterprise Manager interface. Groups are purely an Enterprise Manager tool; SQL Server does not use server groups, and each server has no record of being part of any group. If you use Enterprise Manager on two different computers, you can create different SQL Server groups on the two computers without affecting the servers in any way. Groups in Enterprise Manager have nothing to do with security.
Accessing Registration Information
Enterprise Manager allows you to maintain private or shared registration information:
- By default, registration information is private, which prevents others from having access to your Enterprise Manager configuration.
- You have the option of specifying that registration information be shared. This allows multiple users to use the same Enterprise Manager configuration on one computer or allows multiple users using different computers to get their Enterprise Manager configuration from a central computer.
To configure private or shared registration information, select Options from the Tools menu in Enterprise Manager. The SQL Server Enterprise Manager Properties dialog box appears, as shown in Figure 4.1.
Figure 4.1 The SQL Server Enterprise Manager Properties dialog box
Exercise: Creating Shared Registration Information
In this exercise, you will create shared registration information using SQL Server Enterprise Manager.
- To create shared registration information
- On the Tools menu, click Options.
- In the SQL Server Enterprise Manager Properties dialog box, uncheck the Store User Independent check box, and then click OK.
- Expand SQL Server Group.
- Right-click SQL Server Group, and then click New SQL Server Registration.
- Click Next.
- Your server name should be in the Available Servers text box. If it is not, click on your server or (local) in the Available Servers list. Click Add> to add your server to the Added Servers list. Click Next.
- Click Next again to accept Windows NT authentication as the authentication mode for this registration.
- Click the Create A New Top-Level SQL Server Group To Add The SQL Server(s) To option and type Shared Reg Info Group in the Group Name box. Click Next.
- Click Finish to register your server.
- The Register SQL Server Messages dialog box confirms that your server was registered successfully. Click Close.
- Expand the Shared Reg Info Group.
- On the Tools menu, click Options.
- In the SQL Server Enterprise Manager Properties dialog box, check the Store User Independent check box, and then click OK.
- The Shared Reg Info Group no longer appears under Microsoft SQL Servers, and your server is once again in the default SQL Server Group.
Are any servers registered? Why or why not?
Answer
The Register SQL Server Wizard appears.
Note that two groups are now listed below Microsoft SQL Servers.
SQL Server Enterprise Manager has successfully registered your server.
Exercise: Configuring the SQLServerAgent Service
In this exercise, you will modify the SQL Server properties of your server to start and restart the SQLServerAgent service automatically.
- To configure the SQLServerAgent service to start automatically and to restart automatically
- Right-click your server, and then click Properties.
- Select the Autostart SQL Server Agent check box, and then click OK.
Client Installation
The SQL Server management tools can be installed by themselves in order to manage SQL Server on another networked machine. The procedure to do this is the same as that for installing the complete version of SQL Server. You can install the client utilities by specifically selecting them in the custom installation options screen and leaving the server components unchecked.
If you attempt to install a version of SQL Server that is not valid for a particular operating system (for example, if you try to install the Standard Edition on Windows 98), the Setup program will automatically display the custom installation options screen with the utilities selected.
Once the utilities are installed, you will have to register the SQL Server(s) you want to manage. Remember that the client and server must be using the same network libraries. This is especially important for Windows 95 and Windows 98 machines. SQL Server's default network library is Named Pipes, which is not available in the Windows 95 and Windows 98 environments. After installing, use the Client Network Utility to specify the correct network library.
Configuring SQL Server
The first time you use SQL Server, you should assign the SQL Server sa login account a password and review configuration options.
Assigning the SQL Server sa Login Account Password
When SQL Server is installed, the Setup program does not assign a password for the SQL Server sa login account. You should assign a password to this account to prevent unauthorized users from logging on to SQL Server with administrator privileges. Assign a password, using SQL Server Enterprise Manager or the sp_password system stored procedure.
Dynamic Resource Management
SQL Server manages most SQL Server resources dynamically, based on current system and user requirements. In most cases, SQL Server is able to manage these resources more efficiently than a system administrator can. Manually configuring SQL Server options is not recommended.
However, in some situations, you might need to set server options manually, such as when you want to limit the number of user connections and control the use of memory. You can use SQL Server Enterprise Manager or the sp_configure system stored procedure to configure or view these options. For more information on this topic, search for "Setting user connections" or "Setting memory" in Books Online.
Configuring Default ANSI Settings
SQL Server displays certain behavior that is different from the standards specified by ANSI SQL-92. In these cases, you have the option of using the SQL Server behavior or the behavior specified by SQL-92. These options can be specified for a database or for a connection.
To specify the behavior for a database, use SQL Server Enterprise Manager or the sp_dboption system stored procedure to set the ANSI null default, ANSI nulls, quoted identifier, and ANSI warnings database options.
Use the SET command to set ANSI behavior off or on for a single connection. For example, the SET QUOTED_IDENTIFIER command is used to specify whether identifier names can be enclosed in double quotes. Options specified at the connection level override options set for a database. Many of these options are set automatically by the database interface software when a client connects to SQL Server. For example, the SQL Server ODBC driver sets all options to be ANSI compatible whenever it connects.
Troubleshooting SQL Server Installation
If you encounter difficulties in installing or connecting to SQL Server after installation, try to identify the problem by reviewing the Cnfgsvr.out file, viewing log information, and testing network connectivity.
Review the Cnfgsvr.out File
The Cnfgsvr.out file is an output file that is generated by the scripts that run during setup; it records Database Consistency Checker (DBCC) error messages. This text file, which you can view in Notepad, can be found in the C:\Mssql7\Install folder.
View Log Information
SQL Server and Windows NT log information about the installation and operation of SQL Server. Each time the SQL Server and SQL Server Agent services start, new logs are created. Application events are appended to the Windows NT application event log. The following table describes the different logs you can view.
| Log | Description | Location | View by using |
|---|---|---|---|
| Sqlstp.log | Provides information about the installation process of SQL Server | C:\Winnt | Any text editor |
| Windows NT application event log | Provides information about application-related events in Windows NT | Microsoft Event Viewer | |
| SQL Server error log | Provides information about SQL Server events | C:\Mssql7\Log | SQL Server Enterprise Manager or any text editor |
| SQL Server Agent error log | Provides information about warnings and errors specific to SQL Server Agent | C:\Mssql7\Log | SQL Server Enterprise Manager or any text editor |
Exercise: Viewing the SQL Server Error Log
In this exercise, you will review the entries in the SQL Server error log.
- To view the SQL Server error log
- In the console tree of Enterprise Manager, expand your server, expand Management, and then expand SQL Server Logs.
- Click Current to open the current error log.
- Scroll through the error log.
What caused all of the entries in this file?
Answer
Exercise: Viewing the Windows NT System and Application Event Logs
In this exercise, you will open the Windows NT system and application event logs and review the entries that relate to the installation and startup of SQL Server.
- To view the Windows NT system and application event logs
- On the taskbar, click the Start button, point to Programs, point to Administrative Tools, and then click Event Viewer.
- On the Log menu, click Application.
Does the system log contain any entries that were generated by the installation or startup of SQL Server?
Answer
Does the Windows NT application event log contain any entries that were generated by the installation or startup of SQL Server?
Answer
Test Network Connections
If a local client can connect to SQL Server but a network client cannot, use the makepipe, readpipe, odbcping, or ping utility to determine the source of the problem.
Some Common Problems
Finding a solution to a problem involves isolating and verifying the symptom. The following table describes solutions to common problems. For more information, search for "Setup troubleshooting" in Books Online.
| Symptom | Problem | Solution |
|---|---|---|
| A SQL Server service does not start. | The SQL Server services cannot access a domain controller. | Reestablish access to a domain controller or reconfigure the SQL Server service to use the Local System account. |
| "Error 1069: The service did not start due to a logon failure." | The password for the domain user account was changed. | Use Services in the Control Panel to specify the new password for the SQL Server service. |
| The SQL Server service account requires the appropriate permissions on the local computer. | Verify that the domain user account has the required local user rights on the SQL Server computer. | |
| The SQL Server service does not start. | SQL Server entries in the Windows NT registry are corrupted. | Run the regrebld utility to rebuild the SQL Server registry entries. |
| A SQL Server management tool cannot connect to SQL Server. | The SQL Server service has not been started. | Verify that the SQL Server service has started. |
| "A connection could not be established to [servername]." | The client and server network libraries do not match. | Modify the default network library on the client, add a network library to the server, or both. |
| You do not have permission to administer the SQL Server computer. | Log on to Windows NT with an account that has permission to administer the SQL Server computer, or connect with a SQL Server login account, such as sa. |
Lesson Summary
After you install SQL Server, it is important that you verify the completeness of the installation. SQL Server and Windows NT provide a number of features that allow us to identify possible problems with our installation. Once the setup is complete, SQL Server 7 provides intuitive tools that an administrator can use to modify configuration options.