What's New in SQL Server 7.0?

New features extend SQL Server's performance & scalability

SQL Server 7.0 is the most significant release of SQL Server since the product's introduction in 1989. Two years in the making, SQL Server 7.0 has undergone profound changes in its core database engine, its management tools, and data access capabilities. Microsoft's primary goals for SQL Server 7.0 (code-named Sphinx) focus on scalability, ease-of-use, and data warehousing; SQL Server 7.0 makes significant strides in all three areas. Database administrators and systems administrators alike need to know about SQL Server 7.0's new features. For a quick look at the advantages and disadvantages of using this new release, see the sidebar "SQL Server 7.0's Pros and Cons."

Manage Databases with MMC
One of SQL Server 7.0's new features is the addition of the Microsoft Management Console (MMC) for SQL Server administration. MMC is the new standard management console for all BackOffice products because Microsoft wanted to provide a common management interface for the BackOffice suite.

SQL Server 7.0 includes an MMC snap-in component, the SQL Server Enterprise Manager (EM). Screen 1 shows this snap-in, which facilitates centralized management of multiple SQL Server systems. You can run MMC and the SQL Server EM on Windows 9x systems and Windows NT systems. Both platforms let you access all SQL Server 7.0 server and database configuration options.

SQL Server EM presents a tree view of the SQL Server objects in the left pane of the window and specific information about these objects in the right pane. SQL Server EM also provides several new management features. For example, you can directly modify table schema and data types of existing columns, and SQL Server EM will automatically generate and execute the required SQL scripts. In addition, SQL Server EM provides a new Table Open option to let you view the contents of a table, as you see in Screen 2.

Although the early beta versions of SQL Server 7.0 didn't support management of SQL Server 6.5 systems, Microsoft added this ability to later versions. However, you must install the SQL Server 6.5 client utilities and the SQL Server 7.0 client utilities to take advantage of this support.

Simplify Routine Tasks
SQL Server 7.0 introduces several administrative wizards that let you perform many common database management tasks including registering servers; creating databases, tables, indexes, stored procedures, alerts, database maintenance plans, and Web pages; setting up user security and database replication; scheduling backups; importing and exporting data; and tuning indexes. These wizards reduce the need to rely on stored procedures to perform these tasks. You select the links in the right pane of the SQL Server EM window to run most of these new wizards. Although Microsoft primarily designed these wizards to help new SQL Server administrators be immediately productive, the wizards also help experienced administrators quickly perform common tasks.

Analyze Graphical Queries
Another new and improved management tool is SQL Server 7.0's Query Analyzer. Query Analyzer is a vastly updated version of the SQL Server 6.5 ISQL/w utility. Similar to the older ISQL/w utility, Query Analyzer lets you interactively enter and execute SQL statements and create stored procedures. However, in addition to small improvements such as color-coded SQL syntax and grid output, Query Analyzer lets you graphically represent the different steps that the SQL Server's query processor uses to execute your query.Screen 3 shows a brief preview of Query Analyzer's graphical Show Plan, which shows how SQL Server processes the query.

Query Analyzer can quickly show you a lot of information about how SQL Server's query processor is working. In the top portion of Screen 3, you see an example SQL SELECT statement. The bottom portion of the screen shows you the steps the query processor performs to complete the select request. These steps read from right to left in chronological order.

In the example in Screen 3, the authors and the titleauthor tables from the pubs database provided the input on the far right. Note that different icons denote different access methods. The wide, straight green arrow on the authors table tells you that SQL Server or the query processor used an index scan to process this table; the thinner, bent green arrow on the titleauthor table means that SQL Server or the query processor used an index seek to process this table. In the middle of the screen, two nested loops ordered the data and the Compute Scaler function calculated the values the query needed to return a result set. Positioning the mouse pointer over any icon displays a pop-up menu that provides additional information about that processing step.

Other graphical management tools new to SQL Server 7.0 include the Visual Data Modeler and SQL Profiler. The Visual Data Modeler lets you graphically view and design database schema, and the SQL Profiler provides a graphical trace utility.

