Chapter 6 - SQL Server Features

  • Article

Microsoft SQL Server supports a set of features that result in the following benefits:

  • Ease of installation, deployment, and use 

    SQL Server includes a set of administrative and development tools that improve your ability to install, deploy, manage, and use SQL Server across several sites. 

  • Scalability 

    The same database engine can be used across platforms ranging from laptop computers running Microsoft Windows 95 to large, multiprocessor servers running Microsoft Windows NT, Enterprise Edition. 

  • Data warehousing

    SQL Server includes tools for extracting and analyzing summary data for online analytical processing (OLAP). SQL Server also includes tools for visually designing databases and analyzing data using English-based questions. 

  • System integration with other server software 

    SQL Server integrates with e-mail, the Internet, and Windows. 

Ease of Installation, Deployment, and Use

Many databases capable of supporting all of the processing needs of an enterprise are complex and difficult to administer. Microsoft SQL Server includes many tools and features that simplify your ability to install, deploy, manage, and use databases. SQL Server provides database administrators with all the tools required to fine-tune SQL Server installations running production online systems. SQL Server is also capable of operating efficiently on a small, single-user system with minimal administrative overhead.

Dynamic Self-Management 

SQL Server version 7.0 reconfigures itself automatically and dynamically while running. If more work is done in SQL Server, it can dynamically acquire additional resources, such as memory. As the workload falls, SQL Server frees the resources back to the system. If other applications are started on the server, SQL Server will detect the additional allocations of virtual memory, and reduce its use of virtual memory to reduce paging overhead. SQL Server can also increase or decrease the size of a database automatically as data is inserted or deleted.

The amount of dynamic reconfiguration in each SQL Server installation can be controlled by database administrators. A small database used by someone not familiar with databases can run with the default configuration settings, in which case it will configure itself dynamically. A large production database monitored by experienced database administrators can be set up to give the administrators full control of configuration.

Complete Administrative Tool Set 

SQL Server offers database administrators several tools for managing their systems.

  • SQL Server Enterprise Manager is a snap-in component for Microsoft Management Console (MMC). 

    MMC supports the management of multiple types of servers from a single console, such as Microsoft Windows NT Services, Microsoft Internet Information Servers, Microsoft SNA Servers, and SQL Servers. An administrator at a single console has the ability to manage all the servers on a worldwide network. SQL Server Enterprise Manager shares a subset of the MMC user interface for Web administration. It presents all SQL Server objects in a hierarchical console tree with an easy-to-use graphical user interface. 

  • SQL Server Agent allows the definition and scheduling of tasks that run on a scheduled or recurring basis. 

    It also supports alerting administrators when certain warning conditions occur, and can even be programmed to take corrective action. 

  • SQL Server Profiler offers administrators a sophisticated tool for monitoring and analyzing network traffic to and from a server running SQL Server. 

    It also profiles server events such as the acquisition of locks. 

  • SQL Server Performance Monitor integrates SQL Server counters into the Windows NT Performance Monitor, allowing administrators to monitor and graph the performance of SQL Server with the same tool used to monitor Windows NT Servers. 

  • The Index Tuning Wizard analyzes how an SQL statement, or group of statements, uses the existing indexes on a set of tables. 

    The wizard makes recommendations on index changes that would speed up the SQL statements. 

Programmable Administration 

Administering SQL Server can be highly automated, freeing database administrators to design new databases and applications.

SQL Distributed Management Objects (SQL-DMO) are a set of OLE Automation objects that can be used to code applications with the logic to administer a SQL Server system. This gives application packages the ability to embed SQL Server into their applications transparently. Experienced database administrators can also use SQL-DMO to build applications for many of the common administrative tasks unique to their site.

Routine, recurring tasks for a database can be implemented as automatically scheduled jobs that run without constant supervision by an operator. For example, after a database administrator has designed a backup procedure for a server, the backups can be implemented as a set of automatic jobs.

SQL Server can also be programmed to raise alerts when specific events occur. The actions taken by alerts can take several forms:

  • E-mail, paging messages, or Windows NT net send messages can be sent to the affected parties. 

    For example, if the number of Full Scans (a scan of an entire table or index) in a server exceeds a specific number, an e-mail can be sent to the database administrator for investigation. 

  • A predefined job can be executed to address the problem (if it is relatively routine and can be addressed programmatically). 

