Deep dive into SQL Server 2008

Microsoft's 'Katmai' is packed with performance and management features enterprises will love, and even small shops will have reasons to make the upgrade

Look at it like this: Database size isn't the reason you've chosen enterprise edition. Because most versions of SQL Server can handle unlimited data size, enterprise edition is generally chosen for the features. As a result, you're likely to have plenty of SQL Server boxes in your shop with very large data sets that aren't on enterprise, and they need compressed backups too, which means you probably already have a third-party backup solution in place. To switch backup routines for a small subset of your SQL Server boxes just doesn't make sense. You'll surely want to have the same solution for your entire environment.

Second, unlike SQL Server, the third-party backup solutions have object-level restore, which can come in very handy in a number of situations. If you go with SQL Server's Backup Compression for your enterprise servers, you're losing functionality.

Third, the third-party solutions have centralized repositories and provide centralized reporting and alerting. So if you use SQL Server's native compression, you've effectively eliminated centralized management of backups for those boxes.

There are other features that third-party backup solutions bring to the table, but incomplete coverage, object-level restore, and centralized backup management are the biggest reasons that SQL Server's Backup Compression isn't going to be viable for most shops.

Index improvements

Indexes have received an overhaul. Not only can indexes be compressed in SQL Server 2008, but you can also build filtered indexes. Filtered indexes have a "where" clause, allowing you to partially index a large table. This may not seem very useful at first, but there are situations where it's very beneficial.

For example, let's look at a Sparse Column situation. Say you have 400 million records in a table with a significant number of null values, and you've defined a Sparse Column, so you're not taking up space for all of those nulls. Well, if you don't want to take up storage space with null values, then you surely don't want to take up index space with them either. Here you would define a filtered index on that sparse column where the value is not null so that only rows with actual data in them are indexed.

Not only can you save a lot of index space with filtered indexes, but the queries that use those indexes will be faster because they're running against a subset of the entire data set. You also shrink the maintenance window and re-index space for the index.

Join the newsletter!


Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

Tags databasessql server 2008software applications

More about InformaticaMicrosoftNICEOracleParadigmSAP AustraliaSpeed

Show Comments