®

 

SQL Server 7.0

Client/Server Database Management System

 

Features Guide

November 1998

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Abstract

This Features guide highlights the most important new features in Microsoft® SQL Server™ 7.0. This software release includes many additional features that are not covered in this Guide, or only covered briefly in the At a Glance section.

 


© 1998 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Microsoft, ActiveX, BackOffice, the BackOffice logo, FoxPro, JScript, PivotTable, Visual Basic, Visual C++, Visual InterDev, Visual Studio, Windows and Windows NT are either trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Other product or company names mentioned herein may be the trademarks of their respective owners.

Microsoft Corporation • One Microsoft Way • Redmond, WA 98052-6399 • USA

0698 Part no. 098-807658


Contents


What’s New in Microsoft SQL Server 7.0........................ 1

Overview                                                                                             1

SQL Server Design Goals                                                                    1

Leadership and Innovation                                                               1

Easy to Use                                                                                    1

Scalable and Reliable                                                                      2

Data Warehousing                                                                           2

Features at a Glance............................................................. 3

Important Considerations................................................ 13

Supported Platforms                                                                         13

Performance Testing and Benchmarking                                              13

Windows 95/98 Platforms                                                                   13

Features in Depth.................................................................. 14

Performance, Reliability, and Scalability                                              14

Introduction                                                                                   14

Relational Engine                                                                           14

Storage Engine                                                                              16

Utilities                                                                                              18

Backup and Restore                                                                       18

Database Consistency Checker (DBCC)                                          19

Bulk Data Load                                                                              19

Ease of Use............................................................................... 20

Introduction                                                                                       20

For the Desktop                                                                             20

For the Workgroup                                                                         20

For the Enterprise                                                                          20

Wizards                                                                                             20

Dynamic Self Management                                                                 20

Multi-site Management                                                                       21

Microsoft Management Console (MMC) Support                                 21

Alert/Response Management                                                              21

Job Scheduling and Execution                                                           22

Distributed Management Objects                                                        22

Profiling and Tuning Tools                                                                  22

SQL Server Profiler                                                                        22

Graphical Query Analyzer                                                               23

Index Tuning Wizard                                                                       23

Security                                                                                             23

Consistency and Standards Compliance                                             24

Upgrades                                                                                          24

Visual Database Tools                                                                       24

Distributed, Mobile and Embedded Databases.......... 26

Windows 95/98 and Windows NT Workstation                                      26

Replication                                                                                        26

Embedded Applications                                                                     27

Data Warehousing................................................................ 28

Overview                                                                                           28

SQL Server 7.0 Product Enhancements                                               28

OLAP Services                                                                                  28

Data Transformation Services (DTS)                                                   29

PivotTable Service                                                                             30

Microsoft Repository                                                                         30

English Query 2.0                                                                              31

Microsoft Management Console (MMC)                                              31

Universal Data Access and OLE DB                                                   31

Internet, Intranet and E-Commerce.............................. 33

Full-text Search                                                                                  33

Web Assistant                                                                                   33

Proxy Server Integration                                                                     33

Replication across the Internet                                                            34

Active Server Pages Support                                                             34

Integration............................................................................. 35

Microsoft Office 2000                                                                        35

Microsoft Visual Studio                                                                      36

Appendix A – Technical Specifications......................... 38

Maximum Sizes and Numbers                                                             38

Appendix B – Wizards............................................................. 40

 




What’s New in Microsoft SQL Server 7.0


Overview

Microsoft SQL Server 7.0 is a defining release for Microsoft’s database products, building on the solid foundation established by SQL Server 6.5. As the best database for Windows NT®, SQL Server is the RDBMS of choice for a broad spectrum of corporate customers and independent software vendors (ISVs) building business applications. Customer needs and requirements have driven significant product innovations in ease of use, scalability and reliability, and data warehousing.

SQL Server Design Goals

Leadership and Innovation

Innovations will enable SQL Server 7.0 to be a leader in several of the database industry’s fastest-growing application categories. These include e-commerce, mobile computing, branch automation, line of business applications and data marts.

We believe important areas of leadership and innovation in Microsoft SQL Server 7.0 will include:

·         First database to scale from the laptop to the enterprise using the same code base, offering 100% code compatibility.

·         First database to support auto-configuration and self-tuning.

·         First database with an integrated OLAP server.

·         First database with integrated Data Transformation Services.

·         The Data Warehousing Framework is the first comprehensive approach to the solving the metadata problem.

·         First database to provide multiserver management for large numbers of servers.

·         Wide array of replication options of any database.

·         Tight integration with Windows NT Server, Microsoft Office and the BackOffice® family.

·         Universal Data Access, Microsoft’s strategy for enabling high-performance access to a variety of information sources.

Easy to Use

Customers are looking for solutions to business problems. Most database solutions just bring multiple layers of cost and complexity. Microsoft’s strategy is to make SQL Server the easiest database for building, managing and deploying business applications. This means providing a fast and simple programming model for developers, eliminating database administration for standard operations, and providing sophisticated tools for more complex operations.

SQL Server 7.0 lowers the total cost of ownership through features like multiserver, single-console management; event-based job execution and alerting; integrated security; and administrative scripting. This release also frees up the database administrator for more sophisticated aspects of the job by automating routine tasks. Combining these powerful management facilities with new auto-configuration features, Microsoft SQL Server 7.0 is the ideal choice for branch automation and embedded database applications.

Scalable and Reliable

Customers make investments in database management systems in the form of the applications written to that database and the education that it takes for deployment and management. That investment must be protected: as the business grows, the database must grow to handle more data, transactions, and users. Customers also want to protect investments as they scale database applications down to laptops and out to branch offices.

To meet these needs, Microsoft delivers a single database engine that scales from a mobile laptop computer running the Windows® 95 or Windows 98 operating system, to terabyte symmetric multiprocessor clusters running Windows NT Server Enterprise Edition. All these systems maintain the security and reliability demanded by mission-critical business systems.

New to release 7.0 is a version designed for the growing needs of the mobile computing marketplace, with innovative new features like low memory footprint, automatic tuning and multi-site replication.

SQL Server is also the ideal choice for high-end OLTP and data warehousing systems, with scalability features like dynamic row-level locking, intraquery parallelism, distributed query and very large database (VLDB) enhancements.

Data Warehousing

Transaction processing systems remain a key component of corporate database infrastructures. Companies are also investing heavily in improving understanding of their data. Microsoft’s strategy is to reduce the cost and complexity of data warehousing while making the technology accessible to a wider audience.

Microsoft has established a comprehensive approach to the entire process of data warehousing. The goal is to make it easier to build and design cost-effective data warehousing solutions, with technologies, services, and vendor alliances.

The Microsoft Alliance for Data Warehousing is a coalition that brings together the industry’s leaders in data warehousing and applications. The Microsoft Data Warehousing Framework is a set of programming interfaces designed to simplify the integration and management of data warehousing solutions.

Product innovations in SQL Server 7.0 improve the data warehousing process, with:

·         OLAP Services is an essential component for enterprise solutions that require online analytical processing (OLAP), from corporate reporting and analysis to data modeling and decision support.

·         Data Transformation Services for importing, exporting, and transforming data.

·         Improved handling of complex queries and very large databases.

·         The Microsoft Repository, a common infrastructure for sharing information.

·         Visual Design Tools for creating and maintaining database diagrams.

·         Integrated replication, including multi-site update, for maintaining dependent data marts.

·         Integration of third-party solutions.

 

Features at a Glance


 

 

 

 

Feature

Description and Benefits

Performance, Reliability, and Scalability: Relational Engine

The relational engine improves reliability, security, and performance while scaling from mobile laptops to terabyte SMP systems.

Auto Statistics

The query optimizer manages statistics gathering, guaranteeing efficient plan evaluation. New sampling algorithms increase performance.

Big/Smart I/O

Bigger I/O sizes support high throughput rates. I/O blocks are 4x larger, pages are 8k, extents are 64k and scans use 64k blocks. Smart I/O is key technology for enhancing performance with big I/O sizes. More efficient read-aheads, physical row-order scans and parallel I/O also improve performance.