Installation and Upgrade 

When a SQL Server 7.0 compact disc is placed in a CD-ROM drive, the compact disc has an autorun application that lets the user make several choices:

  • Install or upgrade SQL Server 

  • Install pre-requisite software 

  • View the documentation on the compact disc before the product is installed 

  • View an evaluation guide explaining the benefits of SQL Server 7.0 features 

The installation or upgrade of SQL Server is driven by a GUI application that guides the user through the information needed by SQL Server Setup. The Setup program itself detects automatically if an earlier version of SQL Server is present and, after version 7.0 is installed, asks the user if they want to launch the SQL Server Upgrade Wizard to quickly guide them through the upgrade process. The entire installation or upgrade process is accomplished quickly and with minimal input from the user.

Sites needing to install SQL Server on many servers can take advantage of the SQL Server unattended installation feature to install SQL Server with the same configuration on all the servers.

Building SQL Server Applications 

SQL Server has several advantages in building applications:

  • Native support for many common database APIs, including ADO, OLE DB, ODBC, and Embedded SQL for C.

    These APIs include powerful, low-level APIs, such as ODBC and OLE DB, that allow programmers control over the interaction between the application and database. They also include APIs such as ADO that support Rapid Application Development (RAD). 

  • SQL-DMO, SQL-DTS, and replication components.

    These are OLE Automation objects that can be used to write customized applications to administer a server running SQL Server. These objects were used by Microsoft to build SQL Server Enterprise Manager, proving that they are robust enough to perform any administrative task. 

  • SQL Server Query Analyzer

    This component allows programmers to develop and test SQL statements interactively. It includes aids such as a graphical display of the execution path of an SQL statement and color coding of different syntax elements to increase the readability of SQL statements. 

See Also 

In This Volume 

Administration Architecture

Installing SQL Server

Application Development Architecture

Scalability

The same Microsoft SQL Server version 7.0 database engine operates on Microsoft Windows 95/98, Microsoft Windows NT Workstation, Windows NT Server, and Windows NT Server Enterprise Edition. The database engine is a robust server that can manage terabyte databases being accessed by thousands of users. At the same time, when running at its default settings, SQL Server 7.0 has features such as dynamic self-tuning that let it work effectively on laptops and desktops without burdening users with administrative tasks.

Same Server Across Windows 95/98 and Windows NT platforms 

Because the same SQL Server 7.0 database engine runs on Windows 95/98, Windows NT Workstation, and Windows NT Server, the same programming model is shared in all environments.

In general, an application written against a SQL Server installation operating in one environment works on any other SQL Server installation. The Microsoft Search service is not available on the Windows 95/98 and Windows NT Workstation operating systems. SQL Server databases on those platforms do not support full-text catalogs and indexes.

The differences in the behavior of SQL Server when running on the different operating systems are mainly due to features that are not supported by Windows 95/98. Generally, these features, such as asynchronous I/O and scatter-gather I/O, do not affect the data or responses given to applications. They just prevent SQL Server installations running on Windows 95/98 from supporting the same levels of performance as is possible for SQL Server installations on Windows NT. SQL Server installations on Windows 95/98, however, do not support failover clustering and cannot publish transactional replications.

Dynamic Row-level Locking 

SQL Server dynamically adjusts the granularity of locking to the appropriate level for each table referenced by a query. When a query references a small number of rows scattered in a large table, the best way to maximize concurrent access to data is to use fine-grained locks such as row locks. However, if a query references most or all of the rows in a table, the best way to maximize concurrency may be to lock the whole table to minimize the locking overhead and finish the query as quickly as possible.

SQL Server maximizes overall concurrent access to data by choosing the appropriate locking level for each table in each query. For one query, the database engine may use row-level locking for a large table where few rows are referenced, page-level locking for another large table where many rows on a few pages are referenced, and table-level locking for a small table in which all the rows are referenced.

Very Large Database (VLDB) Improvements 

