Table of Contents

Chapter 1

Microsoft SQL Server Overview

Certification Objectives *

SQL Server Background *

History of the SQL Language *

History of Microsoft SQL Server *

Database Object *

Description *

SQL Server Architecture *

From The Classroom *

SQL versus Access *

Client/Server Architecture *

Database Architecture *

Administration Architecture *

Server Architecture *

Application Architecture *

Cursors *

Non-logged Operations *

Joins *

Devices and Databases *

Page *

Extent *

Certification Objectives

With the increased complexity of each new version of SQL Server, corporations are requiring their database administrators to also be knowledgeable developers. Administrators not only have to be in charge of backing up the database and managing the security of users, they also must assist in deploying database clients and applications. The responsibilities of the database administrator are growing every day. Because of this growth, it is very important that they have knowledge of the elements, architecture, and resources of the database. This chapter will discuss the components and various designs of Microsoft SQL Server. This overview will help you become familiar with SQL Server.

SQL Server Background

Microsoft SQL Server is the prime choice of many corporations for their relational database management system (RDBMS). This is largely due to SQL Server’s tight integration with Windows NT, and its user-friendly interface. The new release of SQL Server 7.0 further reduces the cost of ownership and administrative overhead. In addition to this, many database administrators are anxious to work with it. Before we start, it is necessary to become familiar with the background of SQL Server as well as its language.

History of the SQL Language

In the 1970s, IBM developed the original structured query language (SQL), SEQUEL2, for its prototype RDBMS. This is why SQL is pronounced "SEQUEL." In the 1980s, the language, simply known as SQL, evolved into two standards: the American National Institute (ANSI) and the International Standards Organization (ISO). Microsoft SQL Server 7.0 uses Transact-SQL, which is based on the ANSI-92 standard.

Though SEQUEL2 was designed for data retrieval, Transact-SQL (or T-SQL) has matured to allow both data access and administration performance. T-SQL’s many tasks can be divided into two categories:

History of Microsoft SQL Server

Microsoft and Sybase worked together in porting Sybase’s RDBMS to OS/2. In the late 1980s and early 1990s, Sybase’s RDBMS was one of the most powerful and popular database systems. The new system, now called SQL Server, was a great success. It had a lot of functionality and power that was previously available in database management systems that were found in Unix and NetWare environments.

When Microsoft released Windows NT, it started to develop SQL Server to tightly integrate with this new operating system. As the relationship between Sybase and Microsoft became strained, they formally announced the end of their partnership prior to the release of version 6.0. Microsoft assumed all developmental control of SQL Server 6.0 for NT. Sybase went on to develop their own version of SQL Server for Windows NT.

Elements of a Microsoft SQL Server Database

A database, simply defined as a collection of data, is the core of Microsoft SQL Server. The first step in learning about the insides of SQL Server 7.0 is to familiarize yourself with SQL Server’s many database elements. Table 1-1 lists the main components of the SQL Server database.

Database Object Description
Table A table is a collection of data that is stored in multiple rows and columns.
Default Defaults are used to specify a value when you do not specify a value for a specific field when inserting data.
Columns Columns are objects that store individual elements within a row of data.
Row A row is a single item of data within a table that contains the complete set of columns.
Datatype Datatypes are objects that specify what type of data can be stored in the column. The various datatypes are TinyInt, SmallInt, Int, Real, Float, Smalldatatime, Datetime, Smallmoney, Money, and Char.
Index An index is a database object that can speed up queries by looking up the data by key value instead of having to scan the entire table. SQL Server 7.0 supports clustered and non-clustered indexes. Clustered indexes are indexes which sort the rows in order using a B-Tree. Non-clustered indexes do not sort rows.
Triggers Triggers are a favorite among programmers. They are stored procedures that run when data in the table is modified. such as with an update, delete, or insert. Triggers are often used to ensure consistency among linked tables.
Rule Rules are database objects that are bound to a column, or a user-defined type to restrain the values. For example, a rule can be made to make sure that the zip code in a record contains only numbers.
Stored Procedures A stored procedure is a set of pre-compiled Transact SQL statements that execute as an object. The benefits of using stored procedures is that they are pre-compiled, which translates into faster execution times. Security is much more easily set because permissions can be set for the object only.
View A view resembles a table that shows the data in a table. This type of table retrieves the data with queries, and can contain more than one table. Before the user is able to use the view, they must have both permission on the view and all dependent objects.

