In January 1998, Microsoft shipped Beta 2 of SQL Server 7.0. In conjunction with this release, Microsoft held a reviewerıs workshop at the companyıs Redmond headquarters. This was the first time that the press was allowed to see any significant portion of the product. DBMS was invited to participate, and here are some of the highlights of what we found out.
Main improvement areas in 7.0 are ease of use and management, scalability, and support for distributed, mobile, and embedded databases and data warehousing. The goal with SQL Server 7.0 is to relieve the DBA of all standard operations. Toward this goal, the company added several self-management features to the product. There is a simplified log manager, as well as parallel backup and restore utilities. Memory and lock resources are adjusted dynamically, so file sizes can grow and shrink automatically.
New multisite management capabilities let you group servers, view them as a single entity, and manage them from a single console. A repository maintains schema, profiles, and data transformation metadata for all the servers within your enterprise. The product supports bidirectional replication with DB2, Oracle, Sybase, and Informix, as well as replication to nonrelational data stores via third parties. It also offers merge replication with priority-based merge conflict resolution.
I-SQL W has been enhanced and repackaged as SQL Server Profiler. The tool lets you visually depict server activity and trace criteria, and lets you "capture" output to a screen or display. SQL Server Expert lets you view and analyze the server workload and run "what if" scenarios. It, in turn, will make recommendations to improve performance. The Index Tuning Wizard analyzes your workload and automatically recommends the best index mix. SQL Server Query Analyzer works alongside the tuning wizard to recommend indexes for specific queries. Graphical Showplan gives you a graphical display of query plans.
Security administration has been better integrated in SQL Server 7.0, with Windows NT security. Version 7.0 provides Windows NT authentication and support for multiple Windows NT groups and the Windows NT grant/revoke/deny model. It is also fully compliant with the Entry Level ANSI/ISO SQL-92.
Long lacking in previous versions, row-level locking has finally been added to 7.0, along with support for parallel queries. Smart, read-ahead logic provides dynamic row-level locking for both data rows and index entries. It automatically chooses the optimal level of lock. The server has been configured to cooperate with OS resource managers to give you full access to server resources. As such, you no longer need to preallocate space and manage extents. Dynamic memory facilities optimize memory allocation and usage. A single shared buffer pool alleviates the need to allocate different pools to different processors. Version 7.0 also has increased column and row size and I/O. Specifically, page size has been increased from 2K to 8K, the I/O has been increased to 64K, and variable length character fields can now be up to 8KB. Furthermore, the column limit has been lifted, and you can now add and delete columns from existing tables without having to unload or reload the data. This is possible because of the addition of a new "unicode" datatype, which is a global unique ID.
The query processor has been redesigned to support VLDBs and complex queries normally associated with data warehousing. You can create parallel indices and multiple triggers on the same table. New join strategies include hash, merge, and nested loop joins. The query optimizer manages the gathering of statistics. Performance is further improved with new sampling algorithms.
Microsoft will also offer a scaled-down version of SQL Server 7.0 for Windows 95/98. There is 100-percent code compatibility across all platforms; however, the product does reflect platform limitations inherent in Windows 95 and 98. For example, it will not offer SMP, integrated operating system security, or asynchronous I/O. It does, however, support merge replication, which simplifies mobile client application development. It also offers on-demand memory and disk tuning and dynamic locking, and Microsoft has added only minimal tuning parameters to make administration easier. In Beta 2 they managed to get the footprint down to a working set memory under 4MB and minimum database size 1.5 MB. It may shrink further. There will also be an embedded version of SQL Server 7.0 that will offer the same functionality as the Windows 95/98 version, minus administration tools and development environments, and that will run on all Windows platforms.
Since Microsoft bought MOLAP technology from Panorama Software Systems Inc. in 1996, there has been endless speculation on what the final Microsoft-ized product would look like and what the impact on the rest of the market would be. In conjunction with SQL Server 7.0ıs release, Microsoft will finally make its entry into the data warehousing market with its OLAP Server (code-named Plato, final name yet to be determined). The OLAP Server has a flexible storage architecture that supports MOLAP, ROLAP, and hybrid OLAP depending on the application requirements. New join algorithms improve speed for complex queries. The server also offers intelligent aggregations, an optimized storage engine for VLDBs, a parallel query engine, Data Cube Services, Data Transformation Services (DTS), and low-end visual database modeling tools.
DTS exists as a pure COM object, so it can reside anywhere on the system as a local in-memory data cache. You use DTS to import, export, and transform data between Microsoft SQL Server and any OLE DB, ODBC, or text data source, either interactively or automatically. This tool is primarily for data warehousing; however, it is also the solution for shops switching to Microsoft SQL Server from other databases for the first time. The Data Cube Service is a companion to DTS and provides multidimensional analysis of data on the desktop. You can partition cubes within a single server or across multiple servers, and a user can view data in multidimensional and relational cubes as a single entity. To improve scalability, cubes exist as "virtual cubes" that exist logically but not physically. Excel 9.0, which should ship at the same time as SQL Server 7.0, will serve as the client to OLAP Server, accelerating Excel PivotTables and supporting mobile/disconnected analysis.
Both SQL Server 7.0 and the OLAP Server access data through a native OLE DB interface. This interface gives you low-level COM-based access to data, particularly for non-RDBMS data providers. ODBC is still intended for low-level access to RDBMS data, while ADO is recommended for routine access to RDBMS data. ODBC improvements include support for distributed queries, new 7.0 datatypes, and password encryption.
SQL Server 7.0 requires an on-disk change. If you want to upgrade to SQL Server 7.0 and you know that your database has direct references to system tables, go in and remove them before you upgrade. The majority of problems in beta have been from databases that had direct references to system tables ı something you arenıt supposed to do in the first place. Microsoft made such references impossible in 7.0 by making system tables read-only virtual columns that canıt be indexed. You should also note that Microsoft wonıt be shipping any data warehouse management tools or high-end visual data modeling tools. The company will rely on third parties for the foreseeable future. Furthermore, there is still no JDBC driver in the product, so youıll have to rely on a JDBC-ODBC bridge.
Far from the shared Sybase code of 12 years ago, SQL Server 7.0 has been considerably rebuilt. Some argue that these changes are too late. Others (and I fall into this category) believe that Microsoftıs huge installed base and excellent marketing will drive SQL Server 7.0 deeper into the market, regardless of the timeliness of its functionality. The general public isnıt necessarily cutting-edge. One point that should be made is that SQL Server 7.0 and the OLAP Serverıs success hinge strongly on the industryıs adoption of OLE DB and its distributed component approach for third-party data providers. A database is only as good as the data it can manipulate, and both products were clearly designed with OLE DB in mind.
There is no definite ship date for SQL Server 7.0 or OLAP Server, but Microsoft is planning to push the products during Microsoft Tech Ed in San Diego this June. DBMS will provide feature-length coverage of both products in the coming months.