With the upcoming release of SQL Server Code Name “Denali”, the next version of Microsoft SQL Server,  the supported upgrade paths have changed and are an important consideration for customers on a down-level version of SQL Server. There is some confusion on the differences between database version, database edition and database compatibility and how these relate to supported upgrade paths.

Database Releases

Kalen Delaney has written concise history of SQL Server in Chapter 1 of Inside SQL Server 2000 by Microsoft Press.

The various Microsoft SQL Server releases since 1991 are shown in the following table.

Release Release Name Product Version
11 SQL Server Code Name “Denali”  
10.5 SQL Server 2008 R2 Service Pack 1 10.50.2500.0
  SQL Server 2008 R2 RTM 10.50.1600.1
10.0 SQL Server 2008 Service Pack 2 10.00.4000.00
  SQL Server 2008 Service Pack 1 10.00.2531.00
  SQL Server 2008 RTM 10.00.1600.22
9.0 SQL Server 2005 Service Pack 4 9.00.5000.00
  SQL Server 2005 Service Pack 3 9.00.4035
  SQL Server 2005 Service Pack 2 9.00.3042
  SQL Server 2005 Service Pack 1 9.00.2047
  SQL Server 2005 RTM 9.00.1399
8.0 SQL Server 2000 Service Pack 4 8.00.2039
  SQL Server 2000 Service Pack 3a 8.00.760
  SQL Server 2000 Service Pack 3 8.00.760
  SQL Server 2000 Service Pack 2 8.00.534
  SQL Server 2000 Service Pack 1 8.00.534
  SQL Server 2000 RTM 8.00.194
7.0 SQL Server 7.0 RTM  
6.5 SQL Server 6.5  
6.0 SQL Server 6.0  
4.21 SQL Server 4.21  

 

Database Version

To determine the version of SQL Server you can use one of these methods.

 SELECT @@version
SELECT SERVERPROPERTY('productversion') AS SqlVersion,
SERVERPROPERTY('productlevel) AS SqlLevel,
SERVERPROPERTY ('edition') AS SqlEdition

Database Editions

Database editions package different performance, security, availability and scalability features for licensing purposes.   For example, Enterprise edition exposes enterprise features that are not available in Standard edition. You can get more information on features supported by the various edition of SQL Server here.

Many clients develop database applications using SQL Server Developer edition from MSDN.  Developer edition has the same feature set as Enterprise edition.  An important point here is that if your database uses Enterprise only features, this will limit your options for deployment, disaster recovery and upgrade paths.  The four features that are in this category are:

  • Partitioning
  • Transparent data
  • encryption
  • Change data capture

 As a rule, you can upgrade to the same edition or one that has more features however it is not possible to downgrade to an edition with less features.

Paul Randall has a good post on the pitfalls on Enterprise only features in disaster recovery situations. You can use the DMV – dm_db_persisted_sku_features -  to check your database for Enterprise Only features.

 SELECT * FROM sys.dm_db_persisted_sku_features

Database Compatibility

SQL Server is not up-level compatible. You cannot attach a database that was created or upgraded on SQL Server 2008 to any earlier version of SQL Server.

You also cannot attach a database that was created on an earlier version without going through the proper upgrade procedures. Jonathan Kehayias has great post on the database upgrade process.

New databases inherit the compatibility level of model database.  The default compatibility level of a database depends on the database release.

  • 60 = SQL 6.0
  • 65 = SQL 6.5
  • 70 = SQL 7.0
  • 80 = SQL 2000
  • 90 = SQL 2005
  • 100 = SQL 2008

The compatibility level of a database affects the behaviour of new features.  New features might work with older compatibility level but you may have to modify SET options.

An upgraded database retains its compatibility level unless the compatibility level is altered after the database is upgraded.

 ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

SQL Server 2012 and Upgrade Paths

Microsoft has published the matrix of supported operating system for the next version of SQL Server and the supported upgrade paths.

Upgrades will only be supported from the following SQL Server versions:

  • SQL Server 2005 Service Pack 4 or later
  • SQL Server 2008 Service Pack 2 or later
  • SQL Server 2008 R2 Service Pack 1 or later

This implies that “Denali” will only support the following database compatibility levels.

  • 90 = SQL 2005
  • 100 = SQL 2008
  • 110 = SQL 2012

Customers with databases on down-level versions of SQL Server should start early to review their migration options and upgrade to a “Denali” supported version of SQL Server.

TAGS: , , ,