Enterprise-Level Database Features
Microsoft® SQL Server™ 2000 includes several features that support the complex data storage needs of large Web sites and modern, enterprise data processing systems.
Distributed Query
SQL Server 2000 supports referencing heterogeneous OLE DB data sources directly in Transact-SQL statements. Distributed queries allow you to integrate data from several sources with the data in a SQL Server 2000 database.
OLE DB providers return their results as rowsets in a tabular form. SQL Server 2000 supports functions, such as OPENQUERY and OPENDATASOURCE, that return rowsets from OLE DB data sources. These functions can be used in place of a table reference in a Transact-SQL statement. You can also define linked server names that reference an OLE DB data source, and then reference tables from that data source in the FROM clause of Transact-SQL statements, just as you would reference any SQL Server table.
The distributed query capability of SQL Server 2000 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 2000 can also take advantage of OLE DB providers that publish statistics regarding the distribution of data values in the rowsets exposed by the provider. SQL Server 2000 uses this information to build intelligent queries that minimize the numbers of rows the OLE DB provider must return to SQL Server. This improves the speed of distributed query processing.
Dynamic Row-Level Locking
SQL Server 2000 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 2000 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.
Full Integrity Protection
SQL Server 2000 fully protects the integrity of its databases. All data modifications are performed in transactions, and each transaction is either wholly committed if it reaches a state of consistency, or completely rolled back if it encounters errors. If a server fails, all uncompleted transactions are automatically rolled back from all SQL Server 2000 databases when the server is restarted.
Distributed Transactions
SQL Server 2000 databases can participate in distributed transactions managed by an X/Open XA compliant transaction manager. This includes distributed transactions spanning multiple SQL Server 2000 databases, and also distributed transactions spanning heterogeneous resource managers. The OLE DB Provider for SQL Server 2000 and the SQL Server 2000 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 2000 systems or heterogeneous OLE DB data sources. SQL Server 2000 manages these distributed transactions transparently using the Microsoft Distributed Transaction Coordinator.
Replication
SQL Server 2000 replication allows you to maintain copies of data in multiple sites, sometimes hundreds of sites, using a publish-subscribe metaphor. This allows sites to locate data close to the users who most frequently access it, while keeping it synchronized with copies in other locations.
SQL Server 2000 supports three types of replication. Snapshot replication copies data or database objects as they exist at a particular time. In transactional replication, Publishers and Subscribers first synchronize their data (typically using a snapshot) and then, as data is modified on the Publisher, the modifications are transmitted to the Subscribers. Merge replication lets multiple Subscribers work autonomously with copies of a set of data, and then later merge their updated versions back to the Publisher. Merge replication supports several methods for resolving conflicts in how different Subscribers modify the same data.
Replication in SQL Server 2000 supports queued updating, which allows transactional and snapshot replication subscribers to modify published data without requiring an active network connection.
SQL Server 2000 Replication introduces transformable subscriptions, which allow subscriptions to use the flexibility and power of Data Transformation Services to map, transform, and filter replicated data.
The usability of replication has been further enhanced, making it very easy to administer. Transactional replication can now be synchronized with backing up and restoring databases, eliminating the need to reconfigure transactional replication. You can browse the Windows 2000 Active Directory for publications, subject to proper permissions. SQL Server 2000 introduces new, improved replication wizards, and supports more centralized recording of Publications and Subscriptions.
See Also
Distributed Query Architecture