Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- 2 -
Understanding the Underlying Operating System, Windows NT

By taking advantage of multi- processor and multithreading environments, Windows NT offers significant performance improvements over environments that don't have these capabilities.
Windows NT supports the interconnection of client systems to the SQL Server database.
Windows NT contains built-in monitoring and reporting tools that you can use to monitor SQL Server.

Windows NT, the operating system that SQL Server runs under, has several mechanisms that you should understand to use SQL Server more effectively. Operating-system components or processes are sections of computer code that control how the computer's hardware and other software is used.

Some NT components, for example, control how app-lications use one or more central processors. The MSSQLServer and MSQLExecutive processes of SQL Server use Windows NT processes 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 reviewing the entire Windows NT security model is beyond the scope of this book, having a comprehensive understanding of how your network is set up is important. This understanding includes how users are defined and whether you plan to use the same security model in your SQL Server implementation. In addition to Windows NT processes that control the use of resources such as the CPUs, several system applications control aspects of the operating system affect SQL Server. You can monitor the use of Windows NT and SQL Server processes through the Performance Monitor and change usage based on the statistics collected. Through the Event Viewer, you can display errors and other events returned as the result of SQL Server's activity and use the returned information to interpret and correct those errors.

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

In the NT domain section later in this chapter, you see how you work with users on your system, how you set up new users, and how you assign rights. n


See Chapter 20, "SQL Server Security," for information about how to establish SQL Server security.

Understanding Multiprocessing, Multitasking, and Multithreading

Many times, the differences among the terms multiprocessing, multitasking, and multithreading are confusing. These processes are important in your use of SQL Server, however, because they affect the performance and scalability of the system.

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

In its simplest form, multiprocessing occurs when an operating system switches the use of a computer system's CPU among multiple applications. The operating system must keep track of where each program leaves off so that the program can start again at that spot 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 given period, rather than allowing one process to use a CPU exclusively.

Each application must receive use of a CPU long enough to get a reasonable amount of work done. Also, the switching of the CPU must be accomplished quickly. If an operating system gives each application enough time to use a CPU, and if the switch among applications is performed quickly enough, a user who is interacting with one application may work as though the CPU were dedicated to his exclusive use. This performance can be influenced by having too many applications waiting to use the CPU(s).


NOTE: The interval of time during 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--the one that has been waiting the longest to use the central processor--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. A program on Windows NT can be written in several functional sections, each of which 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. One key attribute of a thread is that it must have its own priority for Windows NT to schedule use of system resources separately for each thread. In Windows NT, the term multitasking also refers to the use of the central processor of an NT system by multiple threads of the same process or different processes.


NOTE: The two types of multiprocessing capabilities are 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 to be spread among others.

A feature such as multitasking with multiple threads is most advantageous when more than one processor is available to be shared. An application that's written in several threads can have each thread execute simultaneously 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. Windows NT can use multiple-CPU systems 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.


NOTE: Multiple-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 performed by a second processor of the system. Multiple-server requests can be handled at one time, greatly increasing the number of workstation clients that can be served by the server.

Understanding Multi-Architecture

An important characteristic of Windows NT is its multi-architecture feature. The term architecture refers to different hardware components that can be used on a computer system, especially central processors. Windows NT runs on computer systems that use different microprocessors for their central processing units.

Windows NT can run on systems that use Intel 386, 486, or Pentium processors, as well as on x86 clones produced by such vendors as Cyrix and AMD. Windows NT also runs on Digital's Alpha AXP, MIPS R4000 series processors, and the PowerPC. The Windows NT distribution CD-ROM contains the separate versions of the installation software for all four systems; more compatible systems are planned.

As you can see, a key advantage of using SQL Server with Windows NT as the operating- system platform is that you have 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. They 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 Multiple-User Environment of SQL Server on Windows NT

Traditional mainframe and minicomputer system databases are accessed by users sitting in front of input/output devices. Windows NT is unlike minicomputer and mainframe systems, in that users don't use dumb terminals as input or output devices. Instead, each user gains access to an NT system running SQL Server by using a computer system that has its own operating system. As discussed in Chapter 1, this system is referred to as the client system. The multiple users of a Windows NT server access an application such as SQL Server from their own client computer system.


