Microsoft SQL Server 6.5 is a highly scalable and high-performance relational database management system. SQL Server 6.5 is the latest release from Microsoft and replaces Microsoft SQL Server 6.0 in BackOffice. SQL Server can manage a large amount of
data in a multiuser distributed client-server environment. It offers a high degree of data availability, concurrency, and integrity while delivering high performance.
Most of the applications developed in the past were host-based, where mainframes had all the processing power. All aspects of an application (application logic, business rules, data integrity, security, and so forth) were implemented on the central host
to which terminals were connected. These terminals did not have any processing power and were used for display purposes only
There are advantages to the host-based computing, the most important being that all aspects of the computing environment are centralized. Because all components of the application exist on the central host, it is simpler to implement security and
provide maintenance. The major disadvantage of host-based computing is the high cost of operation and maintenance.
Faster networks and inexpensive personal computers have made it possible to develop applications that provide a flexible and adaptable development environment resulting in tremendous cost savings. Client-server is a networked computing environment where
a client submits a request over the network that is processed by the server. The processing power is distributed between the client and the server. The client side of application focuses more on the user interface and application logic. Typically, clients
are not responsible for doing any data manipulation. The server, on the other hand, can handle application logic, perform data manipulation, and enforce data integrity, business rules, and security. Unlike terminals in host-based computing, clients in a
client-server environment have processing power and memory. Therefore, some of the business rules can be implemented on the client so that the server resources can be utilized more efficiently.
It is important to balance the load between the client and the server so that resources on both the sides are used optimally. Application and database design have become an important aspect of client-server computing. For example, a poorly designed application can create a network bottleneck by retrieving hundreds of rows from the database, even though only a few rows are necessary for the front-end application.
SQL Server is designed to understand Structured Query Language (SQL), which was developed by IBM for relational databases. Most of the relational database vendors provide SQL as a query language to perform data manipulation in the database. The American
National Standard Institution (ANSI) has set standards for SQL. Various DBMS vendors comply with either ANSI 89 or ANSI 92 standards of SQL. The SQL Server query language called T-SQL (Transact-SQL) is an enhanced version of ANSI SQL and complies with ANSI
92 standards. The following are three components of ANSI SQL:
T-SQL fulfills all the ANSI requirements listed. T-SQL is a very powerful language and provides many 3-GL-like extensions (such as BEGIN-END and IF-ELSE WHILE constructs) to ANSI SQL. SQL Server processes T-SQL commands sent by the clients and, if
required, sends the result back to them.
A database is a collection of tables containing related information. This information can be user data as well as data required by the DBMS to perform its regular functions. A SQL Server consists of multiple databases. Some of the databases are user
databases and some are system databases required by SQL Server to perform its own operation. SQL Server can hold upto 32,767 databases.
Server consists of the following databases:
The master database is critical to the operation of SQL Server. A disk failure may result in a damaged or corrupt master database on a server, rendering all the other databases useless on that server. Therefore, it is quite important to have an up-to-date backup copy of the master database to deal with such disasters.
A database is a collection of tables holding related information. A table consists of columns containing rows of data. There are other objects in a database besides tables. These objects don’t hold any data, but they provide assistance in
implementing business rules, data and referential integrity, business logic, and so forth. Examples of some of these objects are stored procedures, triggers, rules, and defaults. All these objects occupy space in a database and are referred to by a name.
Even though all the objects are referred to by a name, SQL Server assigns a unique integer ID to all the objects, and internally uses these ids to access these objects.
A table consists of rows and columns. A table is created by using the CREATE TABLE T-SQL construct. Each column in a table is required to have a name and a corresponding datatype and, optionally, other attributes. The following example creates a table
called employee:
create table employee ( empid int, emp_lname char(55), emp_mname char(2) NULL, emp_fname char(20), salary money )
SQL Server enables the use of predefined system datatypes or user-defined datatypes that can be attached to a column property. Each column of the table may have other optional attributes associated with it. In the previous example, column emp_mname has
an attribute NULL attached to it signifying that if a record is inserted without specifying a middle name, the employee’s middle name is stored as NULL (unknown value).
A NULL value does not mean spaces or no value. A NULL value means that the value is unknown. In the previous example, each record having a NULL value in the emp_mname column does not signifiy that an employee does not have a middle name; it means that the middle name is unknown or not available.
Datatypes are essential for defining a column property of a table. A datatype specifies the characteristics of a column in terms of size and storage. Datatypes are also used for stored procedure parameters and T-SQL local variables. SQL Server provides
two kinds of datatypes:
User-defined datatypes bring consistency among the columns that are used across various tables in a database. User-defined datatypes are created by using the sp_addtype system stored procedure. In the following example, a user-defined datatype udd_empid
is created and bound to the column empid of the employee table:
sp_addtype udd_empid , " int " create table employee ( empid udd_empid , emp_lname char(55), emp_mname char(2) NULL, emp_fname char(20) , salary money )
A user-defined datatype should be created before it can be attached to a column of a table.
Rules and check constraints are used as a method of enforcing a set of data values on a particular column of a table or a user-defined datatype. A rule is a SQL Server database object that is created and attached to a column of a table or a user-defined
datatype. Rules can be used to validate a specific value or a range of values entered for a column. A rule on a column of a table is activated when data is inserted or updated into a table. SQL Server raises an error if the value entered in the column
violates the rule defined on it. A rule can be bound to a column or a user-defined datatype by using the sp_bindrule system stored procedure. In the following example, rul_salary rule is created and bound to the column empid to ensure that empid is always
a positive number but less than or equal to 100000:
create rule rul_empid as @empid between 1 and 10000 Exec sp_bindrule rul_empid, "employee.empid"
A rule definition can be bound to many columns in various tables in a database.
An alternate method of enforcing a business rule is to use a check constraint when creating a table. A check constraint is similar to a rule, except that it is defined on a column during a CREATE TABLE statement. A check constraint can coexist with a
rule; however, it does not make much sense to use both rules and check constraint to enforce a business rule. The following example shows how to create a check constraint on a table:
create table employee ( empid int constraint gt_zero check (empid between 1 and 100000 ), emp_lname char(55), emp_mname char(2) NULL, emp_fname char(20) , salary money )
Default is a method of inserting a specific value into a column when no value is explicitly specified as a part of an INSERT statement. There are two methods of creating defaults in SQL Server. The first method is to use a CREATE DEFAULT clause followed
by executing the sp_bindefault stored procedure, which binds the default to the column of a table. The second method is to create a default on a column directly by using a DEFAULT clause in a CREATE TABLE statement. The following two examples illustrate
both methods of creating defaults:
create default mname_def as "M" exec sp_bindefault mname_def , "employee.mname" create table employee ( empid int, emp_lname char(55), emp_mname char(2) constraint mname_def DEFAULT "M", emp_fname char(20) , salary money )
A default can also be bound to a user-defined datatype.
Be very careful when using default values for a column of a table if there is also a rule defined on it. Default values should not violate the rule definition. For example, if a rule on the salary column of the employee table allows values between $50000 and $150000 and the default value for the salary column is $25000, an INSERT statement to the employee table with no salary value causes SQL Server to raise an error, because the default on the salary column violates the rule.
One of the most powerful features of SQL Server is stored procedures. A stored procedure is a collection of SQL statements that is pre-parsed and stored on the SQL Server. Each stored procedure is given a name and is referenced by that name. A query
contained in the definition of a stored procedure can be executed by sending an EXECUTE (or EXEC) command followed by the name of the stored procedure to SQL Server. A stored procedure can be called from another stored procedure. Stored procedures can
accept parameter values and return parameter values to the calling procedure along with the return status indicating success or failure of the execution. There are number of advantages to using stored procedures:
SQL Server also comes with a number of system stored procedures. System stored procedures are created in the master database at the time of SQL Server installation. A number of stored procedures are provided to display useful information about database
objects. Some of the system procedures are provided for the system administrator (sa) to perform routine administrative tasks, such as adding/dropping users, creating/dropping database backup devices, and so forth. Most of these stored procedures have
names beginning with sp . The sp_helpdb system stored procedure, for example, displays information about a specific or all databases on a SQL Server.
SQL Server also provides the capability of performing actions outside the SQL Server environment by executing extended stored procedures. Extended stored procedures help application developers perform various tasks that typically fall under the realm of
an operating system. Extended stored procedures are functions stored in a dynamic link library (DLL) loaded as a part of the SQL Server process. In the following example, a file is copied from one directory into another by using an extended stored
procedure called xp_cmdshell:
Exec master..xp_cmdshell "copy c:\sourcedir\filename c:\destinationdir\filename"
All the extended stored procedures have names beginning with xp_. In addition to all the extended stored procedures provided as a part of SQL Server, application developers can create their own extended stored procedures by using Microsoft Open Data
Services (ODS). Open Data Services are discussed later in this chapter under the heading Architecture.
Views in SQL Server are like virtual tables. They provide a snapshot of data from one or more underlying database tables. To an end-user, a view looks like a regular table. SQL Server cannot restrict access to only certain rows of a table (that is, a
user can either select all the rows or no rows at all from a table). By using views, you restrict direct access to the database tables by providing a horizontal and vertical snapshot of the data from underlying tables. Views help simplify data
representation and provide logical data independence. Because SQL Server enables permissions to be granted on the view and not the underlying tables, the end-users need not know about the underlying data structures from which a view is created. Views can
also be used as security mechanisms for data. Data modification to a table can also be done through a view. SQL Server places a few restrictions, however, on doing data modifications on underlying tables through views.
Triggers are database objects created to maintain referential integrity of the database. A trigger is like a stored procedure except that a trigger gets fired automatically whenever data modification takes place on the table on which a trigger is
created. SQL Server allows three kinds of triggers that can be attached to a table:
Triggers are also used to implement complex business rules. Triggers are sometimes used to record certain events. For example, an update trigger on an employee table can record the user ID of the person performing updates on the salary column. Because
triggers are directly attached to the table, they cannot be executed manually or called from a stored procedure.
SQL Server also provides declarative referential integrity (DRI) using CREATE TABLE and ALTER TABLE T-SQL statements. DRI complies with ANSI standards.
In general, it is a good idea to use declarative referential integrity using CREATE TABLE and ALTER TABLE clauses, because such queries are portable across various DBMS. However, some complex tasks such as CASCADE DELETES, determination of before and after values of records in a DELETE statement, cannot be done using DRI. Such tasks can be done easily within a trigger.
SQL Server provides indexes for faster access to the data and to enforce uniqueness of the data in the database tables. Creating an index on a table improves query search performance. SQL server provides two types of indexes: clustered index and
nonclustered index. Both these indexes are B-Tree structures and require separate storage space in the database. You can create indexes on tables either by using the create index statement or by using the UNIQUE or PRIMARY KEY integrity constraints
of the create table clause of T-SQL. In a clustered index, the data in the table is physically sorted based on the columns that constitute a key. In a nonclustered index, data is inserted at the end of the table, and there are indexed pointers to each row
of the table. SQL Server enables only one clustered index per table and up to 249 nonclustered indexes. Because data is physically sorted in case of a clustered index, searches on a clustered index are almost always faster than on a nonclustered one.
Although indexes speed up search operations, inserts, deletes, and updates may be slow, because each of these operations may result in modifying the B-Tree index structure for the table. In general, the following guidelines should help you decide whether
to use an index:
Indexes are mostly transparent to the users. Query optimizer decides whether to use an index on a table for a particular query. If there is more than one index on a table, query optimizer chooses the index that is most effective in terms of number of
I/Os. However, SQL Server allows an application developer to pass hints to the query optimizer to use a specific index in a query.
SQL Server cursors enable row-by-row operation on a given result set from a SELECT statement. A cursor is similar to a file pointer to a particular record in a file consisting of numerous records. Cursors enable users to traverse the query results one
row at a time. They are useful in situations where each row of the result set needs to be examined individually. In order to make use of the cursor functionality of SQL Server, the following steps are required in T-SQL:
SQL Server cursor declaration is like a SELECT statement with a declaration of a cursor by name. By examining the declaration structure, SQL Server compiles the SQL query. Opening the cursor results in the execution of SQL contained in the cursor
declaration, and the cursor pointer is positioned on the first row of the result set. The data fetch stage involves retrieving data from the result set one row at a time. With each data fetch, the cursor pointer moves down to the next row of the result
set. Fetches are allowed until the cursor pointer reaches the end of the result set. Data modification is possible through cursors, although SQL Server places a few restrictions on doing so (for example, an UPDATE statement on a table that uses the WHERE
CURRENT OF T-SQL construct can modify the row on which the cursor is currently positioned). Once the user finishes using the result set, the cursor is closed. SQL Server then releases all the resources required for the cursor operation, except for the
compiled query plan. A user can reopen a cursor after closing it without having to redeclare the cursor. If a cursor definition is not required anymore, the cursor can be deallocated. SQL Server then removes the query plan for the cursor and releases the
data structures associated with the cursor.
SQL Server offers rich cursor functionality. Cursors can be created and used from within stored procedures, triggers, and batch SQL scripts. Using SQL Server cursor functionality, it is possible to directly access the first and last row, fetching
forward and backward using absolute or relative cursor positions in the result set.
In a multiuser environment, it is important to prevent transactions from interfering with each other while preserving data integrity. SQL Server 6.5 uses various kinds of locks depending on the type of transaction. SQL Server allocates space in 2K
pages. Default locking granularity of SQL Server is a page. SQL Server uses the following three types of locking schemes:
Page level locking can become a bottleneck in situations where multiple users are trying to insert records on the same page. To overcome such performance issues, SQL Server 6.5 now has a new feature called Insert row-level locking (IRL). IRL can be
enabled for some or all the tables of a database by using the sp_tableoption stored procedure, enabling users to lock a specific row during insert, instead of the whole page.
Microsoft SQL Server comes with an intelligent cost-based query optimizer, which determines the most efficient path to the data in various tables involved in a join, instead of a predefined rule. A cost-based optimizer does not depend on the way a query
is stated (order of tables in the FROM clause of a query, for example). The following is the sequence of events that take place once a query reaches SQL Server:
A significant factor in deciding cost is the number of physical I/Os that are required to get the desired results. It is always the intent of an optimizer to reduce the number of physical I/Os for a query. The following are some of the parameters SQL
Server analyzes before making a query plan:
By analyzing these parameters, SQL Server quickly builds several plans for query execution and chooses the one that it thinks is the most efficient. Query plans for triggers and stored procedures are created when they are executed for the first time.
These plans stay in the SQL Server cache and are reused upon subsequent execution of the stored procedures and triggers. However query plans are not sharable; that is, if two users invoke the same stored procedure simultaneously, two query plans
will be created—one for each user. SQL Server also provides a number of SQL commands and trace calls to analyze query behavior.
When a stored procedure (or a trigger) is compiled, the text of the stored procedure is stored in the syscomments system catalog of the database in which the procedure is created. The parsed and compiled query tree is stored in the sysprocedures system table.
Microsoft SQL Server comes with built-in extended stored procedures that perform mail-related functions. SQL Mail features enable users to send queries through the mail that can be executed by SQL Server. SQL Server, in turn, can send the results back
through the mail. SQL Mail should be started before you can use the mail features in SQL Server. SQL Mail can be started and stopped by using the xp_startmail and xp_stopmail extended stored procedures, respectively. SQL Server also provides a graphical
user interface to configure, start, and stop SQL Mail. Mail is sent using the xp_sendmail extended stored procedure. This stored procedure can be used to send ordinary messages, query results, blind copy, mail attachments, and so forth. SQL mail can be
used to send alerts or to notify users after the completion of a scheduled task. The following example sends the output of the system stored procedure sp_helpdb on the Internet to a user called netuser:
exec master..xp_sendmail @recipients = 'netuser1@noname.com', @query = ‘sp_helpdb master', @copy_recipients = 'netuser2@noname.com', @subject = 'master database information'
It is possible to send or read mail from within triggers and stored procedures. SQL mail is compatible with Microsoft Exchange Mail or any other MAPI provider. For SQL Server to work with Exchange Mail, it is necessary to set up a domain user account
for exchange. This account is used by SQL Server.
In a distributed client-server environment, sometimes it is necessary to run transactions across multiple servers. It is important to maintain the integrity and consistency of the data on each server; that is, if a transaction fails on one of the
servers, the entire transaction should be rolled back. SQL Server provides a two-phase commit protocol that preserves data consistency across several SQL Servers. A two-phase commit protocol represents the tight consistency model of SQL Server, where it is
ensured that a transaction across servers is treated as a single unit of work. Two-phase commit is implemented programatically. Microsoft DB Library toolkit provides a number of C library calls to implement two-phase commit. Distributed transactions can
also be achieved by using the Microsoft Distributed Transaction Coordinator (MSDTC), which also uses a two-phase commit protocol and runs as a service under Windows NT. This service must be started by using the SQL Enterprise Manager utility from the SQL
Server 6.5 program group. By using the SQL Enterprise Manager graphical tool, it is possible to configure MSDTC and review and control the state of each transaction participating in a distributed transactional environment.
Replication is data duplication from one database to one or more databases on the same or different servers. SQL Server replication is based on the loose consistency model. This means that there is a certain amount of time lag before data gets
replicated from source to destination. Data can be replicated either on a scheduled basis or on a number-of-transaction basis. Both these parameters can be configured by the database administrator at the time of setting up replication. The source database,
which sends the data, is called publisher, and the destination database, which requests the data, is called subscriber. Each table that is participating in replication is called an article. A collection of articles is known as a
publication. It is not necessary to publish all the rows and all the columns of a table (article). SQL Server replication enables horizontal and vertical partitioning of the data from an article—that is, you can publish selected columns and
selected number of rows from a table. All the publishing databases on a server use a distribution database, which acts like a placeholder for all the publications that need to be published. Once the data reaches the subscriber, it may be deleted from the
distribution database. The distribution database can reside on the same server as the publication database or on a remote server. SQL Server replication comes with a graphical user interface to set up and manage replication among various servers. SQL
Server databases can also participate in two-way replication, in which the database that is publishing the data can also subscribe to data from various servers. Version 6.5 is also capable of providing replication to ODBC subscribers other than SQL Server,
such as Oracle.
SQL Server is designed to handle communication from clients using various network protocols. SQL Server includes several Net Libraries that are used to communicate with the clients. At the time of SQL Server installation, you can specify the network
protocols SQL Server should listen on. Currently Multi-protocol, NWLink IPX/SPX, TCP/IP sockets, Banyan VINES, Apple Talk ADSP, DECnet, and Named Pipes are supported. By default, SQL Server listens only on the named pipes protocol. The default pipe name
for SQL Server is \\pipe\sql\query. TCP/IP sockets and Named Pipes are the two most popular protocols used by SQL Server. UNIX clients can access SQL Server by using TCP/IP sockets.
With the growing popularity of the Internet, many organizations have recognized the benefits of extending the enterprise to the Internet and letting people use existing business applications over the Internet without sacrificing reliability and
compromising on security. SQL Server allows users to access database over the Internet. For an application to access SQL Server, both the client and the server must be directly on the Internet. Applications can connect to SQL Server either by using the
absolute IP address of the server or through Domain Name Service (DNS). To access SQL Server through the Internet, the only currently supported protocol is TCP/IP.
Using Microsoft SQL Server it is possible to publish data in the database in the form of a standard Hypertext Markup Language (HTML) file. SQL Server comes with a graphical user interface called SQL Server WEB Assistant, which can be used to publish
data on the Web server. These HTML files can also be created by using SQL Server supplied stored procedures. These files can be viewed by any Internet browser, such as Netscape or Microsoft Internet Explorer. SQL Server allows creation of HTML files from
within triggers. It is possible, therefore, to update these files dynamically whenever the data changes. HTML files can also be updated on a scheduled basis by using SQL Server scheduler.
SQL Server is an advanced and highly scalable relational database management system. SQL Server software makes available a suite of products that provide a foundation for implementing a complete business solution in a client-server environment. These
products include the SQL Server database engine, a number of tools, and a set of application programming interfaces (APIs) to support development environment. The SQL Server product family is designed with a layered approach in mind. Each layer provides a
programmable functionality to extend SQL Server capabilities and also to develop client applications. Each layer builds on top of another and makes use of the services provided by the inner layer. This, in turn, provides services to the outer layer through
a set of function calls. The following are the various layers of SQL Server product family:
The SQL Server database engine is the innermost layer providing all DBMS-related functions. It is designed to work in a distributed client-server environment. SQL Server runs as a service on Windows NT. Unlike many other relational databases that either
simulate thread code in the database engine or use shared memory for user connections, SQL Server is integrated closely with Windows NT to make use of native operating system threads. Use of Windows NT threads results in better performance and stability of
SQL Server. The following are some of the benefits of using native threads:
SQL Server supports Symmetric Multiprocessing (SMP). An SMP hardware is a server that has more than one CPU, and each CPU can handle a load (I/Os, interrupts, and so forth) independent of other CPUs. SMP support enables SQL Server to
run multiple tasks, simultaneously resulting in excellent load balancing. SQL Server is designed in such a way that applications and end-users see a single database process, leaving the number of processors completely transparent to the end-user.
Sitting on top of the SQL Server layer is the SQL Server management layer. This layer is also known as SQL Distributed Management Framework (SQL-DMF). SQL-DMF provides a set of services and objects through which it is possible to access the SQL Server
database engine. Using the components of SQL-DMF, it is possible to develop management tools that are specific to the requirements of an enterprise. Because SQL-DMF is a programmable interface, it is possible to write sophisticated database management
tools. For example, by using SQL-DMF object interfaces, an automated database backup tool can be written that will allow consistency checks on a database on a flexible schedule, perform database backups, raise alerts (mail and/or page) in case of failures,
and send backup reports to relevant people.
This layer provides a set of application programming interfaces (APIs) access to SQL Server database engine, and consists of the following components:
Open Client DB-Library is a set of C API calls that are used in writing client applications. These libraries include routines that send T-SQL requests to the server and process the results of the queries. Using these libraries, it is possible to write
customized applications with comprehensive error handling features. An example of a DB-Library application is ISQL/w, which is supplied with SQL Server software.
ODBC is also an API used in developing client applications. The main difference between ODBC and Open Client DB-Library is that applications written using DB-Library calls are specifically written for SQL Server, whereas the applications using ODBC are
written to be independent of the database running at the backend.
Microsoft ODS provides a set of C API calls for server-based applications. An example of these applications is extended stored procedures. These applications are added to SQL Server to enhance the functionality of SQL Server. Typically, these
applications are written to provide non-SQL-related features and to create gateways between SQL Server and non-SQL-Server-based applications. Applications written using ODS can accept connections from Open Client DB-Library and ODBC clients.
The outermost layer is the application layer. All the third-party software and tools that interact with the SQL Server database engine are written using Open Client DB-Library or ODBC APIs. Examples of such applications are MS Query, MS Access, ISQL/w,
and so forth.
The SQL Server product comes with a number of graphical tools and command-line utilities. These tools and utilities are installed on the server machine at the time of SQL Server installation. These tools and utilities help in performing various tasks.
These tasks may range from something very simple, such as adding a user to a database, to more complex tasks, such as setting up replication. Some of the graphical tools available with SQL Server product family are described in the following sections.
SQL Enterprise Manager is possibly the most important tool for the database administrator. This tool enables enterprise-wide management of SQL Servers. SQL enterprise manager is a 32-bit application; therefore, it can run either on Windows NT or Windows
95 clients. Here are some of the major functionalities provided by SQL Enterprise Manager:
SQL Performance Monitor integrates SQL Server with Windows NT Performance Monitor. SQL Server performance counters can then be collected by running this tool or the Windows NT Performance Monitor. This tool provides valuable performance statistics
regarding a number of SQL Server performance counters, such as processor time, cache hit ratio, I/O batch size, disk transfer/sec, I/O lazy writes/sec, and so forth. Performance Monitor objects are discussed in Chapter 4,
"Monitoring Environment," under the section Understanding SQL Server Performance Counters.
SQL Server provides three types of security modes:
The SQL security manager tool is used to manage login accounts for SQL Servers that are using Windows NT Integrated Security.
ISQL is a graphical query tool that enables a user to send queries to SQL Server. From a single ISQL session, a user is enabled to open multiple connections to one or more SQL Servers. Some of the capabilities of ISQL include reading queries from and
writing queries to an operating system ASCII file, saving query results, formatting query results, and analyzing a query plan by using the showplan and statistics io options.
SQL Client Configuration Utility enables a client to set the default net-library. Network libraries are DLLs that perform network operations (such as passing network packets) between the client and SQL Server. A client uses a specific Net-Library to
connect to SQL Server depending upon the network protocol. For example, to connect to a SQL Server from a client using Named Pipes network protocol, DBNMPNTW.DLL network library is required. If one is using TCP/IP network protocol, the network library
required to connect to SQL Server is DBMSSOCN.DLL. For a client to connect to SQL Server using a specific Net-Library, SQL Server must be configured to listen on that protocol. This tool also displays the current version number and timestamp of the
Net-Library being used on the server or the client. The Client Configuration Utility also helps in creating multiple alias names for a particular SQL Server.
SQL Service Manager is used to stop, pause, and start SQL Server, SQL Executive, and MSDTC services. These functions can also be performed using SQL Enterprise Manager.
SQL Setup is provided to reconfigure SQL Server. Database administrators use this tool to change network support options, install new languages, upgrade or remove SQL Server, and set SQL Server options. Some of these options include defining the
location of the SQL Server errorlog, location of the master database device, whether to start SQL Server and Executive at machine boot time, defining a profile name for Microsoft Exchange, and so forth.
SQL Server Web Assistant
SQL Server Web Assistant is a graphical tool that enables publication of SQL Server data from SQL queries or stored procedures into HTML files. These files can be placed on a Web server and be browsed using a standard browser. Using this tool, it is
possible to define a frequency at which these Web pages can be updated.
SQL Trace
SQL TRACE is an administrative tool that provides an online display of all or specific SQL statements going to a specific SQL Server. It also captures connects and disconnects to a SQL Server. This tool enables a database administrator to create filters
based on login name, application name, and host name. Output from SQL trace can be viewed on the screen online or can be saved to a file. Multiple filters can be created against a single server and can be active at the same time. Filters can be paused,
stopped, deleted, and saved for future references. SQL Trace is discussed in Chapter 4 under the topic Using SQL Trace.
BCP stands for bulk-copy program. This utility is used to transfer data between SQL Server and an operating system file. The file format (for example field terminator, record terminator, and so forth) can be specified by the user. The most frequent use
of BCP is to import data from another data source. BCP can be used to import data into a SQL Server database from a file created using an Excel spreadsheet. BCP can also be used to export data. BCP can create an operating system file from a SQL Server
database table, for example. Other products (such as a spreadsheet program or another DBMS import utility) can then import the data from the file created by BCP. This utility provides a number of optional parameters that can be passed during data import or
export. These options include number of rows to transfer, starting record number, last record number, network packet size, and maximum number of errors before BCP aborts. The SQL Server BCP utility also enables users to export data from a VIEW, which is a
very helpful feature when one wants to export selective data from various tables. A user has to be a valid user of the database in order to import/export data from a database.
ISQL is a command-line interface to send queries to SQL Server. It is also possible to send a batch of queries using ISQL. This utility provides many optional parameters that can be passed on the command line. These include name of input file containing
SQL commands, name of output file where query results will be redirected, print statistics, column formatting, and so forth. A valid database login ID is required for a user to use this utility.
MAKEPIPE AND READPIPE utilities are provided with SQL Server to test network connectivity using Named Pipe protocol. Typically, the MAKEPIPE utility is run on the server where it opens a pipe and waits for a client to connect to it. The READPIPE utility
is run on a client to make sure that it is able to communicate on the pipe connection created by MAKEPIPE.
Earlier versions of Microsoft SQL Server were primarily used by the businesses to support departmental-level applications. With SQL Server 6.5—which includes features such as built-in replication, SQL Mail, Distributed Transaction Coordinator, versatile and easy-to-use administrative tools, and extremely rich T-SQL query language—Microsoft offers a low-cost and high-performance database engine capable of handling mission-critical applications at the enterprise level. In the next chapter, you examine the step-by-step procedure for installing SQL Server 6.5 on Windows NT.
