Microsoft SQL Server 2005 review

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 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.