NOTE: 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 and other workstations, can't do any processing. Dumb terminals replaced the card readers and printers that were used as input and output devices in 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

Windows NT enables you to establish networks and to connect to other computer systems. The connectivity feature of Windows NT serves several purposes:

You can use the connectivity components of Windows NT to connect an application on a client workstation to the SQL Server database on the NT server. All the specified uses of client/server connectivity, such as remote administration, monitoring, and data transfer, are necessary in a system using SQL Server.


See Chapter 19, "SQL Server Administration," for more information on administration of SQL Server.

See Chapter 21, "Optimizing Performance," for a more detailed discussion of Using NT performance monitoring.


Sharing Resources

Windows NT networks are set up as domains, each of which can have several workgroups.

A domain is one form of a Windows NT network. Before a computer can be added to a domain, an account must be set up. The administrator controls rights for this account.

Workgroups are logical groupings of systems in a network, arranged by department, task, or some other method. Users are placed in workgroups to make locating and using shared resources easier.

Both methods of sharing resources could work for sharing a SQL Server resource. Workgroups are geared toward users and, therefore, don't offer 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.


See Chapter 20, "SQL Server Security," for more information on how SQL Server uses the built-in security of Windows NT.

Installing Network Software

You add network protocols to allow access to and from different network types through the Network Properties dialog box (see Figure 2.1). To access this dialog box, choose Settings, Control Panel from the Start menu, and double-click the Network icon. (Alternatively, you can right-click the Network Neighborhood icon on the Desktop and then choose Properties.) You can add, remove, configure, or update components in this dialog box.

FIG. 2.1
You can manipulate several network software properties at the same time.

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

FIG. 2.2
After the installation is complete, you 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

If your NT workstation has a built-in network hardware interface or an installed network interface card, or NIC, its associated network software is installed during the Windows NT Workstation or Server installation. You can add or change network interface cards on PC workstations that don't have network interfaces on their motherboards.

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

One reason 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- and 32-bit NICs perform some network operations faster than 8-bit NICs do. Other NIC characteristics can also affect performance, including the buffer sizes and the types of media supported.

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

Click the Add button in the Adapters tab of the Network Properties dialog box to display the Select Network Adapter dialog box. Then select the network adapter card in the Network Adapter card list. In Figure 2.3, the selected adapter card is the 3Com Etherlink II Adapter.

FIG. 2.3
You need to add adapter-card software if you add a second or different NIC to your system.

Network adapter cards typically require you to specify an IRQ level and an I/O base address when you add adapter software. The IRQ level and I/O base address should match the ones specified by the manufacturer.

Windows NT can detect and configure some adapter cards automatically. Before buying a NIC, you should consult a reputable dealer, who can tell you whether the card can be set up by Windows NT automatically. You can also check with Microsoft to learn about NICs that Windows NT can configure automatically.

Some major manufacturers provide NICs that allow you to set the IRQ and addresses by using software rather than jumpers on the card. Cards that can be set up automatically can be advantageous, especially if you have a large number of other interface cards installed in your PC workstation.

The more cards you install in your system, the more likely it is to have conflicts. Two cards that have identical default IRQ or address settings will not work as expected if those settings aren't changed. Some interface cards allow few changes to be made in their IRQ or address settings. If you can use software to set some of your interface cards to a large number of values, you can prevent many 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 may have to change one or more cards later to eliminate conflicts and to allow all cards--including your network card--to work.

You should also ascertain whether the interface cards, including the network interface cards that you buy for NT, are supported by Windows NT. 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, as well as the current settings, if you changed them from the factory defaults. You should also run any diagnostic program to learn quickly whether the network adapter cards function properly.

Select the adapter card from the Installed Adapter Card list in the control panel application and then click OK to install the network adapter by using the default NT driver, or click Have Disk to use a manufacturer's driver. The user's involvement in the installation of adapter cards depends on the capabilities of the card and the sophistication of the driver software. Most of the settings may be determined automatically; other settings may need to be supplied by you during installation.


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


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

Understanding Workgroups

