SQL Server 7.0 boasts features, power

Microsoft Corp. has packed new features and significant performance enhancements into SQL Server 7.0, which was released with much fanfare last fall. After testing the software for two months, we found it to be a solid choice for any government agency looking for a powerful Windows NT database server. And with its upgrade wizards and tools, it's an easy and obvious upgrade for organizations running SQL Server 6.5.

SQL Server 7.0 is truly a major new release of the company's flagship database. Every area of the product has been enhanced in some way. Many of the underlying components were rewritten from scratch. As a result, SQL Server 7.0 is more user-friendly and capable.

Beyond the basic functionality, Microsoft has added new features for which most other database vendors charge extra. Online analytical processing (OLAP), a query optimizer and a full-text search engine are part of the shipping SQL Server 7.0 product. You also get an entire suite of profiling and tuning tools that make optimizing the performance of your database as simple as following a cookbook.

Performance also has been enhanced. In benchmark tests comparing SQL Server 7.0 with SQL Server 6.5, we found notable speed improvements in online transaction processing (OLTP) and Decision Support System (DSS) queries.

Features

SQL Server 7.0 has many new features that improve the product in terms of performance and usability. In the performance area, Microsoft has added dynamic row-level locking, a redesigned query processor and a number of profiling and tuning tools that make it easy to tune SQL Server 7.0 to a specific environment. In the usability area, new wizards, such as the Web Publishing wizard, make it a snap to perform common tasks, such as exporting data to a World Wide Web page. There are also new utilities, such as Data Transformation Services, which lets you easily import and export data to a wide variety of other formats.

DTS provides all the tools necessary to move data in and out of an SQL Server 7.0 database. To test the functionality of DTS, we used a flat file containing more than 78,000 records of ZIP code information. The DTS wizard walks you through the entire process, prompting you for everything it needs to know to import or export your data. Before you execute a task, you're given the option of saving the DTS package that you've created in case you want to do the same thing again.

Once you've answered all the questions, the system starts importing or exporting the data. For our sample case, the system imported the entire 78,000-plus-record file in a little more than 71 seconds for a 200 MHz server, 25 seconds for a 300 MHz desktop system and 58 seconds for a 133 MHz laptop.

The DTS wizard supports direct import/export of Oracle databases as well as Access, Excel, FoxPro, Paradox, dBase and Text files. A provision allows for the inclusion of scripting code if you need to perform an operation on the data, such as converting fields from one type to another or selecting records based on a particular query.

The Web Assistant wizard lets you push data from an SQL Server 7.0 database out to the Web. A supplemental dialog requires you to select how you want the wizard to update the data by choosing either on demand, at a regular scheduled time or periodic interval, or only when something in the database changes. Finally, the wizard will automate the creation of the Web page for you.

Performance

SQL Server 7.0 offers significant gains in speed over the previous version, although the performance may not knock your socks off. Indeed, we expect most users will upgrade to SQL Server 7.0 for reasons other than faster performance. Specifically, in our testing of OLTP, Version 7.0 processed nearly 10 percent more transactions than Version 6.5 in the same period of time. Version 7.0 also offers solid gains in DSS queries. Our test queries showed Version 7.0 to deliver results about 35 percent faster when the server workload was light but, understandably, not as fast when the server workload was heavy.

Bear in mind, however, that database performance varies significantly, depending upon the platform used as well as the specific queries and transactions performed. In other words, while users can expect decided performance improvement with Version 7.0, the specific amount of improvement will vary.

Application Development

Microsoft's application development platform of choice is the company's recently released Visual Studio 6.0 package. This product includes Visual Basic 6.0, Visual C++ 6.0, Visual J++ 6.0, Visual Interdev and Visual FoxPro. All the products have been tied closely to the SQL Server 7.0 architecture, and they provide a solid base from which to develop database applications.

To test how easy it is to create a simple program to access an SQL Server 7.0 database, we used Visual Basic 6.0 and the Database Project wizard. After making a connection to the server, we were able to browse the ZIP code database that we had previously loaded with DTS, using the visual database tool. From there, it was a simple task to build a program for browsing and searching the database. The total time spent connecting to the server and building the simple application was less than 15 minutes. We liked the ability to make changes to the SQL Server database from within the Visual Basic environment without having to go to the SQL Server management console.

