Chapter 10 - Administration Architecture

Each version of Microsoft SQL Server seeks to automate or eliminate some of the repetitive work done by database administrators in earlier versions of SQL Server. Because database administrators are typically among the people most highly trained in database issues at each site, this frees a valuable resource to spend more time working on database design and application data access issues.

The administration of SQL Server exhibits these characteristics:

• The SQL Server version 7.0 database server reduces administration work in many environments by being more dynamic in its use of resources than earlier versions of SQL Server. The server acquires system resources such as memory and disk space automatically when needed, and frees the resources when they are no longer required. While large OLTP systems with critical performance needs are still monitored by trained administrators, SQL Server 7.0 can also be used to implement smaller desktop or workgroup databases that do not require constant administrator attention. 

• SQL Server provides a set of graphical tools that allow administrators to perform administrative tasks easily and efficiently. 

• SQL Server provides a set of services that allow administrators to schedule the automatic execution of repetitive tasks. 

• SQL Server can program the server to handle exception conditions, or to at least send e-mail or pages to the on-duty administrator. 

• SQL Server publishes the same administration Application Programming Interfaces (APIs) that are used by the SQL Server utilities. These APIs support all of the administration tasks of SQL Server. This allows developers of applications that use SQL Server as their data store to completely shield users from the administration of SQL Server. 

DDL and Stored Procedures

Transact-SQL is the language used for all commands sent to Microsoft SQL Server from all applications and contains statements that support all administrative work done in SQL Server. These statements fall into two main categories:

Data Definition Language

The SQL language has two main divisions: Data Definition Language (DDL) that is used to define all the objects in an SQL database, and Data Manipulation Language (DML) that is used to select, insert, update, and delete data in the objects defined using DDL. The Transact-SQL DDL used to manage objects such as databases, tables, and views is based on SQL-92 DDL statements, with extensions. For each object class, there are usually CREATE, ALTER, and DROP statements, for example CREATE TABLE, ALTER TABLE, and DROP TABLE. Permissions are controlled using the SQL-92 GRANT and REVOKE statements and the Transact-SQL DENY statement.

System stored procedures

Administrative tasks not covered by the SQL-92 DDL are typically done using system stored procedures. These are stored procedures whose names start with sp_ or xp_ and are installed when SQL Server is installed. Examples of system stored procedures are:

• sp_addtype (defines a user-defined data type) 

• sp_configure (manages the server configuration option settings) 

• xp_sendmail (sends an e-mail or page) 

SQL Server also exposes the SQL-DMO, SQL-NS, DTS, and Replication Component APIs. These are all comprised of OLE Automation objects that encapsulate either DDL or system stored procedures. When an application calls one of the objects, the object actually translates the request to one or more Transact-SQL DDL or system stored procedure statements that are then sent to the server.

SQL Distributed Management Framework

The SQL Distributed Management Framework (SQL-DMF) is an integrated framework of objects, services, and components used to manage Microsoft SQL Server. SQL-DMF provides a flexible and scalable management framework that is adaptable to your needs. It lessens the need for user-attended maintenance tasks (such as database backup and alert notification) by providing services that interact directly with SQL Server.

The key components of SQL-DMF are core elements of SQL Server. SQL-DMF allows you to proactively manage the SQL Server installations on your network by allowing you to define:

• All SQL Server objects and their permissions. 

• Repetitive administrative actions to be taken at specified intervals or times. 

• Corrective actions to be taken when specific conditions are detected. The corrective actions can either be tasks defined to resolve the issue, or alerts by pages or e-mail to people who can resolve the issue. 

The illustration shows the main components of SQL-DMF.

 

SQL-DMF Applications

There are three main classes of applications that use SQL-DMF. These applications provide the interfaces for users managing Microsoft SQL Server.

SQL Server Enterprise Manager

