Chapter 01 - Introducing Microsoft SQL Server

You can also use a command-line interface to perform all operations with Microsoft SQL Server. A dialect of SQL is used with SQL Server for interactive and application program access to data. (SQL is the de facto standard for database operations, and every vendor's database product should include a version of it.)

The Microsoft Open Database Connectivity model (ODBC) uses SQL to connect to databases even in those cases where the underlying database doesn't natively know SQL. In those cases, SQL is translated into a set of commands that will accomplish the requested call. After you master SQL, you can work with any ODBC data source that you need to access.

Although this book includes coverage of how to use the command-line tool for issuing interactive SQL commands, you should remember that you can perform most operations through the application tools that use the Windows Graphical User Interface. You can use either interface or both interfaces, depending on your interest. If you're already familiar with another SQL dialect, you may initially find that it's simpler to issue direct SQL commands for all operations.

Exploring the Origin and Evolution of SQL Server

In 1988, SQL Server for OS/2 was co-developed by Sybase, Microsoft, and Ashton-Tate when it first came to the PC platform. After SQL Server was ported to NT, Microsoft became the lead partner in the project and Ashton-Tate left the picture. Both Microsoft and Sybase sold and supported the product through version 4.21, as they were by and large the same product. In 1993, partly due to the Windows NT operating system version developed by Microsoft, the co-development/licensing agreement ended. Since that time, Microsoft has focused on the NT Server versions of SQL Server, while Sybase has worked on and sold the minicomputer versions of the SQL Server. Version 6.5, released in April, is the latest version in the progressive tuning and enhancement of the database engine.

If you start Microsoft SQL Server using a command line, the information returned in a separate window (which also is written to a separate log file) includes a Sybase copyright notice—evidence of Microsoft SQL Server's origin. Three Sybase copyright notices can be seen in the following excerpt from the startup of an earlier version of SQL Server on Windows NT.

94/08/09 16:17:37.92 kernel
    SQL Server for Windows NT 4.21 (Intel X86)
     Jan 27 1994 21:47:39
Copyright (c) 1988-1994 Microsoft Corporation;  
Copyright Sybase, Inc 1987-1994
94/08/09 16:17:38.08 kernel   
    Copyright (C) 1988-1993 Microsoft Corporation.
94/08/09 16:17:38.08 kernel   
    Copyright Sybase, Inc. 1987, 1993
94/08/09 16:17:38.08 kernel   
    All rights reserved.
94/08/09 16:17:38.08 kernel   
    Use, duplication, or disclosure by the United 
   States Government is subject
94/08/09 16:17:38.10 kernel   
   to restrictions set forth in FAR 
   subparagraphs 52.227-19(a)-(d) for civilian
94/08/09 16:17:38.16 kernel   
    agency contracts and DFARS 252.227-7013(c)
    (1)(ii) for Department of Defense
94/08/09 16:17:38.16 kernel   
    contracts. Sybase reserves all unpublished
    rights under the copyright laws of
94/08/09 16:17:38.16 kernel   
    the United States.
94/08/09 16:17:38.16 kernel   
    Sybase, Inc. 6475 Christie Avenue, 
    Emeryville, CA 94608, USA.
94/08/09 16:17:38.16 kernel   
    Logging SQL Server messages in file 
    'G:\SQL\LOG\ERRORLOG'
94/08/09 16

Microsoft originally licensed the core components of SQL Server from Sybase to make the product available on PC platforms running OS/2 and, more recently, Windows NT.

You can see from the heritage of SQL Server that it's by no means a new product to the marketplace. Indeed, it's been through many different rounds of revisions, updates, and upgrades, and certainly represents a world-class database platform on which you can develop mission-critical applications. You don't need to worry about "breaking in" a new-to-the-marketplace platform in your organization.

Understanding Relational Features of SQL Server

The model for relational databases was designed by Dr. E.F. Codd in 1970 to store, retrieve, and manipulate data in a way that was easier than the hierarchical and network databases. Hierarchical and network databases were difficult to design and sometimes difficult to write proper queries for access to data.

Hierarchical and network databases were difficult to work with for several reasons. The physical and logical definitions of data storage in hierarchical and network databases had to be done by using a cryptic definition language syntax. Another difficulty of working with data definitions came from the fact that different types of internal pointers, numeric references to data locations, and more had to be set up and stored through the database. The pointers were used for the subsequent direct retrieval of data.

In Codd's relational database model, the data is referenced as though it's stored in a two-dimensional table. The actual physical storage of the data—although significant for the time it takes to store, change, or retrieve data—is insignificant syntactically for reference. The two-dimensional table model permits data to be referenced as the rows and columns of the table.

In a relational database, data is referenced as the rows and columns of a table. You can easily visualize data stored as a table because you often encounter data stored in tables in everyday life. For example, you reference train or plane schedules in the form of a table and you also create typical worksheets in the form of a table.

Fig. 1.1 - Example of common table.

The rows of a table are unordered in Codd's relational model. In the relational model implemented in Microsoft SQL Server, the rows of a database table are also unordered (they're in the order in which they were entered), unless a clustered index is created for the table. After you create a clustered index for a table, the rows are stored in ascending order by the one or more columns that you use to create the index. Later, this book covers more about clustered indexes.

For more information about clustered indexes and the other types of indexes supported by SQL Server, see Chapter 10, "Managing and Using Indexes and Keys."

It's important, however, that the statements you use in the retrieval language to access table rows are independent of the order of the rows. If you require that the rows of a table are retrieved and displayed in an order, the statement that you issue to retrieve the rows must specify the row order. The rows are sorted as they're retrieved for your query.

The original relational model required each row to be uniquely defined by at least one column of a table, the unique key. The unique row requirement ensures that each row is accessed or changed independently and uniquely from other rows of the table. The query language used to access table rows can use only data stored within each row to separate one row from another.

SQL Server, however, doesn't require you to define unique table rows. You can create two or more rows of a table that can't be referenced separately from one another. Although you may not find a use for duplicate rows, some users feel such rows are desirable in some applications. In Chapter 2 you'll learn more about relational design concepts and techniques, but for now it's important to understand what's possible, if not practical. If you do want to prevent duplicates, you can add a constraint to tables to prevent duplicate rows.

As you'll see in Chapter 2, unless you're absolutely certain you must allow the storage of duplicate rows, you should ensure that table rows are unique. In the absence of enforced uniqueness, it's too easy to accidentally add one or more duplicates to the table. After you add the duplicate rows, it's difficult to remove or update them.

In the relational database model, data that's stored across tables in one or more databases is combined during the access of the rows during an inquiry. For example, if the Employees table contains columns such as Name, Department, and Badge, a second table named Pays can contain the columns Hours_Worked, PayRate, and Badge.

You can define the Badge column in both tables. This way, you can subsequently retrieve column values from both tables in a single query. You combine columns from multiple tables by using statements that call out the columns you need and specify the information common to both tables in the Where clause. You'll read more about the syntax of this operation, but for now, it's important to understand only that this pulling together of information based on common values is known as a join.

For more information on Where clauses, see Chapter 6, "Retrieving Data with Transact-SQL."

The example in the Employees and Pays tables uses the relational capabilities of SQL Server to retrieve information from each table using the corresponding badge numbers. An example Select statement would be as follows:

Select * from Employees, Pays where 
    Employees.Badge = Pays.Badge

It's easiest to maintain referential integrity if table rows are unique. This ensures that there will be only a single row in a second table. Make sure you maintain the badge number if it is modified or deleted in the first table. In the relational-database model, the column, or set of columns, that uniquely defines the rows of a table is referred to as a key.

A key that uniquely defines the rows of a table is called a primary key. If you add the column(s) that is a primary key in one table to a second table, the column(s) added to the second table is called the foreign key. It's a foreign key because the new columns referencing the first table are used only to allow the matching of corresponding rows between the tables. For more information, see Chapter 10, "Using and Managing Indexes and Keys."

In earlier database systems, internal pointers were created and maintained within the database to link the corresponding rows stored in the tables. The pointer mechanism created a problem, however, because when the database was created, you had to define the data that was later combined during retrieval.

Older hierarchical and network databases don't use terms such as table or row. Hierarchical and network databases use their own terminology to describe data. For example, the equivalent of a row of a relational database is called a record type. The equivalent of a column of a relational database is called a data item.

If you neglected to identify data that must be combined later during retrieval, you couldn't do it once the database structure was created. You had to re-create the logical and physical structure of the database. The main problem involved with using hierarchical and network databases was that changes in data-retrieval combinations were impossible to make without redesigning the database.