Distributed Queries

The query processor provides native support for heterogeneous distributed queries though OLE DB. Universal data access to relational and non-relational data is inherent in the query processor and native to the Transact-SQL language.

Index Utilization Strategies

New indexing strategies enable significant performance improvement. New strategies include the use of multiple indexes in a single table or multiple tables, multi/covered and join indexes, parallel index creation on the same table and automatic statistics maintenance by default.

Joins

New hash and merge join strategies improve performance for certain types of data retrieval, in addition to nested loop joins. Multiple join types can be used within a single query. The query processor recognizes certain common join types, such as star query joins, and optimizes for these specific join types.

Parallel Queries

Performance is improved with intra-query parallel execution of a single query across multiple processors. Steps in a single query are executed in parallel, delivering the optimum response time.

Query Processor

The query processor has been redesigned to better support the large databases and complex queries found in decision support, data warehousing, and OLAP applications.

Sorting

Sort speed is greatly improved especially when tempdb is on a striped disk set.

Triggers

Flexibility is improved with multiple triggers per table change and direct recursion of triggers.


 

Feature

Description and Benefits

Performance, Reliability, and Scalability: Storage Engine

Database applications can now be widely deployed due to intelligent, automated storage engine operations. Sophisticated yet simplified architecture improves performance, reliability and scalability.

Dynamic Memory

Improves performance by optimizing memory allocation and usage. Simplified design minimizes contention with other resource managers.

Dynamic Row-Level Locking

Full row-level locking is implemented for both data rows and index entries. Dynamic locking automatically chooses the optimal level of lock (row, page, multiple page, and table) for all database operations. This feature provides improved concurrency with no tuning. The database also supports the use of hints to force a particular level of locking.

Dynamic Space Management

A database can automatically grow and shrink within configurable limits, minimizing the need for DBA intervention. It is no longer necessary to pre-allocate space and manage data structures.

Large Memory Support

SQL Server version 7.0 Enterprise Edition will support memory addressing greater than 4 GB, in conjunction with Windows NT Server 5.0, Alpha processor-based systems and other techniques.

Log Manager

Simplified design improves performance for truncation, online backup, and recovery operations.

Read Ahead

Smart read-ahead logic improves performance and eliminates the need for manual tuning.

Reliability

Concurrency, scalability and reliability are improved with simplified data structures and algorithms. Run-time checks of critical data structures make the database much more robust, minimizing the need for consistency checks.

Scalable Storage

The new disk format and storage subsystem provide storage that is scaleable from very small to very large databases. Specific changes include:

Simplified mapping of database objects to files eases management and enables tuning flexibility. DB objects can be mapped to specific disks for load balancing.

More efficient space management including increasing page size from 2K to 8K, 64k I/O, lifting of the column limit, variable length character fields up to 8k, and the ability to add and delete columns from existing tables without a unload/reload of the data.

Redesigned utilities support terabyte size databases efficiently.

Unicode

Native Unicode, with ODBC and OLE DB Unicode APIs, improves multilingual support.

Performance, Reliability, and Scalability: Utilities

Utilities run much faster and have less impact on server operations. New features are designed to support the underlying database architecture and to provide more flexible system management.

Backup and Restore

Parallel backup and restore utilities scale at device speeds. Low impact on operational systems—very high server transaction processing is maintained during full on-line backup.

Bulk Data Loading

Data import/export speed is greatly improved. Now uses OLE DB and works in conjunction with the query processor to plan and optimize queries.

DBCC

Checks physical and logical consistency of database. Patented single-pass algorithm speeds performance. New features are supported and can fix some problems. New Storage Engine architecture minimizes need for DBCC, but it’s still a good practice.


 

Feature

Description and Benefits

Ease of Use and Management

Microsoft’s strategy is to make SQL Server the easiest database for building, managing and deploying business applications. This means providing a fast and simple programming model for developers, eliminating database administration for standard operations, and providing sophisticated tools for more complex operations

Administration Wizards

Many new wizards simplify advanced tasks such as creating databases, scheduling backups, importing and exporting data, and configuring replication.

DBA Profiling and Tuning Tools

New tools provide advanced profiling and tuning:

·          Profiling improves debugging by allowing the capture and replay of server activity.

·          Index tuning wizard provides guidance through the index tuning process.

·          Graphical query analyzer allows easy, in-depth query analysis.

Distributed Management Objects

Independent software vendors and corporate developers can easily develop custom management applications. The COM-based framework exposes all management interfaces for SQL Server. Automation components and custom applications can be written using Visual Basic®, Visual Basic for Applications, and Java scripting.

Dynamic Self-management

Reduced need for DBA intervention: memory and lock resources are adjusted dynamically; file sizes grow automatically; auto-tuning features guarantee consistent performance under variable load conditions.

Event/Alert Management

Enhanced ability to monitor performance, availability and security status through policy-based event management. Improved alert management provides automatic notification and recovery in response to thresholds and severity levels.

Job Scheduling and Execution

The job scheduling and execution environment is extended to allow stand-alone, multiserver, single-step, multi-step jobs and job step with dependencies. Great flexibility is provided though a variety of scripting environments: Visual Basic Scripting Edition, Java scripting, Windows NT commands and custom ODBC and OLE DB programs.

Multi-site Management

Improved power and flexibility for managing multiple servers. Drag-and-drop and single commands can be used to implement changes across groups of servers. Management is simplified through the use of a repository that maintains schema, profiles and data transformation metadata for all servers in the enterprise.

Security

Security administration is improved and simplified through better integration with Windows NT security and new server and SQL Server roles. Windows NT integration includes authentication, support for multiple groups, grant/revoke/deny model and dynamic use of groups.

Standards Compliance

Full compliance with the ANSI/ISO SQL-92 Entry Level standards. Views are included for the ANSI/ISO schema information tables as defined in SQL-92, providing a standard method for metadata examination.

Version Upgrade

Databases are easily transferred from version 6.x to 7.0, via a fully automated upgrade utility. Customers are able to quickly get up and running on the new version and take advantage of new features with minimal impact on operations.

Visual Data Modeler

New tools provide a graphical interface for building and managing schema and other database objects.


 

Feature

Description and Benefits

Replication

SQL Server 7 delivers a broad spectrum of innovative Replication technologies for building distributed business applications.

Ease of Use

Simplified user interface with wizards, improved monitoring, scripting, and troubleshooting.

Heterogeneous Support

Standard published APIs support bi-directional replication with other data providers like Oracle, DB2, Sybase and Informix. Replication to non-relational data stores is also supported via third party solutions.

Immediate Update

Changes to a Subscriber’s data can be immediately propagated to the Publisher via two-phase commit, and then to other Subscribers using Transactional or Snapshot replication.

Internet Support

Anonymous pull subscriptions allow servers on the Internet to subscribe to publications without having to register with the publisher. This model allows large numbers of servers to participate in SQL Server replication.

Merge Replication

Merge is a new replication model in which users work freely and independently. At a later time the work is combined into a single uniform result. This model is ideal for offline or disconnected applications.

Merge Replication Conflict Resolution

Methods are provided to resolve merge conflicts via priority-based resolution. A standard interface is provided to support business rule reconciliation.

Multi-site Update

Allows updates on multiple copies of the same data at different locations.

Scalability

Replication to hundreds of servers and thousands of users is supported through a streamlined architecture that reduces contention on replication tables.

Snapshot Replication

Snapshot replication takes a snapshot of the published data in the database at one moment in time. Snapshot replication requires less constant processor overhead than transactional replication because it doesn’t require continuous monitoring of data changes on source servers.

Transactional Replication

Transactional replication is the original SQL Server Publisher/Subscriber model. It uses the transaction log to monitor changes made to data. Changes are queued and then sent and applied to Subscribers.


 

Feature

Description and Benefits

Desktop and Mobile Systems

SQL Server 7.0 now scales downward to provide a fully featured RDBMS targeted for workstation and mobile applications. Common source code for all platforms¾from Windows 95 and 98 operating systems to clustered systems¾resolves compatibility issues. Mobile clients are fully supported with merge replication and conflict resolution.