Microsoft Management Console (MMC) provides a common environment for managing several types of server software on a Microsoft Windows network. Individual server applications, such as SQL Server, provide components called snap-ins that expose the management functions of the application to MMC users. SQL Server Enterprise Manager is the SQL Server MMC snap-in component. SQL Server Enterprise Manager supplies the primary interface for users who are administering copies of SQL Server on the network.

DCOM applications and Active Server Pages

The SQL-DMO objects can be incorporated as either remote or local objects in DCOM applications and Web applications, such as in Active Server Pages (ASP).

Applications and ISV tools

Applications, written either in-house or by independent software vendors (ISV), can use the SQL-DMF APIs to administer and configure SQL Server. This allows applications to shield the administration of SQL Server from their users if the application has chosen to embed SQL Server to store its data. ISVs who produce tools for managing server applications also use the SQL-DMF APIs to build features for managing SQL Server in their tools.

SQL-DMF APIs

The core functionality of SQL-DMF is exposed to applications by three APIs: SQL Namespace, SQL Distributed Management Objects, and Distributed Transformation Services. These APIs are implemented as sets of dual-interface COM interfaces.

SQL Namespace

The SQL Namespace (SQL-NS) API exposes the user interface (UI) elements of SQL Server Enterprise Manager. This allows applications to include SQL Server Enterprise Manager UI elements such as dialog boxes and wizards.

SQL Distributed Management Objects

The SQL Distributed Management Objects (SQL-DMO) API abstracts the use of DDL, system stored procedures, registry information, and operating system resources. SQL-DMO can be used to program all administration and configuration tasks in Microsoft SQL Server.

Distributed Transformation Services

The Distributed Transformation Services (DTS) API exposes the services provided by SQL Server to aid in building data warehouses and data marts. These services provide the ability to transfer and transform data between heterogeneous OLE DB and ODBC data sources. Data from objects or the result sets of queries can be transferred at regularly scheduled times or intervals, or on an ad hoc basis.

See Also 

In Other Volumes 

"Developing SQL-DMO Applications" in Microsoft SQL Server Distributed Management Objects 

"Programming DTS Applications" in Microsoft SQL Server Building Applications 

"Programming SQL-NS Applications" in Microsoft SQL Server Building Applications 

SQL Server Agent

SQL Server Agent runs on the server running Microsoft SQL Server. It is responsible for:

• Running SQL Server tasks scheduled to occur at specific times or intervals. 

• Detecting specific conditions for which administrators have defined an action, such as alerting someone through pages or e-mail, or a task that will address the conditions. 

• Running replication tasks defined by administrators. 

SQL Server Agent is like an auxiliary operator that is responsible for handling the repetitive tasks and exception handling conditions defined through the other SQL-DMF components.

See Also 

In This Volume 

SQL Namespace API

SQL-DMO API

SQLServerAgent Service

In Other Volumes 

"Data Transformation Services" in Microsoft SQL Server Distributed Data Operations and Replication 

Graphical Tools

Microsoft SQL Server includes many graphical utilities that allow users, programmers, and administrators to easily and quickly:

• Administer and configure the parts of SQL Server they control. 

• Determine the catalog information in a copy of SQL Server. 

• Design and test queries for retrieving data. 

In addition to these tools, SQL Server contains many wizards to walk administrators and programmers through the steps needed to perform more complex administrative tasks.

SQL Server Enterprise Manager

Microsoft Management Console (MMC) is a tool that presents a common interface for managing different server applications in a Microsoft Windows network. Server applications provide a component called an MMC snap-in that presents MMC users with a user interface for managing the server application. SQL Server Enterprise Manager is the Microsoft SQL Server MMC snap-in.

SQL Server Enterprise Manager is the primary administrative tool for SQL Server and provides an MMC-compliant user interface that allows users to:

• Define groups of servers running SQL Server. 

• Register individual servers in a group. 

• Configure all SQL Server options for each registered server. 

• Create and administer all SQL Server databases, objects, logins, users, and permissions in each registered server. 

