Introduction to the Reviewers Guide

Overview

This Reviewers 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. For further information, additional sources are listed below.

Audience

This Guide assumes that reviewers are already familiar with the basic features of relational database management systems (RDBMS), and have some experience with previous versions of SQL Server.

Reviewers who are less familiar with RDBMS technology, especially Microsoft SQL Server, should request additional training and informational materials.

Information Organization

There are several sources of additional information for reviewers who wish to analyze the new features in SQL Server 7.0:

· Books On Line, the documentation for SQL Server 7.0, which contains both concise and in-depth sections on new features

Additional information will also be posted on Microsoft’s SQL Server Web site at www.microsoft.com/sql, including:

· A set of exercises that provide step through guidance for important product features

· Solution guides for Data Warehousing, Line of Business, Mobile and e-commerce

Product Evaluation Assistance

Contact via E-mail

For prompt assistance in reviewing of SQL Server 7.0, contact the SQL Server reviews team by e-mail:

sqlrev@microsoft.com

Reviewers’ Hotline

For a recorded status message on current beta issues, or to leave a message if you do not have immediate access to email, call the SQL Server Reviewers’ Hotline: 425-703-9654

Product Training

Product training classes for SQL Server 7.0 at Microsoft Authorized Technical Education Centers will be available in the near future. Reviewers interested in attending the classes should contact the SQL Server reviews team for further information.

Additional Information

Copies of SQL Server 6.5 are available on request, for comparison purposes.

An updated version of the Microsoft BackOffice Resource Kit will ship in early 1999. It will include a section on SQL Server 7.0 with white papers and utilities.

Customer references are available on request.

What’s New in Microsoft SQL Server 7.0

Overview

SQL Server 7.0 is a defining release for Microsoft’s database products, building on the solid foundation established by SQL Server 6.5. We believe that SQL Server is the best database for Windows NT®, and the RDBMS of choice for customers and independent software vendors who are seeking to improve productivity. Customer requirements have driven significant product innovations, especially in the areas of ease of use, scalability 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, including business operations, business intelligence, mobile workforces and electronic commerce. Important areas of leadership and innovation in Microsoft SQL Server 7.0 include:

· Scales from the laptop to the enterprise using the same code base with 100% code compatibility

· Simplified management with many new Wizards

· Automated configuration and tuning

· Integrated OLAP Services

· Integrated Data Transformation Services

· Integrated Text Search

· Wide array of replication options

· Simplified multiserver management

· Distributed and heterogeneous query capabilities

· Comprehensive strategy for simplifying, automating, and integrating the process of implementing a data warehouse

· Integration with Windows NT Server, Microsoft Office, the BackOffice® family and Visual Studio® development system

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

Easy to Use

Ease of use is important for customers who are looking for solutions to business problems. Microsoft’s strategy is to make SQL Server the easiest database for building, managing and deploying business applications. This means automating configuration and tuning, including sophisticated tools to simplify complex operations and providing a fast and simple programming model. Database developers and administrators are scarce commodities—now they are free to focus on more important tasks.

Desktop, workgroup, and enterprise environments all have their own requirements. At the high-end, scalable and powerful tools allow simplified management of large numbers of servers. Wizards and proactive monitoring tools allow administrators to handle more mid-range systems. For the low end, systems adapt to the needs of their environment, hiding the complexity of many operations.

Scalable and Reliable

Scalability and reliability is critical because customers make major investments in database management systems—in licensing and building applications, and in the education necessary for deployment and management. That investment must be protected as the business grows, so the database must grow to handle more data, transactions, users—and it must also scale 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. Deploying applications across this range of systems is now feasible because of SQL Server’s 100% compatible code base and built-in support for a spectrum of data replication needs.

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, intra-query parallelism, distributed query and very large database (VLDB) enhancements.

Data Warehousing

Data warehousing is becoming increasingly important as companies seek to improve their understanding of data, although transaction-processing systems remain critical for corporate database infrastructures. Microsoft’s strategy is to reduce the cost and complexity of data warehousing while making the technology accessible to a wider audience. Our comprehensive approach to the process of data warehousing enables customers to build cost-effective solutions via a combination of vendor alliances, services and technologies.

