Chapter 03 - Understanding the Underlying Operating System, Windows NT

• Understand the Windows NT features of multiprocessors and multithreading - Windows NT supports advanced operating systems features such as multithreading and multiprocessors.

• Understand and use the built-in networking components - Windows NT provides support of the interconnection of client systems to the SQL Server database.

• Use the reporting facilities of Windows NT - Windows NT contains built-in monitoring and reporting tools which are used to monitor SQL Server.

Windows NT, the operating system that Microsoft SQL Server runs on, has several mechanisms that you should understand to help you use SQL Server more effectively. Operating system mechanisms are sections of computer code that control how the computer's hardware and other software is used. For example, some NT mechanisms control how one or more central processors are used by applications. The MSSQLServer and MSQLExecutive processes of Microsoft SQL Server use Windows NT mechanisms to service client systems.

Windows NT is also responsible for managing the security of the network and its associated resources. You create system users for the server and can control their access to resources, including SQL Server, on that system. Although it's outside the scope of this book to review the entire Windows NT security model, domain administration, and how these components control your network, it's important that you have a comprehensive understanding of how your network is set up, what users are defined, and whether you plan to use this same security model in your SQL Server implementation. Consult Que's Special Edition Using Windows NT for additional information about the Windows NT system.

In addition to mechanisms of Windows NT that control the use of resources such as the CPUs, several system applications control aspects of the operating system that affect Microsoft SQL Server. You can monitor the use of Windows NT and SQL Server components through the Performance Monitor and change usage based on the statistics collected. You can display errors and other events returned as the results of SQL Server's activity through the Event Viewer and, by using the information returned, interpret and correct them.

It's also helpful to understand the different configurations of interconnections among the Windows NT Servers that Microsoft SQL Server is on. The interconnection of server and client is accomplished primarily through network software, which you must understand the basics of to communicate between client and server or server and server.

Understanding Multiprocessing, Multitasking, and Multithreading

Multitasking and multiprocessing are two mechanisms of an operating system (such as Windows NT) that are used to share one or more central processors (CPUs) of the computer system. Earlier operating systems permitted only one application at a time to use a computer's resources. It wasn't long before operating system designers realized that the core resources of a computer system, such as the CPU, could be shared by multiple application programs.

The terms multiprocessing and multitasking refer to the sharing of the CPU(s) of a computer system by more than one application program. The simplest form of multiprocessing is when an operating system switches use of a computer system's CPU among multiple applications. The operating system must keep track of where each program has left off so that the program can be started back up again when the program receives use of the central processor again. This is round-robin scheduling. Round robin scheduling permits each process to use a CPU for a period of time rather than allowing a CPU to be used exclusively by a process.

Each application must receive use of a CPU long enough to get some reasonable amount of work done. Also, the switching of the CPU must be accomplished quickly. If an operating system provides each application with enough time to use a CPU, and the switch among applications is done quickly enough, a user interacting with one application might work as if a CPU is dedicated to their exclusive use. Of course, this performance can also be influenced by having too many applications waiting to use the CPU(s).

The interval of time at which Windows NT exchanges use of the central processor is several hundred milliseconds. Windows NT maintains an elaborate technique to determine what program receives the use of the central processor next. Programs are assigned a priority from 0 to 31. NT grants use of the central processor to the program that has the highest priority (has been waiting to use the central processor the longest) and works down the list of priorities in order.

Operating systems such as Windows NT perform a more sophisticated sharing of the use of a central processor than the simple round-robin approach mentioned earlier. A program on Windows NT can be written in several functional sections, and each section can receive use of a central processor independently. One definition of the term multitasking refers to the sharing of the use of a central processor by multiple sections of a program simultaneously.

A complete application program that can use the resources of a system is called a process in the Windows NT system. Each program section that can receive use of a Windows NT central processor is called a thread. A thread must have its own priority as well as other characteristics for Windows NT to schedule use of system resources separately for each thread. The term multitasking on Windows NT also refers to the use of the central processor of a NT system by multiple threads of the same or different processes.

There are two types of multiprocessing capabilities: symmetric and asymmetric. Windows NT uses symmetric multiprocessing, which is the most commonly implemented. Symmetric multiprocessing spreads the processes for both the operating system and applications among all available system CPUs. Asymmetric multiprocessing allows the operating system to be placed on a single CPU and applications spread among others.

