New Features and Enhancements of SQL Server 2000

3 4

SQL Server 2000 has so many new features and enhancements that we will not be able to tell you about all of them here, but we will look at several innovations. These features make SQL Server easier to use and manage, improve SQL Server's performance, and make SQL Server 2000 an excellent database platform not only for your small-scale online transaction processing (OLTP) applications but also for your large-scale OLTP, data warehousing, and e-commerce applications. This section describes some of the more interesting new features of SQL Server 2000, and it tells you about other improvements in SQL Server and where to find more information about them.

Server Enhancements

This section describes some of the new server-side features and enhancements of SQL Server 2000. Many of these will be described in more detail in later chapters.

Extended Memory Support

SQL Server 2000 Enterprise Edition can use the Windows 2000 Address Windowing Extensions (AWE) API to support large address spaces. SQL Server supports up to 8 gigabytes (GB) of memory on a server running Windows 2000 Advanced Server and close to 64 GB of memory on a server running Windows 2000 Datacenter. Only these two operating systems support AWE; neither Windows 2000 Professional nor Windows 2000 Server supports it. Also, a new SQL Server configuration parameter, awe enabled, enables SQL Server to use AWE.

MORE INFO


See "awe enabled Option" in Books Online for more details.

Multiple Instances of SQL Server

With SQL Server 2000, you can run multiple instances of SQL Server on one computer. Each instance has its own system and user databases. Applications can connect to instances on a single computer in the same manner as they would connect to instances of SQL Server running on different computers. You create an instance by using the SQL Server installation CD. Also, one instance of either SQL Server 6.5 or 7 can run in conjunction with one or more instances of SQL Server 2000, but not both at the same time.

You can use instances to group common application tasks so that they will be served by a specific SQL Server instance. This can help reduce contention on the databases because each SQL Server instance will act independently of the others when performing tasks such as backing up data, carrying out jobs, creating indexes, updating statistics, and rebuilding indexes.

Distributed Partitioned Views

Distributed partitioned views is an exciting new feature in SQL Server 2000. This feature is valuable for database systems and Web sites that need the processing power of multiple servers to support a heavy transaction load. With this feature, you can horizontally partition a table across multiple computers running SQL Server and create a view across all member servers. The view makes it appear as if each server has a full copy of the table. Applications can reference the view and do not have to know which member server holds the data.

MORE INFO


For details and guidelines on partitioned views, see "Creating a Partitioned View" in Books Online.

Failover Clustering

Failover-clustering administration features have been much improved for SQL Server 2000. Failover setup is no longer performed by running the Failover Cluster Wizard; rather, it is now part of the SQL Server setup process. Failover clustering is easier to install, configure, and administer in SQL Server 2000 than in previous versions. The following list includes some of the new administration tasks you can perform.

  • Administer failover clustering from any node in the cluster
  • Allow one failover cluster node to fail over to any other node in the cluster
  • Reinstall or rebuild a virtual server in the cluster without affecting the other nodes in the virtual cluster
  • Specify multiple IP addresses for a virtual server
  • Add nodes to or remove nodes from the failover cluster by using the SQL Server setup
  • Specify failover or failback to or from any node in the cluster

For more information about using Microsoft Cluster Services, see Chapter 12. In that chapter, you will learn what a cluster is, when it is useful, and how to configure SQL Server for clustering.

XML Support

Extensible Markup Language (XML) is a World Wide Web Consortium (W3C) standard for representing information in a structured document form, which can be used to transport data between heterogeneous systems. SQL Server 2000 has new features that support XML functionality. Basically, you can use XML to access SQL Server through HTTP via a URL. New features that support XML include the following:

  • A FOR XML clause that you can use in SELECT statements to retrieve data as an XML document instead of in the standard rowset output
  • New system stored procedures to help manage XML data
  • XML update-grams that allow you to insert, update, and delete data in the database
  • The ability to run queries and stored procedures directly through the URL using HTTP
  • The ability to use templates and files in the URL to run multiple SQL statements
  • The OLE DB Provider, which allows XML documents to be set as command text and to return a result set as a stream

See Chapter 23 for more information about using XML to access SQL Server. In that chapter, you will also learn some Internet-related programming concepts.

Database Maintenance Operations

SQL Server 2000 has been enhanced to improve the performance of and simplify some of the database maintenance operations that the administrator performs. These enhancements include faster differential backups, parallel database consistency checks (DBCC), and parallel scanning with DBCC. Differential backups now occur in an amount of time that is proportional to the amount of data that has been modified since the last full database backup. DBCC now takes advantage of systems that have multiple CPUs by running in parallel on the CPUs, thus increasing the performance (speed) of DBCC. It also now runs without taking a shared table lock when scanning a table, which allows updates to occur on the table simultaneously with the DBCC task.

Referential Integrity

Two new clauses allow you to specify the behavior of SQL Server when you modify a column in a table that is referenced by a foreign key in another table. These clauses are ON UPDATE and ON DELETE, and they can be used in the CREATE TABLE and ALTER TABLE statements. The options for these clauses are CASCADING and NO ACTION. CASCADING with ON DELETE means that if a row is deleted from the referenced (parent) table, that delete will cascade to the foreign key table so that the row will be deleted in the foreign key table as well. CASCADING with ON UPDATE is similar. It means that an update to the referenced column data in the parent table will be cascaded so that the foreign key table is updated in the same manner. If the NO ACTION option is used with ON DELETE or with ON UPDATE, SQL Server will return an error if a referenced row is deleted or a referenced column is updated, respectively, in the parent table, and the delete or update will be rolled back.