Automatic Tuning

On-demand memory and disk tuning, dynamic locking and minimal tuning parameters simplify administration.

Compatibility

100% code compatibility provides the ability to use the same source code across all platforms.

Embedded Version

Independent software vendors (building sales force automation software, for example) can easily license the lightweight, full-featured, low-cost database engine and core components.

Full Featured

The same features are provided as the Server version, except for some Windows 95/98 platform limitations (multiprocessor support, asynch I/O, integrated security).

Integration with
Microsoft Access

Improved integration with the next version of Microsoft Access allows simplified development, prototyping, and upsizing for Access applications.

Lightweight Footprint

Optimized for smaller systems requiring a lightweight memory and disk footprint.

Mobile Clients and Replication

Merge replication simplifies the development of applications for mobile clients.

 


 

Feature

Description and Benefits

Data Warehousing

Microsoft has established a comprehensive Data Warehousing Framework to make it easier to build and design cost-effective data warehousing solutions through a combination of technologies, services, and vendor alliances.

Product Enhancements

A variety of enhancements improve performance and flexibility for data warehousing applications. These include changes to the storage and relational engines, utilities, replication, and administration. The new desktop edition of SQL Server allows users to do full data analysis in disconnected mode.

OLAP Services

Integrated OLAP provides fast, efficient analysis of complex information in data warehouses. SQL Server OLAP Services delivers outstanding flexibility and integration with the Windows family, while lowering the total cost of building, deploying and managing OLAP applications. Features include:

·          Tight integration with Windows NT, Office and the BackOffice family

·          Supports all forms of OLAP (relational, multi-dimensional, and hybrid)

·          Easy-to-use wizards and taskpads via Microsoft Management Console

Data Transformation Services (DTS)

DTS simplifies the process of importing and transforming data from multiple, heterogeneous sources, either interactively or automatically. Custom transformation objects can be created that integrate into third-party applications. DTS supports data lineage, making it easy to track where and when data came from.

English Query

End users are given the ability to pose questions in English instead of forming queries with SQL statements. English Query is targeted for developers of custom applications.

Microsoft Management Console (MMC)

The MMC improves integration and ease of use for data warehousing with wizards and taskpads.

PivotTable Service (PTS)

PivotTable® Service is a companion to OLAP Services that provides desktop multi-dimensional analysis. It provides superior integration with the next version of Microsoft Excel, in-memory data and query caching, and cube persistence.

Repository

The Microsoft Repository is a common, open infrastructure for data warehousing applications, with a broad set of shared capabilities for schema and metadata. Microsoft is extending the Repository with information models for schema, data transformation, scheduling and OLAP.

Universal Data Access

Universal Data Access is Microsoft’s strategy for enabling high-performance access to a variety of information sources: OLE DB and ADO that build on the wide support for ODBC.


 

Feature

Description and Benefits

Internet, Intranet and E-Commerce

SQL Server’s integration with Windows NT and Microsoft BackOffice, and enhanced features for Release 7.0, provide superior Internet, intranet, and e-commerce solutions.

Dynamic Encryption

Encrypts data automatically. Passwords, data, stored procedures; views and triggers can easily be encrypted.

Full-Text Search

SQL Server 7.0 supports a linguistic search of character data stored in the database, which operates on words and phrases, not just character patterns.

Integration—
Internet Information Server,
Site Server, Proxy Server

Provides superior integration with Windows NT Server’s Internet Information Server (IIS) and Site Server. For secure databases on the Internet, users can take advantage of SQL Server integration with Microsoft Proxy Server. This integration allows SQL Server transactions, including replication, to pass though a secure proxy server.

Internet Communications

SQL Server transactions are optimized for the bandwidth constraints imposed by Internet communications. Tabular data streams (TDS) minimize traffic on the Internet, improving performance and conserving bandwidth.

Web Assistant

The enhanced Web Assistant makes it easy to publish data to the web. Provides support for multiple queries per page.

Web-based Management

Users can easily manage servers and server resources using a browser. Web-based management supports a subset of the graphical management tools.


 

Feature

Description and Benefits

Microsoft Windows NT Integration

SQL Server is the most integrated RDMBS with the Windows NT platform, providing superior management and security.

Windows NT Server

Combining the best of an application server, a file and print server, a communications server and Internet/intranet server, Windows NT Server is designed to be easy to manage, use, and scale with your most demanding business needs.

Enterprise Edition

A comprehensive platform for deploying distributed, mission-critical database applications. Scalability and performance are improved with support for 3 GB of application memory, 8-processor systems and other enhancements. High-availability is supported with 2-node failover clustering.

Internet Information Server (IIS)

SQL Server and IIS combine to provide a powerful platform for delivering a wide range of database-driven Web and intranet applications.

Index Server

Works with IIS to index text, HTML and Microsoft Office documents. Supports full-text indexing, query by properties, query by free-text and proximity, “fuzzy” queries, advanced searches, and customizable query forms. Built-in for querying documents in seven different languages.

Microsoft Transaction Server (MTS)

A component-based transaction processing system for developing, deploying, and managing high performance, scalable, and robust enterprise, Internet and intranet server applications.

Point-to-Point Tunneling Protocol (PPTP)

Provides a way to use public data networks, such as the Internet, to create a virtual private network for connecting client PCs with servers.

Services for UNIX

Integrated suite of utilities designed to ease integration with UNIX environments. Provides core interoperability components, including resource sharing, administration, and security.

Microsoft Message Queue Server (MSMQ)

Makes it easy for application programs to communicate with other application programs quickly, reliably, and asynchronously by sending and receiving messages.


 

Feature

Description and Benefits

Microsoft BackOffice Integration

SQL Server is the BackOffice solution for scalable, reliable and high-performance relational database management system requirements.

BackOffice Server

Integrated server suite, optimized for Windows NT Server, makes it easy to develop, deploy, and manage powerful intranet and line-of-business applications. Provides the simplest, most cost-effective way to purchase and use the entire BackOffice family of products.

BackOffice Small Business Server

Provides growing businesses with the essential tools for sharing information and connecting with business partners and customers.

SNA Server

Easily and reliably Integrate IBM mainframe and AS/400 data and applications with Windows NT through the Internet and intranets.

Proxy Server

Provides Internet security with an extensible firewall and Web cache server while improving network response time and efficiency.

Site Server

A comprehensive Web site environment for enhancing, deploying, and managing rich intranet and Internet sites on Windows NT Server and Internet Information Server.

Systems Management Server

Manages Windows-based desktops and servers in the enterprise with software distribution, inventory management and control, remote diagnostics, and remote trouble-shooting.

Microsoft Exchange Server

Provides scalable and reliable Internet and intranet messaging and collaboration services. Includes support for e-mail, group scheduling, chat, news, forums, event scripting for collaboration, broad Internet standards support, and complete interoperability and migration tools for legacy e-mail systems.

 


 

Feature

Description and Benefits

Microsoft Office 2000 Integration

SQL Server’s integration with Office 2000 empowers users with easy data access and familiar desktop applications.

Microsoft Data Engine

MSDE is an enabling technology that provides local data storage for SQL Server compatible applications. It is an alternative to the Jet database engine used by Access 2000. Users can combine the ease of use of Microsoft Access with the scalability of SQL Server.

SQL Server Administration

Access 2000 allows users to perform and manage common SQL Server 7.0 administration tasks, such as replication, backup and restore, and security.

Office Web Components

Office Web Components, such as spreadsheets, charts and PivotTable views, can be linked directly to SQL Server or OLAP Services. This integration provides an easy way to distribute reports and a simple way for users to view and analyze data.

 Connectivity

Connectivity to SQL Server and other databases is improved with a client/server layer that uses OLE DB and ADO.

Developer Tools

The Office 2000 Developer Edition is the easy way for developers to access data in SQL Server databases and integrate it with custom Office-based solutions.

Microsoft Visual Studio Integration

SQL Server and Visual Studio are a powerful combination for building scalable business solutions.

Visual InterDev™

An integrated, team-based development tool for building database driven Web applications based on HTML, Script, and components written in any language.