The Microsoft Alliance for Data Warehousing brings together the industry’s leaders, while standards driven by the Microsoft Data Warehousing Framework simplify integration and management. Product enhancements to SQL Server 7.0 provide industry leadership with enhanced query processing, information delivery, data transformation, and Internet integration. Integrated OLAP Services provide fast and efficient analysis of multidimensional data. The Microsoft Repository provides an open and common standard infrastructure for sharing schema and metadata, encouraging the integration of third-party solutions.

Integration

Microsoft Office

SQL Server’s tight integration with Microsoft Office 2000 empowers users by providing easy access to enterprise data, and with powerful data analysis tools via familiar desktop applications. Improvements include simplified connectivity from Microsoft Access to SQL Server, and Microsoft Excel PivotTable® dynamic views into multi-dimensional OLAP Services data.

Microsoft BackOffice

Microsoft BackOffice enables scalable business solutions for the Internet, intranets and legacy systems. The combination of SQL Server and the other BackOffice components offer integrated security, administration tools and a unified development model—improving productivity for end users, administrators and developers.

Microsoft Visual Studio

Visual Studio simplifies the complexity of enterprise development by offering the same set of Visual Database Tools across the entire suite, allowing developers the freedom to choose the right language for the right purpose. These tools are highly integrated with SQL Server, allowing the developer to visualize and modify database design, simplify debugging of stored procedures, automate scripting and enhance versioning capabilities.

Recent SQL Server Releases

Introduction

This section provides a concise history of recent SQL Server releases. A complete history of SQL Server’s development, from its beginnings up through release 6.5, is described in Inside Microsoft SQL Server 6.5, by Ron Soukup, published by Microsoft Press, ISBN 1-57231-331-5. Copies are available upon request.

SQL Server 6.5

· The Standard Edition of SQL Server was released in April 1996.

· The Enterprise Edition was released in December 1997. It introduced new features including support for Microsoft Cluster Server, 4 GB RAM tuning, English Query and support for systems with up to 8 processors.

· The current Service Pack for SQL Server 6.5 is SP4, released in December 1997. SP3 was released in June 1997, SP2 in December 1996, and SP1 in August 1996. SP5 is expected to be released summer 1998.

SQL Server 7.0

· Beta 1 was released in June 1997 to 200 customers. This group included a limited number of independent software vendors, book authors, courseware designers, OEMs and a few corporate accounts. No copies were made available to press or analysts. This release targeted the testing of low-level functionality and programming interfaces.

· Beta 2 was released at the end of December 1997 to 3000 customers. Coverage in corporate accounts and the ISV community was broadly increased, and international accounts were added. Press and analysts were given copies of Beta 2 at the Reviewers Workshop held on January 21 and 22, 1998.

· Beta 3 was released at the end of June 1998 to a broad audience.

· Release to manufacturing is planned for 4Q of CY 1998.

· SQL Server, SQL Server Desktop and SQL Server Enterprise will be released simultaneously.