The capability to form workgroups is one of the built-in network features of Windows NT. Windows NT allows the interconnection of Windows NT systems in groups that can share resources. As mentioned earlier, a workgroup is a logical set of workstations that share resources with one another. This sharing of resources is the basis for designating workstations as the members of the same workgroup.

The members of a workgroup typically can share the resources equally. Examples of resources that workgroup members can share equally are a disk drive and the directories and files on it.

The capability to share resources among workstations without a designated server system is called peer-to-peer networking. Each system can access another system's resources after they become shared. In such an arrangement, the systems function as both clients and servers. 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 resources should be placed in a logical organization, which is the NT workgroup. You must designate which NT workstations become members of the group. After you form workgroups, you can set up the resources for sharing. The underlying capability of peer-to-peer networking of workgroups permits workgroup client access to a 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, for example, is enabled on each system, 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 a hardware component 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 but 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 access the others' resources should be made members of the same workgroup. This is the basic criterion for the formation of workgroups.

You can, however, define a workgroup based on your own criteria. The placement of two or more workstations in a workgroup is arbitrary, which means that an administrator has full control of designating members of the workgroup. You can have groups of only two members each, for example, if you need such a configuration. You can even place workstations that share no resources in the same workgroup, although this situation is unlikely.

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, memory, processor, and system bus. The individual hardware components of a workstation that is used as a server in a workgroup may not be fast enough to allow that workstation to serve many workgroup members.

Members of a workgroup may access a SQL Server database in a different workgroup as well as in their own. More likely, however, the SQL Server database will be installed on a Windows NT server system in a domain.

Members of a workgroup can access the SQL Server database, even though it is placed in a different type of logical organization, the domain.

In a server-based, domain-model network system, you can buy a large, powerful, fast single system that's the only server in a group of workstations. If your server system has a Pentium, MIPS R4000, or Alpha AXP processor, or multiple i486 processors, it can function as a server for a much larger number of workstations.

In traditional networks, the network configuration is either a workstation/server network or a peer-to-peer network--usually not both. With the introduction of Windows for Workgroups and continuing with Windows 95 and Windows NT Workstation, however, 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 in the network in this environment. The disadvantage is that you typically won't be running hard-core server applications, such as SQL Server, on the workstations of these environments.

Also, as you implement your workgroups, you'll find that logical groupings of your users begin to emerge, even beyond the groups that you've already established. You should consider Windows NT domains if you have difficulty administering workgroup networks. Domains allow you to group users in logical cross-sections and then use these groups to manage security, access to the network, user names, and more.

The latter type of centralized organization provides some of the features of a client/server-based 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 20 or so. Microsoft suggests that you define fewer than 20, but the number of members who interact with one another simultaneously determines the actual limit. You can deviate from the sug-gested limit of 20 workstations, although you should keep the recommended values in mind as you configure your workgroups.

You may want to limit the members of a workgroup to fewer 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 in this chapter, 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 work-groups after they know their share names and optional passwords.

It may help you understand workgroups to think of them as being a loosely organized confederation rather than an integrated republic. The members of a workgroup log on to their workstations, establishing their user names for the network. Their user names and passwords are checked in an account database that resides on a local disk. You administer each workstation separately, including defining a separate account for each workstation.

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

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

Members of a workgroup are displayed together to simplify the sharing of resources. There's no restriction on the workstations that can become members of a workgroup provided by Windows NT. You define the criteria for workstation membership.

A workgroup can include workstations with faster processors or multiple processors and large fast disks to extend the 20-workstation practical limit of the workgroup network. Faster processors and faster disks help extend the limit by performing server tasks faster, thereby allowing more workstations to interact as clients and servers and to have acceptable performance.

You cannot extend your network to a configuration in which your servers support 10,000 or more NT workstations, however. Although you can have tens of thousands of interconnected NT systems, the maximum number in a given network is far smaller and is limited by the number of systems that can perform well while interconnected. Real-life tests indicate that approximately 250 workstations, used in a busy network, comprise a realistic network goal.

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

Another possibility to help better manage your user base is to specify a domain for your computer rather than a workgroup. As mentioned earlier in this chapter, a domain is a more tightly administered group of workstations. You can read about domains in the following section, "Understanding Windows NT Domains."


