Original URL: http://www.theregister.co.uk/2008/08/07/sql_server_2008_review/

SQL Server 2008 moves on, wants to stay friends

It’s not you, it’s the BI and database engine

By Mark Whitehorn

Posted in Applications, 7th August 2008 15:02 GMT

Review Microsoft's SQL Server 2008 is now upon us. More than a year after its initial planned delivery date, Microsoft Wednesday released to manufacturing code for a new version of its database that's almost as significant as its last great release - SQL Server 2005, three years ago.

Microsoft is, with SQL Server 2008, bucking its usual software pattern for a major upgrade followed by a minor one.

SQL Server 2008, though, seems to respect both sides of the equation: there's enough new stuff to keep you occupied while, simultaneously, offering familiarity with the past - in analysis schema, for example. The most apparent changes in SQL Server 2008 come with the addition of a completely new version targeting the web, and with improvements to the database engine and Business Intelligence (BI) tools.

Acronyms unlimited

The SQL Server Web Edition has been designed to provide a robust back-end to web systems. Its feature set is optimized for that task and includes policy-based management tools, automated security policy enforcement and performance data collection tools, all of which can help build a leaner meaner business. Although this edition can only address four CPUs, there are no limits on memory or database size.

The arrival of the web edition continues Microsoft's move into the world of Linux, Apache, MySQL, PHP (LAMP) so let's train a spotlight on this development.

Many Reg readers using the LAMP stack will remember that back in 2005 Microsoft had very little that was comparable. OK, there was the free, Express version of SQL Server, but what about the rest? With the arrival of SQL Server 2008 Microsoft has made determined inroads to redress the position and offer a viable alternative.

Microsoft has been working hard to get Apache and PHP working and optimised for Windows, and MySQL already runs on the Windows platform. Microsoft is arguing that we shouldn't think of LAMP as a single unit in the way the acronym suggests: we should embrace the fact it comprises four components, which we can mix and match as we see fit.

SQL Server 08 analysis services

Looks familiar: analysis schema displayed

A wonderful new range of acronyms is thus born: WAMP, for example (Windows, Apache, MySQL, PHP) and WASP (the same but replacing MySQL with SQL Server) and even WISP (Windows, IIS, SQL Server, PHP) - you get the picture, though one you're not likely to find is LISP (Linux, IIS, SQL Server, PHP).

If you are already a LAMP user and discover you need features in your database engine that are not supported by MySQL - such as BI tools, for example - you are free to build exactly the stack you need, perhaps moving from LAMP to WASP.

The options for what to include in a stack are many and direct comparisons are difficult. Although it is tempting to deploy the Express edition of SQL Server 2008 on cost grounds - it's free - the Web edition is optimized for web-based scenarios. But, and here's the catch: it isn't free.

Engine of change

So what's new in the database engine itself? The resource governor is all-new. This tool lets you allocate available resources into pools. This allows jobs to be grouped depending on their importance and allocated to a resource pool, which in turn enables jobs that need to be completed rapidly (like loading data) to be given more resources than less time-critical tasks. The resource governor is also a useful aid to monitoring resource usage.

Data compression is also a new and welcome addition in these days of burgeoning data volume. Row and page compression are both supported: row compression stores fields using a variable width format thus reducing the bytes needed to store compressible data, and page compression is similar except that it compresses the rows on a page. There is inevitably a performance hit during compression when writing and de-compression when reading, but Microsoft said the reduced disk I/O counterbalances the compression times and even improves query performance.

Transparent Data Encryption (TDE) is another new feature and can be implemented at the engine level, meaning you can protect an entire database without having to change anything in the applications.

SQL Server's database mirroring abilities have been enhanced to make them faster and easier to use. Mirroring transfers log files between servers in order to improve fail-over times when the standby server is called into play. The ability is further improved by log stream compression (another newbie) that speeds up the mirroring process.

The implementation of a new spatial data type is significant because it allows geographical data to be handled and manipulated elegantly. Using this data type it is possible to find, for instance, how many contacts are within a 50-mile radius of your offices - and much more.

SQL Server 08 gauge control

All new: gauge control displays data in a range of flavors

I have written extensively in Project Watch about making use of this feature to plot locations on a map.

The backup system has been rewritten and now delivers a huge improvement in performance, linear scalability and the ability to handle an analysis services database of more than a terabyte. The new backup can usefully replace raw file system copying so that the ability to run in parallel with other operations, amongst other benefits, can be felt. Equally good news is that backup compression is supported in 2008.

New reporting rules

Major changes are evident in Reporting Services, to the point of being an entirely new tool. The familiar tools are still in evidence, namely:

New, though, is the 2008 Report Builder for the power users, supplied as a web download. A stand-alone product, it offers all the layout features of Report Designer with an Office 12 ribbon interface.

There are new controls for displaying data in reports: one is the Gauge. Perfect for displaying a single value, a range of gauges is also useful for comparing several values. A variety of gauge types is available, with pointers, sliders or simply showing a value.

The Tablix control is for rather more complex data than that served by gauges. It combines the abilities of the existing Table, List and Matrix controls and gives much greater control over layout.

New chart types include polar, shape and range, and multiple data series can now be displayed on more than one axis and the scale breaks on the axis are customizable.

Integration Services has acquired a Data Profiling Task that facilitates an understanding of the distribution of data in the source systems that feed into a data warehouse. Previously this has been gained the hard way by issuing multiple queries to ascertain the domain and distribution of values. The new Data Profiler is a data-flow task that lets you define the information you require: eight profiles are included.

SQL Sever 08 data flow task

Graphically speaking: a data-flow task on display

Stack pack

So how has Microsoft packaged this little lot? The complete SQL Server - with all the features described plus the BI tools - is Enterprise edition, available for 32- and 64-bit systems. The Standard edition comes with a subset of features and BI tools targeting departmental users and the online transaction processing (OLTP) systems of small- to medium-sized enterprises (SMEs).

There are also Workgroup, Web, Compact and Express editions. This last is available for free download and ideal for the small business considering a move to a client-server. The Express edition is also more than capable of producing desktop and small server applications, and can be used commercially. For comparison between editions go here.

In summary, then, SQL Server 2008 has some nice features and is a database that I shall certainly be using more of. Microsoft's also put another component piece in to the game to counter the open-source stack. But it'll cost you and it's late to the game. Were these refinements and new features worth the price of delay? That'll all depend on your requirements and your point of view.®