In relational databases such as SQL Server, you can add a new column to a table at any time. This allows you to create relationships with other tables. Unlike typical hierarchical or network databases, the database doesn't need to be redefined; only a single table must be redefined. You don't need to unload the rows of the table and later reload the table to add a new column. You can use SQL's ALTER TABLE statement to make modifications to existing tables.

For more information about the ALTER TABLE statement, please see Chapter 7, "Performing Operations on Tables."

Exploring Client/Server Features of SQL Server

Distributed processing was introduced by minicomputer systems to provide the capabilities of large mainframe computers. The data storage, access, and processing capabilities of several minicomputers could match the processing capabilities of a mainframe computer for some operations by working together.

Each system in a client/server model performs one or more of the data storage, access, or processing operations. Client/server computing can't be done with a system that uses terminals or PCs running terminal emulators connected to another computer. In this arrangement, the terminal or the PC that's used like a terminal is simply too passive—it only sends and displays sets of characters.

When PCs and servers are connected, the overall processing should be divided between the server, mainframe, or minicomputer system and the client system. The client and the server each process work within its own capabilities—a form of teamwork that contributes to the efficiency and speed of the overall operation.

Client/server, as the name implies, also involves an unequal division of processing. The inequality results from the processing disparity between the server and the client. The larger and faster server computer transfers data faster, stores greater quantities of data, and typically performs more extensive processing than the client system.

Smaller PC systems are used as the client in a client/server system because the PCs perform proportionally less of the overall work, relying primarily on the server for heavy-duty data manipulation. Also, the PC's keyboard and monitor allow it to work as an input device (generating commands and data) and as a output device (displaying data to the user).

A client and a server also are defined by the direction of the data flow and operational responsibilities. A large and powerful PC system can function as a server if it receives commands and data from one or more PC systems, processes the data, and returns information to other PC systems. The server is the computer system that receives requests for processing or information from other computer systems.

You can use large, powerful PCs as servers with less powerful PCs as clients and still qualify as using client/server technology. In this environment, the PC servers are usually more powerful than an average PC; this helps them perform processing requests from many clients.

Microsoft SQL Server is a perfect example of a client/server system. The SQL Server database must be installed on the Windows NT platform. The Windows NT operating system provides you with an extremely broad range of processor systems to use as your server. Windows NT is supported on I86 processor-based systems, PPC, MIPS, and Alpha AXP RISC-processor-based systems

Microsoft SQL Server is provided with the server software that's installed on the server system and some client software that's installed on the client PC systems. Windows GUI application tools allow the database and all objects to be created, maintained, and accessed from the client.

The network software components required for the interconnection of clients and the server computer are built into the Windows NT system. Windows NT also provides a choice of network protocols for communication between the client and server systems. A client can run Windows 95, which also contains built-in network software for connection with the Windows NT server system. The Windows 95 client and Windows NT server systems support a wide range of network cards.

In a client/server system where the server application is a database such as SQL Server, the server is responsible for the creation and maintenance of database objects such as the table and indexes. The server maintains referential integrity and security and ensures that operations can be recovered in the event of numerous types of failures.

The client performs all user interaction, including information display and allowing manipulation of the application with the graphical user interface. After rows of data are retrieved from the server, the application can create copies to be held locally and the data can be manipulated. You also can control the type of access to the information. Read-only access is often an excellent option, insulating the user from the master copies of the information that they work with on the server.

If you work with local copies of the information, you can work with the information locally without communicating with the server. After you complete your work, you can send changes back to the server or, if the information was for review only, simply discard the working databases. Of course, you also can manipulate the data directly in the SQL Server database from the client, if needed. You must be sure to update the server with all changes so that other users can access the most recent data in the database.

You can also access SQL Server directly from the server. Direct server access is convenient, especially for administrative operations, but it isn't a client/server approach because the operations occur locally rather than across the network. Microsoft SQL Server comes with 32-bit versions of the SQL Windows application tools for the Windows NT server that are normally used on the clients.

Although you can have client applications validate new or updated data, the validation should optimally be done at the server. For example, a column such as Badge can be checked to ensure that each new or updated badge number is unique and within a specified range. It's safer for the data to be validated at the server as a part of a SQL Server-defined mechanism. If the validation is defined at the server, it will always be in force whether or not the connected client performs a validation.