TIP: If you cannot locate a workgroup or domain system that you want to connect to, you're probably experiencing a conflict with the network drivers on your system. Make sure that you installed the correct drivers and that you matched your network configuration with the settings on your network card.
Also, if you find that you can see other workstations on your network but cannot gain access to the server, check your user name and password. The password that you used to log on to your workstation may not be the same as the password established for your NT domain account.

Understanding Windows NT Domains

Domains are the fundamental architecture that controls how your client systems access the server, whether they are on your local-area network or on the Internet. Domains provide a means of logically grouping systems and users to facilitate administering the systems, accessing your server, and interacting with one another.

Windows for Workgroups introduced peer-to-peer networking for Microsoft platforms. Peer-level networking means that workstations share information stored on their local hard disks with other users on the network. Although this arrangement is a great way to share small to medium amounts of information on a few workstations, a serious bottleneck arises as the number of workstations--and the traffic that they generate--grow.

The bottleneck results from the requirement to manage access to the information and balance performance on a given user's system. You must balance this requirement against access to the information over the network. In systems in which the number of workstations and the amount of shared information becomes a burden on the network, you should consider implementing a more industrial-strength solution. With Windows NT, domains become part of the network picture.

Figure 2.4 shows a sample domain configuration with a two-server domain and a single-server domain.

FIG. 2.4
Servers belong to a single domain, whereas users and systems can use more than one domain.

At first, system administrators may disagree with the configuration shown in Figure 2.4, on the ground that workstations belong in one domain or another. The point of the figure is that servers belong to one domain and one domain only. Workstations and users can sign into and out of different domains as needed, as long as they are authorized in other domains. The important thing is that when you enter, or log into, a given domain, you must abide by the domain's rules and security parameters.

You set up and administer user rights from the User Manager for Domains (see Figure 2.5), which is listed in the Administrative Tools menu in NT.

The User Manager enables you to work with both users and groups, and to assign all rights recognized by the system to those users. When you create the necessary users and groups, apply rights to your system and control access to the files and directories on it.


TIP: You can work with more than one user at a time. If you manage existing users and want to set up the group associations, logon times, and other attributes, first select the users from the list by Shift+clicking and Ctrl+clicking the names in the list; then choose User, Properties.

FIG. 2.5
Users belong to groups, and you control access to resources either by these group assignments or by the individual users.


CAUTION: Be wary of one particular user: GUEST. Strongly consider disabling the account. The GUEST account is dangerous, because any user who logs on to your system without a valid user name and assigned password is assigned to this account. Therefore, any privileges given that account are automatically provided to any user who signs on to the system without otherwise defined access.

The built-in group EVERYONE is also in the system. Every user and group belongs to this group. In addition, the group's settings provide the default privileges to all resources when they are created, unless user access is explicitly revoked or modified. This means that you can assign rights to limit access to your users, but unless you remove the rights for the group EVERYONE from the resource that you set up, all users still have access to it.


In almost all cases, your best option is granting group rights to resources rather than to users, even when you are granting access to a resource to a single user. This procedure will save you time and effort later, when the user is replaced or gains an assistant who needs to have the same level of access. In that case, you need only modify the members of the group; you won't need to change access privileges for resources in your system. Consider the following steps in implementing your system's user database:


CAUTION: Windows NT assigns user rights based on a Lleast rRestrictive model. If you belong to two profiles, for example, and if one profile indicates that you have no access to a resource but the other indicates that you do have access, you can gain access to the resource. The least restrictive of the two profiles indicates that you are allowed access.

Put simply, any user whom you assign to the NOACCESS group, but whom you do not remove from other groups, may have more access rights than you planned. The effect of the NOACCESS group may be weakened because the other groups allow overriding user rights, granting user rights to certain resources. When you revoke a user's access, be sure to review the associated account; make sure that it does not belong to other groups that may influence effective rights.


Working with NT Users

The User Manager for Domains enables you to create new users in several ways. One way of creating new users that offers you the most leverage for your time is choosing the Copy command (User menu) to copy the rights of an existing user. As you can see in Figure 2.6, you set up several options for users who may access your system.

