SQL Server 7.0 Features Enhanced Tool Set

• 01/06/1999

Microsoft Corp.’s SQL Server 7.0 was marketed throughout its development as more capable than SQL Server 6.5 at handling the needs of enterprises that rely on databases such as Oracle8 and DB2. But as of yet, SQL Server 7.0 hasn’t quite met Microsoft’s earlier expectations.

The release of SQL Server 7.0 brings a set of enhanced tools that deliver better performance and ease-of-use than SQL Server 6.5. Among the tools is an auto-tuning memory system that looks into the operating system, checks memory availability, and adjusts SQL Server’s cache and internal table parameters accordingly.

Long overdue for a change, the management tools have been rearranged. Several tools, including the old Security Manager, are now integrated into the Enterprise Manager. Enterprise Manager is a tool that better manages both remote and local servers in the enterprise.

The ability to assign roles as well as assign permissions to users is also a useful change. One can either create a set of user names and passwords from within SQL Server 7.0, or use the permissions established within Windows NT Server itself.

The network used to test SQL Server 7.0 included an existing SQL Server 6.5 database running on a dual processor Pentium 200 with 256 MB of memory, fast SCSI-2 disk drives, and a plethora of subordinate equipment. The machine running SQL Server 7.0 was equally powerful, and nearly identically equipped.

The servers were on a switched 100 Mbps Ethernet backbone system sporting Cisco 2820 and 2916-XL switches along with a routed platform to the users on the other side of a Cisco 2514 router. The test was designed to see how SQL Server 7.0 would treat user requests, database backups, and queries from an Internet connection, as well as its response to users running Windows 95 and Windows NT Workstation laptops.

The installation process was the most troublesome part of using this product. SQL Server 7.0 requires Service Pack 4 (SP4), which is where the trouble began. SP4 replaced critical drivers for our disk controllers: an Adaptec 2940UW and standard EIDE controllers in one workstation and one small department server. This rendered the servers unusable. Each had to be rebuilt from scratch. Even a Compaq Prosignia, which was on the hardware compatibility list (HCL), was trashed by SP4. But one of our older servers, running Micronics D5-CUB motherboards and Asus P2B hardware, installed SP4 with nary a hiccup -- and neither is on the HCL. We checked the hardware out completely and found no reason for the random failures with SP4. Once the servers were rebuilt with SP3, we had full and complete operation.

It is important to note that not even the ERD or the option to select the last known successful configuration fixed these catastrophic and replicable upgrades. Other users report successful installations of SP 4 and are happy with the upgrades. We recommend making certain you have solid and tested backups before applying SP4.

After surviving the SP4 difficulties, the next step was to install Internet Explorer 4.01 SP1 for about half of the installation selections. The standard server installation required the use of IE4 whether we wanted it or not. While it was bothersome to have to use just this product, the installation went without incident.

The installation of the remaining SQL Server 7.0 program went smoothly. The new wizards made the installation as simple as it could be. A full installation consumed 209 MB of disk space before creating any database devices or tables.

From top to bottom, the installation -- hardware problems aside -- took about 90 minutes, including all of the reboots required during the various stages of software installation.

From past experience, SQL Server 6.5 was a monster to tune and adjust memory in the server. Memory fragmentation, memory leaks in queries, and other anomalies could cause the server to run out of memory at inopportune times. Finding SQL Server 7.0 automatically adjusting the server’s memory to fit the demand is a blessing. If data is deleted, tables are automatically reduced in size, and likewise, grown to meet increased demands. The rate of automatic adjustments can be tweaked, along with the size of the incremental adjustments.

In normal operations, with all functions enabled and running, SQL Server 7.0 consumed 58 MB. When pressed with numerous queries and supporting our back-end operations for Web servers, the database used an average of less than 1 MB for each persistent connection.

Clearly, there are performance gains with this new platform. We were impressed with the performance of the server when we flooded it with data from two Web servers and our internal operations. The database expansion process slowed the server CPU by less than 4 percent from normal operations with 13 active processes. When we bumped this up to 54 processes, CPU utilization jumped on the dual processors, as was expected. Memory utilization, however, increased from an average of less than 1 MB per process to slightly more than 1.5 MB per process.

On the programming side, we hit SQL Server 7.0 with Visual FoxPro, Visual Basic and Visual C++ front ends. Our applications worked well with no changes other than adjusting custom admin tools to experience many of the new features found in SQL Server 7.0.

Next, we tested the import and export capabilities of the new database. Many older legacy databases, despite some of the remaining legacy formats, were well handled. We brought in our existing Internet Information Server 3.0 logging files from the SQL Server 6.5 database. This went without incident, except for an inexplicable delay in the initial import of the data. We reimported the data six more times and experienced the delay once more. When we retested the process a day later, the problem was not there and never returned.

When we tried the same import with FoxPro, Excel, and flat file data, the import flew like a rocket. We saw no indication in the trace logs or system logs that explained the stark differences between the speeds of importing various data. All imports worked fine with no data corruption, but the process was time consuming. Administrators should be sure network communications between servers are stout and sturdy: such as 100 Mbps switched links or ATM OC-3.

In addition to the SP4 difficulty, we encountered a problem when creating new database devices. SQL Server 7.0 allows varied settings for automatically growing and shrinking databases. We selected the defaults when creating new devices without thinking of the impact they would have on the disk subsystem. With 1 MB selected as the size of the database device, the importing of the data caused massive disk activity and CPU degradation during the process. The database device grew, stored data, grew again, and stored more data -- this cycle caused terrible delays in server operations and excess server memory utilization.

That little operation seems like a rookie mistake, but it emphasizes the careful attention needed when setting up the server operations. Mirroring database devices was equally stressing on server operations and the network itself, where we mirrored across other servers on other parts of the network. Again, it is critical to ensure that other servers and the physical network infrastructure are adequate.

The latest incarnation of Microsoft’s premier database tool set was long awaited and its new features were long overdue. Although it may not deliver the enterprise capability that Microsoft claimed it would, it’s a step in the right direction.

We found the core components similar enough to SQL Server 6.5 that we felt right at home. The changes to the component’s locations made a huge difference in the ease of use, and significantly reduced overall maintenance tasks.