• Define and execute all SQL Server administrative tasks on each registered server. 

• Design and test SQL statements, batches, and scripts interactively by invoking SQL Server Query Analyzer. 

• Invoke the various wizards defined for SQL Server. 

SQL Server Query Analyzer

SQL Server Query Analyzer is a graphical user interface for designing and testing Transact-SQL statements, batches, and scripts interactively. It can be called from SQL Server Enterprise Manager.

SQL Server Query Analyzer offers the following features:

• Free-form text editor for keying in Transact-SQL statements. 

• Color coding of Transact-SQL syntax to improve the readability of complex statements. 

• Results presented in either a grid or a free-form text window. 

• Graphical diagram of the showplan information showing the logical steps built into the execution plan of a Transact-SQL statement.

  This allows programmers to determine what specific part of a poorly-performing query is using a lot of resources. They can then explore changing the query in ways that minimize the resource usage while still returning the desired data. 

• Index Tuning Wizard to analyze a Transact-SQL statement and the tables it references to see if adding additional indexes will improve the performance of the query. 

See Also 

In Other Volumes 

"Analyzing a Query" in Microsoft SQL Server Diagnostics 

"Index Tuning Wizard" in Microsoft SQL Server Database Developer's Companion 

Performance Monitor

Windows NT Performance Monitor is a tool for monitoring resource usage on a computer running Microsoft Windows NT. Users can set up charts that present resource usage information in graphical form. Windows NT Performance Monitor has many different counters, each of which measures some resource on the computer.

Windows NT Performance Monitor is extensible so server applications can add their own performance counters. Microsoft SQL Server adds counters to Windows NT Performance Monitor to track items such as:

• SQL Server I/O. 

• SQL Server memory usage. 

• SQL Server user connections. 

• SQL Server locking. 

• Replication activity. 

See Also 

In Other Volumes 

"Monitoring with Windows NT Performance Monitor" in Microsoft SQL Server Administrator's Companion 

Import and Export Data

The Import and Export Data icon in the Microsoft SQL Server 7.0 program group launches the Data Transformation Services (DTS) Wizard. The wizard walks users through the DTS functions of importing, exporting, validating, and transforming data and objects between heterogeneous OLE DB and ODBC data sources.

See Also 

In Other Volumes 

"Data Transformation Services Import and Export Wizards" in Microsoft SQL Server Distributed Data Operations and Replication 

SQL Server Profiler

SQL Server Profiler is a tool that captures Microsoft SQL Server events from a server. The events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. SQL Server Profiler is used for activities such as:

• Stepping through problem queries to find the cause of the problem. 

• Finding and diagnosing slow-running queries. 

• Capturing the series of SQL statements that lead to a problem. The saved trace can then be used to replicate the problem on a test server where the problem can be diagnosed. 

• Monitoring the performance of SQL Server to tune workloads. 

See Also 

In Other Volumes 

"Monitoring with SQL Server Profiler" in Microsoft SQL Server Administrator's Companion 

SQL Server Service Manager

SQL Server Service Manager is used to start, stop, and pause the Microsoft SQL Server components on the server. These components run as services on Microsoft Windows NT, and as separate executable programs on Microsoft Windows 95/98:

MSSQLServer Service

Database server for SQL Server.

SQLServerAgent service

Agent that runs scheduled administrative tasks.

Microsoft Search service (Windows NT only)

Full-text search engine.

MSDTC service (Windows NT only)

Manager for distributed transactions.

SQL Server Service Manager is a taskbar application and follows the standard behavior of taskbar applications. When minimized, the SQL Server Service Manager icon displays in the area of the taskbar clock on the right of the taskbar. Right-click the taskbar item to get a menu including all the tasks SQL Server Service Manager supports.

To maximize SQL Server Service Manager, double-click the icon. When SQL Server Service Manager is maximized, clicking the close button of the SQL Server Service Manager window does not terminate the application; it only minimizes SQL Server Service Manager to the taskbar. To terminate SQL Server Service Manager, right-click the SQL Server Service Manager icon on the taskbar and select the File/Exit menu item.

See Also 

In Other Volumes 

"Starting, Pausing, and Stopping SQL Server" in Microsoft SQL Server Administrator's Companion 

Client Network Utility

The Client Network utility is used to manage the client Net-Libraries and define server alias names. It can also be used to set the default options used by DB-Library applications.

Most users will never need to use the Client Network utility. To connect to Microsoft SQL Server they will need to specify only the network name of the server on which SQL Server is running (if the SQL Server software was installed with the defaults on both the client and the server, this will usually be all that a user must specify).

In some cases, a SQL Server installation may be configured to listen on alternate network addresses and the client must explicitly specify the alternate address, or a client will want to set up an alias to specify in place of the server network name in the connection request. These actions are done using the Client Network utility.

See Also 

In This Volume 

Communication Components

In Other Volumes 

"Managing Clients" in Microsoft SQL Server Administrator's Companion 

Server Network Utility

The Server Network utility is used to manage the server Net-Libraries. It is used to specify the network protocol stacks on which the server will listen for client requests, and it can be used to specify that Microsoft SQL Server will listen on a nondefault network address.

Most administrators will never need to use the Server Network utility. During setup, they will specify the server Net-Libraries on which SQL Server will listen. If they take the default network addresses, clients can connect to SQL Server by just specifying the network name of the server on which SQL Server is running.

See Also 

In This Volume 

Communication Components

In Other Volumes 

"Network Connectivity Options" in Microsoft SQL Server Administrator's Companion 

Miscellaneous Utilities

These Control Panel utilities are also used to manage parts of Microsoft SQL Server:

• ODBC Administrator

  The ODBC Administrator utility is used to add, delete, and edit ODBC data sources for all ODBC drivers on the computer, including data sources for the SQL Server ODBC driver. It can also be used to list the versions of all the ODBC drivers installed on the computer. 

• Services (Windows NT only) 

  The Services application in Control Panel can be used to start, pause, and stop Microsoft Windows NT services, including the services managed by SQL Server Service Manager. 

SQL Server also installs several command prompt utilities that can be used when building .cmd files to work with SQL Server. For more information, see "Getting Started with Command Prompt Utilities" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Automated Administration Architecture

Microsoft SQL Server provides features that allow administrators to program the server to administer itself for many repetitive actions or exception conditions. This frees the administrators to spend more time on activities such as designing databases and advising programmers on efficient database access coding techniques. Applications from any vendor can choose SQL Server as their data storage component and minimize the administrative requirements of customers by automating administrative tasks.

These automation features are not limited to database administration tasks such as scheduling backups. They can also be used to help automate the business practices that the database supports. Applications can be scheduled to run at specific times or intervals. Specific conditions detected in the system can be used to trigger these applications if they need to be executed before the next scheduled time.

The features that support the automation of administrative tasks are:

• SQL Server Agent 

  SQL Server Agent is a separate executable program that executes administrative jobs and alerts defined by the system administrators. It runs as a service named SQLServerAgent on computers running Microsoft Windows NT, as an executable file on computers running Microsoft Windows 95/98. 

• Jobs 

  A job defines an administrative task. Each job has one or more steps; each step specifies a Transact-SQL statement, Windows command, executable program, replication agent, or Microsoft ActiveX script. Jobs can be run once, scheduled to run at periodic intervals, or specified to run when the server is idle. 

  Jobs give administrators the ability to define when administrative tasks are performed. Each job can combine various operating system commands, Transact-SQL statements, stored procedures, and applications to complete complex administrative functions. Each job step can be very complex. For example, a Windows command could be a command or batch file that contains many commands. The Transact-SQL statement executed by a step could be a stored procedure containing many Transact-SQL statements. 

  SQL Server Agent runs these tasks at the times specified without the need for human intervention. Complex procedures with error-checking logic can be designed into each job to address the most likely conditions the job would encounter. These capabilities result in the ability to build complex, robust jobs that run all periodic maintenance. 

• Events and alerts 

  Each copy of SQL Server running on Windows NT records significant things that happen to it in the Windows NT application log. Each entry in the log is called an event. SQL Server administrators can define alerts that specify a job to be run when a specific event occurs. SQL Server Agent compares the SQL Server events in the application log against the alerts defined by administrators. If a match is made, the job specified in the alert is executed. 

  Windows 95/98 does not have event logs. Copies of Desktop SQL Server running on Windows 95/98 use a SQL Server Profiler-based mechanism to communicate events to SQL Server Agent. 

  SQL Server creates events for errors with a severity of 19 or higher. Events are also raised if a RAISERROR statement is executed using the WITH LOG clause, or the xp_logevent system stored procedure is executed. This allows Transact-SQL scripts, triggers, stored procedures, and applications to raise events that could fire a job. 

• Operators 

  Operators are e-mail and page addresses defined to SQL Server for use in alerts. An alert can be defined that either e-mails or pages a specific person. If SQL Server is running on Windows NT, it can also use the Windows NT net send command to send a network message to a Windows NT user or group. 

• Triggers 

  Triggers are used to enforce business logic. Triggers can be integrated with automated administrative tasks by using either RAISERROR or xp_logevent to generate an event that fires an alert. For example, assume that a retail company has an inventory database, and all of their suppliers accept electronic orders. Every night, a scheduled job executes an application that reviews all inventory levels and, using guidelines established by management, either places orders with preferred providers for items in short supply or prints a report for the purchasing agents. This could be backed up by a DELETE trigger on the parts table that fires a similar job for emergency orders if heavy sales deplete the inventory during the day. 

See Also 

In Other Volumes 

"Automating Administrative Tasks" in Microsoft SQL Server Administrator's Companion 

"Enforcing Business Rules with Triggers" in Microsoft SQL Server Database Developer's Companion 

Backup/Restore Architecture

The backup and restore components of Microsoft SQL Server provide the capability of creating a copy of a database. This copy is stored in a location that is protected from failures of the server running SQL Server. If the server running SQL Server fails, or if the database is somehow damaged, the backup copy can be used to re-create, or restore, the database.

SQL Server has sophisticated backup and restore capabilities:

• Control with the BACKUP and RESTORE statements.

  Users can execute these statements directly from applications or Transact-SQL scripts, stored procedures, and triggers. It is more common, however, to use the SQL Server Enterprise Manager interface to define a backup schedule and let SQL Server Agent run the backups automatically according to the schedule. The Database Maintenance Wizard can be used to define and schedule a full set of backups for each database. This fully automates the backup process such that it proceeds with minimal or no need for operator action. 

• Maintenance of a set of backup history tables in the msdb database.

  These record the backups that are made for each database. If a database has to be restored, the SQL Server Enterprise Manager Restore Database dialog box presents the user with a list of all the backups available for the database. The Restore Database dialog box also has logic to display which set of the backups in the history can be used to restore the database in the shortest possible time. When the dialog box is displayed, the backups needed to restore the database are checked. If the user knows that one of the backups is not available, for example if a tape cartridge was damaged or lost, they can deselect that backup and SQL Server Enterprise Manager calculates a new restore process. When the user agrees with the restore process, SQL Server Enterprise Manager restores the database, prompting for tapes as needed. 

• Backups that can be performed while the database is in use, allowing backups to be made of 24x7 systems that must run continually.

  The backup processing and internal data structures of SQL Server version 7.0 have been improved so that backups maximize their rate of data transfer with minimal effect on transaction throughput. 

• Faster data transfer rates of backup and restore operations, making it much more capable of supporting very large databases (VLDB).

  The new data structures in SQL Server 7.0 databases and new backup and restore algorithms significantly speed the rate at which backup and restore operations can transfer data. SQL Server backup and restore operations can also run in parallel against multiple backup files or tape drives. This further improves the backup and restore data transfer rates. 

• RESTORE statement to re-create the database automatically if necessary.

  This eliminates the need to execute a separate CREATE DATABASE or CREATE DATABASE FOR LOAD statement if the database does not exist at the time the RESTORE statement is executed. 

• Interrupted backup and restore operations started near the point of the interruption when they are restarted. 

• Verification of a SQL Server 7.0 backup before an attempt to restore the database. 

Backup and restore processes should be planned together. The administrators must first determine the criticality of the data in the database. They must determine if it is acceptable to just restore the database to a point such as the night before the failure, or if the database must be restored to a point as close as possible to the time of failure. They must also determine how long the database can be unavailable, whether it must be brought back online as quickly as possible, or if it does not need to be restored immediately.

After the restore requirements are determined, the administrators can then plan a backup process that maintains a set of backups that will meet the restore requirements. The administrators can choose the backup processes that can be performed with the minimum effect on the system as it runs, yet still meet the restore requirements.

See Also 

In Other Volumes 

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion 

Backup Devices

Microsoft SQL Server version 7.0 backups are stored using the Microsoft Tape Format (MSTF). MSTF is not specific to tapes; it can also be used for backing up to either disks or named pipes. Each time a SQL Server backup is performed, it forms a backup set. This backup set is stored in an MSTF unit called a media. MSTF media can store backup sets from different software.

Using the MSTF format allows SQL Server to work with administrative utilities and products from other vendors that manage MSTF format backups. SQL Server backup sets can share media, such as MSTF tape drives, with backup sets from other server software. SQL Server does not compress its backup sets, but would take advantage of the compression provided on MSTF backup devices.

See Also 

In Other Volumes 

"Backup Media" in Microsoft SQL Server Administrator's Companion 

Types of Backup and Restore Processes

Microsoft SQL Server version 7.0 supports four different types of backups. These can be combined to form many different types of backup and restore processes, each tailored to the availability requirements of the database. The four types are:

• Database 

• Transaction log 

• Differential 

• File and filegroup 

See Also 

In Other Volumes 

"Choosing a Backup and Restore Strategy" in Microsoft SQL Server Administrator's Companion 

Database Backup and Restore

A database backup makes a copy of the full database. Not all pages are copied to the backup set, only those actually containing data. Both data pages and transaction log pages are copied to the backup set.

A database backup set is used to re-create the database as it was at the time the BACKUP statement completed. If only database backups exist for a database, it can be recovered only to the time of the last database backup taken before the failure of the server or database.

See Also 

In Other Volumes 

"Database Backups" in Microsoft SQL Server Administrator's Companion 

Transaction Log Backup and Restore

A transaction log backup makes a copy of only the log file. A log file backup by itself cannot be used to restore a database. It is used after a database restore to recover the database to the point of the original failure. For example, assume a site performs a database backup on Sunday night and a log backup on each of the other nights. If one of the data disks for the database is lost at 2:30 P.M. Tuesday, the site can:

1. Back up the current transaction log. 

2. Restore the database backup from Sunday night. 

3. Restore the log backup from Monday night to roll the database forward. 

4. Restore the log backup taken after the failure. This will roll the database forward to the time of the failure. 

A transaction log recovery requires an unbroken chain of transaction log backups from the time of the database backup to the time of the failure.

See Also 

In Other Volumes 

"Transaction Log Backups" in Microsoft SQL Server Administrator's Companion 

Differential Backup and Restore

A differential backup makes a copy of all the pages in a database that have been modified after the last database backup. Differential logs are used primarily in heavily used systems where a failed database must be brought back online as quickly as possible. Differential backups are smaller than full database backups, so they make less of an effect on the system while they run.

Assume a site does a full database backup on Sunday night. They make a set of transaction log backups every 4 hours during the day, with the backups from one day overwriting the backups from the day before. Each night they make a differential backup. If one of the data disks for the database fails at 9:12 A.M. on Thursday, the site can:

1. Back up the current transaction log. 

2. Restore the database backup from Sunday night. 

3. Restore the differential backup from Wednesday night to roll the database forward to that point. 

4. Restore the transaction log backups from 4 and 8 A.M. to roll the database forward to 8 A.M. 

5. Restore the log backup taken after the failure. This will roll the database forward to the time of the failure. 

See Also 

In Other Volumes 

"Differential Database Backups" in Microsoft SQL Server Administrator's Companion 

File and Filegroup Backup and Restore

Microsoft SQL Server supports backing up or restoring individual files or file groups within a database. This is a relatively sophisticated backup and restore process usually reserved for very large databases (VLDB) with high availability requirements. If the window of time for backups is not long enough to support backing up the full database, then subsets of the database can be backed up at different times. For example, if it takes three hours to back up a database, and backups can be performed only during a two-hour window each day, and then half the files or file groups can be backed up on one night and half the next.

If a disk holding database files or filegroups fails, the site can restore just the lost files or filegroups. They must also be making transaction log backups, and must restore all transaction log backups made after the file or filegroup backup.

File and filegroup restores can also be made from a full database backup set. This allows for a quicker recovery because only the damaged files or filegroups are restored in the first step, not the entire database.

See Also 

In Other Volumes 

"Using File or Filegroup Backups" in Microsoft SQL Server Administrator's Companion 

Fuzzy Backup and Restore Operations

Microsoft SQL Server version 7.0 uses industry-standard fuzzy backup algorithms. These new algorithms have several main benefits for users:

• The BACKUP statement runs faster and has less effect on users modifying data while the statement is processing. 

• The RESTORE statement is faster. 

A RESTORE operation restores the database to the state it was in at the time the BACKUP statement finished. In earlier versions of SQL Server, a LOAD statement restored a database to the state it was in at the time the DUMP statement started.

In a SQL Server fuzzy backup and restore operation:

• Extents containing data are written to the backup set without regard to synchronizing pages being modified by users during the backup. This significantly reduces the effect the backup has on current users. It also allows the backup to copy pages serially. The elimination of any random reads speeds the backup process in heavily used systems. It does mean, however, that the pages in the backup are stored in an inconsistent, unrecovered state. 

• The transaction log is copied as part of the backup. 

A RESTORE statement:

• Creates the database if it does not exist, and initializes the extents in the database. This step is bypassed if the database exists when the RESTORE statement is executed. 

• Copies in the extents found in the backup set. The process is fast because all the extents are in a serial sequence. Extents not found in the backup set are ignored; they are not initialized as empty extents. 

• Uses the transaction log to recover the database. The database modifications recorded in the log are rolled forward to the end of the end of the log, and then any incomplete transactions are rolled back. This returns the database to a consistent, recovered state that corresponds to the state the database was in at the time the BACKUP statement completed. 

Parallel Backup and Restore

Parallel backup and restore operations improve the capability of Microsoft SQL Server to manage very large databases. The BACKUP and RESTORE statements use parallel I/O in a number of ways:

• If a database has files on several disk devices, BACKUP uses one thread per disk device to read the extents from the database. 

• If a backup set is stored on multiple backup devices, both the BACKUP and RESTORE statements use one thread per backup device. 

• If a database is defined with files on several disk drives, and RESTORE has to create the database, RESTORE uses one thread per disk device while it is initializing the database. 

Data Import/Export Architecture

Microsoft SQL Server has several components that support importing and exporting data:

• Data Transformation Services (DTS) 

  DTS can be used to import and export data between heterogeneous OLE DB and ODBC data sources. A DTS package is defined that specifies the source and target OLE DB data sources; the package can then be executed on an ad hoc basis or at scheduled times or intervals. A single DTS package can cover multiple tables. DTS packages are also not limited to transferring data straight from one table to another, as the package can specify a query as the source of the data. This allows packages to transform data, such as running a query that returns aggregate summary values instead of the raw data. 

• Replication 

  Replication is used to create copies of data in separate databases and keep these copies synchronized by replicating modifications in one copy to all the others. If it is acceptable for each site to have data that may be a minute or so out of date, replication allows the distribution of data without the overhead of requiring distributed transactions to ensure all sites have an exact copy of the current data. Replication can therefore support the distribution of data for a relatively low cost in network and computing resources. 

• Bulk copying 

  The bulk copy feature of SQL Server allows for the efficient transfer of large amounts of data. Bulk copying transfers data into or out of one table at a time. Bulk copying supports the following bulk copy transfers:

  • From one SQL Server table or view to another table or view. 

  • From a SQL Server table or view into a data file, such as a text file or tab-delimited file. 

  • The result set of a query into a table, view, or data file. 

  • The contents of a data file into a table or view. 

  There are several ways the bulk copy feature can be used:

  • The bcp command prompt utility. 

  • The OLE DB Provider for SQL Server has a provider-specific IRowsetFastLoad interface for bulk copies. 

  • The SQL Server ODBC Driver supports a set of bulk copy functions. 

  • The Transact-SQL BULK INSERT statement. This is the fastest of the bulk copy methods. The data file is accessed directly from SQL Server itself, eliminating the overhead of communicating data from a client application to the server. 

  • The DB-Library API supports a set of bulk copy functions. 

• Distributed queries 

  Distributed queries allow Transact-SQL statements to reference data in an OLE DB data source. The OLE DB data sources can be another copy of SQL Server, or a heterogeneous data source such as Microsoft Access or Oracle. SELECT INTO and INSERT statements can be used to:

  • Export data from a SQL Server data base to an OLE DB data source. 

  • Import data from an OLE DB data source into SQL Server. 

See Also 

In Other Volumes 

"Importing and Exporting Data" in Microsoft SQL Server Administrator's Companion 

"Replication" in Microsoft SQL Server Distributed Data Operations and Replication 

"Data Transformation Services" in Microsoft SQL Server Distributed Data Operations and Replication 

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion 

Data Integrity Validation

Transact-SQL has a set of DBCC statements that are used to verify the integrity of a database. Microsoft SQL Server version 7.0 improves these statements in several ways.

The need to run the statements has been reduced significantly. There have been two architectural changes in SQL Server that make the databases even more robust than in earlier versions of SQL Server:

• The database engine has fail-fast logic to detect potential errors closer to the time they originate. This means errors are less likely to persist long enough to cause problems in a database. 

• The data structures in the database are simpler. This means they are easier to manage and less likely to have errors. 

In earlier versions of SQL Server, it was recommended that DBCC statements be made a regular part of a database backup strategy. It was recommended that databases be checked before being backed up. In SQL Server 7.0, this is no longer necessary.

The DBCC statements themselves also run significantly faster than in earlier versions of SQL Server. Checks of complex databases typically run 8 to 10 times faster in version 7.0 than in 6.5 and checks of some individual objects have run over to 300 times faster in version 7.0 than 6.5. In SQL Server 6.5, DBCC CHECKDB processed the tables serially. For each table, it first checked the structure of the underlying data and then checked each index individually. This resulted in very random pattern of reads. In SQL Server 7.0, DBCC CHECKDB performs a serial scan of the database while performing parallel checks of multiple objects as it proceeds.

Another option introduced in SQL Server 7.0 is to have the DBCC statements repair minor problems they might encounter. The statements have the option to repair certain errors in the B-tree structures of indexes, or errors in some of the allocation structures.

See Also 

In Other Volumes 

"DBCC" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Optimizing DBCC Performance" in Microsoft SQL Server Diagnostics