TechRepublic's weekly backgrounders provide a quick but informative look at an IT topic of interest to our readers. This week we'll look at SQL Server 7.0.
Executive Abstract
Microsoft's SQL Server 7.0 is a significant upgrade, both for current SQL Server users and for the industry as a whole. Developers and end users will like this product, and SQL Server 7.0's market share should continue to grow.
SQL Server 7.0 is a significant database upgrade, in part, because it makes certain high-end capabilities, such as OLAP, affordable to a larger market, while upping the bar on ease of use and price/performance ratios. It is limited by its reliance on Windows NT, which does not scale as well as other operating systems and which is seen by some as too unstable for mission-critical applications. Following are the key features and benefits of this upgrade.
Integrated OLAP services
This is one of the most interesting, and perhaps the shrewdest, new feature of SQL Server 7.0. No other leading back-end database includes OnLine Analytical Processing services out of the box; previously, if you wanted it, you had to buy a third-party tool. By including OLAP in the base SQL Server 7.0 package, Microsoft is putting pressure on the other vendors and bringing a new technology to a tier of clients whom otherwise might not have considered using it.
Integrated Data Transformation Services
The Data Transformation Services (DTS) eases the use of SQL Server 7.0 as a data warehouse. DTS provides a graphical means of building and automating the import, export, and transformation of data from a variety of non-SQL Server resources. When combined with the Intelligent Agent feature, DTS positions SQL Server 7.0 as a good candidate for smaller data warehouses.
Dynamic space management
One of the most frustrating aspects of earlier versions of SQL Server was the hard-coded space definitions. The size of the database was set at creation, and while it could be changed later, it was certainly neither fun nor easy. SQL Server 7.0 now can grow and shrink databases as needed.
Replication options
The most important of these would be the new Merge Replication, which allows users to update data while disconnected from the primary database, then replicate their changes the next time they connect, with SQL Server 7.0 providing priority-based conflict resolution. This is primarily aimed, of course, at traveling users such as salespeople. These users, and others, may benefit from the SQL Server Desktop, a low-footprint, fully compatible back-end database for mobile applications that runs on a client machine. Another welcome feature is bi-directional Access replication, which enables Microsoft Access databases to replicate with SQL Server 7.0 databases.
Ease-of-use additions
One of Microsoft's key selling points for SQL Server 7.0 is its ease of use. And it has backed up that claim with a number of new or improved features, including:
- · Automatic Tuning, which eliminates some of the need for user-driven tuning work
- · Multisite Management, which accommodates not only management of distributed databases, but also multilingual global environments, and
- · The inclusion of SQL Server 7.0 in the Microsoft Management Console, thus enabling the IT professional to manage SQL Server 7.0 from within the main management tool for NT
- · New tools, wizards, and utilities
- · A new English language query capability, which enables users to submit information requests as questions and then builds a structured query from the question.
Office 2000 integration
As one would expect, SQL Server 7.0 is tightly integrated with Office 2000, the pending upgrade to the Microsoft Office suite. The primary integration features involve Access 2000. Previous versions of Access used the Jet database engine. Now, users will have a choice of using the new Jet 4.0, which ships with Office 2000, or the new Microsoft Data Engine (MSDE), which is code-compatible with SQL Server 7.0. Basically, using the MSDE with Access makes it possible to scale an Access application to SQL Server 7.0 without rewriting. Access 2000 also supports OLE DB, a new standard for data access. By using OLE DB, Access 2000 databases can connect directly to SQL Server 7.0 instead of going through Jet. (Note that OLE DB works not only with SQL Server 7.0, but also with Oracle, Thor, and JOLT.)
Pivot tables have been an Excel feature for some time. Excel users can access the SQL Server 7.0 OLAP services through Pivot Table Services. This means that users will be able to create persistent local cubes of information, linked and updated from the SQL Server OLAP. The wizards in Access 2000, such as the Report and Form Wizards, will now work against a back-end SQL Server 7.0 database. Access 2000 users will be able to create SQL Server 7.0 objects from within Access itself, including tables, views, stored procedures, and database diagrams. In addition, users will be able to administer certain SQL Server 7.0 functions from within Access 2000, including replication, backup and restore, and security.
Other software integration
As noted above, the OLE DB function works with SQL Server 7.0, Oracle, Thor, and JOLT databases. Additionally, the OLAP services in SQL Server 7.0 will work not only against data in a SQL Server 7.0 database, but also against data in Access, FoxPro, Oracle, Sybase, or Informix databases. These can be running on NT servers, as well as on UNIX and mainframe platforms.
Performance
Both in Microsoft's own tests, and in tests conducted by others, SQL Server 7.0 is significantly faster than SQL Server 6.5. The query processor has been redesigned, partially for better support of OLTP and OLAP. New join techniques are now used by the query processor. None of these join techniques were used in 6.5 and all become more critical the larger the join. If SQL Server 7.0 is running on a multiprocessor computer, it will now execute a query across those processors. Indexing operations have also been modified to be more intelligent and to improve performance.
For additional information:
www.microsoft.com (Search for SQL Server 7.0)