Increased High-End Scalability
SQL Server 7.0 includes new features to improve its high-end scalability. Microsoft increased the maximum amount of memory that SQL Server can address. Under NT 4.0, Enterprise Edition's Virtual Memory Manager (VMM), SQL Server 7.0 can address up to the NT 4.0 maximum of 4GB of RAM. With NT 5.0, SQL Server 7.0 supports 64-bit addressing, which lets SQL Server address up to 32GB of RAM on 64-bit platforms such as Alpha.

Another enhancement Microsoft added to improve SQL Server 7.0's high-end scalability is better support for symmetric multiprocessing (SMP). Although SQL Server 6.5 took advantage of multiple processors, independent tests show it can't scale past four processors. Microsoft designed SQL Server 7.0 to let it take advantage of more processors, including 8-way systems. Future issues of Windows NT Magazine will examine SQL Server's 7.0's SMP scalability in detail. In addition, SQL Server 7.0 adds support for intra-query parallelism to better support multiple processors. Intra-query parallelism is a high-performance SMP processing technique that lets one query execute on multiple CPUs. This feature lets SQL Server provide the shortest response time by maximizing the use of the available processors.

Increased Performance
According to SQL Server's designers, Microsoft had left SQL Server's internal structures unchanged since the company acquired SQL Server from Sybase. Microsoft knew it must significantly change the software to support the next generation of the product. Not only did SQL Server 7.0 need to grow bigger, it needed to grow faster. To enable these speed improvements, Microsoft made many changes to the SQL Server 7.0 core database structures and query engine. Table 1 presents a partial list of the most significant changes.

One basic change (not shown in Table 1) is the increased page size. SQL Server's page size increased from 2KB to 8KB. Microsoft also increased the extents from 2KB to 8KB and increased the basic I/O size for scans to 64KB.

Unlike previous versions, SQL Server 7.0 can back up databases online while minimizing the effect on the system's performance. Microsoft tests demonstrate that the online backup degrades performance by only about 5 percent. Another backup enhancement is the introduction of incremental database backup and restore. The new incremental backup feature lets you back up database changes instead of having to back up the entire database.

Microsoft also improved the performance of SQL Server 7.0's query processor by making the query engine smarter than it was in previous versions. Many new enhancements target complex queries and decision-support applications, and how to handle large databases better. The query processor now performs read-ahead and parallel I/O during unordered scans for greater performance. In addition, the query processor uses several new high-performance processing techniques such as multi-index filters, merge joins, and hash joins. Multi-index filters let SQL Server use index values to satisfy a data request, a process that can reduce physical I/O. If the index contains all the information that the query engine needs, the engine doesn't need to access the base table. Merge joins use multiple indexes by merging the subsets of two or more indexes on the fly to speed up complex queries. Hash joins let you join tables when no appropriate indexes are available. Hash joins are useful in ad-hoc queries. The query processor makes an initial rough pass (i.e., hash) on the file and groups the data into subsets. The query processor then checks these subsets before retrieving the data. Finally, the query processor uses a cost basis, which it calculates according to the type of request, the projected processor, and I/O requirements, to select the appropriate processing technique.

My informal testing confirms that SQL Server 7.0's performance-related enhancements are worthwhile. When I ran custom database applications on a 200MHz Pentium-based server with 96MB of memory, the applications performed noticeably better running against SQL Server 7.0 than SQL Server 6.5.

Dynamic Tuning and Memory Management
One of SQL Server 7.0's biggest enhancements is the introduction of dynamic resource management. Dynamic resource management eliminates the need to pre-allocate a given amount of NT's memory and hard disk space for SQL Server. SQL Server 6.5 required you to manually configure the amount of memory you wanted to allocate to SQL Server and the amount of disk space you wanted to assign to each database device. As your SQL Server 6.5 system grew, you manually altered the configuration settings for these resources to keep SQL Server optimized. SQL Server 7.0 eliminates the need to manually allocate system resources. SQL Server 7.0 integrates tightly with the operating system (OS) and automatically increases and decreases the amount of memory and disk space needed.

SQL Server 7.0's dynamic disk allocation also eliminates devices. In previous versions of SQL Server, you had to assign a given amount of disk storage to a device to contain your databases. This disk storage didn't dynamically change as the databases changed. SQL Server 7.0 uses OS files instead of devices to contain databases. When you define a SQL Server 7.0 database, you can specify a growth limit and a maximum size. When the database becomes full, it automatically expands by the size of the growth increment. A database can expand up to the limit of available disk space or until it reaches its maximum configured size.Screen 4 presents SQL Server 7.0's new database creation window.