· The SQL Server compatible Microsoft Data Engine (MSDE) will be released as a component of Microsoft Office 2000 and the Microsoft Visual Studio development system.

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 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. 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 improves concurrency without the need for tuning.
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 supports 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
Management and Ease of Use 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 administration for standard operations, and providing sophisticated tools for 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.
Enterprise Manager	SQL Server Enterprise Manager is a comprehensive administrative tool for SQL Server, greatly simplifying the process of managing few to many servers. It is a snap-in component for the Microsoft Management Console (MMC), which presents a common interface for managing server applications.
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 use 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 Microsoft Access 2000 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.
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.
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: · Support for all forms of OLAP (relational, multi-dimensional, hybrid) · Graphical management of cubes and databases · Integrated with the SQL Server Enterprise Manager via the Microsoft Management Console · Scalability with partitions · Cube optimization based on usage patterns · Easy metadata viewing
PivotTable Service	PivotTable® Service is a companion to OLAP Services that provides desktop multi-dimensional analysis. It provides superior integration with Microsoft Office 2000 with in-memory data and query caching, and cube persistence.
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.
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.
Replication	Anonymous subscriptions and built-in support for Internet distribution simplify data replication to the Internet.
Web Assistant	The enhanced Web Assistant makes it easy to publish data to the web. Provides support for multiple queries per page.

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.
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 Message Queue Services (MSMQ)	Provides loosely coupled and reliable network communications services based on a messaging queuing model. MSMQ makes it easy to build reliable applications that work over unreliable but cost-effective networks.
Microsoft Transaction Services (MTS)	A component-based transaction processing system for developing, deploying, and managing high performance, scalable, and robust enterprise, Internet and intranet server applications.
Services for UNIX	Integrated suite of utilities designed to ease integration with UNIX environments. Provides core interoperability components, including resource sharing, administration, 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.
Windows NT Server 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.

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	An 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.
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 interoperability and migration tools for legacy e-mail systems.
Proxy Server	Provides Internet security with an extensible firewall and Web cache server while improving network response time and efficiency.
SNA Server	Easily and reliably integrate IBM mainframe and AS/400 data and applications with Windows NT through the Internet and intranets.
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.

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.
Access Integration	Remote connections can be made to SQL Server, combining the rich form and reporting tools in Microsoft Access with the power and scalability of Microsoft SQL Server. Enables users to create and modify database tables, views and stored procedures. Allows management of common SQL Server 7.0 tasks, such as replication, backup and restore, and security.
Connectivity	Connectivity to SQL Server and other databases is improved with a client/server layer that uses ADO and OLE DB.
Data Access Pages	Data Access Pages are interactive, dynamically linked HTML pages that let users analyze and manipulate data from within a web browser. Data can be included within the context of the page, or linked from a SQL Server database.
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 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.
Excel Integration	The powerful data analysis features of Microsoft Excel 2000 are extended to large volumes of corporate data. Dynamic views in Excel PivotTables can be created using multi-dimensional data via SQL Server OLAP Services.
Office Web Components	Microsoft Office Web Components provide links to Microsoft SQL Server and SQL Server OLAP Services. Users get an interactive view of data within a browser and are updated when changes are made to the linked data source.
PivotTable Service	Office 2000 supports Microsoft PivotTable Service, which is a companion to OLAP Services for providing client-side access to OLAP data. In-memory data and query caching, and cube persistence enable a new generation of analysis tools.
Microsoft Visual Studio Integration SQL Server and Visual Studio are a powerful combination for building scalable business solutions.
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.
Query Builder	Visual tools simplify the creation of complex SQL statements and database views against SQL Server databases.
Stored Procedure Debugging	SQL language programming for SQL Server is simplified with the ability to remotely debug stored procedures.
Visual InterDev™	An integrated, team-based development tool for building SQL Server driven Web applications based on HTML, script, and components written in any language.

Important Considerations

Product Nomenclature

The official product name is “Microsoft SQL Server 7.0.” The Microsoft internal project name during the development process was “Sphinx.”

The official product name for the OLAP server that is integrated with SQL Server 7.0 is “Microsoft SQL Server OLAP Services.” The Microsoft internal project name during the development process was “Plato.” Other proposed names appeared in beta documentation, but were never made official.

The official name for the companion to OLAP Services that provides client-side access to OLAP data is “Microsoft PivotTable Service.“ It was previously referred to as “Data Cube Services.”

Supported Platforms

SQL Server 7.0 is supported on Windows NT 4.0 with Service Pack 4 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.

Pricing, Packaging and Licensing

This information is not included in this Reviewers Guide because it is covered separately in the following documents:

· SQL Server 7.0 Pricing Details

· SQL Server 7.0 Packaging Summary

· SQL Server 7.0 Upgrade Policy

· SQL Server 7.0 Data Sheet

Performance Testing and Benchmarking

Microsoft takes formal benchmarking seriously, especially for relational database management systems. This type of benchmarking is unquestionably the most technically challenging 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 essential 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 are publicly available.