Database Designer

Developers can visually design and modify SQL Server database tables and relationships, and create stored procedures and database functions directly within the development environment.

Stored Procedure Debugging

SQL language programming for SQL Server is simplified with the ability to remotely debug stored procedures.

Query Builder

Visual tools simplify the creation of complex SQL statements and database views against SQL Server databases.

Important Considerations


Supported Platforms

SQL Server 7.0 is supported on Windows NT 4.0 Service Pack and Windows NT 5.0 (post Beta 1 builds), on both Intel and Alpha platforms. Windows 95 and Windows 98 are supported on Intel platforms.

SQL Server OLAP Services is supported on Windows NT 4.0 Workstation and Server, on Windows NT 5.0 Beta 2 and later. It is supported on both Intel and Alpha platforms.

PivotTable Services are supported on Windows NT 4.0 Workstation and Windows NT Server, and Windows 95 and Windows 98.

Performance Testing and Benchmarking

Microsoft encourages customers to evaluate the performance of SQL Server 7.0. Customer feedback is important for performance refinements before final release of the product. Performance issues are considered at every stage of the development process, however beta development and testing is primarily focused on implementing and testing new features, not necessarily on final performance optimization.

Microsoft takes formal benchmarking very seriously, especially relational database benchmarks. For any SQL Server 7.0 pre-release software, including all beta releases, performance comparisons or raw performance data may not be published under any circumstances.

RDBMS benchmarking is unquestionably the most technically challenging type of benchmarking in the computer industry. For that reason, the SQL Server license agreement includes a database industry standard clause that prohibits the publishing of benchmark numbers without vendor permission. If you are considering the inclusion of benchmarking as part of an evaluation of final release code, it is important for you to contact us at the earliest opportunity. We are interested in working closely with organizations that are considering product evaluations of relational database management systems, to help ensure that your efforts provide the best information to your audience.

Microsoft is dedicated to providing high-performance products to customers. To help SQL Server meet this goal, a performance engineering team is dedicated to evaluating and improving product performance. The SQL Server team is proud that SQL Server consistently holds top performance and price/performance rankings on industry standard benchmarks. Results from industry standard and ISV benchmarks for SQL Server 7.0 will be released in due course.

Windows 95/98 Platforms

SQL Server 7.0 for Windows 95/98 is designed for desktop, mobile and small workgroup applications. It supports the same high level of reliability as the version for Windows NT but not the same levels of users and transaction loads.

 

Features in Depth


Performance, Reliability, and Scalability

Introduction

Performance, reliability, and scalability are areas of focus for this release. Not only is SQL Server 7.0 enhanced along these dimensions, but it is also a foundation for future improvements.

Relational Engine

Query Processor

The query processor has been redesigned to support the large databases and complex queries found in decision support, data warehouse, and OLAP applications.

The multi-phase optimizer quickly finds the optimum plan for OLTP queries, and also includes several new execution strategies that can improve the performance of complex queries.

The query processor now uses hash join, merge join, and hash aggregation techniques to scale to larger databases than those supported by the nested-loop join technique, which was the only 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 database. All indexes on a table are maintained concurrently and constraint evaluations are part of the query processor’s execution plan. These two factors simplify and speed the updating of multiple rows of a table.

Information is automatically extracted from the statistics using fast sampling. This ensures that the query processor uses the most current statistics and it reduces maintenance requirements.

A query governor protects the server from runaway queries. It has no run-time overhead.

The query governor cost limit option lets you specify an upper limit for the time in which a query can run.

The query processor uses OLE DB to communicate with the data-storage components of Microsoft 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.

Parallel Query

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 run in parallel. SQL Server 7.0 automatically determines 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, for all aspects of query execution (inserts, deletes, selects). It is enabled in one place, which is more effective than implementing parallel query multiple times in multiple operations.

Distributed Query

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 non-relational as well as relational data sources.

Distributed queries provide SQL Server users with access to data stored in:

·         Multiple SQL Servers

·         Heterogeneous databases (DB/2, Oracle)

·         File systems (images, documents, video)

·         Network sources (directories, index servers)

This approach, called Universal Access, differs from the approach of exporting the data into the database (the Universal Server approach). One of the problems of the Universal Server approach is that data is immediately out of date in the database. You must either re-export the entire data set, or find a way to export only the changed data. SQL Server distributed queries using OLE DB technology allow you to keep data stored where it currently resides, and still access all data in one query.

Indexing Operations

Indexing operations have been enhanced in several areas:

SQL Server now uses index intersection and index union to implement multiple indexes in a single query. Shared row indicators are used to join two indexes on the same table. 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 record identifier. If a table does not have a clustered index, nonclustered indexes continue to use the physical record identifiers to point to the data pages. In both cases the row locator is stable. 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. If a table does not have a clustered index, page splits do not occur.

In earlier versions of SQL Server, nonclustered indexes used physical record identifiers (page number, row number) as row locators. For example, if the leaf node of a clustered index (data page) was split, many rows were moved to a new data page, and thus had new physical record identifiers. All of the nonclustered indexes had to be updated with these new physical record identifiers, which could require a significant amount of time and resources.

Triggers

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.

Big/Smart I/O

The new page and row formats improve performance when large blocks of data are retrieved. Each I/O operation retrieves blocks that are 4x larger than in version 6.5, now pages are 8k, extents are 64k and scans use 64k blocks.

Smart I/O is even more important than simply doing big I/O. This is a collection of techniques designed to keep the disk subsystem pumping pertinent data as fast as possible. These include:

·         Read aheads – the query processor gives hints to the storage engine.

·         Physical order row scans – when determing the cost of a query, it can be more efficient to scan in physical order and do a hash join and sort the small number of resulting rows, rather than using indexes.

·         Unordered scans – when data is spread out over multiple drives, unordered scans are issued in parallel and improve retrieval speed.

·         Parallel query – uses an advanced queuing mechanism to improved performance even when data is not partitioned.

·         Cache manager – redesigned to improve performance when reading large amounts of data.

Storage Engine

Dynamic Row-level Locking

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 dynamically adjusts the resources it uses for larger databases, eliminating the need to manually adjust the locks server configuration option. It automatically chooses between page locking (preferable for table scans) and row-level locking (preferable for inserting, updating, and deleting data).

Dynamic Memory and Space Management

SQL Server version 7.0 automatically and dynamically reconfigures itself while running. If SQL Server detects increased numbers of users logged on, it can dynamically acquire additional resources such as memory. As users log off, SQL Server frees the resources back to the system. SQL Server can also automatically increase or decrease the size of a database as data is inserted or deleted.

Database administrators can control the amount of dynamic reconfiguration in each SQL Server. A small database used by someone not familiar with databases can be set up to largely configure itself. A large production database monitored by experienced database administrators can be set up to give them full control of configuration.

New Page and Row Formats

New page and row formats allow the server to easily scale from low-end to high-end systems, improving performance and manageability. The 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 8K in size, increased from 2K. 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.

Databases and Files

SQL Server 7.0 simplifies the relationship between a SQL Server database and the Windows file system, enabling greater scalability. Databases reside on operating-system files instead of on logical devices. You can create a database and all its files with a single statement or with the Enterprise Manager.

When a database is dropped, its files are also deleted. SQL Server 7.0 allows database files to expand automatically, eliminating the need for manual intervention.

Filegroups are supported. Filegroups allow data in a single database to be spread over multiple files. It is also the basis for partitions, a future enhancement to SQL Server.

Unicode

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 Microsoft SQL Server, the new data types that support Unicode are ntext, nchar, and nvarchar. They are the same as text, char, and varchar, except for the wider range of characters supported and the increased storage space used.

Data Type Changes

The maximum length of the char, varchar, binary, 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. The handling of Nulls and empty strings has been improved. A new uniqueidentifier data type is provided for storing a globally unique identifier (GUID).

Text and Image Datatypes

Text, ntext, and image values are not stored as part of the data row but in a separate collection of pages of their own. For each of these values, all that is stored in the data row is a 16-byte pointer. For each row, this pointer points to the location of data. A row containing multiple text, ntext, or image columns has one pointer for each column.

