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.
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.
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.
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.
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.
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 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.
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.
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.
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).
| Server Scalability | Distributed Query Architecture |
| Distributed Transactions Architecture |