Windows 95/98 Platforms

SQL Server 7.0 Desktop running on 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. Windows 95/98 do not support multiple processors, disk arrays and other features that are available with Windows NT.

Changes Since Beta 3

Introduction

A number of improvements and additions were made to SQL Server, the OLAP Services, DTS and other components since the Beta 3 release. Improvements and bug fixes were based on internal testing and feedback from Beta customers. Performance improvements were made to many components.

SQL Server Enhancements

Replication

· A new “Synchronize” utility is placed in the “Accessories” folder, which synchronizes all replication components on your system, including SQL Server replication.

Management Tools

· A database transfer GUI was added that allows you to transfer the DDL and data from one SQL Server 7.0 database to another SQL Server 7.0 database.

· The DTS Designer has been significantly enhanced including new tasks, scheduling, repository metadata, object transfer and data lineage.

· The Query Analyzer provides more detailed pre and post query execution plans including percent cost of statement and individual operators

· The Profiler has enhanced playback capabilities.

Relational Engine

· Automatic statistics creation is now a database option and is enabled by default.

· The new command SET STATISTICS PROFILE ON shows the query plan and returns results via multiple result sets from a single query.

Setup

· Windows NT 4.0 SP4 is required.

· Microsoft Internet Explorer 4.01 SP1 is required.

Security

· The guest user no longer appears by default in new databases because it is no longer in the model database.

OLAP Services Enhancements

Write-back Support

· Supports typical enterprise OLAP applications, such as budgeting and forecasting, that make use of the ability to write data back to the cube.

· Full transactional semantics and robust multi-user concurrency.

· Implemented using partitions, with updates into ROLAP partition.

· Updates are available immediately, and are aggregated at execution time.

· Uses SQL Server to manage the transactional requirements of update.

· Changes to the cube are immediately reflected in all rolled-up and derived cells, allowing the user to get immediate feedback on the effect of changes.

· Supports "what if" functionality, where users can test changes in the data to analyze effects without making the changes permanent.

Member Properties

· Member properties are additional “virtual” dimension properties that are saved with the cube, and can be used as if they are actual dimensions.

· The size and complexity of the cube is not increased, nor is any additional information stored in the fact tables or aggregations.

· Commonly used for filtering, sorting and execution of advanced calculations.

· Attributes can be used to contain localized (international) variations of the member names.

Distributed Query Resolution

· Intelligent architecture manages query traffic and balances workload between client and server.

· Automatically selects whether the calculation is best executed on the client or the server, based on the size and complexity of the data being accessed, improving scalability and response times.

· Supports extended functions from Microsoft Excel, Visual Basic for Applications (VBA) or any ActiveX® scripting language. Automatically detects and installs Excel and VBA function libraries, making an additional 400 functions available.

· Specialized functions may be written to extend the standard libraries.

User Interface

· Usability improvements were made based on feedback from Beta sites and Microsoft’s usability labs. For example, information displays were enhanced in the OLAP Manager.

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.

Nonclustered indexes are improved. In previous 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 memory resources. 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 allow files to be grouped together for administrative and data allocation/placement purposes. They also allow new files to be added to new disks. 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. 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. 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.

Management and 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

Management

The Microsoft Management Console (MMC) is a tool that presents a common interface for managing different server applications in a Microsoft Windows-based network. Server applications provide a component called an MMC snap-in that presents MMC users with a user interface for managing the server application. SQL Server Enterprise Manager is the Microsoft SQL Server MMC snap-in.

SQL Server Enterprise Manager is the primary administrative tool for SQL Server and provides an MMC-compliant user interface that allows users to:

· Define groups of servers running SQL Server.

· Register individual servers in a group.

· Configure all SQL Server options for each registered server.

· Create and administer all SQL Server databases, objects, logins, users, and permissions in each registered server.

· Define and execute all SQL Server administrative tasks on each registered server.

· Design and test SQL statements, batches, and scripts interactively by invoking SQL Server Query Analyzer.

· Invoke the various Wizards defined for SQL Server.

Wizards

SQL Server includes more than 25 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 logical devices. 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.

