What's new in SQL Server 2008

This article was originally published in VSJ, which is now part of Developer Fusion.
You’ve probably seen some of the launch publicity for SQL Server 2008, along with its stablemates Windows Server 2008 and Visual Studio 2008. Under the banner “Heroes Happen Here”, Microsoft is launching all three products worldwide on 27 February, with a UK customer launch scheduled for 19 March. However, if you’re hoping to rush out and buy SQL Server 2008, you’ll have something of a wait, as the actual software won’t be released to market until the third quarter of 2008. This means you’ve plenty of time to think about whether SQL Server 2008 is worth waiting for, and worth shelling out for once it’s actually on the shelves.

The good news is that there are real improvements for a range of users and uses of SQL Server, and if any of those improvements fall into your own area, you’ll be pretty pleased. One enhancement that will appeal to a lot of users is a new report designer that is aimed at users. It looks like an Office application, with drag and drop field placement and the ability to include charts and maps. Yes, I know it’s hardly revolutionary for people working in applications such as Access, but this is SQL Server we’re talking about.

The next news to appeal to everyone is the ability to control what resources are being used by particular applications. In the past, if some unfortunate started a query that took half a week and really needed a supercomputer to have any chance of every completing, you were in trouble; someone had to notice, go and shut down the job, and give the user a thick ear. Now you have a resource governor that you can use to limit the percentage of resources a particular role or application can take. You can monitor and limit the percentage usage of CPU bandwidth, execution times, blocking times, and idle times, as well as timeout waits. If one of your preset limits is reached, you can choose either to trigger an event or stop the process. Much less confrontational than the thick ear, though possibly not as satisfying!

Now we get to a couple of areas designed to appeal to the “data heads” among you: spatial data and freeform data. Starting with the star of the show, spatial data means that you can use SQL Server 2008 to ask (and get answers to) questions like “which customers live within 10 miles of our shops”. To support this, SQL Server 2008 has data types, indexes and functions. The new Spatial Data type allows Latitude, Longitude, and GPS-based data entries to be stored inside SQL Server.

You can also plot the distribution of your records on a map using Virtual Earth, and as the functions have been written to conform to the Open Geospatial Consortium standards, we’re likely to see a lot of third party add-ons and expansions of the SQL Server facilities. It’s worth pointing out that the addition of spatial data brings SQL Server into line with Oracle and DB2, both of which already support spatial data in their own ways.

The ability to show records on maps will undoubtedly be a hit with a certain type of user, but potentially more useful is the improvement to SQL Server’s ability to store unstructured data. There used to be a limit of 2GB of freeform data per document, and you can now choose between accessing the data directly using the file system, or using SQL Server to manage the storage and retrieval in file groups. The latter option offers you security and better manageability; the file system method will be faster.

Now for the final (and for many the real) reason you’ll all eventually move to SQL Server 2008. With the release of SQL Server 2008, Mainstream Support for SQL Server 2000 is being phased out. Mainstream Support encompasses security updates, non-security hotfixes for problems, complimentary and paid support, as well as the ability to submit requests for product feature changes. This support will end in April 2008, though Extended Support (security updates and paid support) will be in place until 2013. Microsoft’s current policy of offering Mainstream Support for two years after the RTM of the next version suggests that SQL Server 2005 Mainstream support will be dropped in 2010. Of course, if your organisation is willing to keep paying for support, Microsoft will keep providing those updates.

Screenshot
You can control the resources used with the new Resource Governor

Management

As developers, you may not be responsible for day-to-day management of SQL Server, but those of you who do will see a change with the introduction of the Declarative Management Framework (DMF). This lets you administer your databases based on policies rather than the old task-based administration. Most DBAs spend their life doing things like backups, query management, event log checking, and database optimisation. Under the new system, you work out policies saying what’s allowed, what’s restricted, what must happen, and define them using Management Studio, then SQL Server 2008 does the work to ensure those policies are adhered to.

Security

I’m sure the recent high-profile losses of databases by Government departments has focused the attention of your users on security. SQL Server 2008 has a range of options to help you keep your data more securely, and to be able to prove no-one has been looking at things they shouldn’t. You can choose several ways to encrypt your data, the most useful being the fact that SQL Server lets you encrypt the entire database data and log files using a technique called Transparent Data Encryption. Next, you can encrypt your backups using Backup Encryption. This prevents anyone changing the backup, which can be vital if you need to comply with auditing requirements to show that the ‘truth’ about your data at a particular time and date cannot have been changed. The backup auditing also prevents accidental data disclosure, and you can specify exactly which users can restore the database from a backup. Finally, SQL Server offers new options for External Key Management. In particular, it now supports Hardware Security Modules (HSM), which are third party hardware products that are used to store data keys in a separate location from the data they are used to access. If your applications have financial elements, and in particular if you process credit card payments, this means your applications can be properly secure.

Auditing is another area where SQL Server has been beefed up. In addition to auditing logons and logoffs, SQL Server 2008 now defaults to tracking failed login attempts to the application log, and you can now choose to audit database actions either to a separate log file or to the application log. You can also now audit any changes to data at a column level. You can define the auditing using TSQL, which as you can imagine gives you pretty granular control over exactly when you’re going to log data changes.

Screenshot
The new SQL Report Designer looks similar to Office and lets you add charts and maps to your reports

Developer features

As developers, the main news is that SQL Server 2008 is moving to using LINQ (Language Integrated Query) rather than T-SQL in your application language such as C#. Don’t panic; you can still use T-SQL, this is just the new recommended alternative. LINQ has the advantage that you get a standard syntax no matter what the underlying data source, so you could use the same syntax for SQL Server and XML queries, for example, or third party LINQ suppliers. LINQ was released as a part of .NET Framework 3.5 in November, and Visual Studio 2008 supports it. It can be used to access third party data sources so long as the data is encapsulated as objects. Another improvement for developers is the fact that using SQL Server 2008 and ADO.NET, you can now create high level business objects, so you can work in terms of Customer objects rather than the columns in the Customers table. If you start your development in an entity relationship (E-R) modeller, you’ll be able to use the same objects in both environments. Once in Visual Studio, there are new ADO.NET frameworks that you can use to access the objects, including a Line-of-Business (LOB) framework and an Entity Query Language (eSQL).

A final benefit (though there are many other minor improvements) is the introduction of new methods for creating applications that need to synchronise with a central database, but which aren’t permanently connected. If you’re using SQL Server 2005 there are already some features to help with this, but they’re more integrated and improved in SQL Server 2008.

Screenshot
SQL Server 2008 adds new spatial data types so you can store location information

Conclusion

The decision of whether to jump early or late will depend very heavily on exactly what you do with your databases. If you are mainly working with standard data with little or no requirement for exotic data types; if you already have a good reporting solution; if the business intelligence features aren’t particularly of interest; and if your security and compliance are sufficient for your needs, you can afford to wait before moving. There are still advantages, but you’re not going to see a dramatic change in what you can offer your users. On the other hand, SQL Server 2008 looks like it will be a good product to work with. Microsoft has added some attractive options that make it easier to get end results in terms of good reports and the BI features. The spatial and non-structured data types will be enough to make you move if they fit your database requirements, and if you struggle to control resources, the Resource Governor alone will make it worth moving.


Kay Ewbank, the editor of Server Management magazine, is an experienced database analyst who has followed the development of database technology from dbase through to today’s modern relational and post relational databases

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“In theory, theory and practice are the same. In practice, they're not.”