Microsoft® SQL Server™ version 7.0 introduces these server improvements and new features:
These settings can be allowed to automatically configure themselves.
Many server configuration options have been streamlined and simplified. For example, by default the server dynamically adjusts its memory and lock resource use. A database increases allocated resources when necessary without ever committing them and decreases the resources used when they are no longer needed. Earlier versions of SQL Server required manual adjustment of these settings.
Databases now reside on operating-system files instead of on SQL Server logical devices. You can create a database and all its files with a single CREATE DATABASE statement or use SQL Server Enterprise Manager to create new databases and modify (alter) existing databases. Database files expand automatically, eliminating the need for administrators to issue an additional ALTER statement. A new CREATE DATABASE statement syntax incorporates this file-based implementation.
Database files can automatically grow from their originally specified size. When you define a file you can specify a growth increment. Each time the file fills, it increases its size by the growth increment. Each file can also have a maximum size specified. If a maximum size is not specified, the file can continue to grow until it has used all available space on the disk. This feature is especially useful when SQL Server is used as a database embedded in an application where the user does not have ready access to a system administrator. The user can let the files grow automatically to lessen the administrative burden of monitoring the amount of free space in the database and allocating space manually.
A database consists of two or more files: one data file to hold tables and other objects, and another file to hold the transaction log. Each file is used by only one database. A single file cannot be shared by multiple databases. When a database is dropped, its files are also deleted. Note that you can detach a database to preserve the files, rather than only to drop a database.
SQL Server 7.0 now supports full row-level locking for both data rows and index entries. The lock manager has been optimized to complete lock requests faster and with less internal synchronization. Many OLTP applications can experience increased concurrency, especially when applications append rows to tables and indexes.
The lock manager adjusts the resources it uses for larger databases dynamically, eliminating the need to adjust the locks server configuration option manually. It chooses automatically between page locking (preferable for operations locking rows in many pages) and row-level locking (preferable for operations that affect a relatively small number of rows, or rows scattered throughout a table).
The query processor has been redesigned to support the large databases and complex queries found in decision support, data warehouse, and OLAP applications. The query processor includes several new execution strategies that can improve the performance of complex queries.
It now uses hash joins and more sophisticated merge joins, as well as advanced hash aggregation techniques. These techniques can scale to larger databases than those supported by the nested loops join technique, which was the primary join technique supported by SQL Server 6.5. SQL Server 7.0 uses index intersection and union techniques on multiple indexes to filter data before it retrieves rows from the tables in the database. All indexes on a table are maintained concurrently as you make data modifications. Constraint evaluations are now considered as part of the query processor’s execution plan. These two factors simplify and speed the updating of multiple rows of a table.
The SQL Server 7.0 query processor extracts information from the statistics and regathers statistics automatically, optionally using fast sampling. It also chooses automatically to collect statistics needed by the optimizer. This ensures that the query processor uses the most current statistics and reduces maintenance requirements.
The query optimizer recognizes certain commonly occurring database and query structures, such as star or snowflake schemas, and chooses an execution plan based on a careful cost analysis particularly well-adapted to queries against these structures. For example, the SQL Server 7.0 query optimizer uses the techniques of Cartesian product index manipulation and semi-joins to process queries against star schemas.
The query optimizer has a wide set of execution strategies and many of the optimization limitations of earlier versions of SQL Server have been removed. An improved costing model and compile-time enhancements, such as predicate transitivity and constant folding, greatly improve the quality of query plans.
The query processor supports new query hints, and some query limitations have been loosened or removed. For example, a single query can now reference 256 tables, and the number of internal work tables used by a query (16 in earlier versions) is no longer limited.
The query processor uses OLE DB to communicate with the data-storage components of SQL Server. OLE DB provides the SQL Server 7.0 query processor with distributed and heterogeneous query capabilities. It supports distributed queries between multiple SQL Server 7.0 servers as well as to any OLE DB provider.
SQL Server 7.0 supports parallel execution of a single query across multiple processors. A CPU-bound query that must examine a large number of rows often benefits if portions of its execution plan are run in parallel. SQL Server 7.0 determines automatically which queries will benefit from parallelism and generates a parallel execution plan. If multiple processors are available when the query begins executing, the work is divided across the processors. Parallel query execution is enabled by default.
During query optimization, SQL Server looks for queries that might benefit from parallel execution. For these queries, SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. After exchange operators are inserted, the result is a parallel query execution plan. A parallel query execution plan can use more than one thread, whereas a serial execution plan, used by a nonparallel query, only uses a single thread for its execution. The actual number of threads used by a parallel query is determined at query plan execution and initialization and is called the degree of parallelism.
You can now append multiple triggers of the same type to a single table. For example, a single table can have one delete trigger, three insert triggers, and two update triggers. This enhancement allows you to put different business rules into different triggers. A database option allows triggers to call themselves recursively.
Distributed queries access data that can be stored in multiple data sources on either the same or different computers. These queries take advantage of OLE DB, the emerging standard for data access of nonrelational as well as relational data sources.
Distributed queries provide SQL Server users with access to data stored in:
These new formats allow the server to scale easily from low-end to high-end systems, improving performance and manageability. They also enable new capabilities in future releases, such as embedded tables, multiple types of rows on a single data page, and databases that can be electronically mailed. There are new disk formats for pages, rows, extents, data files, and log files. The new page and row formats support row-level locking, are extensible for future requirements, and improve performance when large blocks of data are accessed, because each I/O operation retrieves more data.
All database pages are now 8 KB in size, increased from 2 KB. The maximum number of bytes in a row is now 8060 bytes, and the limit on character and binary data types is 8000 bytes, increased from 255 bytes. Tables can now have 1024 columns, a significant increase over the 250 columns supported previously. SQL Server also now supports Unicode data types. For more information, see Enhanced Data Type Support later in this topic.
Backup and restore operations run much faster, have less performance effect on server operations, and have new features.
A differential database backup captures only those data pages that have changed after the last full database backup. Differential backups can often eliminate much of the time the server spends rolling transactions forward when recovering transaction logs. A portion of the database can be restored, or rolled forward, to minimize recovery time in the event of media failure. Restoring a backup is easy because the restore process creates the database and all necessary files automatically.
Backup supports the Microsoft Tape Format, allowing SQL Server backups to share the same tape media with other backups, such as those written by the Microsoft Windows NT® Server Backup program.
The DBCC statements have been redesigned to provide dramatically improved performance. In addition, bulk copy operations now validate constraints and fire triggers as the data is loaded.
The bulk copy utility (bcp) uses OLE DB to communicate with SQL Server, supports all data types, and is much faster. Index maintenance strategies are improved to make loading data into tables with indexes more efficient.
The security architecture is better integrated with Windows NT and provides increased flexibility. Database permissions can now be assigned directly to Windows NT users. You can define SQL Server roles to include not only Windows NT users and groups but also SQL Server users and roles.
A SQL Server user can be a member of multiple SQL Server roles. This allows database administrators to manage SQL Server permissions as Windows NT groups or SQL Server roles, rather than as individual user accounts. Database access and permissions can now be managed using Windows NT groups. New fixed server and database roles such as dbcreator, db_owner, and securityadmin provide more flexibility and improved security than the system administrator login. However, it is no longer necessary to run SQL Server under a sysadmin account.
SQL Server 7.0 now provides a native Microsoft OLE DB Provider for SQL Server that supports the OLE DB 2.1 API. The driver also supports bulk copy operations and the ability to obtain metadata for linked tables used in distributed queries. The OLE DB Provider for SQL Server supports all the new data types and features introduced in SQL Server 7.0.
Applications using the ADO API can now use the OLE DB Provider for SQL Server instead of using the Microsoft OLE DB Provider for ODBC over the SQL Server ODBC Driver. The OLE DB Provider for SQL Server exposes more SQL Server functionality than the Microsoft OLE DB Provider for ODBC, and reduces the number of layers the application must use to communicate with SQL Server.
The SQL Server ODBC Driver has been enhanced to support the ODBC 3.51 API. The driver also now supports the bulk copy functions originally introduced in DB-Library, and the ability to obtain metadata for linked tables used in distributed queries. SQL Server ODBC Driver 3.7 supports all the new data types and features introduced in SQL Server 7.0, and is fully Unicode compliant.
SQL Server Enterprise Manager is based on a Distributed Management Framework (SQL-DMF). SQL-DMF allows for centralized administration of all SQL Server installations in an organization. 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.
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.
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.
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.
The maximum length of the char, varchar, and varbinary data types is now 8000 bytes, an increase from the limit of 255 bytes in SQL Server 6.x. The Transact-SQL string functions also support these very long char and varchar values. The use of text and image data types can now be reserved for very large data values. The SUBSTRING function can be used to process text and image columns, allowing T-SQL procedures to process these columns. The handling of nulls and empty strings has been improved. A new uniqueidentifier data type is provided for storing a globally unique identifier (GUID).
SQL Server 7.0 for Microsoft Windows® 95/98 and Windows NT Workstation is a fully-featured RDBMS targeted for workstation and mobile applications. Common source code for all platforms, from Windows 95/98 to clustered systems, resolves compatibility issues. Mobile clients are fully supported with merge replication and conflict resolution.
SQL Server 7.0 for Windows 95/98 is perfect for embedded applications because it provides a fully-featured database engine and core components. It enables lightweight, full-function, and low-cost applications. Features for embedded applications include:
SQL Server 7.0 for Windows 95/98 is fully-featured except for a few limitations imposed by the operating system. For example, SMP, named pipes, full-text search, asynchronous I/O, and integrated security are not supported on Windows 95/98.
Microsoft’s goals for SQL Server 7.0 are to provide leadership in distributed solutions, for a large variety of applications for replication. The replication model continues to build on the “publish and subscribe” metaphor introduced in SQL Server 6.0. New replication interfaces are available for custom heterogeneous applications.
Three major types of replication are available. The type used for an application depends upon requirements for transactional consistency, site autonomy, and the ability to partition the data to avoid conflicts.
One of the most notable new features is the immediate-updating Subscribers option, where data replicated by SQL Server 7.0 can be modified at multiple sites. This is a relatively advanced topic, with different solutions appropriate for different applications.
Replication is built directly into SQL Server 7.0 and SQL Server Enterprise Manager, and is not a separate add-on. SQL Server 7.0 replication offers many usability improvements and enhancements, making replication significantly easier to set up, administer, deploy, monitor, and troubleshoot. Wizards are included for most common replication tasks. SQL Server 7.0 also includes enhancements for Internet replication. Anonymous subscriptions and built-in support for Internet distribution simplify data replication to the Internet.
SQL Server 7.0 also includes COM interfaces that open up the store-and-forward replication services. This allows heterogeneous data providers to use the SQL Server 7.0 replication infrastructure to publish their data.
Other replication enhancements:
Full-text search support is provided for character-based data in SQL Server tables. The full-text search facilities can be used to creates special indexes of all pertinent words in selected columns of selected tables. Extensions to the Transact-SQL language exploit these indexes to support linguistic and proximity searches. Several languages are supported.
Administration of full-text indexes can be managed either through SQL Server Enterprise Manager or through a new set of stored procedures. Additional new stored procedures can be used to obtain information about full-text indexes.
SQL Server now supports Unicode data types, which makes it easier to store data in multiple languages within one database by eliminating the problem of converting characters and installing multiple code pages. Unicode stores character data using two bytes for each character rather than one byte. There are 65,536 different bit patterns in two bytes, so Unicode can use one standard set of bit patterns to encode each character in all languages, including languages such as Chinese that have large numbers of characters. Programming languages also support Unicode data types.
The fact that Unicode data needs twice as much storage space is offset by the elimination of the need to convert extended characters between code pages. In SQL Server, the new data types that support Unicode are ntext, nchar, and nvarchar. They are exactly the same as text, char, and varchar, except for the wider range of characters supported and the restrictions on storage space (for instance, 4,000 characters instead of 8,000 characters).
Databases are easily transferred from SQL Server 6.x to 7.0, by using a fully functional upgrade utility. Customers are able to quickly get up and running on the new version and take advantage of new features with minimal effect on operations.
The following scenarios illustrate how to carry out the upgrade process. Depending on how servers are set up, elements may need more than one scenario, but together they cover all the requirements.
Depending on the amount of disk space, the upgrade takes place on a single computer using a disk-to-disk Named Pipes connection or a tape drive. Upgrades can be done over a direct pipeline with enough disk space. Otherwise, the SQL Server Upgrade Wizard can export the SQL Server 6.x catalog data, objects, and databases to a tape. Disk space occupied by SQL Server 6.x is reclaimed, and the SQL Server Upgrade Wizard is run again to import and upgrade the SQL Server 6.x catalog data, objects, and databases. When the upgrade is complete, SQL Server 7.0 immediately takes over as the production server.
Install SQL Server 7.0 on one computer and then connect to another computer where the existing SQL Server 6.x installation is installed. The upgrade takes place using a Named Pipes connection to transfer data. When the upgrade is complete, SQL Server 7.0 is running on the new computer on which it is installed, and SQL Server 6.x continues to function on the existing server.
SQL Server has expanded server administration capabilities to manage multiple servers using one centralized server. In this version of SQL Server, you can:
Using SQL Server Enterprise Manager, a system administrator can define a multiserver configuration, naming one server as the master server that communicates and distributes jobs, alerts, and event messages to target servers named in the configuration. The system administrator can manage and monitor server performance and the enterprise's database from a central console running SQL Server Enterprise Manager.
Microsoft and its Data Warehousing Alliance partners have developed a set of repository extensions to provide a common development infrastructure. The repository encourages sharing of information, such as database schema, metadata, and data transformations, used in data warehousing applications.
Other metadata models will also be generated from the open design process, and the early models continue to evolve. The shared repository provides increased interoperability between the data warehousing and development tools available from many different vendors. Users benefit from easier system administration, a broader selection of compatible products, and the advanced features available from repository-based products.
The new repository extensions help expand the data warehousing market by providing an open, common infrastructure across all data warehouse and database design and analysis tool vendors. Metadata integration is one of the most important challenges that the industry must overcome to facilitate successful data warehouse and data mart implementations.
The open design review period began with the availability of preliminary specifications for data transformation services and online analytical processing (OLAP) extensions to the repository's Open Information Model. These important extensions are two of several information models being added to Microsoft Repository to facilitate the development of a broad selection of compatible products and advanced capabilities for data warehouse systems.
This phase of the open design process concluded in early 1998 with updated specifications, final input and early repository-based data warehousing product demonstrations. Information on obtaining a copy of the specifications and providing feedback during the design review period is available through the Microsoft Web site at http://www.microsoft.com/repository.
Indexing operations have been enhanced in several areas. SQL Server now uses index intersection and index union to take advantage of multiple indexes in a single query. Earlier versions of SQL Server employed no more than one index per table in a query.
If a table has a clustered index (and thus a clustering key), the leaf nodes of all nonclustered indexes use the clustering key as the row locator rather than the physical row location identifier. If a table does not have a clustered index, nonclustered indexes continue to use the physical row location identifiers to point to the data pages. When a leaf node of a clustered index is split, the nonclustered indexes do not need to be updated because the row locators are still valid.
SQL Server provides a host of new features for data warehousing and data marts. Some of these features discussed in detail in other sections include:
Designed to support online analytical processing (OLAP) applications, OLAP Services is an essential product for a wide array of enterprise solutions ranging from corporate reporting and analysis to data modeling and decision support.
OLAP Services incorporates intelligent aggregation selection, automatically choosing a subset of all possible aggregations from which the remaining aggregations can be quickly calculated when they are needed. The Aggregation Design Wizard provides additional flexibility by allowing the cube designer to specify the tradeoff between disk storage requirements and the amount of precalculated aggregation.
Cubes can be partitioned to spread data across several servers. Data is seamlessly presented to the user as if it were stored in one place. This feature enables the cube designer to make the most effective and efficient use of existing data storage facilities.
The data model provides a great deal of flexibility. DSS supports full MOLAP, ROLAP, and hybrid OLAP implementations, offering the OLAP database designer the opportunity to choose the model most appropriate to the needs of the organization. The underlying data model or data models chosen by the cube designer are invisible to the client application and its user.
PivotTable® Service is a companion to OLAP Services that provides client access to OLAP data for custom applications.
PivotTable Service runs on client workstations and enables organizations to use Visual Basic or other languages to develop custom applications that can make use of OLAP data from OLAP Services or data directly from relational databases by using Microsoft OLE DB technology. When it is used with OLAP Services, PivotTable Service automatically apportions processing and caching to the most appropriate locations and enables multiple clients to dynamically access the same cubes.
PivotTable Service can also store cubes locally on a client so you can analyze data without connecting to OLAP Services. This mobile solution enables an analyst to take the data home or on the road for analysis and presentation.
Tools for user analysis and presentation of OLAP data can be developed to use Desktop PivotTable Service. Microsoft technology such as Microsoft ActiveX® controls and Microsoft Office implementations are being created to provide powerful graphical user interfaces. The Microsoft Desktop Data Cube Service also provides interfaces that independent software vendors are using to develop third-party applications.
The TCP/IP Sockets Net-Library has been enhanced to work with Microsoft Proxy Server and provide secure communication across the Internet. Connections can be made to SQL Server through Proxy Server, providing secured access to data. Unauthorized users are prevented from connecting to private networks. This keeps sensitive data secure by controlling all the permissions and accesses to the listening port. Proxy Server is integrated with Windows NT Server user authentication. Access is blocked to restricted sites by ranges of IP addresses, domains, or individual users to ensure that Internet permissions are used appropriately.