A big benefit of using server-side validation is that you'll protect the database from access from applications that may access the database in "non-standard" manners. This includes applications (such as Excel, Access, and Word) that can connect to the database using ODBC. In each case, rules and integrity checks that you implement on the server will still be enforced, even though the client application may be unknown.

If you rely on client applications to validate data before it's sent to the server, you must ensure that all the client applications do it consistently. You must also ensure that changes aren't made directly at the server, where no validation mechanism has been defined. It's simpler and more reliable to implement server validation.

You also can perform validation in client applications in addition to validation on the server. Client validation can be specific to the client application that isn't enforced by server validation mechanisms. When the updates are sent to the server, it will still enforce its own validation.

Examining SQL Server Features and Components

Microsoft SQL Server allows you to define up to 32,767 databases. If you realize that the definition of a database is a centralized repository for the storage of information, it is difficult to be overly constrained by the 32,767-database limit. It's unlikely you'll encounter any situation in which you need to define more than this very liberal limit. If you do, you'll certainly want to be looking at adding additional servers to your network to help balance the load. In a typical production installation, you'll often find that less than five—and often only one—application-oriented databases will be in service on any given server.

You can also define up to 2 billion tables within each of your 32,767 databases. It's not likely that you'll need anywhere near 2 billion tables in a database. With most typical systems, you'll have only several hundred tables in a database.

You can define up to 250 columns for each table. In Chapter 2, where you'll learn about database design, you'll see that when you normalize your database tables, you largely overcome this limitation. As you'll see, SQL Server allows columns from as many as 16 tables to be combined in a single query.

The number of rows in a table is effectively unlimited for Microsoft SQL Server. You're limited in practice by the capacity of the storage medium on which tables are stored, and databases and their tables can be stored across multiple physical disks. Microsoft SQL Server allows databases to expand to include up to 32 physical disks.

The 32-disk limit is imposed indirectly through subordinate logical structures. You store SQL Server databases, transaction logs, indexes, and tables on logical structures called segments. You can expand a database by adding segments that are created on devices. A database can include up to 32 segments. See Chapter 4, "Creating Devices, Databases, and Transaction Logs" for information about creating databases and segments.

You can define up to 250 indexes for each table, only one of which can be defined as a clustered index. An index is a structure that allows the table rows to be retrieved more quickly than they could without using an index. In a clustered index, the table rows are sorted and maintained in storage in a physically ordered state. That is, rows that are sorted before and after one another are also stored in that sorted order. An index is often defined for the columns that are referenced in retrieval statements. 250 indexes should provide fast retrieval of table rows.

Indexes require additional storage space in the database for the index structure that must be created and stored. One performance recommendation is to define as few indexes as you need because of the space they take up. You still need to define enough indexes to allow the rapid retrieval of rows. You should define the minimum number of indexes that you require; it would be unusual for you to need more than 250 for a single table.

Devices and Databases

Each database is created with a set of system tables in which SQL Server records data about the database objects such as tables or indexes that you subsequently create. Like a relational-database product, SQL Server keeps the control information about your database objects in a relational database—the set of system tables.

Transact-SQL

Transact-SQL adds additional keywords to those of the original SQL for the formation of data retrieval, storage, and manipulation. When SQL Server's implementation of SQL was put into place, like other database vendors, Microsoft added features and extensions to the language.

Compared to other vendors' SQL dialects, Transact-SQL has less unique syntax. Arguably, the SQL dialect SQL-PLUS, used with the relational database Oracle, has the most additional unique syntax. Although the large set of unique syntax in some SQL dialects is useful, the use of dialect-specific syntax makes stored sets of SQL commands non-portable and can be the cause of many headaches when moving your systems between servers.

Remember that some SQL dialect is used with all relational databases. If you work with more than one relational database, or if you must convert from one to another, it's easier to use the SQL syntax that's the most generic. Stored sets of SQL statements—if they use generic syntax—can be easily converted or used across relational databases.

Transact-SQL is best characterized as lean and mean. You have just enough enhancements to basic SQL to write functional queries. Transact-SQL contains statements to create logical storage units, the devices, as well as the databases that reside on the devices. You can also use Transact-SQL statements to create the objects, such as tables, that are stored within the databases.

