Product review: Microsoft SQL Server 7.0

SQL Server 7.0, with its improved performance and scalability, will make the industry stand up and take notice of Microsoft as a serious player in the database market. At least that's the message from four users who have worked with it since its first beta in 1997.

Although the users said SQL Server 7.0 is a significant advance over SQL Server 6.5, they also said some competitors still have an edge in certain features. For example, SQL 7.0 still can't perform enhanced data loads like products from Oracle and Informix, said Lee Parrish, development manager at Commercial Financial Services in Tulsa, Oklahoma. And the conversion to SQL 7.0 isn't a slam-dunk for anyone because the physical layout of the server files has changed from previous versions.

But in the end, SQL 7.0's superior price/performance ratio and the availability of trained staff won over these users.

"Hey, it's not Oracle, but SQL 7.0 shows enough maturity that we can confidently stay on Microsoft. Besides, I defy anyone to find an Oracle [database administrator] at a reasonable rate," said Dave Lish, vice president of information systems at Transamerica Flood Hazard Certification in New Jersey, a division of Transamerica Corp.

Raw speed is what's driving users to upgrade to SQL 7.0, despite predictions from analysts -- such as Mitch Kramer at Patricia Seybold Group in Boston -- who say its greatest lure may be new features that will lower the cost of implementing data warehouses and forever change the data warehousing market.

"The speed [improvement] is so noticeable, some of our users actually asked us what we did to the system," said Jonathan Kaley-Isley, director of information technology at Relocation Tax Services LLC in Denver.

SQL 7.0 isn't necessarily faster than Oracle or Informix, but it's now in the performance race with the addition of the row-level locking functionality that those competitors already have.

Row-level locking lets the database dynamically resize itself when it needs more space. Without it, the database can bog down as it tries to back itself out of trouble, a problem Kaley-Isley said happened too often with his 170Gbyte database.

Dynamic locking was the first feature Lish tested, and he saw response times cut almost in half. His 72Gbyte database contains flood-certification records on 100 million residential and commercial properties and is queried about 15,000 times a day by mortgage lenders who need flood certification information. "A 100 per cent performance boost over 15,000 searches a day means a great deal to us and our customers," Lish said.

Dynamic locking also helps improve SQL 7.0's scalability, which had been subject to criticism in the past, according to Kaley-Isley, who said the function allows the database to dynamically allocate its own resources. Kaley-Isley said his database grew from 4Gbytes to 170Gbytes with minimal intervention. "I've just added disk drives. SQL 7.0 automatically discovers them and says, ‘Yeehaw, got another gig.' [Version] 6.5 couldn't do that."

SQL 7.0 also is stacked with new and updated utilities to make it more suitable for large databases. Commercial Financial Services expects SQL 7.0 to extend the life of its hardware because the redesigned query tool will let the collection agency reduce the number of indexes in its database. Commercial's data warehouse had been limping along under SQL 6.5, according to CIO Chris Horrocks.

Horrocks is running three servers and says a 35 per cent storage reduction on each is realistic. "For us, that is like having the equivalent of four servers, which is a savings of a quarter of a million dollars," Horrocks said. "This is a rare occasion where an expenditure in software will save us a lot in the hardware area."

Ease of use has always been Microsoft's hallmark, "but SQL 6.5 seems like it was an awful lot of work now that we're using 7.0," Kaley-Isley said. "It doesn't eliminate the need for a [database administrator], it just makes our job a lot easier."

Many functions that database administrators manually performed in 6.5, such as database consistency checks, now are done automatically and on the fly in 7.0. Lish said a full consistency check of his database under 6.5, including checking the index, would last more than 60 hours. Under SQL 7.0, it's completed in seven to 10 hours and doesn't require human intervention.

Not everything about SQL 7.0 ran smoothly for these users. Each reported some difficulty in transporting their data over to SQL 7.0, even though it came from SQL 6.5.

That's because Microsoft changed the physical layout of the server's files, making everyone go through a conversion. "There were a lot of utilities we needed to update -- [Microsoft Data Access Components], OLE [for databases], etc. -- before our data would convert correctly. The process certainly wasn't as clean as it could have been," Kaley-Isley said.