Table 1: Elements of a Microsoft SQL Server Database

Exam Watch: One of the most important uses for a trigger is to enforce the data integrity between one or more tables.

SQL Server Architecture

SQL Server 7.0 is Microsoft’s new release of its relational database management system (RDBMS). All the components must work together to make SQL Server an effective RDBMDS. The architecture defines how the many components work together to enable SQL Server to function.

From The Classroom

SQL versus Access

People are often confused regarding the differences between SQL Server and Microsoft Access. The main distinction is that MS Access is an application development tool and a workgroup database, while SQL Server is just a database. The following table should help further explain the differences.

  SQL Server MS Access
Application Development Tool No Yes
Workgroup Database (1-30 people) Yes Yes
Enterprise Database (hundreds or thousands of users) Yes No
Triggers and Stored Procedures Yes No
Part of Microsoft Back Office Yes No
Part of Microsoft Office No Yes (Some Versions)

David Smith, MCSE + Internet

Client/Server Architecture

Microsoft SQL Server 7.0 is designed with a robust client/server architecture. With a client/server architecture, the application is shared between the servers and the client computers. A user who wishes to access the data accesses it through client applications. The database sends only the necessary information needed. In SQL Server, most calculations are done on the server end, and only the needed data is forwarded to the clients. Since the server is where most of the storage and calculating is done, hardware upgrade costs are set mostly for the server.

One of the benefits of the client/server architecture is that tasks are distributed across tiers. This encourages maximization of all your hardware. By having one or more tiers, all the computers running contribute to the entire SQL Server model. There are two types of client/server architecture: two-tier and multi-tier.

In the two-tier client/server architecture, the client shares some of the processing. This is because the business logic runs on the client application. The business logic includes the business and security rules of Microsoft SQL Server, such as logins. In the multi-tier client/server architecture, the business logic is on a separate computer, which further distributes the tasks of the client application.

Database Architecture

Though users see a database as one logical passive entity, the database actually exists as two or more files. In those files, the operation of SQL Server 7.0 depends on the four system databases included with the system. They consist of the master database, model database, tempdb database, and the msdb database.

The master database is used to control much of the operations and system level information of SQL Server. It does this by storing system information in the thirteen system tables, called system catalog. The system catalog stores user account information including the following: security, ID, passwords, databases stored on the server, environment variables, system error messages, and system stored procedures.

The tempdb database is used as a temporary storage area for work tables and temporary stored procedures. SQL Server 7.0 supports two types of temporary tables: the global temporary tables and the local temporary tables. The global temporary tables, prefaced by ##, are available to all clients. The local temporary tables, prefaced by #, are only available to the clients that created them. Use of tempdb is very fast, because activities are not logged, therefore increasing response times.

The model database is used as a template for all new databases created on a system. When new databases are created, the contents of the model database are copied over. The model database can be customized to create a new default database structure for new databases. New databases must be at least the same size as the model database in order for all data in them to be copied over.

SQL Server Agent uses the msdb database for the following tasks: scheduling, replication, system operator information, and recording operators. The data is stored in the system tables within the msdb tables. The ten system tables are as follows: sysalerts, sysbackupdetail, sysbackuphistory, syshistory, sysnotifications, sysoperators, sysrestoredetail, sysrestorehistory, sysservermessages, and systasks.

Administration Architecture

SQL Server 7.0 includes many new tools and features that will simplify and speed up its routine tasks. A new feature included in SQL Server 7.0 is dynamic self-management. This feature frees up an administrator from constantly monitoring the server to see the amount of resources the current sessions are taking up. SQL Server can automatically shrink or increase the size of the database as rows are added or deleted. Memory is constantly being dynamically allocated depending on the workload of the server.

Administrative Tools

Administrative tools allow administrators to easily administer and configure the different components of SQL Server. It includes administrative tools such as:

Server Architecture

