Has SQL Server 2005 been worth the wait?

After five years of waiting, users will finally get their hands on a completely new version of Microsoft's database product when SQL Server 2005 and a new database management system (DBMS) are released next week.

After five years of waiting, users will finally get their hands on a completely new version of Microsoft's database product when SQL Server 2005 and a new database management system (DBMS) are released next week.

SQL Server 2005 includes features such as database mirroring, XML integration, online indexing, automatic database tuning and full text search. It will also have enhanced business intelligence, online analytical programming (Olap), data mining and reporting tools.

SQL Server was introduced 12 years ago, when it was seen as a workgroup database and used extensively for small applications. Over the years it has matured to become an enterprise database management system supporting medium and large deployments.

Some experts feel that because SQL Server has gradually matured, with no radical overhaul, users have not benefited from the breakthroughs in technology to the same degree as those using rival products, especially in areas such as high-end performance. Over the past five years Oracle has released two major versions (9i and 10g), and IBM has rolled out a series of DB2 8.x releases.

But SQL Server has gained the support of analyst due to its reputation of being the easiest enterprise DBMS to manage, and tends to be less expensive than Oracle, IBM and Sybase. In addition, most SQL Server deployments are for small to medium-sized database applications running on one-to-four-processor Microsoft Windows servers.

Now, Microsoft is trying to scale it up so it can be used in larger enterprise deployments.

Microsoft will release four versions of SQL Server 2005, with the top version targeted at high-end users, to compete with Oracle and IBM. For the first time, it will boast performance features such as partitioning, previously available only on high-end databases such as Oracle and DB2.

Partitioning in SQL Server creates smaller subsections to improve performance. The other performance booster is parallel index creation, which speeds up the database indexing process. These features have been available from Oracle for several years.

Noel Yuhanna, senior analyst at Forrester Research, said SQL Server has come a long way in establishing itself as a strong enterprise DBMS, dominating in manageability, integration and low cost. But he noted that it had often struggled to be seen as a leader in high-end performance and scalability, where it continues to trail IBM and Oracle in advanced features.

"Microsoft is positioning SQL Server 2005 to compete head-on with Oracle and IBM in performance and scalability, but is SQL Server ready for high-end performance delivery? There is no indication yet that Microsoft will become a high-end DBMS player any time soon," he said.

Yuhanna said Forrester estimates that of the 2,000 high-end database deployments worldwide with production databases larger than 1Tbyte, about 80 run SQL Server, with most running Oracle or DB2.

"SQL Server 2000 implementations for high-end delivery have stretched the technology well beyond what it was originally designed for," he said.

The question is whether SQL Server can handle very large workloads working with multi-terabyte databases and thousands of concurrent users running on large SMP servers or distributed clustered environments. "SQL Server 2005 is under increased pressure to deliver high-end performance," said Yuhanna.

In the summer, Microsoft announced a series of audited TPC (Transaction Processing Performance Council) benchmark results that showed SQL Server 2005 was more than 38% faster than SQL Server 2000, with  transactions per minute (tpmC) in excess of one million. The supplier also claimed the new database would provide a low cost per transaction.

However IBM has recorded a TPC benchmark of 3.2 million tpmC, significantly raising the bar on high-end performance delivery. Microsoft now faces the daunting task of delivering even higher performance with its SQL Server 2005 release. Two years ago, Microsoft trumpeted then-leading benchmarks and pledged to stay on top. If SQL Server 2005 does not deliver record-breaking, or at least competitive numbers, it will lose some of the credibility that it has built up as a high-end DBMS.

In addition, Forrester reported that some beta SQL Server 2005 users found no key high-end scalability benefits, or the level of high-end performance and scalability needed to meet their growing application requirements. "This has been a key concern about SQL Server 2005, and some firms are migrating to Oracle, DB2 and other DBMS instead of waiting for another major SQL Server release beyond 2005," Yuhanna said.

Some of SQL Server 2005's performance features, such as partitioning and parallel index creation, have been available from Oracle for several years, so in some respects, SQL Server 2005 is playing catch up with the competition at the high-end.