SQL Server has high speed optimizations that support VLDB environments. Earlier versions of SQL Server could support databases up to 200 MB or 300 MB in size. SQL Server 7.0 can support terabyte databases effectively.

The Transact-SQL BACKUP and RESTORE statements have been optimized to read through a database serially and write in parallel to multiple backup devices. Sites can also reduce the amount of data to be backed up by performing incremental backups that back up only data changed after the last backup, or by backing up individual files or file groups.

Multiple bulk copy operations can be performed concurrently against a single table to speed data entry.

Operations that create multiple indexes on a table can now create them concurrently.

SQL Server databases now map directly to Windows files, simplifying the creation and administration of databases. The database page size has been increased to 8 KB and extents to 64 KB, which results in improved I/O.

Improved Query Optimizer 

The SQL Server 7.0 query optimizer has new access methods to speed query processing. These improved access methods are often matched to improvements and simplifications in the on-disk data structures in the database.

The SQL Server 7.0 query optimizer uses serial, read-ahead I/O when scanning tables and indexes for improved performance. The optimizer also uses merge and hash algorithms for performing joins.

The SQL Server 7.0 query optimizer natively supports the prepare/execute model of executing SQL statements. The optimizer also has efficient algorithms to match SQL statements from applications with existing execution plans from a prior execution of the same statement. In systems where many users are running the same application, this can reduce the resources needed to compile SQL statements into execution plans.

Intra-query Parallelism 

When running on servers with multiple CPUs, SQL Server 7.0 can build parallel execution plans that split the processing of an SQL statement into several parts. Each part can be run on a different CPU and the complete result set built more quickly than if the different parts were executed serially.

Replication Improvements 

Replication in SQL Server 7.0 supports pull subscriptions using Subscriber Distribution and Merge Agents. Anonymous subscriptions (those unknown to the Publisher) and Internet subscriptions can also be configured.

Replication has achieved several import performance gains due to the improved schema of the distribution database, improvements in the Log Reader Agent technology, and the ability to replicate the execution of stored procedures using transactional replication.

The usability of Replication Monitor has been greatly enhanced, making it much easier to support a greater number of users from a single server.

Distributed Query 

SQL Server 7.0 supports referencing heterogeneous OLE DB data sources in Transact-SQL statements. OLE DB providers return their data as rowsets, which operate like a table. SQL Server 7.0 supports new functions that return rowsets from OLE DB data sources. These functions can be used in place of a table reference in a Transact-SQL statement.

The distributed query capability of SQL Server supports referencing the OLE DB rowsets in data modification statements such as INSERT, UPDATE, and DELETE, if the OLE DB provider supports updates. The OLE DB rowset modifications are protected by distributed transactions if the OLE DB provider supports the required interfaces.

Full Integrity Protection 

SQL Server fully protects the integrity of its databases. Updates are performed in transactions, and each transaction is either wholly committed if it reaches a state of consistency or all rolled back if it encounters errors. If a server fails, all uncompleted transactions are rolled back from all SQL Server databases when the server is restarted.

Distributed Transactions 

SQL Server databases can participate in distributed transaction managed by an X/Open XA compliant transaction manager. This includes distributed transactions spanning multiple SQL Server databases, and also distributed transactions spanning heterogeneous resource managers. The OLE DB Provider for SQL Server and the SQL Server ODBC Driver both support enlistment in distributed transactions.

Transact-SQL scripts and applications can have their local transactions escalated dynamically to distributed transactions if they reference objects on other SQL Server systems or heterogeneous OLE DB data sources. SQL Server manages these distributed transactions transparently using the Microsoft Distributed Transaction Coordinator (MS DTC).

See Also 

In This Volume 

Server Scalability

Distributed Transactions Architecture

Distributed Query Architecture

Data Warehousing

Microsoft SQL Server version 7.0 introduces several components that improve the ability to build data warehouses that effectively support decision support processing needs.

Data Warehousing Framework 

The Data Warehousing Framework is a set of components and APIs that implement the data warehousing features of SQL Server. It provides a common interface to be used by various components seeking to build and use a data warehouse or data mart.