The client sends queries through many components before SQL Server is able to process them. SQL Server uses the Tabular Data Streams (TDS) 4.2 and 7.0 protocol. SQL Server’s new TDS 7.0 supports new features. These include unicode support and support for char, varchar, binary, or varbinary values longer than 255 bytes. TDS packets are built by the Microsoft OLE DB Provider for SQL Server, the SQL Server ODBC driver, or the DB-Library DLL. These packets are then sent to the first layer, called Server Net-Libraries. Server Net-Libraries encapsulates TDS packets into network packets. They are then sent to the next layer, known as Open Data System (ODS). The ODS then routes the packets to the necessary function depending on the type of TDS packet it is (such as login packet or SQL query packet.) Returned data goes back in the reverse direction.

Application Architecture

SQL Server application uses both API and Transact SQL components to access data on the database. The API defines a way to code the program in order to access the objects, properties, and interfaces to access the database. This greatly speeds up application development, because many common tasks have been converted into functions. The developer spends less time because they can just use the functions instead of coding the entire task. SQL Server 7.0 supports many APIs including the following: Active X Data Objects (ADO), Object Linking and Embedding Database (OLE DB), Open Database Connectivity (ODBC), Remote Database Objects (RDO), Data Access Objects (DAO), ESQL, DB-Library for C, and DB-Library for Visual Basic.

Transact SQL (which is an SQL-92 standard) is a language which allows users to access the database and create and modify the data. The four main SQL statements are SELECT, UPDATE, DELETE, and INSERT.

Operation of a SQL Server Database

The main operation of the SQL Server database is to maintain the data and ensure data integrity. It is up to the administrator to tune the database to optimize the speed and efficiency of the queries, as well as maintaining the day-to-day operations of the database. SQL Server has many available operations that allow the administrator to specify data retrieval or set logging. These components are important in ensuring that data is correct. The main operations for SQL Server are cursors, nonlogged operations, and joins.

Cursors

Cursors are pointers to subsets within a result set. A result set is the set of data returned from a SELECT statement. The benefit of using cursors is increased speed and efficiency since manipulating data within a smaller subset is quicker than within the entire result set. There are four types of cursors:

Non-logged Operations

Non-logged operations are used to prevent the transaction log from filling up and using up disk space. If the transaction log ever needs to be applied, the non-logged operations will not be re-applied. If you must use non-logged operations, it is a good idea to use a differential backup on your database. A differential database will record the changes made to your database since the last full backup. Another thing to consider is that more important database logs, while leaving the less important data unlogged.

Joins

A join is a query that allows you to retrieve data from multiple tables based on logical relationships. Joins are often used to retrieve data from one table to select rows in another table in one operation. There are three types of joins:

Resources in the Environment

SQL Server uses many resources during table creation. By understanding the resources available, you will be able to make more efficient use of the SQL Server resources. The main resources are storage and memory.

Memory

Memory is one of SQL Server’s most valuable resources. Memory is used to lock, open databases, set up connections, and open database objects such as views, triggers, and constraints. With the new version of SQL Server, the RBDMS dynamically adjusts memory. It polls the system periodically to determine the optimum amount of memory that should be available. This saves the database administrator a lot of time in manually adjusting memory.

Storage

The data storage structures are the main resources in storing SQL Server data. The storage structures consists of devices, databases, extents, and pages.

Devices and Databases

Devices are pre-allocated storage space, stored in the form of a file or a partition. SQL Server stores device information in the Master database in the sysdevices system table. Devices are 1MB to 32GB in size. A database can also span multiple drives. One single SQL Server can store as much as 256 devices. Devices can hold one or more databases. The database is an information store created in a device. Information on the database is stored within the Master database in the sysdatabase system table. Databases range from 1 MB to 1 TB in size.

Page

The smallest unit of storage in SQL Server is the page. The page is 8K and there are six types of pages. The six types of pages are shown in Table 1-2:

Page Type Includes
Data Data rows
Text/Image Data on text, ntext, and image
Global Allocation Map Information of allocated extents
Page Free Space Information on free space on pages
Index Index information
Index Allocation Map Extent allocation in tables or indexes