FIG. 2.6
When you copy an existing user, the copy inherits the groups and privileges of the original.


NOTE: If you choose the Must Change Password option, an additional step is required the first time that a user logs onto the system: Each person must change his or her password. This practice may seem to be a good idea at first, but be wary of the user's particular type of system.
In some environments--specifically, Windows 3.x systems--the system may not allow users to change their passwords. In such cases, you effectively lock the users out of the system, because NT bars them from the system until the passwords have been changed.

If you have a user blocked from signing onto the system, review the account and make sure that this option is not checked. Also make sure that the Account Disabled option is not selected. This option also prevents access to your system.


The following sections briefly cover the various aspects of user accounts.

Assigning Groups

The Groups button enables you to set up the groups to which the user belongs. Figure 2.7 shows the Group Memberships dialog box.

FIG. 2.7
Work with users in groups to simplify management tasks.

When you assign a user to a group, he gains the rights and privileges associated with that group. By double-clicking groups listed in the Member Of and Not a Member Of list boxes, you add and remove membership in groups. After the user is assigned to the appropriate groups, choose OK to save the changes and return to the new user's Properties dialog box.

Controlling Access to Resources

After you create your users and groups, you must assign permissions. Assigning permissions is the final step toward securing your system at a general level. This section shows you the basics of applying permissions to system resources.

Windows Explorer is the key to applying security to various resources on your system. Select the directory resource that you want to share, and right-click it. The shortcut menu that appears (see Figure 2.8) has an option that establishes sharing for the resource.

Choose Sharing from the menu. This option not only sets up the share name and the number of users accessing the resource, but also offers the option to set permissions on the resource by clicking the Permissions button. Figure 2.9 shows the dialog box in which you set these initial options.

FIG. 2.8
Choose Sharing from the shortcut menu to set up or maintain sharing options for the selected resource.

FIG. 2.9
Setting up the initial share information is a straightforward process.


NOTE: This information on securing share-level security on your system pertains to setting up shares on the server. Remember that share-based security is different from NTFS-based security. If you establish rights based on a share, the rights are not enforced unless the share is accessed.

If you use the directory that the share relates to without using the share, the rights have no effect.

If you need to protect content on the server, you must use NTFS for the file system. You also must set rights based on the permissions for the directory, not the share.



NOTE: If you select a share name that is longer than the DOS-standard format, some DOS workstations may not be able to access the resource. If you select such a name, you are warned about this fact as you apply the share information for the resource.

If you click OK or Apply at this point without setting permissions in the permissions dialog box, all users who have access to the resource via a share, a parent directory, and so on have open, Full Control access to the resource. Be sure to click the Permissions button.

The underlying file system dictates how permissions change. If the NTFS (the NT File System) is installed, you gain additional options. These options enable you to apply specific permission subsets, rather than just generic permission categories. The following list shows the general access permission categories that you can assign:

When you choose Add, you have two options:

When you choose OK, the users are listed in the Permissions text box. If the permissions are properly applied, choose OK again to save and apply the changes. Now the resource is available with your declared permissions.

Understanding the NT Performance Monitor

You must be able to monitor the use of system resources by applications, including the components of SQL Server, to properly control the system. The Windows NT system provides extensive performance-monitoring capability.

The Performance Monitor administrative tool, which controls the monitoring and display of the use of system resources, graphically displays the performance of one or more computers in a network. Resources or entities that can be monitored are called objects. Objects can include processes, threads, processors, and memory. Counters are used with objects to record usage statistics. You can record and, later, review performance information displayed in 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. You can, for example, collect and display the percentage of time that both system code and user code use the CPU, such as the SQL Server and SQL Monitor processes.


See Chapter 21, "Optimizing Performance," for more information on how to use the information returned by the Performance Monitor.

The chart window, which is one of four displays called views, appears in an initialized state. Open the Performance Monitor from the Windows NT Administrative Tools group. Figure 2.10 shows the main window of the Performance Monitor.

FIG. 2.10
The statistics are collected from the currently running system automatically after you select them.


