Product Reviews

It's five years since the last version of SQL Server, and in that time Microsoft has prepared a bumper crop of new features and enhancements to delight DBAs, developers and managers. There's a new front end, new features in the back end and new services on the side.

DB ENGINE

The biggest addition to SQL Server 2005 is the ability to write stored procedures, functions, triggers, user-defined types and aggregates in any .NET language such as VB .NET or C#. Transact-SQL, the language usually used with SQL Server, is great for set-based operations but not so good for general programming tasks because it's so specialised. Languages using .NET have constructs and abilities that aren't found in T-SQL. You also have full access to all the namespaces and classes in the .NET Framework. VB .NET or C# code is also subject to all the CLR security, memory management and type-safe checking, so it isn't capable of compromising the security or integrity of SQL Server in the way extended stored procedures (XPs) have been in the past. To write a VB .NET or C# stored procedure or trigger, you use Visual Studio 2005 to create a new project from the SQL Server template. This creates the assembly and manifest needed for your code and uploads it to the server when you're ready to deploy it.

While you can now write stored procedures in VB .NET or C#, that doesn't mean you have to or should: T-SQL is still the best language to choose if you want to work with sets of data in the traditional way.

In SQL Server 2005, you can define a column as containing XML data, optionally associating an XML schema with the column. You can also create variables or parameters of type XML. If you specify a schema, the XML fragment or documents you store are validated against that. You can also specify XML-based constraints by creating user-defined functions that return TRUE/FALSE based on an XML parameter.

You can query data in XML columns using the XQuery language. This is an extension to the XPath language but has better iteration and sorting. XQuery is still under development by the W3C (World Wide Web Consortium), and SQL Server implements a version of XQuery aligned with the July 2004 Working Draft. Unfortunately, W3C released another Working Draft in September 2005, but that isn't even a Candidate Recommendation, let alone a standard. Microsoft has extended the XQuery language to allow document fragments to be inserted, updated and deleted.

XML documents and fragments stored in XML columns can be up to 2GB each. Large documents would be time-consuming to search using T-SQL and XQuery, so SQL Server allows XML columns to be indexed to speed up access. These indexes shred the XML into nodes and values to give quick access to rows where a node exists or rows where a node has a particular value.

SQL Server 2005 can provide direct access to stored procedures, defined functions and batch statements as Web Services using HTTP, SOAP and WSDL without requiring SQLXML, ODBC or TDS. This gives greater access to SQL Server in heterogeneous environments, better integration from Visual Studio 2005, JBuilder and other IDEs, improved support for mobile devices and other 'occasionally connected' computers, and better security without the need to implement additional firewalls. The CREATE ENDPOINT command sets up web addresses to which SQL Server will monitor requests and stored procedures or functions available at that address.

Microsoft has made many enhancements to SQL Server to improve its already impressive uptime. Creating, altering or dropping indexes or even restoring a database shouldn't stop users from using the database at the same time. When recovering a database, users get partial or full access to recovered data before recovery is fully complete. Rather than stop at the first error, restoration will continue if there are errors and, if a database is marked as suspect, members of the sysadmin group will still be allowed read-only access to help diagnose the problem. Just in case things go badly wrong and the server can't accept any new connections, there's now a dedicated connection for sysadmins to use to diagnose and correct problems.

If your 64-bit or 32-bit with AWE (Address Windowing Extensions) hardware allows it, you can add memory to the server without stopping it. SQL Server

ADVERTISEMENT

instances dynamically allocate memory based on their current workload even when using AWE memory.

You can have a hot-standby server through database mirroring, or mirror your backups to two, three or four devices to guard against failure of a backup device or loss of media. For high availability, you can configure SQL Servers into 'failover' clusters. The Standard Edition will support two-node clustering and the Enterprise Edition will support as many nodes as the underlying OS (eight on Windows Server 2003).

The final comment to make about the DB engine concerns management: out goes the old SQL Server Enterprise Manager and in comes SQL Server Management Studio. This is a total rewrite, taking the look and feel of Visual Studio 2005. Management Studio is easy to use and powerful. As well as allowing full manipulation of all diagrams, tables, columns, functions and security features, there are a host of useful reports about the state of the server and the transactions it's running.

CHANGE OF SERVICE

The Service Broker is a new feature for storing message queues in SQL Server. It brings new T-SQL statements to send and receive messages, giving you a reliable, persistent communication channel between two parties. The Broker can be used between two apps on the same SQL Server instance or across multiple instances and multiple servers. It can be used, for instance, to implement asynchronous triggers or to collect data from remote sites where communications may be patchy. With a secure message queue built into the database, it's easy to set up apps such as these without having to implement a separate service such as MSMQ.

The old Data Transformation Services (DTS) has been rewritten and is now known as SQL Server Integration Services (SSIS). This has a new extensible architecture and can cope with jobs that DTS would have found difficult or even impossible. SSIS has a new designer in which you drag and drop sources, tasks and destinations and join them together to describe data flows for loading, transforming, cleaning, aggregating, merging and copying data. SSIS packages are easy to build and schedule and fast to run. They're also flexible. There's a Migration Wizard to convert old DTS packages into SSIS, but sometimes the migration won't be 100 per cent automatic and intervention will be required.

SQL Server Analysis Services has also been rewritten, making it easier to create, deploy and manage business intelligence solutions. The BI Development Studio is integrated into Visual Studio 2005 with templates and wizards to get you started quickly. Point the BI Development Studio at your database and it will quickly identify facts and dimensions in all the tables and build cubes to suit. You get to rename dimensions and facts to make them easier to understand by end users, and you can easily group dimensions into hierarchies. Analysis Services can be used to design Key Performance Indicators (KPIs), which you can then expose through Reporting Services or Microsoft's new Business Scorecard Manager. If you have databases that use the old version of Analysis Services, a Migration Wizard will help you convert them to this new format.

Reporting Services is the least changed of all the services, but it still has some impressive new features. Users can change the sort order of the data while viewing a report. They can also print reports directly from Internet Explorer and a new Report Builder module allows end users to design their own reports based on models provided by a more skilled report designer. You can now have multivalued parameters to reports, giving more flexibility in selecting what data to report. Report designers can also set hidden parameters that end users can't change but the designer is able to set programmatically.

AND FINALLY...

Significant improvements have been made to the replication of data between servers, including new security and manageability enhancements and a new Replication Monitor. Transactional and snapshot replication is supported from Oracle databases and there's improved support for non-SQL Server replication subscribers. Subscribers can now replicate data using HTTPS, which is useful for Windows Mobile devices.

As well as the new Management Studio, there are tools for configuring SQL Server, tuning the database engine, profiling the performance of SQL Server and Analysis Services and Importing and Exporting data.

SQL Server 2005 is a hugely capable database system. The price has gone up compared to the 2000 version, particularly for the Enterprise Edition, but you still get a huge amount for your money. Analysis Services and Integration Services are the most obviously changed, but every area has been improved, from pure database engine performance to security of data replicated to far-flung clients. SQL Server 2000 was a great database platform; SQL Server 2005 is outstanding.