Table 2: Different Types of Pages

Extent

An extent, which is 64K and consists of eight contiguous pages, is the smallest unit of space allocated to indexes and tables. As data in a table or index grows beyond the capacity of the original allocated units, more extents are allocated. To allocate space more efficiently, SQL Server allocates two types of extents: uniform and mixed.

SQL Server first allocates a mixed extent to a new table or index. A mixed extent is owned by numerous objects, with a different object being able to own one or more pages. This is much more efficient for a smaller table or index since many objects can share the extent, therefore enabling it to utilize more of the space. As the table or index gets larger, SQL Server will convert its allocation to a uniform extent. A uniform extent is an extent owned by only one object.

Data Sources

Microsoft SQL Server 7.0 now includes native support for the ODBC and OLE DB APIs. Unlike previous version of SQL Server, access to data sources no longer requires any translation to any other APIs. This greatly reduces overhead and increases efficiency.

ODBC

Microsoft conceived Open Database Connectivity as an open standard to allow universal heterogeneous database access. ODBC is a database application programming interface (API) that allows universal heterogeneous database access. ODBC is an open standard with X/Open and ISO compliance. ODBC does this by standardizing an interface so the type of database will be invisible to the database application developer. One of the most important things that came with the standardized interface is the common SQL grammar. All clients use the same SQL statements to manipulate the data in the database regardless of whether it is DB2, Oracle 8, or Microsoft SQL Server. Clients use SQL to access the data, while the data manager translates the language to native calls to the database. ODBC applications require the ODBC data sources to connect to their database. The ODBC data source stores the ODBC drivers and information for connection. In order for the ODBC data source to be available for the client, you must install the ODBC data source on the client and register it with a unique DSN (data source name). With the use of ODBC, developers only need to access the database via ODBC and not worry about the kind of database it is. The driver manager automatically converts all universal commands to native commands. With ODBC, there is no longer a need to know the native API for each database.

OLE DB

OLE DB data source is much different than the ODBC data source. The OLE DB data source is the set of initialization interfaces required for an OLE DB application to connect to an OLE DB data store. The OLE DB interface is based on the COM (Component Object Model). Unlike ODBC, which uses the driver manager to load drivers and translate the ODBC calls to native calls, OLE DB eliminates the need for this component. Without the bulky driver manager, OLE DB accesses the data by means of COM wrappers, which speed up database access and maximizes efficiency.

Adding Data Sources

In order for users to access ODBC data sources, you must first install it on their computers. Exercise 1-1 shows you how to add an ODBC data source in Windows NT.

Exercise 1- 1:Adding an ODBC Data Source in Windows NT

  1. Click on Start | Settings | Control Panel.
  2. Double-click on ODBC.
  3. Click the System DSN and click ADD.
  4. Click SQL Server, then click Finish.
  5. You will now see the Create a New Data Source to SQL Server wizard, as shown in Figure 1-1.
  6. Figure 1: Create a New Data Source to SQL Server Wizard

  7. Under Name: type in Pub. Under "Which SQL Server do you want to connect to?", select {local}. Click Next.
  8. The next screen should show a screen similar to Figure 1-2. Under How Should SQL Server verify the authenticity of the login ID?, choose With SQL Server authentication using a login ID and password entered by the user. Click on the Client Configuration button and choose the network library you use to connect to SQL Server. The default is named pipes. Click Next.
  9. Figure 2: Setting the Connection in the Create a New Data Source to SQL Server box

  10. Click on the Next in the next screen. You should see a screen similar to Figure 1-3.
  11. Figure 3: Last screen in Create a New Data Source to SQL Server

  12. Click Finish.
  13. The next screen will prompt you whether or not you want to Test your connection. Click Test Data Source. If the connection is successful, you should see the same screen as Figure 1-4.

Figure 4: SQL Server ODBC Data Source Test

OLE DB is based on COM. Because of this, in order for you to access the OLE DB data source you need the following: the name of the OLE DB source, information on locating the source, login ids, and the name of the exposed object or query. The name of the object is used to expose a rowset. The query is sent to the OLE DB data source to expose a rowset.

Deleting ODBC Data Sources

