Manage Learn to apply best practices and optimize your operations.

Reasons to upgrade to SQL Server 2008

A SQL Server 2008 upgrade investment could pay off handsomely for your customers, due to some strategic changes in the platform and many feature improvements, some of which require no code changes to implement.

Service provider takeaway: Service providers have ample reason to prep their customers for a speedy upgrade to SQL Server 2008.

SQL Server 2008, due for release in the third quarter, will bring improvements on multiple fronts. Whereas adoption of SQL Server 2005 was slow for a number of reasons, there are many compelling incentives -- at both the macro level and the micro level -- to upgrade to SQL Server 2008. We'll lay out the big-picture ways SQL Server 2008 differs from its predecessors and then drill down into key features that are new or updated in the database management system.

More SQL Server resources
Chapter Excerpt: Common SQL Server Integration Services (SSIS) package deployment scenarios

Chapter Excerpt:
SQL Server 2005 Practical Troubleshooting

SQL Server 2008 carries some strategic advantages over SQL Server 2005 and SQL Server 2000. SQL Server 2008 has enhancements that will reduce your customers' total cost of ownership (TCO) and get them more bang for their hardware buck; for example, the new version handles both data compression (which results in saved disk space and faster disk access) and backup compression. This translates into higher transactions per second from the same hardware. And, the product will be backward-compatible with SQL Server 2005 and has the same look and feel, so users will be comfortable with it right off the bat. (SQL Server 2005 introduced a lot of new tools and features, and companies faced a skills gap when the product was initially released.)

Despite the lack of interface differences between 2005 and 2008, Microsoft has invested heavily in training resources to prepare the SQL Server community for the 2008 version. Significantly, mainstream support for SQL Server 2000 will end this month. Customers that are running SQL Server 2000 can pay to upgrade to SQL Server 2005 and later upgrade to SQL Server 2008 at no charge. Or, they can go directly from SQL Server 2000 to SQL Server 2008.

Traditionally, IT managers like to wait for the first service pack release before they take on an upgrade, following the theory that the Version 1.0 guinea pigs will have shaken out the problems before the service pack is released. But there's evidence that that approach isn't beneficial with SQL Server 2008. For instance, Microsoft tests its builds internally every night; copies of the latest builds are tested -- for both performance and functionality -- on hundreds of virtual machines. Show-stopping bugs are detected and squashed well before the release-to-manufacturing (RTM) version ships.

And the company's service pack strategy is shifting. While service packs contain a group of bug fixes and additional functionality, bugs fixes are released in "cumulative updates," which are released at regular intervals even before the first service pack ships. Customers waiting for the first service pack before taking definitive steps toward upgrading may be postponing their plans unnecessarily. It's also worth noting that some of the problems that crop up after an upgrade can be traced to mistakes made by a customer's IT staff rather than the product. For instance, staff might have deployed the product for unintended usages or using unsupported methods.

Finally, migrating to SQL Server 2008 this year should pay dividends through the next two releases of SQL Server, essentially giving your customers at least three to six years (if not more) in which they won't have to face the challenge of a new upgrade (assuming that Microsoft sticks to its announced 18- to 36-month release schedule).

Your clients are likely to have a requirement that makes the upgrade to SQL Server 2008 compelling. It's important for service providers to familiarize themselves with the new features to identify what will be most relevant for customers. For customers with stringent auditing requirements or heavy SQL full-text search consumers, the features are obvious. For other clients, you might need to dig deeper to find what will drive an upgrade to SQL Server 2008.

Ramped-up features of SQL Server 2008

Beyond the macro-level advantages, SQL Server 2008 has many feature improvements that will make the product compelling for your customers. These improvements generally fall into one of three categories: those that can be put into use immediately, with no code changes needed; those that require minimal changes; and those that require re-architecting.

First, the pain-free improvements:

* SQL full-text search: The full-text search catalogs will reside in the database. Full-text searches won't be remote queries but will be performed in a similar manner to other indexes. This will bring huge gains in performance. Also, users will be able to join search results with other search arguments without the query optimizer waiting for the entire results set from the full-text engine.

* Policy-Based Management: All SQL Server instances on the network can be managed with the Distributed Management Framework/Policy-Based Management feature of SQL Server 2008. You'll be able to tell which servers fall out of predefined categories -- for example, which servers have table names that are owned by the dbo role.

* Native SQL Server database and log backup compression: This feature will reduce the amount of time required to perform database and log backups and restores. Not only are the backup and restore times dramatically reduced, but the database and log backups are normally compressed to about 5% of the original backup size. The smaller backup sizes make them far more manageable.

* Hot-add CPU: This feature will enable you to add a CPU to a server without service interruption, should your hardware support it.

* Performance: SQL Server 2008 has better performance than SQL Server 2005, via enhancements to features such as partitioning and data loading. In addition, the product's Performance Studio, SQL Server 2008's new administrative console, will collect performance-based data on SQL Server instances and allow you to detect changes to workloads or system response.

* Enhanced auditing features: These allow highly granular auditing on all objects or a specific update.

* Transparent data encryption: This will allow for a database, its log file and its backup to be encrypted, providing high security while optimizing performance.

Next come the enhancements that require small changes to use:

* Filestream data type: This enables storing of columns in the file system, providing very fast access for binary large objects (BLOBs) -- much faster than if they were stored in the database.

* Column-level compression: The contents of a table will be compressible on the row or page level, with minimal (if any) decrease in performance. While this will mean that the table will consume less space on disk, the real gain is that more data can be read in a single 8 KB chunk, since the 8 KB page now holds more data.

* Server Broker: This feature, which first shipped in SQL Server 2005, has been improved. In SQL Server 2008, conversations can be prioritized.

* SQL Server Common Language Runtime User-Defined Data Types (CLR UDTs): CLR UDTs will be able to contain up to 2 GB of data.

* TSQL enhancements: The Merge function will allow you to selectively merge data into a table where the data does not exist or update or delete data that does exist. Table Value functions will allow you to pass a read-only table to a stored procedure. Grouped sets will have enhancements around how you can group in a group operation. And the hierarchyID function will enable tree-like functions as part of the SQL language.

* XML features: SQL Server 2008 will have lax schema validation (intended to support Office 2007 documents but can also be used to relax the very strict validation enforced when you tried to store XML documents in the XML datatype) and support for lists and unions. For database administrators (DBAs) working with XML, the lax schema validation can be especially valuable.

Finally, here are the improvements that take a bit of work to take full advantage of:

* Support for the remote BLOB API: BLOBs can now be stored in remote BLOB stores such as EMC's Centera, Fujitsu's Nearline and even a Windows file server. A reference will be stored in the table that will point to the actual location of the BLOB on the BLOB store.

* Spatial data types: SQL Server 2008 will support geospatial-aware data. You'll be able to store latitude and longitude coordinates in tables and perform relational queries on them to determine nearest neighbor or true geographic distances between points.

* Integration into Visual Studio: This enables Language Integrated Query (LINQ) queries, entity-aware frameworks and source control integration into Microsoft's SourceSafe source code control application.

About the author
Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.

Dig Deeper on Database software management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.