SQL Server 2008 � An Insight

Written by Justin Hostettler-Davies on November 2008

I attended Microsoft�s UK Server Launch 2008 in Birmingham this summer where �Katmai�, code name for SQL Server 2008 was introduced. I know what you�re thinking; they�ve only just released 2005? You could (like me) be still getting to grips with SQL 2005.

There are many new features due to be released with SQL 2008, in fact too many for me to explain in detail here in this article, improvements have been made across the SQL spectrum including Reporting Services, Analysis Services and Integration Services but I have tried to capture & highlight new components that I found interesting and think that would benefit us all in the future, whether you manage an OLTP environment or an OLAP environment.
SQL Server 2008 brings something new for everyone � whether you�re an avid DBA or more involved in database development. In most cases, like at our workplace there is always a crossover of DBA/Developer tasks anyhow.

You may not be immediately pursuing an upgrade to 2008 but I�m sure many of us will benefit greatly from features discussed in this article, so its always good to know what�s �coming around the corner�. I would, at least encourage you to evaluate the new features and start to plan for the future, whether it be short or long-term.

Getting Started

There are three ways you can get the newer version of SQL Server on your system. The first is to upgrade either SQL Server 2000 or 2005 to 2008. It will transfer all of the data, keep your settings, security, database encryption and so on to the higher version, replacing all of the files along the way (sounds straightforward, use the Upgrade Advisor too � just in case). The other method is to install SQL Server on the same system as the earlier version. It will leave the old system intact, replacing only those common files that are needed by the new version. This is called a �side-by-side� installation. Finally, you can install SQL Server 2008 on a SQL-less box and start from scratch, allowing you then to simply export/re-import data from other servers etc.

The installer in SQL Server 2008 has changed and has a few new interesting features.

Now when you install, you download one set of media. The evaluation editions, Developer Edition and all of the other editions are on a single DVD, and when you enter your license code it will install the proper bits. Note, this is not the case with the Express edition though � it has its own DVD.

Also the example databases (AdventureWorks etc) are no longer included. You now have to get all of the examples from Microsoft�s Codeplex website. (Not sure whether this is convenient or not really?)

So what�s been improved?

Backup Compression

I had to start with this one � one of my biggest headaches is the ability to perform regular/more frequent backups, all because of the lack of disk space we have available. The time taken to perform the backup and any subsequent restores hinders our day-to-day operations somewhat.

Now, SQL Server 2008 supports backup compression.

As data becomes vitally important, the need for it to be readily available 24-7 etc and the fact that the volume of that data continues to fluctuate � the importance of backups has also been considerably increased. More often than not your backup of your database will be very similar in terms of size (disk space) to the actual database data itself. An ideal scenario states that numerous historical backups will be kept for those all important �just-in-case� situations. The light at the end of the tunnel for organisations (like mine!!!!) comes in the form of backup compression.

Hopefully it will eliminate the need for us, and you, to hunt down temporary disk space, scour the enterprise for spare servers and hog network bandwidth etc

The real thorn in our side I suppose, is the time taken to perform backup and restore operations. Prior to SQL Server 2008, during the backup process, the database has to be read and then written to a new location � this is an awful lot of I/O activity. Restoring is the same.

Well, those magicians at Microsoft have finally found the cure. As the database is being written into the backup, it gets compressed, hence taking less disk space, less network resources and overall time. And of course, if its takes less time to backup, it will decrease the time taken to restore � which when you�re fighting to get a crashed database server back online at 3am in the morning, is more than welcomed.

OK, some of you may actually perform compression of your backups anyway, so disk space doesn�t become an issue, but with SQL Server 2008 this further complication becomes unnecessary. And I�m sure there are other methods out there for compressing your backups and third party tools for instance, Mr Gates and his team are now just supplying the option �out-of-the-box� so to speak.

And besides maybe that�s another advantage of SQL 2008 � you no longer need to purchase licenses for those third party solutions! Plus, as a DBA, there is less to maintain (worry about!).

As SQL Server 2008 is still in its infancy, I�m sure, as feedback continues to be submitted; even more advances in this area will be forthcoming. With regards to exact figures, I�ve seen 5:1 ratios mentioned, but obviously this will all depend on your particular data set and configuration.

Backup compression will certainly save my organisation money, either on additional software licenses or on storage/network costs, but equally important � time. Thanks Bill !
Another compression feature, called Sparse Columns, allows you to store nulls without taking up physical space. Sparse Columns will come in handy for large tables containing many null values. (It's just too bad you can't use Sparse Columns and Data Compression on the same table!)

Oh and by the way, surprise, surprise!! Backup Compression is only available in Enterprise Edition.