The database properties in Screen 4 illustrate some of the new SQL Server 7.0 database characteristics. When you build a new database, SQL Server creates an OS file in the MSSQL7DATAdirectory. In this example, you see that creating the new winntmag database creates the file MSSQL7DATAwinntmag_Data. Likewise, SQL Server creates a file for the MSSQL7DATAwinntmag_Log transaction log when it creates the database. The options at the bottom of the window let you control how the database will dynamically allocate storage. You can control whether the database expands by MBs or by a specified percentage. You can also set maximum limits or turn off dynamic disk allocation.

Dynamic Cost-based Locking
The addition of dynamic row-level locking required extensive changes to SQL Server's core database engine. SQL Server 6.5 relied on page-level locking, which provides good performance but doesn't offer a lot of granular control in an online transaction processing (OLTP) environment. Page-level locking locks users out of a row in a database that other users aren't accessing but that is still on the same page as another row that another user's application is waiting to update. Conversely, row-level locking provides completely granular control, but its greater overhead causes worse performance than page-level locking, especially in batch update scenarios. Instead of using strictly page-level or row-level locking, SQL Server 7.0 uses a locking scheme based on cost. Query Analyzer uses a combination of required CPU and I/O resources to calculate the locking cost. Based on the Query Analyzer's cost calculation, SQL Server uses row-level locks for OLTP-type, single-row update transactions and page- or table-level locks for batch-type transactions involving multiple rows.

Win9x Support
In addition to improving SQL Server 7.0's upward scalability, Microsoft has also extended the software's reach downward with support for Win9x. This support lets you scale SQL Server applications down into a small branch office environment that consists of only a small group of Win9x systems. To optimize SQL Server 7.0 for the Win9x platform, the software incorporates a smaller footprint than previous versions. Microsoft targeted the minimum working set for SQL Server 7.0 to run in about 4MB of memory.

Unlike with some databases, SQL Server 7.0 under Win9x uses the same source code as the NT version to help maximize compatibility between implementations on the two platforms. As a result, all the SQL statements and data access methods that work with the NT implementation will work with the Win9x implementation. However, you encounter some differences between the two platforms when you run SQL Server 7.0. For example, SQL Server runs as a service under NT and runs as a standard executable under Win9x because Win9x doesn't support services. Likewise, Win9x lacks NT's security model and offers no integrated security support. As a result, Win9x must use SQL Server's built-in security; NT has the option of using SQL Server's integrated security.

SQL Server's Win9x support has led some users to speculate that SQL Server 7.0 will replace Microsoft Access as the desktop database engine. However, considering that Microsoft is currently developing Access 4.0, don't count on the SQL Server engine becoming the new desktop standard anytime soon. However, the SQL Server engine will play a big role in the upcoming version of Access. Access 98 will use both the Jet database engine and a new implementation of the SQL Server engine (code-named Stinger) natively.

Data Warehousing and OLAP
Improved data warehousing support is a major part of SQL Server 7.0. The biggest new data warehousing-related feature is the addition of Microsoft's new online analytical processing (OLAP) server, known as the Microsoft SQL Server OLAP Services. Microsoft SQL Server OLAP Services provides the multidimensional data navigation and query capabilities that decision-support applications need. Microsoft SQL Server OLAP Services supports Multidimensional (MOLAP), Relational (ROLAP) or Hybrid (HOLAP) implementations. Microsoft SQL Server OLAP Services can also integrate with Microsoft Office applications such as Excel Pivot Tables and includes the Object Linking and Embedding Database (OLE DB) for OLAP provider and ActiveX Data Object MultiDimensional (ADOMD) that enable custom access. Screen 5 presents the Microsoft SQL Server OLAP Services.

In addition to supporting OLAP, SQL Server 7.0 supports Distributed Merge Replication so mobile and offline users can work autonomously and then merge their offline information into a central database. Distributed Merge Replication and Win9x support are a potent tag-team that can take SQL Server into the small branch office environment. In this scenario, branch offices can use their local Win9x database during the day and then replicate the day's transactions to a central NT server in the main office overnight. Merge replication can take place using Remote Access Service (RAS) connections or across the Internet.