A feature such as multitasking with multiple threads is most advantageous when more than a single processor is available to be shared. An application that's written in several threads can have each thread execute simultaneously on Windows NT. The two major components of Microsoft SQL Server, the server and monitor process, are written as multiple threads to take advantage of the multithread form of multitasking available on Windows NT. This is referred to as a multiprocessor environment.

You can use a powerful single-processor system for SQL Server such as a Digital Alpha AXP, MIPS R4000, PowerPC, or Intel Pentium Pro system. Multiple CPU systems can be used by Windows NT because of its symmetric multiprocessing capability. Symmetric multiprocessing allows several threads to execute simultaneously, regardless of whether they're running application or operating-system code.

Multi-CPU systems are particularly advantageous for use as servers for a SQL Server database because I/O requests from client systems can be handled while other operations, such as account validation, are done in a second processor of the system. Multiple server requests can be done at a time that greatly increases the number of workstation clients that can be served by the server.

Understanding Multi-Architecture

For example, Windows NT can run on a system that uses Intel 386, 486, or Pentium processors. NT can also run on x86 clones produced by such vendors as Cyrix and AMD. Windows NT will also run on Digital's Alpha AXP, MIPS R4000 series processors, and Motorola's PowerPC. The Windows NT distribution CD contains the separate versions of the installation software for all four systems, with more compatible systems planned.

As you can see, a key advantage to using Windows NT as the paltform for SQL Server is that you have so many choices of computer systems to use as a server for a SQL Server database. Windows NT and SQL Server are scalable from a desktop PC to a large Alpha AXP or MIPS system. It will even scale to systems with one or more processors that have enough power to replace a minicomputer or even a large mainframe system.

Understanding the Multiuser Environment of SQL Server on Windows NT

Dumb terminals got their nickname because they simply transfer characters to and from the CPU, using the CPU to perform the work with the information. Dumb terminals, unlike PCs or other workstations, can't perform any processing. Dumb terminals replaced the card readers and printers that were used as input and output devices on early computer systems. A dumb terminal combines separate input and output devices in a simple device for input and output.

Each user typically runs Windows for Workgroups, Windows 95, DOS, OS/2, or Windows NT Workstation on a client workstation system. Each operating system allows a user to run applications independently of a central server system. A user at a workstation uses connectivity software—usually, the network operating system—to establish a connection to a central server computer running Windows NT Server.

Understanding the Windows NT Network Components

You can also perform administrative operations through network connections to Windows NT. You use commands to learn the connection status of systems, monitor the flow of control and user data between connections, and alter the characteristics that affect the connections. You can also change the size of network and disk buffers, the temporary storage space in RAM used to store the data coming from one system and received into a second system.

The connectivity components of Windows NT are also used to connect an application on a client workstation to the SQL Server database on the NT server. All the previously specified uses for client/server connectivity—such as remote administration, monitoring, and data transfer—are necessary in a system using Microsoft SQL Server.

See chapter 17 "Optimizing Performance" for a more detailed discussion of Using NT performance monitoring.

For more information on administration of SQL Server, see Chapter 18 "SQL Server Administration"

Sharing Resources

Both methods for sharing resources could work for sharing a SQL Server resource. But workgroups are geared towards users and therefore don't offer very sophisticated or versatile security measures. Windows NT offers excellent built-in security through its use of domains for implementation and administration. SQL Server can take full advantage of this security.

For more information on how SQL Server uses the built-in security of Windows NT, see Chapter 21 "Communicating with SQL Server"

Installing Network Software

Fig. 3.1 - You can manipulate several network software properties together.

After entering new configuration information for a network component, you'll be prompted to either reboot the system or leave the system up and running (see fig. 3.2). You network components won't be available until you reboot.

Fig. 3.2 - After the installation is complete, you'll need to reboot the system for the changes to take effect.

If you're adding the additional network software that comes with Windows NT, you need to confirm or change the path for the NT distribution. If you're installing optional network software, enter the path for its distribution.

Configuring Adapter Cards

The manufacturers of network interface cards use software, referred to as drivers for their NIC. If you change from one NIC to another, you have to change the driver software. The need to change network adapters can arise for a number of reasons. For example, you might want to upgrade as faster cards become available, replace a faulty card, or add a new, special-function card to the system.

As mentioned, one of the reasons you might want to update your card is to improve network performance. You can change from a slower 8-bit NIC to a faster 16- or 32-bit NIC. The 16- or 32-bit NICs perform some network operations more quickly than 8-bit NICs. Other NIC characteristics can also affect performance, including items such as the buffer sizes and types of media supported.