NOTE: You need to start the logging process manually after you select the components that 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 starts, so no information is displayed. The three additional views that you can display are Alert, Log, and Report. To select objects to be monitored and displayed, or to be recorded in a log file, choose Add to Chart from the Edit menu.

Selecting Objects and Counters in a Chart View

You choose objects for monitoring from the Object drop-down list. You choose a counter for an object from the Counter list. Each object has a different default counter. The default object is Processor, with a default counter of % Processor Time.

In the preceding section, 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 display an explanation of the selected counter. The counter % Processor Time, for example, is explained at the bottom of the Add to Chart dialog box as the percentage of time that a processor is executing an executable thread of code.

After you choose an object counter, click the Add button to add the counter line to the display. After you choose all object counters, click the Done button to display the chart view. The Cancel button changes to Done when you choose a counter for display. The chart view in Figure 2.11 shows the percentage of time that the processor was busy executing code.

FIG. 2.11
Each counter is assigned a different color automatically.

Displaying Information in a Report View

You can display the information collected by the Performance Monitor for object counters in a report rather than a chart. A report view presents the information in tabular format. You may find a report format to be preferable for viewing statistics, because the numeric representation of all counters is displayed. You can create a report by choosing Report from the View menu.

A new report is blank because you haven't selected any object counter information. You select the object counters for a report by choosing Add to Report from the Edit menu. Only object counter values are 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 Figure 2.12).

FIG. 2.12
Multiple counters are available for monitoring most objects in the Performance Monitor.

After you choose the object counters, click the Done button to display the report view. The report is organized by objects, with all counters for the same object grouped below 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 2.12 shows the counters for each of the three objects 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.

Selecting Objects and Counters in an Alert View

An alert is a line of information displayed in the alert view of the Performance Monitor when the value of an object counter is above or below a value that you define. The entry in the log includes a date and time stamp, the actual object counter value, the criteria for returning the entry, the object value counter, and the system.

Choose Alert from the View menu to display an alert view, which is initialized by default. Choose Add to Alert from the Edit menu to display the Add to Alert dialog box.

You choose the computer, object counter, color, and instance (if appropriate), similar to the way that you choose these elements 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 that you define.

Selecting Objects in a Log View

The log view allows the selection of objects and their counters to be logged for subsequent display and analysis. You display the log view by choosing Log from the View menu. Like the other views, the log is initialized by default. No object counters are defined for it.

Choose Add to Log from the Edit menu to open the Add To Log dialog box (see Figure 2.13). You can choose objects in this dialog box. Click the Done button to display the log view with your selected objects.

FIG. 2.13
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.

Choose Log from the Options menu to display the Log Option dialog box, in which you can specify the name to be given the log file, its location, and the interval at which counters will be written to the log file.

You can pause the log by clicking the Pause button or stop it by clicking the Stop button. Counters for the objects included in the log file are available for subsequent viewing.

Displaying and Interpreting SQL Server Events

You can use an integrated logging tool in Windows NT to log information about application, system, and security operations called events. This tool, called the Event Viewer, controls the logging and subsequent display of information about all events.

The Event Viewer records the date and time of the occurrence, source, type, category, ID number, user name, and computer system for Windows NT and application-defined operations. You can 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. Information recorded in the system section may be related to the use of SQL Server's system processes.

Events are occurrences you should know about that occur 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.


CAUTION: You shouldn't disable event logging when you use SQL Server. If you do, you lose the information recorded about database operations, which could 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 2.14, the window is large enough to display one-line listings of 21 system events. The most recent event, which is listed first, is selected. If you choose Save Settings on Exit from the Options menu, the last log viewed appears in the Event Viewer window when you run it again.

FIG. 2.14
The Event Viewer can display information from the last log that you examined.

The Log menu allows you to view events from the S_ystem, Security, or Application log (see Figure 2.15).

FIG. 2.15
Information about SQL Server events is recorded in the application log.

Configuring the Application Event Log

You should configure the application log of the Event Viewer after you install SQL Server. Choose Log Settings from the Log menu to display the Event Log Settings dialog box. You can set the maximum size of the log file (in kilobytes) and the period of time during which events should be recorded, and indicate whether to overwrite events if the log file is full. Separate settings are kept for each of the three logs: system, security, and application. In Figure 2.16, the system log file is set to a maximum size of 512K, and events are set to be overwritten in a week.