Not surprisingly, you can also use Transact-SQL statements to add and manipulate data and other database objects. Four keywords are used to form statements that perform all basic data storage, retrieval, and manipulation. Use INSERT to add new rows to a database table. Use DELETE to delete rows from a table and use UPDATE to change rows of a table. Use SELECT to form various statements for the retrieval of data from one or multiple tables.

The INSERT, DELETE, UPDATE, and SELECT statements—as well as other statements—use a generic form of SQL for data manipulation. The extensions to Transact-SQL are principally for flow control to direct the execution order of statements. Use flow-control statements in organized sets of SQL statements that are stored as objects within your database.

Stored sets of Transact-SQL statements contained within the SQL Server database are called stored procedures, which are compiled so that they rapidly execute SQL statements. You can use stored procedures in addition to programs for database access and manipulation because they can use variables and parameters, return errors and status, and use flow control to control the execution order of SQL statements.

For more information on Stored Procedure and how to implement them, see Chapter 13, "Managing Stored Procedures and Using Flow-Control Statements."

A trigger is a special type of stored procedure used to maintain referential integrity in a SQL Server database. You create insert, delete, and update triggers to control the addition, deletion, or updates to corresponding rows of related tables for which the trigger is defined. Triggers are an excellent way to maintain referential integrity because you have complete control over the operations that they perform, and they're server-based.

You also use several additional objects—rules, defaults, and constraints—to help control or apply values automatically to table columns. You use a default to supply a value to the column of a database table when the insertion of a new row doesn't specify a value for the column. A rule constrains the values that can be entered into the column of a table. A constraint is used to define a characteristic of a table column, such as requiring only unique values.

Two Windows NT processes are part of the SQL Server set of components. You issue Transact-SQL statements that are conveyed to and performed by the two server processes. You can use a tool such as the Performance Monitor to display the characteristics of the SQL Server processes. For example, in figure 1.2, shows the working set for the two SQL Server processes SQLSERVR and SQLEXEC. The working set of a process is the percentage of processor time that's directly allocated to a process.

Fig. 1.2 - The Performance Monitor displays the characteristics of the SQL Server processes.

The SQLSERVR process is divided into multiple threads for execution. Threads are the separate units of execution on a Windows NT system. The division of one of the two SQL Server processes into multiple threads allows multiple execution in a multiprocessor environment such as Windows NT. If your server has more than one processor, different operations on the SQL Server databases can be executed simultaneously.

You can buy a wide range of computer systems with multiple processors and increased memory to enhance the Windows NT version of Microsoft SQL Server and have your transactions performed as rapidly as you need.

You can use Windows NT's Performance Monitor not only to monitor the performance of SQL components, but to learn about the function of components. For example, you can perform different Transact-SQL statements and use the Performance Monitor to determine which component is activated to perform the operation.

Command-Line Applications

From a command-line session, you invoke ISQL with the command isql. You can use several parameters on the ISQL command line. For example, you can enter the user name and password following ISQL to bring you directly into an ISQL command session.

The following example shows the initiation of a command session. The command prompt is successively numbered until the termination command GO is entered.

isql /Usa /P<password> /S<server>
1>

usage: isql [-U login id] [-e echo input]
      [-p print statistics] [-n remove numbering]
      [-c cmdend] [-h headers] [-w columnwidth] 
      [-s colseparator]
      [-m errorlevel] [-t query timeout] [-l login timeout]
      [-L list servers] [-a packetsize]
      [-H hostname] [-P password]
      [-q "cmdline query"] [-Q "cmdline query" and exit]
      [-S server] [-d use database name]
      [-r msgs to stderr] [-E trusted connection]
      [-i inputfile] [-o outputfile]
       [-b On error batch abort]
       [-O use Old ISQL behavior disables the following]
            <EOF> batch processing
            Auto console width scaling
            Wide messages
            default errorlevel is -1 vs 1
      [-? show syntax summary (this screen)]

Table 1.1 summarizes the function of each parameter. Each parameter (also called a switch in the Microsoft SQL Server documentation) is preceded with a forward slash (/) or a hyphen (-). The command isql /? displays the hyphens (-), although the hyphen or forward slash can be used. (The use of the hyphen in command-line ISQL is inherited from the Sybase version of SQL Server.)

Table 1.1—ISQL Command-Line Parameters