You might need to change the NIC on the server system to get adequate performance for queries made against your SQL Server database. You might also change the NIC on selected client systems that require faster access to the server database.

Choose the Add button from the Adapters page in the Network properties dialog box to bring up the Select Network Adapter dialog box. Select the name of the network adapter card from the Network Adapter card list. The selected adapter card in Figure 3.3 is the 3Com Etherlink II Adapter.

Fig. 3.3 - You'll need to add adapter card software if you add a second or different NIC to your system.

Network adapter cards typically require that an IRQ level and an I/O base address be specified when the adapter software is added. The IRQ level and I/O base address should match the one specified by the manufacturer. Windows NT can automatically detect and configure a number of adapter cards.

Before buying a NIC, you should consult with a reputable dealer, who can tell you whether the card can be set up automatically by Windows NT. You can also check with Microsoft to learn of NICs that can be automatically configured. Some of the major manufacturers provide NICs that also allow the IRQ and addresses to be set through software rather than jumpers on the card. Cards that can be automatically set up can be advantageous to use, especially if you have a large number of other interface cards installed in your PC workstation.

The more cards you've installed in your system, the easier it is to have conflicts. Two cards that have identical IRQ and/or address settings by default will, if unchanged, cause one another to not work as expected. Some interface cards provide few changes to be made to their IRQ or address settings. If some of your interface cards can be set through software to a large number of values, you can more easily prevent card-setting conflicts.

Ideally, you should check the specification of all cards that you want to use in your PC workstation to determine whether all IRQ and address conflicts can be eliminated. If you don't do this, you might have to later change one or more cards to eliminate conflicts and allow all cards to work, including your network card.

You should also check that the interface cards, including the network interface cards that you buy for NT, are supported. Microsoft provides a list of the supported interface cards, including network NICs that can be used with Windows NT.

You should know the factory default settings for your network adapter card and the current settings, if you've changed them from the factory defaults. You should also run any diagnostic program, to learn quickly if the network adapter cards functions properly.