So that�s one major problem solved, what else does 2008 offer us?

Resource Governor

Dare I mention it, but Oracle has had this feature for a while now (as with a few of the newly introduced features with SQL 2008 actually), but at last SQL Server now has its own workload management mechanism. Bear in mind though, this is Microsoft's first stab at granular resource management.

Resource Governor enables you to limit the amount of CPU and memory that incoming application requests can use.

Having experience with Oracle RDBMS too, I have seen the benefits of being able to allocate, prioritise and limit specific resources to support individual workloads. The main driver for this is obviously the need to improve overall performance of your SQL Server database. You can now identify the problem areas and limit the no of ad-hoc queries that get run and cause havoc with your overall system.

For some organisations, with the improvements in performance that this new feature can give, Resource Governor is a much-welcomed addition.

Hot Add Processors

SQL Server 2008 supports dynamically adding CPUs to a running system. Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. Always a selling point too, reducing downtime and server maintenance headaches!

Transparent Data Encryption

Any new feature that offers improvement in security is always greeted with a positive response. Transparent Data Encryption encrypts your database files automatically, without needing to alter any existing applications. This also applies to the database logs and your database indexes.

As well as general data, backups can also be encrypted. Good news for all companies hoping to avoid a repeat embarrassment of a DVLA-like scenario where if the data somehow gets �mislaid� in transit, then you can be assured that the data is encrypted and hence deemed useless if found in the wrong hands. And because this data is encrypted, you can control access of that data to specific users or groups.

Configuration Servers

This is a new method of administering multiple servers. An instance of sql server is designated as a configuration server to maintain list of registered servers. I can already see this being put to good use in my workplace.

Change Tracking

SQL Server now allows applications to obtain incremental changes to user tables by tracking changes, which enables developing synchronization applications is easier and faster. This should prove popular with DBAs who want to speed up ETL processes too � and lets be frank, anything that speeds up those laborious processes has to be a plus point.

Partitioning

Now I haven�t had too much opportunity to play with partitioning etc but 2 improvements in this area include:-
Query Processing on Partitioned Objects
SQL Server 2008 improves query processing performance on partitioned tables by changes the way query plans are represented. This enhances the partitioning information provided in execution plans.

Partition Switching on Partitioned Tables and Indexes
Partitioning data enables you to manage and access subsets of your data quickly and efficiently while maintaining the integrity of the entire data collection. Now you can use partition switching to quickly and efficiently transfer subsets of your data by switching a partition from one table to another.

Spatial Data Storage, Methods and Indexing

Spatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads, or lakes.
A new GEOGRAPHY data type that enables you to store spatial data and perform operations on it
A new GEOMETRY data type that enables you to store planar spatial data and perform operations on it.
At the Birmingham launch, this feature proved very popular indeed and some very good working examples were presented.

FILESTREAM storage

FILESTREAM in SQL Server 2008 enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data.

Improved Management

If there's any one new feature in SQL Server 2008 that will change the way DBAs manage their environments, it's probably Server Groups. Server groups let you run any query you like against an entire group of servers simultaneously.
So instead of having to cycle through all of your servers to check job status, deploy stored procedures, and so on, you can manage or push code to any number of servers with a single query.

On top of that, Server Groups can be further extended and enhanced to do some great things. Just be careful what scripts you are running against a server group!

SQL Server Management Studio Enhancements

Anything that makes the way we work and use tools on a daily basis easier is always welcomed news :-

Transact-SQL Query Editor IntelliSense

Hooray � what took them so long?!
The Transact-SQL Editor now provides IntelliSense functionality such as word completion and error underlining. IntelliSense is provided for frequently used Transact-SQL elements.

Transact-SQL Error List Window

SQL Server Management Studio includes an Error List window that displays the syntax and semantic errors generated from the IntelliSense code in the Transact-SQL Query Editor.

Tools Enhancements

In the Query Editor window, you can query multiple servers at the same time by opening query windows from registered server groups. The query results can be combined into a single results pane, or can be returned in separate results panes.
You can access SQL Server Profiler from the Query Editor window, from the Query menu, the Query Editor toolbar, or by pressing CTRL+ALT+P.

Transact-SQL Enhancements
There are numerous enhancements in TSQL, I�ve touched upon a few below:-

Lock Escalation Option
A new LOCK_ESCALATION option of ALTER TABLE allows you to disable lock escalation on a table.

Query Hints
Table hints can now be specified as query hints to provide advanced query performance tuning options.

Hierarchyid Data Type
SQL Server 2008 introduces a new system-provided data type to encapsulate hierarchical relationships.

MERGE Statement
This new Transact-SQL statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table.

Table-Valued Parameters
The Database Engine introduces a new parameter type that can reference user-defined table types.