Parameter	Function
a packet_size	Packet size for data transfer 512 through 65535; NT default is 8192
b	On error batch abort
c cmdend	Specifies the command terminator; default is GO
d dbname	Issues a USE dbname command on entry into ISQL
E	Use trusted connection
e	Echo input
H wksta_name	Specifies the workstation name
h headers	Number of rows to print between column headings
i inputfile	Specifies an input batch file for execution
L	Lists the local and remote servers
l timeout	login timeout
m errorlevel	Sets error-level displays to this level or higher
n	Omit prompt line numbers
O	Use old behavior
o outputfile	Specifies file where statement output is directed
P password	Specifies password; prompted for if not specified
p	Display performance statistics
Q "query"	Executes a .SQL batch file and exits the ISQL session
q "query"	Executes a .SQL batch file
r [0|1]	Controls redirection of error-level messages
S servername	Specifies the server name; default is local
s colseparator	Set column separator; default is blank
t timeout	Command timeout in seconds; default is no timeout
U login_id	Case-sensitive SQL Server user account name
w columnwidth	Set column width; default is 80
?	Shows syntax

Table 1.2 lists the set of commands used after you enter ISQL. These commands must be used at the beginning of a command line (a 1> prompt).

Table 1.2—ISQL Commands

Command	Purpose
GO	Default command terminator; executes a statement
RESET	Clears statements before execution
ED	Invokes the default system editor
!! command	Executes a NT command
QUIT or EXIT()	Exits ISQL
Ctrl+C	Terminates a query without exiting ISQL
SHUTDOWN	Stops the SQL Server and exits ISQL

You can use the command-line recall feature of Windows NT (the [ua] key) to recall previous commands that you've entered within ISQL. Your ISQL commands are limited to a maximum of 1,000 characters per line.

Applications

The first of the applications allows you to enter Transact-SQL statements. The second, the SQL Client Configuration Utility, allows you to define the set of database and network library routines for database operations performed from a client system. The third, SQL Server Books Online, provides you with a complete set of SQL Server manuals organized for retrieval through the Books Online Browser. The final major tool is the Enterprise Manager. It's likely that you'll spend a fair amount of time in this utility as it provides access to the core administrative functions for SQL Server.

ISQL/w

After you start the ISQL/w application, you sign in to SQL Server by indicating your user name, password if necessary and the server you want to use. SQL Server maintains its own list of users who may connect to a server from a client system by using a valid login ID and user name.

For more information on implementing and managing SQL Server security, see Chapter 19, "SQL Server Security"

Figure 1.3 shows a select statement used to retrieve all rows from the system table sysdatabases, which contains a list of all defined databases. The tabular list of columns of information kept for the databases is displayed on the Results screen.

Fig. 1.3 - SQL statements are entered in the Query page of ISQL/w.

Your query output is displayed in a separate Results screen, which you can reach by clicking the Results tab. In addition, when you submit a query, ISQL/w automatically switches the current view to the Results tab. The Results tab shows information such as the rows returned by a query and error messages. You can use the scroll bars to view the entire query output. For example, figure 1.4 shows the tables that are automatically created when SQL Server is installed.

Fig. 1.4 - You can use ISQL/w from both the client and server systems.

The SQL Client Configuration Utility

Fig. 1.5 - Establish the client configuration by selecting the appropriate tab.

You should use the latest version of the network and database libraries on your system. If you have a mixed version environment, you'll have to consider either upgrading all servers to the most-recent server software, or attempting to use previous-versions drivers with the newer servers. While this will work in most cases, you might experience some problems using new features.

The Net-Library default is set to Named Pipes when you install the SQL Server client application tools on a client system. Named pipes is the default communication mechanism used for communication between the client application and the SQL Server system. You can choose a different network library to use an alternate communication mechanism from named pipes. For example, as figure 1.6 shows, you can choose alternate mechanisms for the TCP/IP, IPX/SPX, and Banyan VINES transport protocols.

Fig. 1.6 - Several communication mechanisms are available.

The SQL Client Configuration Utility is helpful because a client may need to connect to more than one server. By installing all different protocols needed, the client workstation will have access to the routines located in network and database libraries.

Using SQL Server Books Online

Fig. 1.7 - You can print or copy pages or complete sections of the Books Online information.

SQL Service Manager

