September 11, 2009 at 9:56 am
We are developing the latest version of our product on SQL 2008 but since our clients are not required to run the application on SQL 2008 we have the compatibility level set to SQL 2005 on the databases on our SQL 2008 development servers. The purpose of this is to verify that our product will run properly on SQL 2008 and is backwards compatable to SQL 2005. However we are finding that even though we have the database set to SQL 2005 compatibility it still allows a developer to use some of the SQL 2008 features. We used a similar process when we migrated from SQL 2000 to SQL 2005 and having the lower compatibility prevented developers from using the SQL 2005 features. This seems like a bug to me.
I assume others are running into this problem.
October 9, 2009 at 8:10 am
I do this all the time and don't run into this problem. Perhaps if you were to post some examples of 2008-only features you are able to execute within the context of a database with compatibility level set to 2005?
October 9, 2009 at 8:15 am
I think there is very little issue with this provided you don't use any SQL 2008 only functionality.
The places where you needto be conscious are SSIS, SSAS, and SSRS. If you are doing anything with those there isn't a 2005 compatibility mode.. you would need to develop with the 2005 tools.
CEWII
October 9, 2009 at 9:17 am
One of the issues was with the new Date and Time datatypes. Even though the database was set to SQL 2005, it allows the developer to use these new data types. We discovered this when we started testing the application on a SQL 2005 environment.
With the SQL 2000 to 2005 migration we could be assured that developers would not inadvertantly use a new feature by setting the compatability level of the database at SQL 2000.
Since our application must be backwards compatable (to SQL 2005) for this interim version, this is very important that our application works on SQL 2008 and SQL 2005.
October 9, 2009 at 9:19 am
Elliott W (10/9/2009)
I think there is very little issue with this provided you don't use any SQL 2008 only functionality.
That is what I am trying to prevent but leaving the compatability level set to SQL 2005. With 50 developers one is likely to use something and we might not catch it until we test against prior versions. I would like to prevent it during the coding phase.
October 9, 2009 at 10:20 am
It's a compatibility mode, not an emulation. It's designed to make sure that any functionality that has been deprecated in 2008 is still supported when in 2005 mode, so applications that don't support the current version can still run against it. It's not meant to work in reverse and prevent new functionality from working (which is useful in most cases as you can slowly bring code up to date while still maintaining compatibility with older code)
The 2000 compatibility mode was exactly the same - it lets you use non-ANSI syntax like *= that would error in 2005 or 2008 mode, but still allows you to use new functionality that was part of the new version (such as CTE's).
October 9, 2009 at 10:24 am
Ok, thanks for the reply. It doesn't work like I thought it did and apparently we just didn't get bit by it on our SQL 2005 migration. I am trying to reduce my database sprawl and not have both a SQL 2005 and SQL 2008 environment that I require developers to unit test their SQL code against. But perhaps that's what I will have to do.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply