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
*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.
Microsoft SQL Server is the prime choice of many corporations for their relational database management system (RDBMS). This is largely due to SQL Servers 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.
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-SQLs many tasks can be divided into two categories:
History of Microsoft SQL Server
Microsoft and Sybase worked together in porting Sybases RDBMS to OS/2. In the late 1980s and early 1990s, Sybases 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 Servers 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 7.0 is Microsofts 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.
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
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.
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.
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 allow administrators to easily administer and configure the different components of SQL Server. It includes administrative tools such as:
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 Servers 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.
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 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 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.
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 is one of SQL Servers 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.
The data storage structures are the main resources in storing SQL Server data. The storage structures consists of devices, databases, extents, and pages.
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.
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
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.
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 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.
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
Figure 1: Create a New Data Source to SQL Server Wizard
Figure 2: Setting the Connection in the Create a New Data Source to SQL Server box
Figure 3: Last screen in Create a New Data Source to SQL Server
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.
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
Exam Watch: You cant 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 vendors database. ODBC Data Source must be installed on clients 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 NetWares 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
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