In SQL Server 7.0, the pages are logically organized in a b-tree structure, while in earlier versions of SQL Server they were linked together in a page chain. The advantage of the method used by SQL Server 7.0 is that operations starting in the middle of the string are more efficient. SQL Server 7.0 can quickly navigate the b-tree, while older versions of SQL Server had to scan through the page chain.

Individual text, ntext, and image pages are not limited to holding data for only one occurrence. A page can hold data from multiple rows; the page can even have a mix of these data types.

Utilities

SQL Server 7.0 server utilities run much faster and have less impact on server operations. A variety of new features are included to support the new database architecture and provide more flexible system management.

Backup and Restore

The Backup and Restore utilities are an important safeguard for protecting mission-critical data stored in SQL Server databases. These utilities allow the complete restoration of data after a wide range of problems such as media failure, user errors or permanent loss of a server. Backup and restore utilities are designed to meet a variety of business needs while scaling from large enterprise systems to laptops.

Backups are fast, low-impact operations, so there is no excuse for not implementing a comprehensive strategy for safeguarding data. SQL Server takes full advantage of hardware capabilities to backup and restore databases as quickly as possible. Backup and restore operations achieve maximum read/write throughput with disk and tape devices, and use all devices in parallel. In addition, online backups have minimal impact on transaction rates, so backups can commonly be run during normal operations.

Restore is designed to get systems up and running as quickly as possible with guaranteed data integrity. Since SQL Server 7.0 databases are self-describing, there is no need to create the database and associated files prior to the restore—databases are recreated automatically. This feature offers numerous benefits:

·         Files may be relocated, for example to avoid a damaged disk drive.

·         Script maintenance and manual intervention are minimized.

·         The potential for human error is greatly reduced.

·         Using information about previous backup operations, SQL Server formulates a plan to recover the damaged database in the minimum amount of time. After the plan is approved and optionally modified, the required restore and recovery operation are performed automatically. In some obvious circumstances intervention is necessary, for example when an essential backup device is offline.

·         Backup and restore support other features that can decrease the time to recover a database, including:

·         Differential database backups minimize the need to roll forward a large number of transactions. Differential backups consist of data pages that have changed since the last full backup. Restore will often be faster than applying transaction logs covering the same time period.

·         If only part of the database is damaged or lost, only the damaged files need to be restored, followed by application of the transaction log. SQL Server will roll the restored file forward to be consistent with the rest of the database at the time of failure.

·         SQL Server provides many features for management of backups:

·         Multiple backups can be stored on a single device, and descriptive information can be obtained quickly and easily.

·         SQL Server backups can share media with any application that supports Microsoft Tape Format.

·                   Backups are protected from being unintentionally overwritten, and you can verify that a backup is complete and readable at any time without restoring it.

Database Consistency Checker (DBCC)

DBCC statements check the logical and physical consistency of a database, memory usage, and performance statistics and provide other administrative functions. Running DBCC is good for peace of mind, a good standard practice and a valuable tool and is no longer considered mandatory for normal database maintenance. As customers gain expertise with SQL Server 7.0, they can run it less often and determine the schedule that works best for them.

The DBCC commands are much faster than earlier versions of SQL Server, running at near disk speed. Also, many commands have been consolidated to simplify the process of database verification.

DBCC can now repair errors and inconsistencies that it finds during verification. This can be very useful if a backup is not available. It can also be used to delay a restore until a more convenient time. It is important to remember that the repair capability does not in any way diminish the need for backups.

Bulk Data Load

Bulk data loading is significantly faster than previous versions of SQL Server and offers many new features. Efficiency and flexibility is improved because bulk data loads go through the query processor, which develops a plan just as it does for normal queries. This provides optimized, high performance bulk loading, and other features such as full constraint validation during load.

SQL Server 7.0 allows improved index maintenance strategies, such as creating multiple indexes simultaneously, that make loading data into tables more efficient than earlier versions. Speed can be greatly improved by loading data from multiple external files in parallel into the same table.

Ease of Use


Introduction

Desktop, workgroup, and enterprise environments all have their own specific requirements. At the high-end, the scalability and power of the management framework is critical. For the low-end, ease-of-use is critical. Workgroup and Enterprise customers will benefit from the ease-of-use improvements provided for the low-end.

For the Desktop

·         Eliminate the requirement for a DBA for standard operations.

·         Hide the complexity of server configuration, DBCC, maintaining index statistics, database backups.

·         Remove the need to define hardcoded configurations for the server. Configurations should adapt to the specific needs of the environment.

·         Make it easy to transition from the desktop products like Microsoft Access, FoxPro®, and Paradox to SQL Server.

For the Workgroup

·         Reduce the need for trained database administrators

·         Make part-time maintenance easier

·         Proactively monitor and resolve problems

·         Provide Wizards for normal operations to reduce learning curve

For the Enterprise

·         Provide the infrastructure for distributed management of large numbers of SQL Servers

·         Address the basic needs for moving data within their environment (Data Warehousing and Data Transformations)

·         Provide sophisticated tools to monitor, profile, and analyze server performance

·         Provides support for troubleshooting user and server problems

Wizards

Microsoft SQL Server includes many new wizards that are used to walk administrators and programmers through the steps needed to perform more complex administrative tasks. A complete list of wizards is listed in Appendix B.

Dynamic Self Management

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 overcommitting them and decreases the resources used when they are no longer needed. Earlier versions of Microsoft 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 the Enterprise Manager can create new databases and modify existing databases. Database files expand automatically, eliminating the need for administrators to issue an additional Alter statement. A new Create Database 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 (sa). The user can let the files grow automatically to lessen the administrative burden of monitoring the amount of free space in the database and manually allocating additional space.

Database devices and segments no longer reside on top of operating-system files. Rather, a database consists of two or more Windows files. Each Windows 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.

Multi-site Management

Microsoft SQL Server has expanded server administration capabilities to manage multiple servers using one centralized server. In this version of SQL Server, you can:

·         Group servers into logical functioning units, such as by departments or business units.

·         Perform cross-server transactions, such as creating an application that reviews and transfers product inventory from a remote warehouse to a central warehouse.

·         Create multi-step jobs, schedule the job, manage job step flow, and store job success or failure information at a central location.

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 Management Console (MMC) Support

The Microsoft Management Console is the new user interface and framework for BackOffice server management. This shared console provides a convenient and consistent environment for Microsoft SQL Server and other snap-in administration tools. The SQL Server MMC snap-in console is called SQL Server Enterprise Manager. The MMC point-and-click user interface is similar to Windows Explorer.

Alert/Response Management

Alerts can be defined to raise a response when an event occurs with a system or user-defined error or severity level. Alert responses are one or more of the following:

·         Sending operator notification

·         Logging the alert in the Windows NT application event log

·         Raising an SNMP trap

·         Executing a job

The SQL Server Agent monitors events in the Windows NT application event log. When an event occurs, the SQL Server Agent compares the event details with the alerts defined for the environment. If a match is found, the SQL Server Agent implements the specified alert response and writes a response message in the Windows NT application log.

System Administrators can manage alerts for stand-alone or multiserver SQL Server system through SQL Server Enterprise Manager. Administrators can also define pager schedules so that the correct DBA can be paged depending on the time of day or day of the week.

Job Scheduling and Execution

SQL Server jobs can increase system efficiency and productivity. By creating repeatable processes and procedures using the SQL Server Enterprise Manager to create jobs, resources are freed to perform other administrative tasks. Using the job features in SQL Server Enterprise Manager, the following tasks can be defined and edited:

·         Stand-alone (local) or multiserver jobs

·         Job schedules

·         Single step or multistep jobs

·         Actions to take on success or failure of each job step

Distributed Management Objects

SQL Enterprise Manager is based on a distributed management framework. This framework allows for centralized administration of all SQL Servers in an organization. It also provides COM interfaces for administration activities so that independent software vendors can write custom applications, and so users can use Visual Basic for Applications or JScript® to manage their servers. SQL Server 7.0 extends the capabilities of the framework to include self-managing components, more detailed activity monitoring and filtering, and an event model.