User-Defined Table Type
The Database Engine introduces a new user-defined table type that supports representing table structures for use as parameters in stored procedures and functions, or in a batch or the body of a stored procedure or function.

Compatibility Level
ALTER DATABASE SET COMPATIBILITY_LEVEL replaces sp_dbcmptlevel for setting the database compatibility level.

GROUPING SETS
The GROUPING SETS, ROLLUP, and CUBE operators are added to the GROUP BY clause.

Table Hints
The FORCESEEK table hint is added to provide advanced query performance tuning options.

Transact-SQL Row Constructors
Transact-SQL is enhanced to allow multiple value inserts within a single INSERT statement.

Powershell

The inclusion of PowerShell will reinvent the way DBAs manage their environments by taking complicated cursors out of management scenarios. It will allow us to do complicated things so much easier than in Visual Basic or T-SQL.
This is the debut of PowerShell in SQL Server, and there are some rough spots. But I expect the boffs at Microsoft will continue to enhance & improve. Rumours state that PowerShell will eventually replace sqlcmd as the command line interface.

Other improvements

Other areas that have been enhanced include Database Mirroring where they have improved log performance, improved use of log send buffers and introduced Page read-ahead during the undo phase. A database mirroring partner running on SQL Server 2008 or later versions now automatically tries to resolve certain types of errors that prevent reading a data page.

They have introduced new SQL Server Extended Events and Dynamic Management Views (e.g. to aid presentation of memory information). The number of DDL triggers and event notifications have been expanded to include numerous stored procedures that perform DDL-like operations.

There is a lot of buzz also around the introduction of Change Data Capture which has been designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format � I need to look into this in more detail and it sounds a very interesting new feature.

Reporting Services

I�ve been playing around with SSRS (SQL Server Reporting Services) in 2005 a lot recently and looking at SQL Server 2008 it seems has though it�s had a major overhaul. For starters, it no longer relies on IIS for its report server capabilities.
Another major enhancement, called Tablix, is a new data element type that combines table, matrix, and list. Tablix gives you an easy way to work with grouping by allowing you to group on columns or rows as well as define adjacent or parent/child groups. Warning here, you may need to slightly change the way you author reports etc

Report Builder has been improved upon too. They have removed a number of limitations that could make it difficult to work with. Now, you can do almost anything in Report Builder that you can do in Visual Studio. Report Builder comes with wizards that make formatting data and charts automatic. Which is good news for people like me because; there is almost no reason to write reports in Visual Studio anymore.

Analysis Services

There is a new designer for aggregations, cubes, relationships and more. The Business Information Development System (BIDS) is now based on Visual Studio 2008. Many new enhancements are in Data Mining, including changes to the time algorithms. Plus there�s more integration with Microsoft Office for BI.
These, of course, are just a handful of the new features. There are a lot more powerful and innovative features which have been built into SQL Server 2008 which you can discover day by day as you work with it, and probably won�t get to know about before SQL Server 2011 arrives!

Notification Services

Notifications Services is no more in SQL Server 2008. It kinds of bugs me that a Service like this came and went so quickly (two rev's, SQL 2000 & 2005 and now no more). SQL Server 2005 generated a lot of enthusiasm with vastly improved BI and enterprise tools like NS, then they go ahead and pull it after only one product cycle!
I had always wanted to play with this. I guess it's a good thing I never invested much time in it.

Integration Services

Microsoft has taken some big steps to ensure that SSIS (SQL Server Integration Services) can compete directly with its competitors with some performance gains. In SQL Server 2008, you can now perform lookups of data and cache the data in a local file. The upshot is, obviously, that it will be much faster � so ETL processes are vastly enhanced.

Another scenario where SSIS improvements come into play is in handling changed data which reduces the time to load etc tremendously. There are other enhancements in SSIS as well. There�s a new data-profiling task that makes it easier to identify any data purity issues. And, SSIS scripts can now be written in both VB and C#.

To conclude
As I mentioned at the beginning, you may still be getting your head around SQL Server 2005 and all the new features that came with that version. Whether you decide to upgrade or not SQL Server 2008 is a different animal again, so ensure you take time to read up on all the new functionality, this article has merely highlighted certain areas and hopefully given you a taster.

Or you may still be trapped in the SQL 2000 world, I urge you to consider your upgrade options. On the one hand why upgrade to 2005 first, now 2008 is available? But on the other hand, SQL 2008 is still in its infancy and will no doubt be subject to much change.

Whichever situation you find yourselves in, be rest assured that Microsoft are continuing to improve SQL Server and its capabilities and the future can only be good.

If you like this article you can sign up for our monthly newsletter . There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Click Here