Data Transformation Services
SQL Server 7.0's new Data Transformation Service (DTS) facilitates transferring data between SQL Server and various other database systems. DTS uses an OLE DB architecture that lets DTS work with relational and non-relational data sources. You can access DTS from its wizard interface or use it procedurally from the Visual Basic Script (VBScript) or Jscript source files that it can generate. The DTS wizard steps you through the process of selecting a source and target data source and the type of conversions that you want to apply to all the data columns. Screen 6 shows the DTS column mappings screen for the authors table in the pubs database.

During the process of selecting the tables and columns, DTS automatically generates a script that an OLE-compliant language such as VB can call to perform the transformation under program control. Screen 7, page 124, shows the script generation window with an example VB script.

DTS is primarily oriented at importing and exporting data to SQL Server, but it also supports 100 percent of both the source and target data sources. As a result, you can use it to transfer data between other external data sources without involving SQL Server. For example, you can use DTS to exchange data directly between an Access database and DB2 or Oracle. Combining this feature with DTS's scripting support lets you use DTS for basic cross-platform data replication.

Data Access Enhancements
To enable database access, SQL Server 7.0 includes several OLE DB providers (e.g., the native OLE DB Provider for SQL Server, OLE DB Provider for ODBC, OLE DB Provider for Oracle, and OLE DB Provider for Jet), ADO 2.0, the ODBC 3.5 driver, and a new version of SQL Distributed Management Objects (DMO). OLE DB and the ADO object model let you perform heterogeneous queries that incorporate data from relational databases (e.g., SQL Server) and non-relational sources (e.g., Excel spreadsheets, indexed sequential access method--ISAM--databases, and mail or document management data sources).

The new SQL-DMO management framework uses a new base object that makes it incompatible with SQL-DMO applications developed using the SQL Server 6.5 object framework. Although Microsoft renamed the SQL Server 7.0 root SQL-DMO object from SQLOLE to SQLDMO, a lot of the framework is the same. I was able to convert most of my existing SQL Server 6.5 SQL-DMO applications in just a few minutes.

Microsoft English Language Query is another data access feature in the new SQL Server release. This feature lets users submit database queries without understanding SQL or other developer-oriented data access methods. With the Microsoft English Language Query, the developer uses the built-in authoring tool to map the database schema to English syntax. The end user can then use the built-up English syntax to perform database queries.

Migration Strategies
SQL Server 7.0 proves that there's no such thing as a free lunch. SQL Server 7.0's changed database structures require you to reload all your existing data into the new SQL Server 7.0 databases. Migrating data is time consuming in the best of circumstances. To help make this job easier, Microsoft provides a Migration Wizard, which can migrate all your existing 6.x database objects to 7.0. The migration process is very flexible (i.e., you can migrate all your databases at once, or you can migrate selected parts).

You must choose between two basic migration strategies. You can perform a side-by-side installation where SQL Server 7.0 runs next to an existing SQL Server 6.x installation on the same computer. Alternatively, you can perform a computer-to-computer installation where SQL Server 7.0 runs on a separate system from SQL Server 6.x. With the side-by-side installation, you can use only one version of SQL Server at a time. Fortunately, SQL Server 7.0 provides a handy Switch feature that lets you quickly switch between versions.

Regardless of which migration strategy you choose, you have two basic paths for migrating your databases: You can use the direct pipeline method or you can use media storage (i.e., tape or network share) to migrate. The direct pipeline method uses a custom OLE DB provider that's quite fast. This method takes data directly from your existing SQL Server 6.5 databases and converts it to SQL Server 7.0 format. Microsoft has reported that the throughput is greater than 1GB per hour and that the company has migrated an 80GB SAP database in about 14.5 hours. The media storage method is available in case you don't have enough disk space to support the direct pipeline method.

Stay Tuned
Touching on all of SQL Server 7.0's new features in one article is a difficult task. Most of the changes will move SQL Server from the role of department server into the role of enterprise database server. The inclusion of the Microsoft SQL Server OLAP Services and support for the Win9x platform will bring about profound changes in the database marketplace. Look for Windows NT Magazine to provide ongoing coverage of SQL Server 7.0's development and features and those of competing database products such as Oracle, DB2, and Sybase. For a list of recent articles on NT-related database products, see "Related Articles in Windows NT Magazine."