Select the adapter card from the Installed Adapter Card list and click OK to install the network adapter using the default NT driver (or click Have Disk to use a manufacturer's driver. Involvement of the user in the installation of adapter cards depends on the abilities of the card and the sophistication of the driver software. All of the settings might be automatically determined or some might need to be supplied to properly install an adapter card.

Many manufacturers repackage network adapter cards that are manufactured by other companies for PC workstations. If your network adapter card doesn't appear in the list, it might be shown under a different name. Check with the vendor from whom you bought the card, the documentation that came with the card, a diagnostic display of the card characteristics, or the labeling on the adapter board itself to find its designation.

Consider buying identical network adapter cards for PC workstations. Several adapter manufacturers provide you additional software to diagnose and monitor network interface card operation, but only if you have matching NICs among PCs.

Understanding Workgroups

The members of a workgroup can typically share the resources of one another equally. An example of a resource that can be shared among workgroup members equally is a disk drive, and the directories and files on it.

This capability to share between workstations without a requirement to have a designated server system is called peer-to-peer networking. Each system can share access to the other's resources after they become shared. In such an arrangement, the systems function as both clients and servers to one another. In this case, a server is a workstation that makes a resource such as a disk available to another workstation. A client is a workstation that accesses the resources of another workstation.

Workstations in a network that share each other's resources should be placed in a logical organization, which is the NT workgroup. You must designate which NT workstations become members of the same group. After you form workgroups, the resources for sharing can be set up. The underlying capability of peer-to-peer networking of workgroups permits workgroup client access to a Microsoft SQL Server database. The peer-to-peer features of a workgroup can also be used to share related information about SQL Server (such as the documentation, which could reside on any shared disk in a workgroup).

After the disk-sharing feature is enabled on each system, for example, you can access another workgroup member's disks. You can execute applications, read or write databases, create documents and spreadsheets, and delete or rename files on the shared disk of another workstation in the workgroup.

A disk drive that's part of the hardware components of a workstation is called a local drive. Local disk drives are directly connected to a workstation.

A remote drive is a disk drive that's accessible to a workstation that physically isn't one of its hardware components. The remote drive is the local drive of another workstation. The physical connection to a remote drive is through the LAN.

Workstations that are part of the same network can be made members of the same workgroup. Workstations that need to assess each other's resources should be made members of the same workgroup. This is the basic criteria for the formation of workgroups.

You can, however, define a workgroup based on your own criteria. The placement of two or more workstations into a workgroup is arbitrary, the meaning of which is that you, as an administrator, have full control over who's made a member of the workgroup. You can have groups of only two members each if you have a need for such a configuration. You can even place workstations into the same workgroup that share no resources (although this serves no purpose).

You'll find that there's a practical limit on the number of systems that can be members of the same workgroup. A constraint results from the speed of the workstation's hardware, including its disk drives, amount of memory, processor, and system bus. The individual hardware components of a workstation that's used as a server in a workgroup might not be fast enough to allow it to serve many workgroup members.

Members of a workgroup might access a SQL Server database in a different workgroup as well as their own. However, it's more likely that the SQL Server database will be installed on a Windows NT server system in a domain. However, members of one or more workgroups can still access the SQL Server database though its placed into a different type of logical organizations of client and server systems.

In a client/server network system, as an alternative to the peer-to-peer model that Windows for Workgroups is based on, you can buy a large, powerful, and fast single system that's the only server in a group of workstations. If your server system is a Pentium, MIPS R4000, Alpha AXP processor, or multiple i486 processors, it can function as a server for a much larger number of workstations.

Traditional networking definitions have typically been that a network configuration is either a workstation/server network or a peer-to-peer network, not usually both. The fact is, with the introduction of Windows for Workgroups and continuing with Windows 95, systems are more typically a mixture of server-based and peer-to-peer based networks. The appeal of the peer-to-peer type of network often is cost. You don't have to implement a huge system to act as a server to other workstations on the network in this environment. The disadvantage is that you won't typically be running hard-core server applications, such as SQL Server, on the workstation of these environments.

Also, as you implement your workgroups, you'll find logical groupings of your users emerging, even beyond the groups you've established. You should consider Windows NT domains if you find it difficult to administer workgroup networks. Domains allow you to group users into logical cross-sections and then use these groupings to manage security, access to the network, user names, and more.

This latter type of centralized organization provides some of the features of a client/server network. If you require more of a client/server configuration for your workstations, including the capability to serve dozens or hundreds of clients, you'll want to use the additional features provided by the Windows NT Server.

A simple rule of thumb for peer-to-peer configured workgroups is to limit their members to no more than 20 or so. Microsoft suggests that you define fewer than 20, but it depends on how many members interact with one another simultaneously that determines the actual limit. You'll find that you can deviate from the suggested limit of 20 workstations, although you should certainly keep the recommended values in mind as you configure your workgroups.

You might want to limit the numbers of members of a workgroup to less than 20 to allow for the occasional load put on your system by connections to your resources from outside your workgroup. Unlike the domain model mentioned earlier, a workgroup isn't a security mechanism and doesn't serve to restrict access to the resources of member workstations. Other members of the network can access the resources of workstations outside their own workgroups after they know the share name and optional password.

It might help you to understand workgroups by thinking of them as a loosely organized confederation rather than as an integrated republic. The members of a workgroup log on to their workstation, establishing their user name for the network. Their user name and password are checked in an account database that resides on a local disk. You administer each workstation separately, including the definition of separate accounts for each workstation.

You designate a workstation as a member of a workgroup when you install Windows NT. You can later use the Network Control Panel to change your membership in a workgroup. You can designate a workstation as a member of only one workgroup at a time.

A new workgroup is created the first time you use its name. This occurs either during the installation of a Windows NT system or when you later change the name of your workgroup. Members of the same workgroup are displayed together when you examine the workstations of your network.

Members of a workgroup are together to simplify the sharing of resources. There's no restriction on the workstations that might become a member of a workgroup that's provided by Windows NT; you define the sole criteria for workstation membership.

You can use workstations with faster processors or multiple processors, and large fast disks in your workgroup to extend the 20-workstation limit of the workgroup network. Faster processors and faster disks help extend the limit by performing server tasks more quickly. This allows more workstations to interact as clients and servers with one another and still have acceptable performance.

You won't, however, be able to extend your network to a configuration in which your servers support 10,000 or more NT workstations. Although you can have several tens of thousands of interconnected NT systems, the maximum number is far less and is limited by the number of systems that can perform well while interconnected. For information about networks supporting users in these quantities, see the next section regarding the domain model available with NT Server.

In a workgroup, you create and administer user accounts on each workstation. You log on to each workstation, and your user name and password is validated at the local workstation. If you have the responsibility for the administration of more than one workstation, you must log on to each one to maintain its account database. This is particularly inconvenient if a user has accounts on several workstations and changes must be made to each one.

Another possibility is to specify a domain for your computer rather than a workgroup. As mentioned previously, a domain is a more tightly administered group of workstations. You can read about domains in the next section Understanding NT Domains.

I tried to establish a connection between my client system and the SQL Server database on the Windows NT Server system. No matter what I do, I can't even see the Windows NT system.

You probably have not installed the proper network drivers. Try checking your network properties. If the network components are all configured correctly, check to see that the proper SQL Server network files are properly installed.

Understanding NT Domains

One feature that you gain with use of the NT Server is the capability to centralize the creation and maintenance of user accounts. User accounts are stored and validated from a single server workstation when you log on, rather than at each workstation.

The centralized account feature of a domain organization will permit you to install Microsoft SQL Server using integrated security. The integrated security feature of SQL Server uses the Windows NT domain accounts rather than the internal Login IDs of SQL Server. See Chapter 19, "SQL Server Security" for a discussion of integrated security.

The centralization of account information includes the user environment of Windows NT. The NT Workstation system allows characteristics—such as the desktop settings and program groups and items—to be modified for each workstation user. The Server allows this user environment information to be stored and maintained on a single workstation.

You create user accounts from the domain controller as one of the centralized administration features of NT Server. The user account is a domain account that allows you to log on to any workstation in the domain, unless you're restricted to log on on selected workstations for security reasons. Logons to the domain are checked against the entries kept in the account server database on the domain controller.

An additional type of group that can be referenced throughout the domain can be defined on an server network. You use these groups to selectively control access to resources. This type of group is called a global group because it can be referenced through the domain rather than locally at a single workstation. After a successful logon, global groups are used to provide access control to the resources of the workstations in the domain. The defining component of the domain is the centralized control of account information. When you log on to a workstation that's part of a domain, you're prompted to log on with your user name and the domain to which you belong. After you successfully log on to the domain, you have access to the resources of the domain that have been provided by the system administrator.

Another feature provided by the Server allows for the automatic duplication of directories and files from a server to one or more workstations. Files and directories are replicated across workstations and automatically updated. These copies are used to improve performance or provide backup copies. Although you can use the Windows NT Server replication feature to copy your SQL Server database, it's preferred that you use the built-in replication of SQL Server, which is discussed in Chapter 20, "Setting Up and Managing Replication."

You also gain additional fault tolerance features for the storage of information on disks. For example, a continuous copy of all the information on a disk can be made on a second disk of a server workstation. Data also can be stored across multiple disks, along with error-correction information, to allow the restoration of data, if it's corrupted.

The server also allows you to fully use features that are provided with the NT Workstation system. A feature that's fully utilized with the Windows NT Server is Remote Access. You're more likely to use the Remote Access features of Windows NT to log on to a server system to perform centralized account administration or other administrative operations for the entire network.

You can use the Remote Access service in NT Workstation, Windows for Workgroups, or Windows 95 (called dial-up networking) for remote access as well, but you're limited in the number of sessions that you can support simultaneously.

The first of the additional features provided with the server system is the capability to organize a network of workstations in a client/server—rather than peer-to-peer—configuration. You can do this without sacrificing the peer-to-peer capabilities of the workstation system configuration. The capability to implement features of both client/server and peer-to-peer models provides an opportunity to tailor the configuration of a Windows NT network to meet your work requirements.

Understanding the NT Performance Monitor

The Performance Monitor tool graphically displays the performance of one or more computers of a network. Resources or entities that can be monitored are called objects and can include processes, threads, processors, and memory. Counters are used with objects to record usage statistics. You can record and later review performance information graphically displayed in a representation called a chart.

You can closely monitor the characteristics of the main resources of the computer system, the CPU(s), RAM, and disks in Windows NT by using the Performance Monitor. For example, you can collect and display the percentage of time both system code and user code use the CPU, such as the SQL Server and SQL Monitor processes. See Chapter 17, "Optimizing Performance" for a discussion on how to use the information returned by the Performance Monitor.

The Chart window, one of four displays called views, is brought up in an initialized state. Open the Performance Monitor from the Windows NT Administrative Tools group. Figure 3.4 shows the main window of the Performance Monitor.

Fig. 3.4 - The statistics are automatically collected from the currently running system after you select them.

You need to manually start the logging process after you select the components you want to monitor. By default, when the monitor is loaded, the logging isn't yet active.

Logging isn't enabled when the Performance Monitor is started, so no information is displayed. The three additional views you can display are Alert, Log, and Report. To select objects to be monitored and displayed (or recorded in a log file), choose Add to Chart from the Edit menu. The Add to Chart dialog box appears, allowing you to select the objects for monitoring (see fig. 3.5).

Fig. 3.5 - If you have a multiple processor server you can monitor each processor using the Instance field.

Selecting Objects and Counters in a Chart View

In the example shown in Figure 3.5, the percentage of processor time for the CPU of the system PACKBELL is selected for monitoring and display.

You can use the Explain button to bring up an explanation of the selected counter. For example, the counter % Processor Time is explained at the bottom of the Add to Chart dialog box as the percentage of time a processor is executing an executable thread of code (refer to fig. 3.5).

After you select an object counter, use the Add button to add the counter line to the display. After you select all object counters, use the Done button to display the chart view. (The Cancel button changes to Done when you select a counter for display.) In the chart view shown in Figure 3.6, the percentage of time the processor was busy executing code is displayed graphically.

Fig. 3.6 - The display of each counter is automatically assigned a different color.

Displaying Information in a Report View

A new report is blank because you haven't selected any object counter information. You select the object counters for a report by opening the Edit menu and choosing Add to Report. Only object counter values will be displayed in the report. In the Add to Report dialog box, the counter % Processor Time for the object Processor on the system PACKBELL is added to the report (see fig. 3.7).

Fig. 3.7 - Multiple counters are available for monitoring most objects in the Performance Monitor.

After you select the object counters, click the Done button to bring up the report view. The report is organized by objects, with all counters for the same object group together under a column header. Instances of the same object are displayed across the page, rather than in a single column.

The report view shown in figure 3.8 shows the counters for each of the three objects specified to be included in the report. For the Processor and PhysicalDisk, the second column shows the instance. PhysicalDrive 0 denotes the first hard drive of the system. Instance 0 of the Processor denotes the first and only CPU of the computer system NT486.

Fig. 3.8 - You should obtain a baseline report of your system to use for later comparisons.

Selecting Objects and Counters in an Alert View

Choose Alert from the View menu to bring up an alert view. The alert view is initialized by default. Choose Add to Alert from the Edit menu to bring up the Add to Alert dialog box (see fig. 3.9).

Fig. 3.9 - A program can be automatically started when the alert value is reached.

You select the computer, object counter, color, and instance (if appropriate), similar to the way you did for chart views. Alerts are different in that they result in the display of information only if the object counter value is greater than or less than a value you define.

Selecting Objects in a Log View

Choose Add to Log from the Edit menu to open the Add To Log dialog box (see fig. 3.10). You can select objects from this dialog box. Click the Done button—Cancel changes to Done when an item is added—to bring up the log view with your selected objects.

Fig. 3.10 - You can log the performance statistics from another Windows NT system running SQL Server by entering its name in the Computer text box.

The selected objects appear in the view with all counters collected for each object. Choosing Log from the Options menu brings up the Log Option dialog box, in which you can specify the name to be given to the log file, its location, and the interval at which counters will be written to the log file. You can pause the log with the Pause button or stop it with the Stop button. Counters for the objects included in the log file are available for subsequent viewing.

Displaying and Interpreting SQL Server Events

The Event Viewer records the date and time of occurrence, source, type, category, ID number, user name, and computer system for Windows NT and application-defined operations. You can then display these events by various categories, order, and amount of detail. Information about operations related to the use of Microsoft SQL Server is recorded primarily in the application log. It's also possible that information recorded in the system section is related to the use of SQL Server's system processes.

Events are occurences that you should know about that happen during the execution of user or system code. The events are logged in the event log file, which is enabled automatically at system startup. You can keep event logs and examine them later as printed reports. You can disable event logging through the Control Panel's Services item.

You shouldn't disable event logging when using SQL Server. Otherwise, you'll lose the information recorded about database operations, which might help you correct problems later.

The first time you use the Event Viewer, its window displays events from the system log. In the example shown in Figure 3.11, the window is large enough to display one-line listings of 21 system events. The most recent event is listed first and is selected. If you've chosen Save Settings on Exit from the Options menu, the last log viewed will come up in the Event Viewer window when it's run again.

Fig. 3.11 - The subsequent startup of the Event Viewer can optionally bring up information in the last log that you've examined.

The Log menu allows you to choose from the System, Security, or Application logs to view events (see fig. 3.12).

Fig. 3.12 - Information about SQL Server events is recorded in the application log.

Configuring the Application Event Log

Fig. 3.13 - Select Overwrite Events as Needed to ensure that no new events are lost at the expense of losing the oldest recorded events.

Displaying Event Details

The information at the top of the detail display is similar to an event line in the initial display of events. The description section of the Event Detail dialog box provides additional information about the event. Figure 3.14 shows the detail for an event from the application log recorded about a SQL Server event.

Fig. 3.14 - If the Type field displays Information or Success, the event isn't an error but just the record of an event that occurred on the system.

For each logged event, several items of information are displayed. The items of information recorded for each event are the date, time, user, computer, event ID, source, type, and category.

You can use the information shown in Table 3.1 to help you interpret the information that's displayed in the Event Detail dialog box for events in all application logs.

Table 3.1—Item Descriptions for Logged Events

Item	Description
Event	Windows NT-assigned event number
Category	Event source; security source can be Login, Logoff, Shutdown, Use of User Rights, File, Print, Security Changes, or None
Computer	Name of computer on which error occurred
Date	Date of event
Event ID	Unique number for each source to identify event
Source	Program that was logged--for example, an application or a system component, including a driver
Time	Time of event
Type	Severity of error--for example, Error, Warning, Information, Success, Audit, or Failure Audit displayed as an icon
User	User name when error occurred; can be blank (N/A)

The Event Detail dialog box (refer to fig. 3.14) shows information about a normal stop of the SQL Server process, probably issued through the SQL Service Manager. The Type field shows an entry of Information, specifying that the event isn't an error. You can use the Description list box's scroll bar to display additional information, if any, for an event. The SQL Service Manager is discussed in Chapter 1, "Introducing Microsoft SQL Server"

The last section of information in the Event Detail dialog box displays a byte dump in hexadecimal. Not all events display a dump--only those where the information is relevant. The information within the dump can be interpreted by someone with knowledge of the application code or the Windows NT system, which resulted in the event. You can select the Words radio button to display the dump in words rather than bytes.

Use the Previous and Next buttons to display the detail for the previous and next events in the current log.

Using the View menu

Fig. 3.15 - Choose Oldest First to reverse the order of displayed events.

Choose Find from the View menu to bring up the Find dialog box (see fig. 3.16). Use Find to locate events by a criteria that you specify in the Find dialog box. You can enter various items for an event in the Find dialog box, including the source, category, event ID, computer, user, and any part of the description. If the event is found, the main Event Viewer window appears with the specified error selected. If the event isn't found, you see a Search failed error message.

Fig. 3.16 - Use the Direction radio buttons to define the direction of the search through the log.

In the Find dialog box, the events to be located are restricted to only a single SQL Server event generated that is related to the activity of the SQL Server process, one of the two components of SQL Server. You can use the Find Next button to display the next entry that matches the criteria that you've specified in the Find dialog box.

You can bring up the Filter dialog box to allow the selection of events using a criteria based on one or more items of an event. You can select the events based on the date and time of all events or the first and last events of a range of dates and times. Also, you can enter the source, category, user, computer, and event ID to filter the events displayed.

By default, Information, Warning, Error, Success Audit, and Failure Audit are selected but can be deselected to restrict the events returned. Success Audit and Failure Audit are valid only for the security log.

Clearing the Event Log

I've looked at the event log, and sometimes I see several transactions that are completed each time the MSSQLServer process is started. Is this a problem?

SQL Server will automatically roll back uncommitted transactions each time the server starts up. This is most likely to occur if you stopped the system or server incorrectly--for example, by turning the power off. You should ensure that you stop the server and formally shut down the system each time to minimize the number of pending transactions that must be examined each time the server is started.

From Here...

For information that discusses selected aspects of the topics mentioned in this chapter, you can review the following chapters:

• Chapter 17, "Optimizing Performance" teaches you how to optimize the performance of SQL Server including the use of SQL Server specific information returned by the Performance Monitor.

• Chapter 22, "Accessing SQL Server Databases Through Front-End Products" teaches you how to use client-workstation-based applications to access the server database.

Table of Contents

02 - Data Modeling and Database Design

04 - Creating Devices, Databases, and Transaction Logs