However, SQL Server 2005 will offer some performance improvement because the 64-bit edition runs on 64-bit processors such as Intel's Itanium and AMD's Opteron, and it is designed to improve the performance of large datawarehouses, batch programs, and large online transaction processing applications. According to Microsoft, users typically claim to achieve a performance improvement of 10% or more with 64-bit.

The 64-bit SQL Server can be configured to run as an in-memory database - where all transactions run in Ram, avoiding the degradation in performance that arises when the database accesses disc drives. But it is early days for application writers to realise the potential and exploit this, said Forrester.

Donald Feinberg, vice-president and distinguished analyst at Gartner, believes SQL Server 2005 will, overall, have been worth the wait, with most of the functions Microsoft promised being incorporated.

"The only notable exception is a DBMS clustering system for high availability," he said. "This feature has been pushed off to a future release, leaving Oracle as the only supplier offering a DBMS with a clustering implementation, through Real Application Clusters.

"Users who are less risk-averse or need the added functionality or increased performance of SQL Server 2005, should consider using the new release in limited production environments in the first quarter of 2006.

"Users that are risk-averse or do not need the added functionality or increased performance, should wait for Microsoft to release Service Pack 1." This is expected in the second quarter of 2006.

However, Kevin Kline, president of international SQL Server user group Pass, said, "I know of several huge enterprises that went to production on beta versions of SQL Server 2005 because of improvements in speed and  new data mining capabilities. The high-availability improvements are a real boon to line database administrators who want to make their databases more scalable and available."

Kline believes the Microsoft product lacks some of the new technology available in Oracle and DB2. He said, "SQL Server 2005 still needs hash partitioning and synonyms, which Oracle and DB2 have. SQL Server also needs multiple tempDBs [temporary databases that run on the master database] as Sybase has. And the biggest feature that Microsoft needs to work on is an answer for Oracle's Real Application Clusters, ie a load-balancing solution  spread across multiple servers."

Michael Azoff, senior research analyst at Butler Group, said SQL Server 2005 Enterprise Edition's free business intelligence system may encourage some users to switch to Microsoft. Another factor was Microsoft's  plans to change the licensing of server software for virtual machines, and this may also influence users.

But as Microsoft continues to enhance the SQL Server database to make it a compelling alternative to DB2 and Oracle in terms of functionality, performance and cost, users do have a lower-end alternative in the open source MySQL database.

MySQL will continue to be attractive to companies carrying out tasks that do not need industrial-strength transaction or analytical processing, Azoff said. "MySQL is still evolving and is not an 'enterprise' database. However, as a back end to websites and other similar applications, it is perfectly good, so expect continued growth for MySQL."

Olap feature 'a big step forward'

A new online analytical processing (Olap) feature in SQL Server 2005 is a big step forward for the database product that will help to support high-end users, according to Nigel Pendse, author of the Olap Report website.

"It supports much more complex data models, larger applications and is a much more robust product because it shares a lot of the online transaction processing database infrastructure, which the 2000 version did not," he said.

"It is very likely that it will continue to gain users at the expense of other Olap servers, as it has ever since it was released at the end of 1998."

But Pendse added, "The one area Microsoft has not addressed in this release is data write-back performance, so the product still will not be very suitable for planning applications. But in most other respects, it is a very strong product, and easily able to compete with far more expensive products."

Key features in SQL Server 2005

Database mirroring: Database mirroring can be used to boost the availability of SQL Server systems by setting up automatic failover to a standby server.

Online restore: SQL Server 2005 lets database administrators perform a restore operation while an instance of SQL Server is running.

Security enhancements: Security enhancements include database encryption, secure default settings, password policy enforcement, and fine-grained permissions control.

.net Framework hosting: Developers can create database objects using Visual C# .net and Microsoft Visual Basic .net.

XML technologies:Native support for storage and query of XML documents.

Web services: Developers will be able to develop web services in the database tier.

Full text search: Support for rich, full text search applications, giving greater flexibility over what is catalogued.

Analysis services: SQL Server 2005 has real-time analytics features.

Reporting services: Reporting services provide self-service, ad hoc report creation, and enhanced query development for Olap environments.

Microsoft Office System integration: Reports served up by the report server can run under Microsoft Sharepoint Portal Server and Office applications such as Word and Excel.


Read more on Database software