Microsoft also is pushing the new Office 2000 Developer's Edition as a tool for creating SQL Server 7.0 applications. Office 2000 offers much tighter integration with SQL Server 7.0, particularly in Access and Excel.

SQL Server 7.0 provides tools that let you build generic database functionality as in stored procedures, queries and data transformation packages. If, however, you want to build custom user interfaces and reports, you'll have to go to either Visual Studio or the new Office 2000.

Administration

All administration tasks use the SQL Server Enterprise Manager console. We were able to do every task we could think of through the Enterprise Manager console, including such things as creating and modifying table structures, viewing tables, testing query statements, and importing and exporting data.

The management folder in the tree under the current server contains a number of management functions. SQL Server Agent provides the mechanism to define alerts, manage database operators and schedule database jobs. The alerts allow you to define conditions that will generate an alert or warning. Each alert can generate a console message and an automatic page of a specified operator.

Microsoft has added several wizards to help guide the administrator through tasks such as database maintenance. The Database Maintenance wizard steps you through the process of creating a database maintenance plan for running integrity checks, updating database statistics and performing backups. We found the wizards to be helpful and complete.

Security

Database security in SQL Server 7.0 works with the Windows NT security system much better than it did in SQL Server 6.5. Windows NT groups and users now are directly supported in the SQL Server 7.0 security framework. You can manage all database security through the operating system instead of separately.

SQL Server 7.0 also works in conjunction with Microsoft's Proxy Server to deliver secure database transactions over the Internet. SQL Server 7.0's remote database connectivity features and Proxy Server provide a way to use the Internet to link databases securely.

You also can implement remote replication in the same manner and feel confident that the process will be accomplished with strong security.

Installation and Configuration

We installed the product on three platforms - a server, a desktop and a notebook - to evaluate SQL Server 7.0's claims of scalability.

First, we loaded the product on a Pentium 200 MHz MMX server with 128M of memory and 4.5G of disk space to evaluate the full installation on a Windows NT 4.0 Server system. The entire process took less than 20 minutes and went very smoothly.

Next, we installed the product on a desktop machine with a Cyrix/IBM 200 MHz Pentium, 64M of memory and a 6G disk drive running Windows 98. This installation was a little more problematic. The first attempt ended with a message that the system could not start the server. After discussions with Microsoft, we found out that the software had a problem identifying the CPU as a Pentium processor. As it turns out, the Cyrix/IBM P200 chip is not a true Pentium chip, and the SQL Server 7.0 documentation says the product only works on Pentium-based computers.

For our next attempt, we installed the product on a Compaq Computer Corp. Deskpro Pentium II 300 MHz system with 32M of RAM running Windows 98, and it ran without incident.

Finally, we installed the product on a Dell Computer Corp. 133 MHz Pentium laptop with 40M of memory and a 2G hard disk. This installation was problem-free and was completed in less than 30 minutes.

Platforms

SQL Server 7.0 works across all the currently supported Microsoft platforms, including Digital Equipment Corp.'s Alpha processor. It supports all of Microsoft's 32-bit operating systems, including Windows 95/98 and Windows NT Server and Workstation. However, a few Windows NT-specific features - such as file-level security, multiple processors and disk arrays - don't work on the 95/98 platform.

-- Ferrill is a principal engineer at Avionics Test & Analysis Corp. He can be reached at pferrill@fwb.gulf.net.

* * * * *

Benchmarking SQL Server

Our sister company, InfoWorld Consulting Services, ran the benchmark tests on SQL Server 7.0 using InfoWorld's enterprise database model. The database contained about 20G of data and 38 tables. The tests consisted of online transaction processing (OLTP) and Decision Support System (DSS) tasks. We tested at three workloads: light, moderate and heavy.

For OLTP, we used 30, 60 and 90 virtual clients to represent those workloads. The OLTP clients ran without think time. OLTP workload tasks mimic a real-world order entry system. For example, we looked up customer information, ordered items, adjusted inventory and paid a commission to a sales representative.

Our DSS workload consisted of nine queries, ranging from simple to complex. For example, our simplest query was a single table select with a "where" clause. Our most complex query joined multiple tables and included sorting and aggregates. We ran the DSS workload from four, eight and 12 clients to represent light, moderate and heavy workloads.