Profiling and Tuning Tools

SQL Server Profiler

The Server Profiler captures a continuous picture of server activity in real time. You pick the items and events that you want to monitor, including Transact-SQL statements and batches, object usage, locking, security events, and errors. SQL Server Profiler can filter these events, showing only the events that you care about. You can replay a recorded trace against the same or another server, re-executing those recorded commands. By focusing on specific events, monitoring and debugging SQL Server issues is much easier.

Graphical Query Analyzer

The SQL Server Query Analyzer is an application that is a tool for the ad hoc, interactive execution of Transact-SQL statements and scripts. Users enter Transact-SQL statements in a full-text window, execute the statements, and view the results in a window. Users can also open a text file containing Transact-SQL statements, execute the statements, and view the results in the Results window.

The Query Analyzer is extremely useful for determining how SQL Server is interpreting and working with a Transact-SQL statement. A user can:

·         Display a graphical representation of the execution plan generated for the statement.

·         Launch the Index Tuning Wizard to determine what indexes can be defined for the underlying tables to optimize the performance of the statement.

·         Display statistics about the performance of the statement.

A variety of options are available for formatting, displaying into a grid, and showing statistics. Usability is enhanced with the color-coded editor and context-sensitive help.

Index Tuning Wizard

The Index Tuning Wizard allows you to select and create an optimal set of indexes and statistics for a SQL Server database without requiring an expert understanding of the structure of the database, the workload or the internals of SQL Server.

 

The Index Tuning Wizard can:

·         Recommend the best mix of indexes for a database given a workload, by using the query optimizer to analyze the queries in the workload.

·         Analyze the effects of the proposed changes, including index usage, distribution of queries among tables and performance of queries in the workload.

·         Recommend ways to tune the database for a small set of problem queries.

·         Allow you to customize the recommendation by specifying advanced options such as disk space constraints

To build a recommendation, the wizard requires a workload. A workload consists of a SQL script or a SQL Server Profiler trace saved to a file or table containing SQL batch or remote procedure call (RPC) event classes.

If you do not have an existing workload for the Index Tuning Wizard to analyze, you can create one immediately using SQL Server Profiler. Once you have determined that the trace has captured a representative sample of the normal database activity, the wizard can analyze the workload and recommend an index configuration that will improve the performance of the database.

Security

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 Microsoft 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 are now managed using Windows NT groups. New fixed server and database roles such as dbcreator, diskadmin, and sysadmin provide more flexibility and security than the system administrator (sa) login.

Consistency and Standards Compliance

Compliance with the ANSI/ISO SQL-92 standards is improved by focusing on SQL-92 as the preferred SQL dialect. Inconsistencies in earlier versions of SQL Server are resolved, including several differences between actual and documented behaviors. In the small number of cases where a change in the default behavior may affect existing applications, options have been provided to preserve early behavior. Views are included for the ANSI/ISO schema information tables as defined in SQL-92, providing a standard way to examine metadata of a Microsoft SQL Server database.

Upgrades

Databases are easily transferred from version 6.x to 7.0, via a fully automated upgrade utility. Customers are able to quickly get up and running on the new version and take advantage of new features with minimal impact 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.

·         Side-by-side: Depending on the amount of disk space, the upgrade takes place on a single computer using a disk-to-disk named pipe connection or a tape drive. Upgrade can be done over a direct pipeline with enough disk space. Otherwise, the Version Upgrade wizard can export the SQL Server 6.x catalog data, objects, and databases to a tape backup or network share. Disk space occupied by SQL Server 6.x is reclaimed, and the Version 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.

·         Computer-to-computer: Install SQL Server 7.0 on one computer and then connect to another computer where the existing SQL Server 6.x is installed. The upgrade takes place using a named pipe connection to transfer data. When the upgrade is complete, SQL Server 7.0 immediately takes over as the production server.

Visual Database Tools

Microsoft Visual Database tools provide an environment that allows you to draw entity-relationship diagrams and design complex queries. With Microsoft Visual Database Tools, you can:

·         Connect to and explore any ODBC (Open Database Connectivity) compliant database.

·         Create and modify databases using database diagrams.

·         Design, execute, and save complex queries.

·         Add, update, and delete data stored in database tables.

·         Design objects, such as tables, triggers, and stored procedures in Microsoft SQL Server and Oracle databases.

·         Drag database objects onto a design surface, such as an HTML template form, and then drag-and-drop to bind controls to those objects.

Distributed, Mobile and Embedded Databases


Windows 95/98 and Windows NT Workstation

SQL Server 7.0 for 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.

Replication

Microsoft’s goals for SQL Server 7.0 are to provide leadership in distributed solutions, including a large variety of applications that require replication. The replication model continues to build on the publish and subscribe metaphor that was introduced in version 6.0. New replication interfaces are available for custom third-party 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.

·         Snapshot replication takes a snapshot of current data in a publication at a Publisher and replaces the entire replica at a Subscriber on a periodic basis.

·         Transactional replication distributes transactions to Subscribers as incremental changes are made.

·         Merge replication allows sites to make autonomous changes to replicated data, and at a later time, merge changes made at all sites. Merge replication does not guarantee transactional consistency, but it does allow the greatest amount of site autonomy.

One of the most notable new features is update replication, where data replicated by SQL Server 7.0 can be modified at multi-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. SQL Server 7.0 now provides completely heterogeneous data-replication services.

SQL Server 7.0 continues to use a log sniffer/data distribution engine approach, rather than the trigger-based approach of older products. This provides better performance and scalability, as well as allowing you to implement the distribution service on a separate server to minimize the impact of replication on production systems.

Embedded Applications

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:

·         Self-tuning and configuration

·         Small memory footprint

·         High performance

·         Complete compatibility with SQL Server versions

SQL Server 7.0 for Windows 95/98 is fully featured except for a few limitations imposed by the operating system. For example, SMP, asynchronous I/O, and integrated security are not supported on Windows 95/98.

Data Warehousing


Overview

Microsoft’s goal is to create a comprehensive data warehousing strategy to make it easier to design and build cost-effective data warehousing solutions with SQL Server. Microsoft is providing a comprehensive set of technologies, services, and vendor alliances to simplify, automate, and integrate the process of implementing a data warehouse and therefore reduce the total cost of system ownership.

The Microsoft’s data warehousing strategy includes the following components:

·         The Microsoft Data Warehousing Framework, an extensible set of COM-based interfaces designed to simplify the integration and management of data-warehousing solutions.

·         The Microsoft Alliance for Data Warehousing, a Microsoft-led coalition that brings together the industry’s leaders in data warehousing and applications.

·         Product enhancements to Microsoft SQL Server including enhanced query processing, information delivery, data transformation, Data Warehousing Framework interfaces, and Internet integration.

·         OLAP Services for providing fast and efficient analysis of multidimensional data

·         The Microsoft Repository, an open and common standard infrastructure for sharing schema and metadata.

SQL Server 7.0 Product Enhancements

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:

·         New join algorithms to improve speed for complex queries

·         Storage Engine optimized for VLDB

·         Utilities optimized for handling large database

·         Replication, group administration, and desktop version provide wide data distribution

·         Parallel Query Engine

In this section, details are provided for additional data warehousing services and features in the SQL Server 7.0 release including:

·         Data Transformation Services

·         OLAP Services

·         PivotTable Service

·         Visual Database Modeling Tools

·         English Query

·         Microsoft Management Console data warehousing wizards and taskpads

OLAP Services

Designed to support online analytical processing (OLAP) applications, OLAP Services is an essential component of SQL Server that serves a wide array of enterprise solutions—from corporate reporting and analysis to data modeling and decision support.

·         Intelligent aggregations provide significantly smaller databases for improved performance, and faster initial and incremental load times.

·         Flexible storage architecture equally supports MOLAP, ROLAP and hybrid OLAP. Application requirements determine storage, not OLAP vendor debates.

·         Ease of use and management lowers the cost of ownership.

·         Numerous analysis functions provide comprehensive 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 pre-calculated 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. OLAP Services supports full Multidimensional OLAP, Relational OLAP, 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.