Fig. 1.8 - If you pause the MSSQLServer service, no additional client or server connections are permitted.

A service on Windows NT such as the MSSQLServr and SQLExecutive of SQL Server are system processes that run in the background within NT. These background processes are used by SQL Server and client systems that require their functions.

The traffic light metaphor simplifies the starting, stopping, and pausing of SQL Server. Double-click Stop or the red light to stop SQL Server; double-click Pause or the yellow light symbol to temporarily pause SQL Server if the server has been started.

You can minimize the SQL Service Manager and still observe the traffic lights to determine whether server service is stopped (red light) or started (green light). The icon that represents the service manager will still show the traffic light.

SQL Enterprise Manager

• Manage user-account and server logins

• Back up and restore databases and transaction logs

• Start, stop, and configure servers

• Check database consistency

• Display server statistics

• Set up and mange database replication

• Create and manage database objects and tasks

• Create and control user accounts and groups

• Control the access control lists

You also may find that it's convenient to perform queries by using Transact-SQL commands from within SQL Enterprise Manager. Click the SQL Query Tool toolbar button of SQL Enterprise Manager's main window (see fig. 1.9) to bring up a window through which you can issue ISQL statements. Unlike ISQL/w, you won't have to connect and log in because you're already connected when you started SQL Enterprise Manager.

Fig. 1.9 - In Enterprise Manager, you can issue SQL statements directly by using the Query Analyzer button.

Although you also can perform all the administrative operations for SQL Server through ISQL, SQL Enterprise Manager allows you to perform the operations with pull-down menus and dialog boxes rather than a command line. In figure 1.10, the grouping of server objects under folders have been expanded to display several different types of server entities.

Fig. 1.10 - Server objects are displayed in a hierarchical fashion in the Server Manager window.

SQL Performance Monitor

Figure 1.11 shows a chart view of several important SQL counters that you can use to monitor the performance of SQL Server on your system. The integration of the SQL Server objects, counters—such as the cache hit ratio—and user connections enables you to select and display SQL Server statistics with NT objects and counters.

Fig. 1.11 - The Performance Monitor is used to display statistics on the performance of both the system and SQL Server.

In addition to the chart display in figure 1.11, you also can dynamically switch to display the counter information as a vertical bar graph rather than a chart. You can also record object counters in a log for later display, or you can display the information in the form of a tabular report.

The Performance Monitor also allows you to set threshold values for SQL Server counter values. When the threshold value is reached, an alert is displayed within an Alert view of the Performance Monitor. A message can also be sent to you about the alert even if you're working on a client workstation.

For more information on performance monitoring techniques, see Chapter 17, "Optimizing Performance"

SQL Security Manager

• Standard security. Standard security requires you to log in to SQL Server using a user name and ID.

• Integrated security. Integrated security requires you to log in only to Windows NT. You don't need to log in a second time when you access SQL Server. You'll still be prompted to sign in, but your user name in SQL Server will be taken from your network login ID.

• Mixed security. Mixed security allows you to log in to SQL Server or use the integrated login of Windows NT. Integrated logins can be used only with connections to the server from clients using named pipes.

Figure 1.12 shows the main window of the SQL Security Manager. The Security Mode in the lower left corner is standard for the Windows NT domain PACKBELL. Login access through named pipes has been granted to users who are members of the SQL Server group Administrators.

Fig. 1.12 - Integrated security provides the simplest account management in SQL Server.

The SQL Security Manager graphically maps Windows NT groups and users to SQL Server accounts and passwords. You can also use the SQL Security Manager to find SQL Server access permissions for mapped accounts.

From Here...

Be sure to also review Appendix A, "Installation and Setup of the Client and Server Software," so you understand what will be installed and where it will be located on your system.

For more information about selected topics, see the following Chapters:

• Chapter 10, "Managing and Using Indexes and Keys" shows you how to create and use keys and indexes.

• Chapter 11, "Managing and Using Rules and Defaults" shows how to create and use rules and defaults.

• Chapter 14, "Creating and Managing Triggers" shows how to create and use triggers to maintain referential integrity in the database.

• Chapter 15,"Creating and Using Cursors" shows you how to use cursors in a programming language to manipulate rows of a database table one at a time.

Table of Contents

Special Edition Using Microsoft SQL Server 6.5 - Introduction

02 - Data Modeling and Database Design