In-Depth

SQL Server 2008 Is Finally Here. Now What?

Microsoft's latest relational database management system contains a host of new features, but are they enough to make you migrate?

• By Eric Johnson
• 10/01/2008

The new version ships with many important features and enhancements that were not contained in SQL Server 2005. Because most of these improvements are features and not overhauls to the engine or the toolsets, many have said that SQL Server 2008 is really just SQL Server 2005 release 2. So just what is a DBA to do? Is it time to migrate to the latest and greatest, or are you just fine in staying with your current SQL Server 2005 or even 2000 environment?

As with most things in life, the answer comes from looking within. If you decide that some of the many new features available in the new release make your life simpler or enhance a process you're currently doing in your SQL Server environment, then migrating to SQL Server 2008 is probably in order. So just what are the new features that would spur a move to the new platform? I'm glad you asked.

Features Galore
SQL Server 2008 brings us an enhanced set of auditing features; perhaps one of the most important is Change Data Capture (CDC). Using CDC, you can capture and record any INSERT, UPDATE or DELETE activity that happens on your database. Once you enable CDC for a database, you can specify on which tables you want to track activity. SQL Server writes information about changes made to the tables to a set of systems tables inside the database on which you're running CDC. When a user runs an INSERT, UPDATE or DELETE statement against your table, the fact that the transaction occurred is recorded along with the relevant data. For INSERTS, the inserted values are recorded, and for DELETE statements the values deleted are recorded. UPDATE statements are a little different. The data as it existed before the update and the data as it exists after the update are both recorded. Using CDC, you can see not only what has happened to your data, but you can also recover data lost due to changes or even re-create an update sequence for testing purposes. This is a great addition to the product, and it really provides a great deal in the way of auditing that had previously only been possible with third-party tools.

SQL Server 2008 also packs many new security features. Transparent Database Encryption (TDE) is a huge step toward keeping your data more secure. With TDE, your database files and their backups are encrypted using a server master key. This ensures that only the SQL Server on which the database resides can open the database and access the information inside. If a would-be hacker gets to your files, he will be unable to open them with another copy of SQL Server. This is huge in terms of the protection of your sensitive data. What's more, as the name implies, it's transparent. You can enable TDE on your databases, and the users and applications won't even know it happened. This means that you won't be wasting time rewriting applications or training users in order to take advantage of this new feature.

A topic that has stirred up the SQL Server community as of late has been data compression. SQL Server 2008 gives us the ability to compress data and save on disk space. Without getting into many specifics, data in SQL Server can be compressed at the page level. This means that when you compress a table, it actually does it a page at a time. I'd advise you to tread lightly with data compression. There has been a lot of debate over the benefit versus the potential overhead. I won't weigh in on the debate here, but you should make sure you do your research and testing before implementing compression in your environment. That said, when disk space is at a premium, you can use this feature to pack more data onto the platters.

Backup Benefits
On the backup side of the house, SQL Server 2008 provides backup compression. Unlike using data compression, I have no reservations about using backup compression. Saving disk space on the drive where you store your backups is great, but the smaller file size on individual backups is fantastic. Have you ever needed to copy a large database from one server to another? Maybe you're setting up replication with a backup, a test environment, or even recovering after a server failure. You may have noticed that 500GB files don't copy over the network with lightning-fast speeds. The basic rule of copying is that the smaller the file, the faster the copy. You've probably even found yourself zipping a backup file in an attempt to make it smaller and more portable. Now, with backup compression, you have the savings of file size built right into the native backup.

To use backup compression, you just need to add the WITH COMPRESSION option to a BACKUP DATABASE statement as shown below:

BACKUP DATABASE Adventureworks2008
 TO DISK = 'D:\Backup\AdventureWorks.bak'  
WITH COMPRESSION   

If you're wondering how much savings backup compression offers, here's an example. I backed up a 965MB database without compression and got a 636MB backup. Using compression, the same database produced a 147MB backup file. Your mileage may vary, but as you can see, the savings can be significant.