Data Transformation Services (DTS)

Data Transformation Services provides the functionality to import, export, and transform data between Microsoft SQL Server and any OLE DB, ODBC, or text file format. Using DTS, it is possible to build data warehouses and data marts in SQL Server by importing and transforming data from multiple heterogeneous sources interactively or automatically on a regularly scheduled basis (requiring no user intervention). Custom transformation objects can be created that integrate into third-party products.

Importing and exporting data is the process of exchanging data between applications by reading and writing data in a common format. For example, DTS can import data from an ASCII text file or an Oracle database into SQL Server. Alternatively, data can be exported from SQL Server to an ODBC data source, or a Microsoft Excel spreadsheet.

A transformation is the set of operations applied to source data before it can be stored in the destination. For example, DTS allows calculating new values from one or more source fields, or even breaking a single field into multiple values to be stored in separate destination columns. Transformations make it easy to implement complex data validation, scrubbing, and enhancement during import and export.

DTS supports multi-step packages, where multiple files can be processed separately, then brought together in a single, final step. Records in a file can be broken up into multiple records in the destination, or multiple records in the source can be aggregated into single records in the destination.

DTS is also integrated with the Microsoft Repository, where it stores metadata, the Data Transformation packages and data lineage, including sources of all transformed data.

Data Transformation Services only moves schema and data between heterogeneous data sources. Triggers, stored procedures, rules, defaults, constraints, and user-defined data types are not converted between heterogeneous data sources.

PivotTable Service

Microsoft PivotTable Service is a companion to OLAP Services that provides client-side access to OLAP data for custom applications.

·         PivotTable Services client-side cache improves performance.

·         Excel PivotTables dynamic views are accelerated.

·         Mobile and disconnected analysis is supported.

Microsoft PivotTable Service runs on client workstations and enables organizations to use Microsoft 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 via 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.

Microsoft PivotTable Service can also store cubes locally on a client machine 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 end-user analysis and presentation of OLAP data can be developed to use Microsoft PivotTable Service. Microsoft technology such as ActiveX controls and Microsoft Office implementations are being created to provide powerful graphical end-user interfaces. The PivotTable Service also provides open interfaces that independent software vendors are using to develop third-party applications.

Microsoft Repository

Microsoft and its Data Warehousing Alliance partners developed a set of repository extensions to provide a common development infrastructure. The repository encourages sharing of information—such as database schema, metadata, 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 datamart 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 www.microsoft.com/repository.

English Query 2.0

The Microsoft English Query environment allows developers to turn their relational databases into English Query applications. This gives end users the ability to pose questions in English instead of forming a query with a SQL statement.

Microsoft English Query applications are created with the English Query domain editor. Information is provided about the database so that English Query can process English questions about particular tables, fields, and data.

A Microsoft English Query application can be deployed in several ways, including within a Visual Basic or Visual C++® based application, or on a Web page running on the Internet Information Server. In the Web scenario, the application front-end is created with a VBScript page, which is designed for use with Microsoft Active Server Pages server-side scripting.

Microsoft Management Console (MMC)

The MMC has extensive usability and manageability features for improving integration for data warehousing. The data warehousing task pads provide an easy method for guiding users through common tasks. OLAP Services can also be managed from MMC.

Universal Data Access and OLE DB

Universal Data Access is a platform, application and tools initiative that defines and delivers standards and technologies and is a key element in Microsoft Corporation’s foundation for application development, the Microsoft Windows Distributed interNet Applications (Windows DNA) architecture.

Universal Data Access provides high-performance access to a variety of data and information sources on multiple platforms. It features an easy-to-use-programming interface that works with practically any tool or language, using the technical skills developers already have. This is accomplished via Microsoft Data Access Components (MDAC), an integrated set of technologies including ActiveX Data Objects (ADO), OLE DB and open database connectivity (ODBC). Developers creating client/server and Web-based data-driven solutions select their choice of best-of-breed tools, applications and data sources to create complete database solutions.

The strategy of Universal Data Access is to assure open, integrated, standards-based access to all types of data—from SQL to non-SQL to even unstructured data— across a wide variety of applications, from traditional client/server to the web. Under this architecture, Active Data Object (ADO) is the high-level interface that most applications developers will use; OLE DB Providers are the data access engines or services, as well as the business logic components that these applications can use in a highly interoperable, component-based environment.

For further information on Universal Data Access and OLE DB, check the following Web sites:

http://www.microsoft.com/data

http://www.microsoft.com/data/oledb

http://www.oledb.com

Internet, Intranet and E-Commerce


Full-text Search

Full-text search support is provided as an integrated service. Previously, RDBMS users had to buy expensive additional components. The full-text search facilities can be used to create special indexes of all pertinent words in selected columns of selected tables. Extensions to the Transact-SQL language exploit these indexes in order to support linguistic and proximity searches. Several languages are supported.

With a full-text query, you can perform a linguistic search of character data in tables enabled for full-text. Unlike the Like predicate, which is used to search character patterns, a linguistic-oriented search operates on words and phrases. Also, the full-text search feature can weigh query terms and report how well a match scored or ranked against the original search term.

The Full-Text Indexing wizard simplifies creation of an indexing catalog. Administration of full-text indexes can be managed either by using extensions to the Enterprise Manager or via a new set of stored procedures.

Web Assistant

The SQL Server Web Assistant is enhanced in SQL Server 7.0 with a new Wizard and formatting option. It is an extremely easy-to-use tool for generating standard Hypertext Markup Language (HTML) files from SQL Server data.

With the SQL Server Web Assistant wizard, you can generate an HTML file once or as a regularly scheduled SQL Server task. An HTML file can be updated whenever relevant data changes by using a trigger. With this task-scheduling flexibility, and the power of Transact-SQL, you can create Web pages to:

·         Schedule a task to automatically update a Web page price list whenever a new item is added or a price is changed, thereby maintaining a dynamic inventory and price list for customers and sales staff.

·         Publish and distribute management reports including the latest sales statistics, resource allocations, or other SQL Server data.

·         Publish server reports with information about who is currently accessing the server, which locks are being held, and by which users.

·         Use extended stored procedures to publish information outside of SQL Server.

·         Use a table of favorite Web sites to publish server jump lists.

Proxy Server Integration

The Windows Sockets Net-Library has been enhanced to work with Microsoft Proxy Server and provide secure communication across the Internet. Connections can be made to Microsoft SQL Server through Microsoft 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. Microsoft Proxy Server is integrated with Windows NT Server user authentication. Access is blocked to restricted sites by ranges of IP addresses, domain, or individual users to ensure that Internet permissions are used appropriately.

Replication across the Internet

SQL Server also makes replicating data across the Internet easy with anonymous subscriptions and built-in support for Internet distribution.

Active Server Pages Support

The ODBC SQL Server 3.5 driver distributed with SQL Server 7.0 fully supports programs using Active Server Pages (ASP) and the Internet Database Connector (IDC).

Integration


Microsoft Office 2000

Office 2000 will significantly improve the viability of using Office as the front end for an enterprise-level database, making it even easier for users to get access to the data they need—no matter where it lives—and providing more options for database owners who need to distribute data.

Overview

The Microsoft Data Engine (MSDE) is an enabling technology that provides local data storage and offers compatibility with SQL Server, similar to Microsoft Jet, the data engine that currently exists in Microsoft Access.

Access 2000 will be able to act as an easy-to-use interface client to SQL Server, providing a more scalable and robust back-end solution that was previously only available to professional database administrators. This allows end users to take advantage of the ease-of-use of Access combined with the scalability and reliability of Microsoft SQL Server.

100 percent Microsoft SQL Server compatibility

Microsoft Access 2000 includes a client/server layer that uses OLE DB to provide direct Microsoft SQL Server connectivity. This lets users combine the rich form and reporting tools in Microsoft Access with the power and scalability of Microsoft SQL Server, providing easy access to important corporate data. Client/server tools to help with administration tasks and design and modification of Microsoft SQL Server tables, schema and queries are also available in Microsoft Access 2000.

Support for OLE DB and ActiveX Data Objects (ADO)