When you are sure you no longer need access to the ODBC data source, you can delete it easily. Exercise 1-2 shows you how to delete an ODBC data source.

Exercise 1-2: Deleting an ODBC Data Source

  1. Click on Start | Settings | Control Panel.
  2. Double-click ODBC.
  3. Click on the type of data source Pub is in, which is System DSN.
  4. Click on Pub.
  5. Click Remove, then click Yes.

Exam Watch: You can’t delete an OLE DB data source because OLE DB is based on COM and not actually given access to the data source via drivers.

Clients

In a client/server environment, the clients run the application on their computer to access the server. SQL Server allows different clients to connect to it simultaneously. Table 1-3 lists the SQL Server 7.0 compatible clients.

Clients Description
OLE DB Proprietary interface based on COM that enables applications to access multiple data sources through interfaces without need for the bulky driver manager. Must use Microsoft SQL Server OLE DB Provider (SQLOLEDB) or OLE DB Provider for ODBC to connect client to OLE DB data source.
ODBC Open standard type to allow universal heterogeneous database access. This is much better for the developer because they only need to know SQL, instead of knowing native calls to each vendor’s database. ODBC Data Source must be installed on client’s local computer.
TCP/IP You can connect to SQL Server through TCP/IP by setting up a server alias for SQL Server and listening on the Windows Sockets Net-Library.
Banyan VINES In order to connect to SQL Server via Banyan VINES, you must use an Windows NT required Banyan VINES client software version 5.56 or later. You must also set up a server alias for SQL Server and listen on the Windows Sockets Net-Library.
Apple Talk Clients can connect to SQL Server by using the Apple Talk ADSP network protocol. You must set up a server alias for SQL Server and listen on the Apple-Talk net library.
IPX/SPX You can connect to SQL Server with Novell NetWare’s native protocol by using NW Link IPX/SPX Compatible Transport. You must set up an alias for the SQL Server and listen on the NWLink IPX/SPX Net-Library.
Named Pipes In order to access SQL Server over named pipes, you must be running Windows NT. Named pipe clients always listen to the default pipe. If you want to listen to a different pipe, the client must be configured.

Table 3: Different types of SQL Server clients

Exam Watch: Most clients require you to set up an alias for SQL Server.

Connecting to SQL Server over the Internet

A SQL Server client can connect to a SQL Server database by using one of the following: SQL Server Query Analyzer, ODBC, or DB-Library. In order for the server to be accessible via the Internet, it must be connected to the Internet, and you must use TCP/IP or Multi-protocol Net-Libraries with TCP/IP support. Exercise 1-3 will show you how to set encryption in Multi-protocol Net-Libraries so that access to SQL Server will be encrypted.

Exercise 1-3: Setting Up Multi-protocol encryption

  1. Set up an account with the same login and password on both SQL Server and Windows NT.
  2. Run Microsoft SQL Server 7.0 setup. On the Microsoft SQL Server-Network Libraries (shown in Figure 1-5), select Change Network Support and click Next.
  3. Select Multi-protocol.
  4. Select Enable Multi-protocol encryption.
  5. Click Finish. Click on Install Now.

Figure 5: Installing encryption on Microsoft SQL Server to enable access on the Internet

Certification Summary

In order for you as an SQL Server administrator to plan the use of SQL Server 7.0 in the corporate environment, you must comprehend the components, operation, resources, and clients of SQL Server 7.0.. Familiarity with every facet of this database system will enable you to make a better decision in the planning, deployment, and administration of SQL Server.

SQL Server is a Client/Server database. Most data processing is done on the server side, while most queries are done on the client side. This maximizes efficiency, since all information can be managed in one place and network resources are maximized. The clients are able to access the data through API and SQL. API is used to define the way the application must be coded in order to access SQL Server. SQL Server natively supports the ODBC and OLE DB API. SQL is a language developers and administrators use to manipulate the data in SQL Server.

This RBDMS consists of the following elements and operations: table, default, row, index, columns, datatypes, joins, and non-logged operations. Knowledge of how each element and operation works in maintaining the operations will help you manage your resources much more efficiently.

Two-Minute Drill