You can create a database and all its files with a single Create Database statement, or use the Enterprise Manager to graphically create new 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.

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.

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.

A C2 security evaluation project is underway with SAIC.

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 Desktop runs on Windows 95/98 and Windows NT Workstation, and 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 Desktop 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

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

OLAP Services incorporates intelligent aggregation selection. It automatically chooses 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.

Additional features include:

· Numerous analysis functions provide comprehensive data modeling and decision support

· Cube optimization based on usage patterns

· Graphical cube management and data modeling

· Review multi-dimensional data layout before committing to disk

· Integrates with Enterprise Manager via the Microsoft Management Console

· Easy metadata viewing

· Comprehensive set of OLAP tutorials for speeding the learning process

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).

One of the biggest challenges is to keep your data mart up to date with your operational systems. Data lineage allows customers to optionally track on a row by row basis when and where their data came from and how it was calculated.

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. Custom transformation objects can be created that integrate into third-party products.

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. Many independent software vendors support and share the metadata model.

Data Transformation Services moves schema, data, triggers, rules, defaults, constraints and user defined data types between SQL Servers. Only schema and data are moved 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 http://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.

Management

The Microsoft Management Console (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 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 is significantly improved for connectivity to enterprise databases. This allows users to easily access the data they need, no matter where it is located. Database application designers now have more options for distributing data to their users.

This section is only a brief summary of the new features in Microsoft Office 2000. White papers that provide an in-depth description of features are available from the Microsoft Office 2000 preview Web site at http://www.microsoft.com/office/preview/default.htm .

Microsoft Access

Microsoft Access 2000 offers developers and users increased flexibility by offering the Microsoft Data Engine (MSDE) as an alternative to an improved version of the existing Access “Jet” database engine.

MSDE is 100% code compatible with SQL Server, protecting customer investments as database needs grow. With MSDE, developers can create one solution that scales from a PC running Windows 95 or 98 and Access 2000 to multiprocessor clusters running SQL Server 7 and Windows NT Enterprise Edition.

Access 2000 improves support for connectivity to back-end databases. Remote connections can be made to SQL Server, combining the rich form and reporting tools in Microsoft Access with the power and scalability of Microsoft SQL Server.

Microsoft Excel

The powerful data analysis features of Microsoft Excel 2000 are extended to large volumes of corporate data. Dynamic views can be created with Excel PivotTables using multi-dimensional data via SQL Server OLAP Services. This provides a new method for high-performance data analysis within the familiar Excel interface.

Office Web Components

The new Spreadsheet, PivotTable, and PivotChart™ components provide the functionality of Microsoft Excel in Microsoft Internet Explorer 5.0. These components tie into a variety of data sources, such as Excel, Microsoft Access, and Microsoft SQL Server, so you can not only access information, but also edit and manipulate that data. Any data in the component can be sent "round-trip" back into the original application with the click of a button.

Developers Tools

The Office 2000 Developer Edition is targeted at professional developers who need to access data programmatically and integrate it with custom Office-based solutions. The 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.

Microsoft BackOffice

See http://www.backoffice.microsoft.com/ for further details.

Microsoft Visual Studio

Visual Studio 6.0 provides a complete suite of tools for developing solutions for multi-tier and database programming and design, and is especially suited for use with SQL Server 7.0. Visual Studio allows developers to choose the programming language that best suits the technical requirements of a specific application. For further details, see the Visual Studio Web site at msdn.microsoft.com/vstudio/.

Database Projects

Developers and database administrators are able to manage projects directly from Visual Studio. Database Projects organize database schema, queries, stored procedures, diagrams and other functions into a single entity.

Projects under source control enable versioning of database objects. Changes can be rolled back to previous versions, and common scripts can be applied 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: ActiveX Data Objects (ADO), OLE DB and ODBC. ADO is a high-level interface to data that completely isolates developers from the underlying technologies, giving corporate programmers access to many types of data without the need for 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.
DTS Import	Guides you through the process of creating DTS packages to import heterogeneous data to a SQL Server database.
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.
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.