Data Transformation Services (DTS) 

DTS provides a set of services that aids in building a data warehouse or data mart. Decision support systems analyze data to find trends of interest to the database users. While online transaction processing (OLTP) databases store large numbers of records covering the details of each transaction, online analytical processing (OLAP) systems want to aggregate and summarize the information to speed analysis of the trends exhibited in the data.

DTS offers support for extracting data from heterogeneous OLE DB data sources and the summarizing or aggregating the data to build a data warehouse.

Repository 

SQL Server 7.0 includes Microsoft Repository, which consists of a set of Microsoft ActiveX interfaces and information models that define database schema and data transformations as defined by the Microsoft Data Warehousing Framework. A goal of the Microsoft Data Warehousing Framework is to provide meaningful integration of multiple products through shared metadata. It combines business and technical metadata to provide an industry standard method for storing the schema of production data sources and destinations.

Repository is the preferred means of storing DTS packages in a data warehousing scenario because it is the only method of providing data lineage for packages. DTS also uses Repository storage to allow transformations, queries, and ActiveX scripts to be reused by heterogeneous applications.

Online Analytical Processing (OLAP) Support 

Microsoft SQL Server OLAP Services provides OLAP processing capabilities against heterogeneous OLE DB data sources. It has efficient algorithms for defining and building multidimensional cubes that can be referenced by applications using the OLE DB 2.0 OLAP extensions or the Microsoft ActiveX Data Objects Multidimensional extensions (ADO MD). OLAP Services is an excellent tool for multidimensional analysis of data in SQL Server databases.

English Query 

English Query makes a definition of the entities and relationships defined in a SQL Server database. Given this definition, an application can use an OLE Automation API to pass English Query a string containing a natural-language question about the data in the database. English Query returns an SQL statement the application can use to extract the data needed to answer the question.

See Also 

In This Volume 

Data Warehousing and Online Analytical Processing

System Integration

Microsoft SQL Server is integrated with other products and environments.

Internet Access 

SQL Server works with other products to form a stable and secure data store for Internet and intranet systems.

  • SQL Server works with Microsoft Windows NT Server security and encryption facilities to implement secure data storage. 

  • SQL Server forms a high-performance data storage service for Internet Information Services Web applications. 

  • SQL Server can be used with Site Server to build and maintain large, sophisticated e-commerce Web sites. 

  • The SQL Server TCP/IP Sockets Net-Library can be integrated with Microsoft Proxy Server to implement secure Internet and intranet communications. 

SQL Server is scalable to levels of performance capable of handling extremely large Internet sites.

The Web Assistant Wizard quickly guides users through the steps to:

  • Generate HTML pages from SQL Server data. 

  • Post SQL Server data to HTTP and FTP locations. 

SQL Server fully supports Web pages using Active Server Pages (ASP) or the Internet Data Connector (IDC).

Security Integrated with Windows NT Security 

SQL Server supports using Windows NT user and domain accounts as SQL Server login identifiers. This is called Windows NT Authentication. Users are validated by Windows NT when the connect to the network. When a connection is formed to SQL Server, the SQL Server client software requests a trusted connection, which can be granted only if they have been validated by Windows NT. SQL Server then does not have to validate the user separately. Users do not have to have separate logins and passwords for each SQL Server system to which they connect.

With Windows NT Authentication, no passwords are transmitted to the server running SQL Server, eliminating a security concern. Also, the SQL Server Multiprotocol Net-Library uses the Windows NT RPC API to send network packets to SQL Server. The RPC API supports encryption, allowing users who want the capability of encrypting all the network traffic between their client computer and SQL Server.

Mail Integration 

SQL Server can send and receive e-mail and pages from Microsoft Exchange or other MAPI-compliant mail servers. This allows SQL Server stored procedures or triggers to send e-mail. SQL Server events and alerts can be set to send e-mail or pages automatically to the server administrators in case of severe or pending problems.

SQL Server can also accept e-mail containing an SQL statement. SQL Server will execute the statement and send the results using a reply e-mail, with an optional CC: list.

See Also 

In This Volume 

SQL Server and Mail Integration

Communication Components

In Other Volumes 

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