Office 2000 will improve support for connecting to back-end databases. OLE DB and ADO provide better performance against Microsoft SQL Server and other OLE DB providers. Office 2000 also takes a client/server approach to dealing with large databases, only retrieving the data the user wants to see instead of the entire data set.

Support for Microsoft SQL Server OLAP Services

OLAP Services is a new full-featured online analytical processing (OLAP) capability, provided as a component of SQL Server 7.0, that allows users to perform sophisticated analysis on large volumes of data with exceptional performance. Office 2000 may be used as a front end that opens access to corporate data to a broader audience of users. For example, users can use PivotTable dynamic views in Microsoft Excel for high performance data analysis of large amounts of data. Users can take advantage of the familiar interface of Excel combined with the scalability of multidimensional analysis stored in Microsoft SQL Server.

Office Web Components

Any of the Microsoft Office Web Components—a Spreadsheet component, Chart component and PivotTable component—can be linked directly to Microsoft SQL Server or Microsoft SQL Server OLAP Services. The components provide an interactive view of data within a browser and will be updated anytime a change is made to the linked data source. This integration provides an easy way to distribute reports, as well as a simple way for users to view and analyze the data they need.

Developers Tools

The Office 2000 Developer Edition will include the Microsoft Data Environment (DE), a user interface-based tool for connecting to Microsoft SQL Server and other OLE DB-compliant data sources. The Office 2000 Developer Edition is the version of Microsoft Office targeted at professional developers. It is the easy way for developers to access data programmatically and integrate it with a custom Office-based solution.

Microsoft Visual Studio

Database Projects

Database projects enable developers and database administrators to manage databases directly from within the Visual Studio development system. Database projects organize database designs and database-related functions, such as SQL queries, database scripts, stored procedures, and database diagrams in one central project. New with Visual Studio 6.0, developers can place a database project under source control to enable versioning of common database objects, such as SQL queries, stored procedures, and database scripts. This allows developers to easily roll back database changes to previous versions, or to quickly apply a common set of scripts across different databases to ensure they are synchronized.

Stored Procedure Editing and Debugging

Developers using Visual Studio, Enterprise Edition can view, implement, edit, and test SQL Server stored procedures using a color-coded stored procedure editor. Developers can step directly from source code executing on a calling computer to SQL Server stored procedure source code executing on a remote server computer.

Universal Data Access

Universal Data Access is Microsoft's strategy for providing high-performance access to all types of information (including relational and non-relational data) across organizations, from the desktop to the enterprise. Universal Data Access enables all Visual Studio tools to access any data source on any platform. Universal Data Access consists of three core technologies: OLE DB, Open Database Connectivity (ODBC), and ActiveX Data Objects (ADO).

All Visual Studio 6.0 tools include new Microsoft-developed OLE DB providers and/or ODBC drivers to Microsoft SQL Server, Microsoft Access, Microsoft FoxPro, Oracle, and AS/400 VSAM databases. In addition, third-party OLE DB providers and ODBC drivers are available for other database systems including Informix, Sybase, IBM DB/2, and most others. Independent software vendors can also easily create custom OLE DB providers to their own proprietary storage formats.

 

ActiveX Data Objects (ADO) is Microsoft's strategic, high-level interface to data that completely isolates developers from the underlying OLE DB and ODBC technologies. ADO supplies an open, application-level data access object model that allows corporate programmers to write database applications over OLE DB data using any language. All of the Visual Studio tools can use ADO to access data. Through ADO, developers now have access to more types of data than ever before and will need to spend far less time writing complex client/server code.


Appendix A – Technical Specifications


Maximum Sizes and Numbers

This table specifies the maximum sizes and numbers of various objects defined in Microsoft SQL Server databases, or referenced in Transact-SQL statements.

 

Object

SQL Server 6.5

SQL Server 7.0

Batch size

128K

128 *Network Packet Size

Bytes per character or binary column

255

8000

Bytes per text, ntext, or image column

2 GB-2

2GB-2

Bytes per GROUP BY, ORDER BY

900

8060

Bytes per index

900

900

Bytes per foreign key

900

900

Bytes per primary key

900

900

Bytes per row

1962

8060

Bytes in source text of a stored procedure

65025

Batch size

Clustered indexes or constraints per table

1

1

Columns in GROUP BY, ORDER BY

16

Limited only by number of bytes

Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement

10

10

Columns per index

16

16

Columns per foreign key

16

16

Columns per primary key

16

16

Columns per base table

250

1024

Columns per SELECT statement

4096

4096

Columns per INSERT statement

250

1024

Connections per client

Max. value of configured connections

Max. value of configured connections

Database size

1 TB

1,048,516 TB

Files per database

32

32,767

File size (data)

32 GB

32 TB

Object

SQL Server 6.5

SQL Server 7.0

File size (log)

32 GB

4 TB

FOREIGN KEY constraints per table

31

63

Foreign key table references per table

16

63

Identifier length (in characters)

30

128

Index key size (bytes)

900

900

Locks per connection

Max. value of locks configured

Max. value of locks configured

Nested subqueries

16

64

Nested trigger levels

16

32

Nonclustered indexes or constraints per table

249

250

Objects in a database *

2 billion

2,147,483,647

Parameters per stored procedure

255

1024

PRIMARY KEY constraints per table

1

1

Rows per table

Limited by available storage

Limited by available storage

SQL string length

128K

128 *TDS packet size

Tables per database

2 billion

Limited by number of objects in a database

Tables per SELECT statement

16

256

Triggers per table

3

Limited by number of objects in a database

UNIQUE constraints per table

249

250 nonclustered and 1 clustered

                               

* Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints.


Appendix B – Wizards


 

Database

Create Database

Guides you through the process of creating a database.

Create Index

Guides you through the process of creating an index.

Create Login

Guides you through the process of creating a logon.

Create Stored Procedures

Guides you through the process of creating stored procedures for adding, deleting, and updating rows in a table.

Create View

Guides you through the process of creating a view.

Data Transformation Services

DTS Export

Guides you through the process of creating DTS packages to export data from a SQL Server database to heterogeneous data sources. Help is included with this wizard.

DTS Import

Guides you through the process of creating DTS packages to import heterogeneous data to a SQL Server database. Help is included with this wizard.

Management

Backup

Guides you through the process of backing up a database.

Create Alert

Guides you through the process of creating an alert.

Create Job

Guides you through the process of creating a job.

Create Trace

Guides you through the process of creating a trace.

Create SQL Server Login

Guides you through the process of granting SQL Server login access to users.

Database Maintenance Plan

Guides you through the process of creating a maintenance file that can be run on a regular basis. Works with the sqlmaint utility.

Index Tuning

Guides you through the process of tuning an index.

Make Master Server

Guides you through the process of setting up a master server.

Make Target Server

Guides you through the process of setting up a target server and enlisting it into a master server.

Register Servers

Guides you through the process of registering SQL Server.

Setup

Guides you through the process of installing SQL Server.

Version Upgrade

Guides you through the process of upgrading SQL Server. The Version Upgrade wizard is not supported on the Windows 95 or Windows 98 platform.


 

Replication

Create Publication

Helps you create a publication from the data in your database.

Uninstall Publishing and Distribution

Removes replication publishing and distribution.

Configure Publishing and Distribution

Helps you specify a server to use as a Distributor and, optionally specify other replication components.

Disable Publishing and Distribution

The Disable Publishing and Distribution Wizard helps you disable publishing, distribution, or both on a server.

Push Subscription

Helps you “push” a subscription from a publication on one server to one or more servers or server groups.

Pull Subscription

Guides you through the process of enabling a publication server to push replicated data to a subscription server. Help is included with this wizard.

Miscellaneous

ODBC Driver

Guides you through the installation of an ODBC data source and ODBC driver. Tests the validity of the connection. Help is included with this wizard.

Full-Text Indexing

SQL Server text-based columns. Creates or modifies population schedules that determine when the information stored in the full-text catalog is updated.

Web Assistant

Guides you through the steps required to create a Web task that creates an HTML page, to import data from an HTML page, or to run an existing Web task.