SQL Server is a high-performance relational database system that is tightly integrated with the Windows NT operating system. This arrangement allows SQL Server to take advantage of the features provided by the Windows NT operating system. SQL Server is an excellent choice for meeting the challenging needs of today's complex client/server systems.
SQL Server's integration with the Windows NT operating system provides the following important features:
SMP allows SQL Server to increase performance through the use of additional processors. SQL Server uses a single process and multiple threads in conjunction with NT's scheduler so that Windows NT can balance work loads across multiple processors. All this occurs without user interaction; it also relieves administrators from the complexities of managing multiple processors.
SQL Server can run on different hardware platforms because Windows NT is a portable operating system. Currently, Windows NT supports the Intel platform, Digital's Alpha architecture, various RISC machines, PowerPC, and other hardware platforms.
The Windows NT operating system supports several different types of network protocols. This level of support extends to the client-side connectivity of SQL Server. This allows you to choose the network protocol that best fits your present and future needs. TCP/IP, IPX/SPX, named-pipes, and Banyan Vines are currently supported.
Windows NT provides crash protection, memory management, preemptive scheduling, and remote management. These types of features enable you to keep SQL Server up and running 24 hours a day, 7 days a week.
SQL Server is designed to take advantage of the NT operating system. This means that several common NT components provide additional functionality to SQL Server.
SQL Server (MSSQLServer), SQL Executive (SQLExecutive), and Distributed Transaction Coordinator ( DTC) are defined as services in the NT control panel (see Figure 4.1). You use the control panel to start, stop, and monitor the status of SQL Server, SQL Executive, and Distributed Transaction Coordinator.
Figure 4.1.
Integration with the Windows NT control panel.
NOTE: Version 4.2x of SQL Server uses different service names: SQLServer and SQLMonitor. In version 6.x, SQLServer became MSSQLServer and SQLMonitor was replaced by SQLExecutive.
The Event Viewer allows administrators to view and track information pertaining to SQL Server (see Figure 4.2). SQL Server logs the following types of messages to the Event Viewer: information, errors, and warnings.
Figure 4.2.
The Event Viewer.
TIP: In the Event Viewer, you can control the size of the event log. To control its size, select the Log Settings option from the Log menu. This action opens the Event Log Settings dialog box. From this dialog box, you can specify a maximum log size and the overwrite behavior. From within SQL Server, you can write your own messages to the event log. Use the extended stored procedure xp_logevent, as in the following example:xp_logevent error_number, message, [severity]
Windows NT configuration information is stored in a database called the Registry. To view and edit the Registry, run REGEDT32.EXE (see Figure 4.3). Normally, the Registry is automatically maintained by your software. You should change information in the Registry only when absolutely necessary. Otherwise, you may inadver-tently introduce errors into your software and operating system.
Figure 4.3.
The Registry.
Following is the registry key to SQL Server 6.x:
HKEY_LOCAL_MACHINE
\SOFTWARE
\Microsoft
\MSSQLServer
NOTE: If you want to view registry information about SQL Server 6.x, be sure to look at the MSSQLServer section and not the SQLServer section. SQLServer is version 4.x.
Through integrated security, SQL Server can use Windows NT user accounts and passwords (see Figure 4.4). This means that a single user account can be used to control access to NT and SQL Server. This significantly reduces account maintenance, eliminates duplication, and simplifies login procedures.
For more information on user accounts, see Chapter 10, "Managing Users."
Figure 4.4.
NT user accounts.
The Performance Monitor provides graphical statistics about the performance of SQL Server and Windows NT (see Figure 4.5). For more information about using the Performance Monitor, see Chapter 18, "Monitoring SQL Server." You also can define alerts in the Performance Monitor. Alerts enable you to track and monitor the frequency of an event (see Figure 4.6).
Figure 4.5.
Performance Monitor.
Figure 4.6.
Alerts in the Performance Monitor.
A primary goal of SQL Server for NT was to provide administrators with easy-to-use graphical administration tools. The ease with which someone can administer SQL Server for NT is a testimony to the success of Microsoft's development efforts. This is quite a different philosophy from SQL Server's competitors: the majority of their products are not intuitive and are command-line based.
The following tools allow you to easily set up, administer, and interact with SQL Server.
Through SQL Setup, you can perform the following functions (see Figure 4.7):
Figure 4.7.
SQL Server Setup.
From the SQL Service Manager, you can start, stop, and pause SQL Server, SQL Executive, and Distributed Transaction Coordinator (see Figure 4.8).
Figure 4.8.
The SQL Service Manager.
TIP: What is the purpose of the yellow stop light in the SQL Service Manager? This a commonly asked question. The reason it exists is to pause the server. By pausing the server, you can prevent users from logging in to SQL Server while still keeping it up and running. This feature is useful when you want to halt users from making new connections but allow existing connections to continue processing; it also allows users a chance to log off normally before bringing down the database.
NOTE: SQL Server can also be started as a service through the control panel or from the command line by using the command NET START MSSQLSERVER.
ISQL/w is the Windows-based version of ISQL (see Figure 4.9). Generally, it is used by developers and end-users who need to execute SQL statements. It does not provide graphical administration (use the Enterprise Manager for graphical administration).
Figure 4.9.
ISQL/w.
Using ISQL/w, you can perform the following functions:
The SQL Security Manager allows you to graphically manage how Windows NT users interact with SQL Server (see Figure 4.10).
NOTE: You must be running integrated or mixed security to take advantage of SQL Security Manager. There is no benefit from the Security Manager if you are using standard security.
Figure 4.10.
The SQL Security Manager.
From the Security Manager, you can perform the following functions:
As an administrator, you will probably spend the majority of your time interacting with SQL Server through the Enterprise Manager. This is where you can administer multiple database servers through a single interface (see Figure 4.11).
Figure 4.11.
SQL Enterprise Manager.
Using the Enterprise Manager, you can perform the following functions:
From the SQL Client Configuration utility, you can perform the following functions (see Figure 4.12):
Figure 4.12.
The SQL Client Configuration utility.
SQL Trace is new with SQL Server 6.5; it is a graphical tool that displays Transact-SQL activity for a selected server (see Figure 4.13). SQL Trace can be used by an administrator or developer to probe user activity and to generate audit trails. The output from SQL Trace can be saved as a script or as an activity log. See Chapter 28, "New SQL Server Utilities," for more information on SQL Trace.
Figure 4.13.
SQL Trace.
MS Query is new with SQL Server 6.5; it is Microsoft's graphical query tool (see Figure 4.14). Common operations such as viewing data, modifying data, and viewing table definitions can be performed through MS Query. In addition to being shipped with SQL Server, this tool can be found in Microsoft Office.
Figure 4.14.
MS Query.
A DBA will probably spend the majority of his or her time using the visual administration tools included with SQL Server. However, there are two non-visual administration tools that a DBA may also use: BCP and ISQL.
BCP stands for Bulk Copy Program. It is a command-line utility that enables you to import and export data to and from SQL Server. The advantage of BCP is that it is fast. Users new to SQL Server are often amazed at how fast it operates. The drawback of BCP is that it can be difficult to use.
By default, BCP.EXE is installed in the mssql\binn directory. For more information on BCP, see Chapter 16, "Importing and Exporting Data."
Following is the syntax for BCP:
bcp [[database_name.]owner.]table_name {in | out} datafile
[/m maxerrors] [/f formatfile] [/e errfile]
[/F firstrow] [/L lastrow] [/b batchsize]
[/n] [/c] [/E]
[/t field_term] [/r row_term]
[/i inputfile] [/o outputfile]
/U login_id [/P password] [/S servername] [/v] [/a packet_size]
NOTE: BCP switches are case sensitive.
ISQL is a command-line utility used for executing queries and administering SQL Server. With the advent of graphical administration tools for SQL Server, the importance of ISQL has diminished. Most people prefer to perform day-to-day administration tasks from the Enterprise Manager instead of ISQL.
Its minimal overhead, however, makes ISQL useful for processing noninteractive routines such as nightly batch jobs.
By default, ISQL.EXE is installed in the mssql\binn directory.
Following is the syntax for ISQL:
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]
[-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]
NOTE: ISQL switches are case sensitive.
SQL Server uses the term object to describe a database component. Common database objects include tables, rules, defaults, user-defined datatypes, views, triggers, and stored procedures.
NOTE: Do not be misled by the term object. SQL Server is not an object-oriented database.
A table is used to store data. It is organized in a row/column manner (see Fig- ure 4.15). You can retrieve, modify, and remove data from a table by using the SQL language.
Figure 4.15.
An example of a table.
A rule is used to enforce a data constraint (see Figure 4.16). Rules are column specific and cannot perform table lookups. Generally, rules are used to enforce simple business constraints.
Figure 4.16.
An example of a rule.
NOTE: In SQL Server 6.x, an alternative to creating a rule is using the CHECK constraint. Another alternative to creating a rule is using a trigger.
Defaults are used to populate a column with a default value when a value is not supplied (see Figure 4.17).
Figure 4.17.
An example of defaults.
NOTE: In SQL Server 6.x, an alternative to creating a default is to use the DEFAULT constraint.
With a user-defined datatype, you can create a custom reusable datatype based on an existing SQL Server datatype (see Figure 4.18). By using user-defined datatypes, you can ensure datatype consistency.
Figure 4.18.
An example of a user-defined datatype.
A view is a virtual table that looks and feels like a real table. Views limit the amount of data a user can see and modify. Views may be used to control user access to data and to simplify data presentation (see Figure 4.19).
A trigger is a user-defined collection of Transact SQL commands that are automatically executed when an INSERT, DELETE, or UPDATE is executed against a table (see Fig-ure 4.20). Triggers are flexible and powerful, which makes them useful for enforcing business rules, referential integrity, and data integrity. Triggers can be column, row, or table specific.
Figure 4.19.
An example of a view.
Figure 4.20.
An example of a trigger.
NOTE: Before version 6.x, SQL Server triggers had to be used to enforce referential integrity. New with version 6.x is the capability to create declarative referential integrity by using the FOREIGN KEY, PRIMARY KEY, and REFERENCE statements. Triggers must still be used to cascade table changes.
A stored procedure is a compiled SQL program (see Figure 4.21). Within a stored procedure, you can embed conditional logic (if/else logic), declare variables, pass parameters, and perform other programming tasks.
Figure 4.21.
An example of a stored procedure.
SQL Server delivers performance and ease of use by tightly integrating SQL Server
and Windows NT. The next chapter discusses how to prepare for the installation of
SQL Server.
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.