Befriending Dragons

Transform Tech with Anti-bullying Cultures

Leave a comment

Out with the Old (SQL 7.0) and in with the New (SQL 2008 SP2)

We are fast approaching the end of support for SQL Server 7.0. It has been in “extended support” for a while, meaning you could call for help but no non-security hotfixes would be created. But after January 11, 2011 extended support for SQL Server 7.0 ends and you will no longer be able to contact Microsoft for help with SQL Server 7.0. See more details here:

But everything is not ending! Indeed we have a new service pack for SQL Server 2008! SQL Server 2008 SP2 is now available for download! SP2 includes all hotfixes from SP1 CU1 to CU8. You can also get the updated feature pack.


•          SQL Server 2008 SP2:

•          SQL Server 2008 SP2 Express:

•          SQL Server 2008 SP2 Feature Packs:


Key improvements in Microsoft SQL Server 2008 Service Pack 2 are:


  • Reporting Services in SharePoint Integrated Mode. SQL Server 2008 SP2 provides updates for Reporting Services integration with SharePoint products.   SQL Server 2008 SP2 report servers can integrate with SharePoint 2010 products. SQL Server 2008 SP2 also provides a new add-in to support the integration of SQL Server 2008 R2 report servers with SharePoint 2007 products. This now enables SharePoint Server 2007 to be used with SQL Server 2008 R2 Report Server. For more information see the “What’s New in SharePoint Integration and SQL Server 2008 Service Pack 2 (SP2)” section in What’s New (Reporting Services).
  • SQL Server 2008 R2 Application and Multi-Server Management Compatibility with SQL Server 2008.
    • SQL Server 2008 Instance Management. With SP2 applied, an instance of the SQL Server 2008 Database Engine can be enrolled with a SQL Server 2008 R2 Utility Control Point as a managed instance of SQL Server. SQL Server 2008 SP2 enables organizations to extend the value of the Utility Control Point to instances of SQL Server 2008 SP2 without having to upgrade those servers to SQL Server 2008 R2. For more information, see Overview of SQL Server Utility in SQL Server 2008 R2 Books Online.

o   Data-tier Application (DAC) Support. Instances of the SQL Server 2008 Database Engine support all DAC operations delivered in SQL Server 2008 R2 after SP2 has been applied. You can deploy, upgrade, register, extract, and delete DACs. SP2 does not upgrade the SQL Server 2008 client tools to support DACs. You must use the SQL Server 2008 R2 client tools, such as SQL Server Management Studio, to perform DAC operations. A data-tier application is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.


SQL Server lifecycle and the compatibility level settings

I’ve had several people ask how the compatibility mode/level fits in with the SQL Server lifecycle. For example, the question might be: once we no longer support SQL Server 2000, does that mean we don’t support databases restored/attached to later versions of SQL and still set to 80 compatibility mode for example? The answer is that each version of SQL Server has a set of compatibility levels it supports. That support does not change based on the lifecycle. So for example, SQL Server 2008 R2 supports compatibility levels 80 (SQL 2000), 90 (SQL 2005), and 100 (SQL 2008). When you restore a SQL Server 2000 database to SQL Server 2008 R2 it remains in 80 compatibility mode until you issue ALTER DATABASE {dbname} SET COMPATIBILITY LEVEL = 100 (in older versions use sp_dbcmptlevel). When extended support for SQL Server 2000 ends in 2013, Microsoft will not support SQL Server 2000 any more. But we will still support a database on SQL Server 2008 R2 that is in 80 compatibility mode because that is a supported level/mode for R2. So the lifecycle refers to the actual instance, not the database level.

I want to add some information here that a lot of people don’t understand. The compatibility level affects only some of the behavior of that database, mostly related to syntax and available features. A database set to 80 compatibility on SQL Server 2008 R2 is NOT the same as a database actually running on SQL Server 2000. For example, you can NOT restore the 2008 R2 80 compatibility mode database back to a 2000 instance. The optimizer is the 2008 R2 optimizer, not the 2000 optimizer. The compatibility mode/level is meant to be a temporary workaround while you fix syntax or keyword issues.