## MS BackOffice Unleashed

- Distributed Client-Server Computing
- Features
  - SQL Server Databases
  - SQL Server Database Objects
    - Tables
    - Datatypes
    - Rules and Check Constraint
    - Defaults
    - Stored Procedures
    - Extended Stored Procedures
    - Views
    - Triggers
    - Indexes
    - Cursors
    - Concurrency and Locking
    - Query Optimizer
    - SQL Mail
    - Distributed Transactions
    - Replication
    - Network Protocols
    - SQL Server and the Internet
- Architecture
  - SQL Server Database Engine
  - SQL Distributed Management Framework
  - SQL Server Application Programming Interface
  - Tools and Utility Programs
- SQL Server Tools and Utilities
  - SQL Enterprise Manager
  - SQL Performance Monitor
  - SQL Security Manager
  - ISQL/w
  - SQL Client Configuration Utility
  - SQL Service Manager
  - SQL Setup
  - Command-Line Utilities
- Summary

#
— 28 —

##
SQL Server 6.5 Overview

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.

###
Distributed Client-Server Computing

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.

###
Features

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:

- Data Definition Language (DDL), required to create database objects
- Data Manipulation Language (DML), required to modify (insert, delete, update) data
- Data Control Language (DCL), required to control security access on various database objects

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.

####
SQL Server Databases

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 controls the SQL Server operations. It checks for user access to SQL Server and holds critical information regarding user accounts, database storage allocations, SQL Server configurable parameters, system stored procedures, and so forth. The master database is created at the time of SQL Server installation.
- The model database provides a template for a user database. It contains all the system tables required by a user database. System tables are tables used to store information about an individual database. For example, system table sysobjects keeps information about all the user tables of a database. Names of most of the system tables begin with sys. When a new user database is created, all the tables are copied from the model database. The model database is created as a part of SQL Server installation.
- The tempdb database provides a scratchpad area to hold temporary objects. tempdb is a shared area used by all the users of SQL Server to create temporary objects and hold intermediate data results. This database is also used by SQL Server to create internal worktables and for data sorting for its own use (for example, worktables created by the GROUP BY and ORDER BY clauses). tempdb is created as a part of SQL Server installation, but can be extended in size by the database administrator, depending on the nature of the applications running on SQL Server.
- The msdb database is used by the scheduler component (SQL Executive) of SQL Server. SQL Server provides the facility to schedule tasks. The various types of tasks that can be scheduled are command file execution (cmd or exe files, or operating system commands), T-SQL statements (including stored procedures), execution, and data replication-related tasks. For example, certain routine tasks such as database backups can be scheduled through SQL Executive. All task schedules, alert notifications, event handling information, and history of the task scheduled is stored in the msdb database. This database is created as a part of SQL Server installation.
- The distribution database is required to provide replication functionality in SQL Server. This database is not created as a part of default SQL Server installation. This database is created when you want to provide replication functionality. The database name does not have to be "distribution." The database administrator can choose a different name. The distribution database receives data from the publishing server and holds the data until it is moved to the subscribing servers. This database is used as a placeholder for all transactions that are waiting to be replicated on the destination databases.
- The pubs database is a demo database created when SQL Server is installed. This database is not required for the working of SQL Server, but is provided mainly as a sample database for educational purposes.
- User-defined databases, consisting of a number of tables holding important information, are created to support development, testing, or production environments for business applications.

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

####
SQL Server Database Objects

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.

#####
Tables

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

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:

- Server-supplied datatypes. SQL Server provides a comprehensive set of datatypes, including int, char, varchar, money, float, datetime, bit, and text.
- User-defined datatypes. SQL Server enables users to create their own datatypes, which are based on SQL server-supplied datatypes. These datatypes can be used in place of system 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 Constraint

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

    )

#####
Defaults

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.

#####
Stored Procedures

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:

- Improved performance. When a T-SQL query reaches SQL Server, it is parsed and compiled before it can be executed. Because stored procedures are pre-parsed, the optimizer does not have to parse it again before executing it.
- Reduced network traffic. Because only the name of the stored procedure is sent across the network instead of an entire batch of SQL statements, there is less traffic on the network.
- Efficient application design. Because stored procedures can accept parameters and can be called from other stored procedures, it is possible to write common application logic and business rules using stored procedures.
- Enhanced security. By granting execute permissions on stored procedures and not on the underlying tables, the users do not need any direct access to underlying database tables. Data manipulation on the tables can be done entirely by 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.

#####
Extended Stored Procedures

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

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

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:

- The UPDATE trigger gets activated as a result of an update statement on the table. A trigger can be written to get activated unconditionally or only when specific columns get updated.
- The DELETE trigger gets activated as a result of a delete statement on the table.
- The INSERT trigger gets activated as a result of an insert statement on the 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.

#####
Indexes

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:

- Columns that are often specified in the order by clause should be indexed so that SQL Server makes use of the indexed order. This eliminates an additional step of sorting the data after retrieval.
- Columns that are often searched for a range of values are good candidates for a clustered index. Once the first value in the range is located, subsequent values are guaranteed to be physically adjacent rows.
- Columns that are frequently used in table joins should be indexed, because SQL Server can locate rows faster if the columns are in sorted order.
- It does not make much sense to create an index on a column that has a low distribution value. For example, creating an index on a column named sex is not going to bring any benefits, because there are only two possible values: male and female.

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.

#####
Cursors

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:

1. Declare a cursor.
2. Open the cursor.
3. Fetch data using the cursor.
4. Modify data through the cursor, if necessary.
5. Close the cursor.
6. Deallocate the cursor.

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.

#####
Concurrency and Locking

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:

- Shared locks are used for operations, such as a SELECT statement, that are read-only in nature. If a shared lock is applied on a page, another transaction can also acquire a shared lock on the same page. Once the read operation is finished on the page, SQL Server releases the lock from that page. SQL Server also provides a feature called HOLDLOCK in which locks are not released after the read operation is finished, but are held for the entire duration of the transaction.
- Exclusive locks are acquired by SQL Server for data manipulation statements such as delete, insert, and update. If such a transaction is in progress, no other transaction can acquire any other kind of lock. Lock on a particular page is released after the transaction is complete.
- Update locks are acquired by a transaction with an intent to modify a page that is currently locked by another transaction. This lock escalates to an exclusive lock once the page becomes available, and the lock exists for the duration of the transaction.

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.

#####
Query Optimizer

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:

1. SQL Server parses the query to check for any syntax errors and existence of objects defined in the query. It then converts these object names to internal integer ids. This process is also called normalization of a query.
2. The query optimizer analyses various access methods for each table in the query, determines the cost of each, and finally decides on the one that is least expensive in terms of the response time.

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:

1. Key distribution values of indexes involved in the join. SQL Server keeps the distribution of key values in the sysindexes system catalog table.
2. Estimated number of physical I/Os from the disk and logical I/Os from the cache to access data.
3. Number of records in each table. If possible, the optimizer makes an attempt to avoid multiple scans on large tables.
4. Search criteria defined in the WHERE clause of the query. This determines which indexes will be used to access the data.
5. Whether a query includes any aggregate functions (such as MIN or MAX). If there is an index on such tables, data can be retrieved directly from index pages instead of data pages, resulting in fewer page scans.
6. Optimizer hints in the FROM clause. Sometimes it is possible for an optimizer to select a query plan that is not very efficient. In such cases, an application developer can pass hints to the query optimizer to use specific indexes defined in the FROM clause of a query.

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.

#####
SQL Mail

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.

#####
Distributed Transactions

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

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.

#####
Network Protocols

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.

#####
SQL Server and the Internet

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.

###
Architecture

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:

- SQL Server Database Engine
- SQL Distributed Management Framework
- SQL Server Application Programming Interface
- Tools and Utility programs

####
SQL Server Database Engine

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:

- Because SQL Server does not have to simulate any threads in the database engine, system overhead and complexity are significantly reduced.
- Because SQL Server executes each task on a separate thread, in case of a protection fault on one of the threads, the problematic user connection can be killed without affecting the SQL Server process as a whole, resulting in better stability and reliability of the system.
- As operating system threading performance improves (for example, when the operating system version upgrades to a higher version), SQL Server automatically gets the performance boost.

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.

####
SQL Distributed Management Framework

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.

####
SQL Server Application Programming Interface

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
- Open Database Connectivity (ODBC)
- Open Data Services (ODS)

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.

####
Tools and Utility Programs

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.

###
SQL Server Tools and Utilities

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

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:

- Register multiple SQL servers.
- Start, stop, and configure SQL Server.
- Start, stop, and configure SQL Mail.
- Start and stop SQL Executive, a scheduler for SQL Server.
- Set up and review tasks scheduled by SQL Executive.
- Set up alerts for various SQL Server events.
- Create and drop backup devices for SQL Server databases.
- Create and drop databases and modify database attributes on SQL Server.
- Back up and restore databases.
- Manage SQL Server user logins and security.
- Create, drop, and modify SQL Server objects.
- Transfer database objects and data from another server, which may or may not be SQL Server.
- Set up replication.

####
SQL Performance Monitor

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 Security Manager

SQL Server provides three types of security modes:

- Standard Security, in which SQL Server handles and validates user connections directly from the clients, bypassing Windows NT security. Users that only want to access SQL Server on a machine are not required to have an account on Windows NT.
- Integrated Security allows SQL Server user logins to be integrated with Windows NT security. A user can maintain one login ID for both Windows NT and SQL Server. This security method allows SQL Server to make use of Windows NT security features and create a more secured environment.
- Mixed Security allows login ids to be validated either by standard or integrated security modes.

The SQL security manager tool is used to manage login accounts for SQL Servers that are using Windows NT Integrated Security.

####
ISQL/w

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

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

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

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.

####
Command-Line Utilities

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.

###
Summary

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.