MORE INFO


Look up "CREATE TABLE" or "ALTER TABLE" in Books Online for the syntax and for more details on these clauses.

Full-Text Searching

SQL Server 2000 includes two new features that provide greater functionality for full-text searching: change tracking and image filtering. Change tracking keeps a log of all the changes made to full-text indexed data so that the index can be updated with these changes. You can update the index manually by flushing the log on a periodic basis, or you can configure updates to occur to the index as they occur to the data by using the autopropagation option. Image filtering enables you to index and query documents that are stored in image columns by extracting textual information from the image data.

MORE INFO


For more information about full-text searching, see "Microsoft Search Service" in Books Online.

New Data Types

SQL Server 2000 includes three new data types to provide greater programming flexibility. The new data types are as follows:

  • bigint An 8-byte integer type (This is the largest integer data type.)
  • sql_variant A type that allows storage of values of different data types
  • table A type that allows applications to temporarily store results for later use

Many other data types are available in SQL Server. For details about the new data types and all other SQL Server data types, see the section "Using System Data Types" in Chapter 10.

Index Enhancements

SQL Server 2000 features some new enhancements for indexing. They provide more flexibility with indexes by allowing you to do the following:

  • Create indexes on computed columns
  • Specify the order in which indexes are created, either ascending or descending
  • Specify whether the index should be created by using parallel scanning and sorting

For information about these enhancements, see "Table Indexes" and "Parallel Operations Creating Indexes" in Books Online. For more information about indexes in general, see Chapter 17.

Administration Enhancements

Several enhancements in SQL Server 2000 are designed to help you administer SQL Server. These new features will make your job a little easier.

Log Shipping

Log shipping allows you to constantly dump and copy transaction log backups from a source server to a destination server or servers, and then load those logs onto the destination server or servers—automatically. You have, therefore, a warm standby of the database and a separate read-only system to perform queries, such as business reports, in order to remove this processing from the source server. You can configure the schedule for each step, which includes configuring delays between copies and loads of log backups.

MORE INFO


See "Log Shipping" in Books Online for more information about this topic.

Performance Analyzer

Performance Analyzer is a new tool in Enterprise Manager. (It's found in the Management folder of each server.) You can use this tool to collect performance data on an individual database or on all databases. The trace data is stored in a table, and an OLAP cube is built from that data. Applications that can read OLAP cubes can be used to view and analyze the performance data.

MORE INFO


For more details, see "Monitoring with Performance Analyzer" in Books Online.

SQL Server Profiler

SQL Server Profiler provides two new ways you can limit a trace: by time and by size of the trace file. You can also trace several new events. To find these, open Profiler and create or edit a trace file, go to the Events tab, and under Available Events, expand the new heading, Database. You will find these four new events: Data File Auto Growth, Data File Auto Shrink, Log File Auto Growth, and Log File Auto Shrink. Then expand the Performance heading and you will find these three new events: Show Plan Statistics, Show Plan All, and Show Plan Text. For more information about using Profiler, see Chapter 35.

SQL Server Query Analyzer

SQL Server Query Analyzer now includes an object browser that allows you to navigate and view database objects. To see this browser, open Query Analyzer, click Tools, and choose Object Browser. (The entire Tools menu is also new.) The browser appears on the left side of the Query Analyzer window. The other options on the Tools menu are Object Search, Manage Indexes, and Manage Statistics. Object Search allows you to search for specific objects in a database by object type, such as views, stored procedures, and user tables. The Manage Indexes and Manage Statistics options allow you to manage indexes and statistics using a graphical interface similar to the one in Enterprise Manager. In addition, the Query menu offers two new options: Show Server Trace and Show Client Statistics. For more details on using Query Analyzer, see Chapter 35.

Replication Enhancements

SQL Server 2000 enhances replication several ways. One of these is a new alternative to the immediate updating subscriber option. This new option is called queued updates. The queued updates option is specific to snapshot and transactional replication. By enabling queued updating, you allow a subscriber to modify published data locally (at the subscriber), even while the publisher is not connected to the subscriber. The transactions that perform the data modifications are queued up, sent to the publisher, and replayed asynchronously whenever the publisher does make a network connection with the subscriber. Loopback detection prevents the transactions from getting replicated back to the originating subscriber.

MORE INFO


See "Queued Updating components" in Books Online.

Another enhancement, for all types of replication, is the support for replication schema changes. You can now add columns to or drop columns from publications and subscriptions without having to drop and re-create the publications and subscriptions. Also, you can now include the schemas for views, procedures, and user-defined functions as articles in a publication.

Merge replication has new enhancements specific to it as well, listed here:

  • New conflict resolvers
  • An option to resolve conflicts interactively
  • Vertical filtering of merge publications
  • The ability to add user-defined functions to dynamic filters
  • Automated management of identity ranges at the subscriber
  • The ability to have alternate publishers when synchronizing data

See Chapter 28 for more information about merge replication.

Additional Enhancements

This chapter has by no means provided an exhaustive list of the new features in SQL Server 2000. There are also many improvements in the areas of Data Transformation Services, OLAP Services, Meta Data Services, and English Query. These are highly specific and thus will not be described in detail here. To find information about these topics, see the following topics in Books Online:

  • Data Transformation Service Enhancements
  • What's New in Analysis Services
  • What's New in Meta Data Services
  • What's New in English Query


Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net