Scalability and Availability
The same Microsoft® SQL Server™ 2000 database engine operates on Microsoft Windows® 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, Windows 98, and Windows Millennium Edition. It also runs on all editions of Microsoft Windows NT® version 4.0. The database engine is a robust server that can manage terabyte-sized databases accessed by thousands of users. Additionally, when running at its default settings, SQL Server 2000 has features such as dynamic self-tuning that let it work effectively on laptops and desktops without burdening users with administrative tasks. SQL Server 2000 Windows CE Edition extends the SQL Server 2000 programming model to mobile Windows CE devices and is easily integrated into SQL Server 2000 environments.
SQL Server 2000 works with Windows NT and Windows 2000 failover clustering to support immediate failover to a backup server in continuous operation. SQL Server 2000 also introduces log shipping, which allows you to maintain a warm standby server in environments with lower availability requirements.
Same Server Across Windows 2000, Windows NT, Windows 98, and Windows Millennium Edition Platforms
The same programming model is shared in all environments, because the SQL Server 2000 database engine runs on Windows NT Workstation, Windows NT Server, Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, Windows 2000 Datacenter Server, Windows 98, and Windows Millennium Edition.
In general, an application written for an instance of SQL Server 2000 operating in one environment works on any other instance of SQL Server 2000. The Microsoft Search service is not available on the Windows NT Workstation, Windows 2000 Professional, Windows Millennium Edition, or Windows 98 operating systems. SQL Server databases on those platforms do not support full-text catalogs and indexes. Applications running on these operating systems can, however, make use of the full-text capabilities if they connect to an instance of SQL Server 2000 on a different computer that supports them.
The differences in the behavior of SQL Server 2000 when running on the different operating systems are due mainly to features not supported by Windows Millennium Edition or Windows 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 instances of SQL Server running on Windows Millennium or Windows 98 from supporting the same levels of performance as are possible for instances of SQL Server on Windows NT or Windows 2000. Instances of SQL Server on Windows Millennium Edition or Windows 98, however, do not support failover clustering and cannot publish transactional replications.
Federated Database Servers
SQL Server 2000 introduces support for updatable, distributed partitioned views. These views can be used to partition subsets of the rows in a table across a set of instances of SQL Server, while having each instance of SQL Server operate as if it had a full copy of the original table. These partitioned views can be used to spread the processing of one table across multiple instances of SQL Server, each on a separate server. By partitioning all, or many, of the tables in a database, this feature can be used to spread the database processing of a single Web site across multiple servers running SQL Server 2000. The servers do not form a cluster because each server is administered separately from the others. Collections of such autonomous servers are called federations of servers. Federations of servers running SQL Server 2000 are capable of supporting the growth needs of the largest Web sites or enterprise database systems that exist today.
To improve the performance and scalability of federated servers, SQL Server 2000 supports high-speed system area networks such as GigaNet.
Very Large Database Improvements
SQL Server 2000 has high-speed optimizations that support very large database environments. SQL Server version 6.5 and earlier can support databases from 200 GB through 300 GB. SQL Server 2000 and SQL Server version 7.0 can effectively support terabyte-sized databases.
The Transact-SQL BACKUP and RESTORE statements are 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 differential backups that back up only the data changed after the last backup, or by backing up individual files or file groups. In SQL Server 2000, the time required to run a differential backup has been improved, making it proportional to the amount of data modified since the last backup.
Multiple bulk copy operations can be performed concurrently against a single table to speed data entry. The database console command utility statements are implemented with reduced locking requirements and support for parallel operations on computers with multiple processors, greatly improving their speed.
Operations that create multiple indexes on a table can create them concurrently.
SQL Server 2000 databases map directly to Windows files, simplifying the creation and administration of databases. The database page size is 8-KB, and the size of extents increases to 64 KB, which results in improved I/O.
Improved Query Optimizer
The SQL Server 2000 query optimizer has new access methods to increase the speed of query processing. These improved access methods are often matched to improvements and simplifications in the on-disk data structures in the database:
- The 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 query optimizer natively supports the prepare/execute model of executing SQL statements. When an application executes an SQL statement, the optimizer has efficient algorithms for determining if the same statement has already been executed by any application. If the optimizer finds an existing execution plan for the statement, it saves processing resources by reusing the existing plan instead of compiling a new plan. 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 multiprocessors, or CPUs, SQL Server 2000 can build parallel execution plans that split the processing of a 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.
Large Memory Support
SQL Server 2000 Enterprise Edition uses the Microsoft Windows 2000 Address Windowing Extensions API to support memory approaching 64 GB of RAM. This allows SQL Server 2000 Enterprise Edition to cache large number of rows in memory, which reduces overhead and speeds its ability to process queries.
Indexed Views
The SQL Server 2000 relational database engine supports creating indexes on views. The result set of the index is materialized at the time the index is created, and is maintained as the underlying base data is modified. Creating an index on a view that performs complex calculations on large amounts of data can speed subsequent queries by orders of magnitude. The performance benefits are not limited to queries that specify the indexed view in their FROM clause, the performance benefits apply to any query that references data covered by the indexed view. This means existing queries can realize performance gains from using the view without having to be recoded to explicitly reference the indexed view. Indexed views substantially improve the performance of large, complex reporting applications that access SQL Server databases.
High Availability
SQL Server 2000 can maintain the extremely high levels of availability required by large Web sites and enterprise systems.
SQL Server 2000 carries forward the SQL Server 7.0 architecture, which has proven to be robust in high-volume Web sites and enterprise systems.
SQL Server 2000 has improved support for Windows NT and Windows 2000 failover clustering. Support for setting up failover clustering is now implemented as a Setup option that is much easier to use than earlier versions of Microsoft SQL Server. SQL Server 2000 also supports up to four nodes in a failover cluster.
SQL Server 2000 introduces log shipping for Web sites and enterprise systems that do not require immediate failover support and can potentially lose some updates. You can create a production database, copy it to a warm standby server, and then use log shipping to feed transaction logs from the production server to the standby at set intervals, such as every 10 minutes. By restoring the logs on the standby, you create a server that can replace the production server in case of a problem. The only data that might be lost would be any modifications made since the last set of logs shipped to the warm standby server. Log shipping can also be used to copy data from a production server to one or more read-only reporting servers, assuming the reporting systems do not have to be kept exactly synchronized with the production server.
See Also
Designing Federated Database Servers