Learn to Merge
The new MERGE statement in SQL Server 2008 makes me just a little giddy. SQL Server developers will love this new bit of T-SQL syntax when writing stored procedures for modifying tables. Gone are the days of "IF-THEN" logic to decide whether a row needs to be inserted, updated or deleted. MERGE allows you to take care of the logic and the modification all in one shot. What's more, you can compare an entire record set all at once instead of going row by row. Here's a quick example of using MERGE:

MERGE tbl_address AS current_addresses
USING ( 
SELECT customer_objid = address_label, 
addressline1, addressline2, city, region, country, zip
code, is_deleted 
FROM @addresses)
AS 
source_addresses(address_label, addressline1, 
addressline2, city, region, country, zipcode, 
is_deleted) 
ON 
( 
current_addresses.address_label = source_address 
es.address_label 
) 
WHEN NOT MATCHED THEN 
INSERT (address_label, addressline1, addressline2, 
city, region, country, zipcode) 
VALUES (source_addresses.address_label, 
source_addresses.addressline1, 
source_addresses.addressline2, 
source_addresses.city, source_addresses.region, 
source_addresses.country, source_addresses.zipcode) 
WHEN MATCHED AND source_addresses.is_deleted 
= 1 
THEN DELETE 
WHEN MATCHED THEN 
UPDATE 
SET address_label=source_addresses.address_label, 
addressline1=source_addresses.addressline1, 
addressline2=source_addresses.addressline2, 
city=source_addresses.city, region=source_address 
es.region, country=source_addresses.country,zip 
code=source_addresses.zipcode

That's a lot to digest, so let's take a quick look at the different sections. The USING section defines the "new" data, in this case a table variable. The ON section defines the join between the new and existing data. Finally, you have a series of MATCHED statements that do things like insert WHEN NOT MATCHED, update WHEN MATCHED or delete WHEN MATCHED and some other value indicates delete. This is a real time saver, and the syntax is much cleaner that the older alternative of using homegrown logic.

Enforce Your Policies
I've saved one of the best new features for last-namely, policy-based management. Have you ever wanted to set up a series of policies that can control what users can and can't do on your SQL Servers? Now you can, using policy-based management. To use a policy, first you must set up conditions. Conditions tell a policy what to look for in your database. Conditions are applied to facets, or objects on your server or in your database. Some common facets are Database, Multipart Name, Tables, Views, Stored Procedure and Users. This list is by no means complete, but it gives you an idea of the sorts of things a condition can look at.

Once you've selected your facet, you'll need to define an expression that tells the policy what it is you want to look at on the facet. For example, if you choose Table as your facet, you can use a handful of fields in your expression. You can look at the create date, name, owner, row count, whether it has a trigger or even the last-modified date. Again, this list is not complete. As an example, if you wanted to restrict users from using the term "table" in a table name, you'd use the Table facet and set an expression of @Name NOT LIKE "table."

Now all that's left is to create a policy that uses the condition. When you create the policy and apply the condition, you also specify the specific objects to look at. You can look at all tables, or specific tables in all databases or specific databases. Lastly, you need to define an evaluation mode to tell SQL Server how to apply the policy. By default, it will only run on demand, meaning that you have to request an audit of your policy. You can also run it on schedule or on change depending on the condition. If you run it on demand or on schedule, you'll receive a report of the objects that violate your policy. If you choose instead to run it on change, then objects will be evaluated as they are created or modified. You can choose to log the event if a policy is violated or even prevent the modification from taking place.

This is just a quick look at policy-based management, but as you can see, it can be a very powerful tool for auditing and controlling the objects in your database.

Decision Time
Like it or not, SQL Server 2008 is here, and you now have to decide whether you want to migrate to the latest RDBMS from Microsoft. The internal engine has not seen a major overhaul in this version, so your migration will be largely based on the new features available.

I hope this gives you a little taste of what's available in the new release, but there's a lot more that we haven't covered.

Do yourself a favor and get familiar with the new options and features of SQL Server 2008. If some of them make your life easier or provide a new function you could benefit from, maybe you should consider making the jump sooner rather than later. Otherwise, you might have some time before you need to upgrade -- but don't wait too long.

Support for SQL Server 2005 won't be around forever.