FIG. 2.16
You may choose Overwrite Events as
Needed to ensure that no new events are lost at the expense of the oldest recorded events.

Displaying Event Details

You can view details about an event by double-clicking a selected event or by choosing Detail from the View menu in the main window of the Event Viewer. You must examine the detail of an event to learn the meaning of the event numbers.

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 2.17 shows the detail for an event from the application log recorded about a SQL Server event.

FIG. 2.17
If the Type field displays Information or Success, the event isn't an error--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 date, time, user, computer, event ID, source, type, and category.

Table 2.1 can help you interpret the information displayed in the Event Detail dialog box for events in all application logs.

Table 2.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, such as 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 shows information about a normal stop of the SQL Server process, probably issued through the SQL Service Manager. The Type field shows the entry Information, specifying that the event isn't an error. You can scroll the Description list to display additional information, if any, for an event.


See Chapter 1, "Introducing Microsoft SQL Server," for further discussion of the SQL Service Manager.

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 in which the information is relevant. The information in the dump can be interpreted by someone who knows the application code or the Windows NT system that is noted in the event log. You can click the Words option button to display the dump in words rather than bytes.

Click the Previous and Next buttons to display the detail for the preceding and following events, respectively, in the current log.

Using the View Menu

You can use the commands in the View menu to control other characteristics of the display of events in the main window of the Event Viewer. The newest events are listed second in the window, for example (see Figure 2.18).

FIG. 2.18
The newest events are listed second in the window.

Choose Find from the View menu to display the Find dialog box, shown in Figure 2.19. Use Find to locate events by 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. 2.19
Use the Direction option buttons to define the direction of the search through the log.

You can display the Filter option to choose events by using criteria based on one or more items of an event. You can choose events based on the date and time of all events or based on the first and last events of a range of dates and times. You also can enter the source, category, user, computer, and event ID to filter the events that are displayed.

By default, Information, Warning, Error, Success Audit, and Failure Audit are selected; you can deselect these options to restrict the events that are returned. Success Audit and Failure Audit are valid only for the security log.

Clearing the Event Log

Choose Clear All Events from the Log menu to empty a log file of all recorded events. If you choose Clear All Events, a precautionary dialog box appears. Click the Cancel button to cancel the emptying of the event log.


NOTE: If you look through the event log and notice transactions that are being completed when SQL Server starts, these events are being rolled back or rolled forward to synchronize with the time when the server was shut down. This is how SQL Server maintains a consistent database in times of unexpected shutdowns. SQL Server examines the database to determine where it left off and ensures that the databases are at the last possible point of consistency.

Reality Check

While you are implementing Windows NT and the users on your system, you can easily forget to remove or control the members in the EVERYONE group. In many implementations, this situation leads to unwanted access to resources on the server. Carefully review the groups to which you assign your users.

As a precaution, in our implementations, we create a user who represents the rights that we want to assign on a general basis. Later, when we add users to the system, we copy that original user, update the user name, and update the password, so we are assured that the user rights are appropriate. In some cases, of course, you want to grant specific additional or lesser rights to a given user, but starting with the copied user provides a good basis for additions.

In addition, if you're using Remote Access Services (RAS) on your server, be sure to remove the GUEST account. If you don't, you may be opening your system to an additional means of logging into and searching your systems. If a person calls in and then logs in with an unknown user name and password, that person is assigned to the GUEST account and given the corresponding rights.

From Here...

In this chapter, you learned about the relevant characteristics of Windows NT, on which you install Microsoft SQL Server. Windows NT's multithreaded design and support for multiple processors is ideally suited for an application such as SQL Server. In addition, the built-in network support of the Windows NT system makes possible a simple, straightforward connection from clients to SQL Server. Last, you learned that information returned by SQL Server is returned to the built-in reporting facilities of Windows NT: the Performance Monitor and the Event Viewer.

For information on selected aspects of the topics mentioned in this chapter, review the following chapters:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.