Change Is Good for Server 2005; Review: Microsoft's database server is more robust, but it's also more complex.

Abstract

Microsoft's SQL Server 2005 is reviewed.

Microsoft Corp.'s long-awaited SQL Server 2005 represents significant change for administrators - the database server includes several new features that add capability and improve performance, but they increase complexity as well.

Five years in the making, SQL Server 2005 is a completely different database server than its predecessors, with a new management interface, improved uptime and better support for XML. In large part, these improvements make management easier and the server more suitable for enterprise applications.

For companies that already have SQL Server-based applications in place, there is a significant benefit to upgrading, and eWEEK Labs saw no ill effects when we upgraded existing applications. However, the differences between SQL Server 2000 and SQL Server 2005 are major, and we recommend that companies making the switch invest in training to ensure a smooth transition.

We tested the Enterprise Edition of SQL Server 2005, which includes a run-time shell of Visual Studio 2005 for the SQL Server Business Intelligence Development Studio. (eWEEK Labs' review of Visual Studio 2005 is on Page 42.) This packaging gives developers, administrators and database analysts better access to SQL Server, but there's a fairly heavy cost in that the new management framework can be unwieldy.

Microsoft has made SQL Server more accessible to more organizations by providing a variety of pricing models.

SQL Server 2005, released last month, is available on a per-processor or per-server and CAL (client access license) basis, and a CAL can be either a user or a device. In addition, Microsoft now has four versions of the database, ranging from the free Express to the Enterprise Edition, which costs $24,999 per processor or $13,969 for a server and 25 CALs. (For a more detailed explanation of the various SQL Server 2005 versions, see story on Page 52.)

With SQL Server 2005, Microsoft clearly aspires to compete with Oracle Corp. for the biggest enterprise applications. SQL Server 2005 doesn't have Oracle Database 10g's management capabilities and scalable architecture, but Microsoft has made considerable strides in automating SQL Server's management tasks and improving performance tuning and uptime. Oracle still sets the standard, but SQL Server 2005 surpasses SQL Server 2000's midtier limitations.

However, by making management more complex, Microsoft has discarded the one significant advantage it had over Oracle Database 10g and IBM's DB2 - ease of administration. This makes DB2 and Oracle Database 10g look all the more attractive for their broader choice of development frameworks, management interfaces, and server hardware and operating systems.

SQL Server 2005 runs on Windows 2000 Server and Windows Server 2003; optional components require additional Microsoft technologies. For example, reporting requires IIS (Internet Information Services) and ASP.Net.

Data availability

Microsoft has improved SQL Server's overall data availability by allowing administrators to perform maintenance and recovery tasks while a database is online. During tests, for example, eWEEK Labs was able to reindex a database while keeping it online. Microsoft also has added the ability to restore a database without bringing it offline. Users can still access tables that are not affected by the database restore process.

There have been a couple of tweaks to the way replication is handled in SQL Server 2005. Companies with a limited number of servers can now set up peer-to-peer transaction replication for real-time replication. This release of SQL Server also supports remote synchronization over HTTPS (HTTP Secure). And, given the importance of business intelligence to this release of the Microsoft database server, we weren't surprised that cluster support now extends to analysis services.

Mirroring capabilities aren't included in this initial release of SQL Server 2005. Currently in beta, the mirroring feature requires three servers: When the principal server fails, a witness server manages the failover to the mirror server. The mirror server can then be used to restore the principal server.

Microsoft officials said their goal is to add mirroring to SQL Server 2005 in the first half of next year.

Common ground

THe integration between sql Server 2005 and Visual Studio 2005 opens SQL Server up to a wider range of development options through the CLR (Common Language Runtime) in the database server. The CLR allows developers more language flexibility for developing database applications, with a choice of Transact-SQL, Visual C++, Visual Basic .Net and Visual C# .Net.

The CLR also will allow developers to tap the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# for use in stored procedures, functions and triggers. Ultimately, this will mean more dynamic database applications, more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors.

Another major overlapping area between SQL Server and Visual Studio can be found in the SQL Server Business Intelligence Development Studio, a development environment within Visual Studio for building SSIS (SQL Server Integration Services) packages . SSIS replaces the DTS (Data Transformation Services) in SQL Server 2000 with a more approachable drag-and-drop environment for developing BI and data mining applications.

In a nod to the difficulty of replacing entrenched database servers and Oracle's prevalence in enterprise applications, Microsoft allows administrators to use SQL Server 2005 and the Business Development Intelligence Studio as a front end for building OLAP (online analytical processing) cubes and prepackaged analysis applications of Oracle databases.

XML is now natively supported in SQL Server, with XML data stored as binary large objects. Applications now can query data contained within an XML document and modify it in place, rather than just query an XML header or contiguous blocks of XML data. XML support extends to Analysis Services, where it is the native protocol for the Analysis Server.

Management makeover

The outward face of sql Server 2005 is SQL Server Management Studio, a tool that rolls in all the applications in SQL Server 2000, including Enterprise Manager and Query Analyzer.

In many ways, we found Management Studio to resemble an all-in-one power tool, but although there is a convenience factor in having everything in one place, we sometimes felt like we were removing the circular saw to get at the screw gun. However, while Management Studio is, at times, unwieldy, it does a good job of providing contextual access to tools and features, and the new capabilities are well-integrated throughout the application. We particularly liked the scripting capabilities within SQL Server 2005, with which we could write and reuse scripts through templates that allowed us to pass parameters from the command line.

Management Studio makes use of the same kind of customization prevalent in other Microsoft applications. For example, we were able to customize our views and tool bar settings to create a developer or administrator view of the tool. In addition, administrators can create their own custom elements, such as frequently used command-line tasks, through the CLR.

We were impressed by several of SQL Server 2005's database management elements. The Maintenance Plan tools, for example, include a wizard and a design view for creating maintenance workflows . We appreciated the visual representation, as well as the ability to drag and drop common tasks to the design view.

As Oracle did with Oracle Database 10g, Microsoft has added a tuning engine to SQL Server that optimizes performance founded on a knowledge base of best-practice tuning parameters. The Database Engine Tuning Advisor provides good options for administrators. For example, when tuning multiple databases with the same workload, the Database Engine Tuning Advisor made some recommendations based on projected time to tune and percent of workload completed.

Oracle still has the edge in database optimization, but SQL Server has been improved significantly in this area and bears careful consideration in competitive evaluations.

Management Studio includes a built-in interface to Microsoft's support forums that allows administrators and developers to post and monitor responses to technical questions. This interface also is customizable, so a company could create its own internal forum for managing application development.

while the jury will be out for quite some time on how secure SQL Server 2005 is, Microsoft has done a good deal to prevent administrators from making mistakes that open the server up to unauthorized access.

The new Surface Area Configuration Tool, for example, allows administrators to see the services that have been installed and are running after initial installation. In addition, it allows administrators to set up features as needed. We also liked that we could easily pull up configuration data, such as protocols and service status, in a dedicated tool.

With Microsoft putting so much capability in a single tool - namely, Management Studio - we were concerned about permissions and rights for the range of developers, administrators and analysts who may access a database. Microsoft has addressed this with the ability to configure rights granularly and to allow administrators to perform maintenance tasks without broad administrative privileges. For example, the Database Engine Tuning Advisor requires just database owner privileges, not administrative privileges, to run.

Kerberos authentication is now supported, so administrators can maintain a consistent log-in policy across applications. We also liked that users have been separated from schema - this makes it much easier to drop users from a database because administrators no longer have to reassign or delete an object before doing so.

EVALUATION SHORTLIST

IBM's DB2 Universal Database 8.2 DB2 provides good self-maintenance tools and redundancy, as well as excellent management tools (www.IBM.com )

Oracle's Oracle Database 10g The Oracle database server sets the bar for automated management and uptime options (www.oracle.com )

MySQL AB's MySQL A lightweight and low-cost alternative for companies wanting to develop Web applications (www.mysql.com )

Technical Analyst Michael Caton can be reached at [email protected]. EXECUTIVE SUMMARY SQL Server 2005 In a complete redesign of SQL Server, Microsoft has put development and business analysis on par with administration. With the ability to execute Visual Basic, Visual C++ and Visual C# code on the new database server, developers can build applications that are more flexible and perform better. Administrators also have new tools for tuning performance and automating maintenance. Companies will be able to get better views of large data sets by developing analysis packages through a Visual Studio-based business intelligence tool set. These improvements come at the cost of added complexity, however. For more information, go to www.microsoft.com . COST ANALYSIS: Microsoft provides companies with the ability to manage cost by offering several different editions of SQL Server 2005, as well as two pricing structures: per processor or a combination of server and client licensing. This is offset, however, by limited server operating system options and the training expenses that companies will incur as their administrators and developers learn the redesigned tool set. KEY PERFORMANCE Indicators performance good interoperability fair Administration good reporting Good