We have a couple of databases on our server.
We would like to upgrade these databases to sql 2008.
Is there any chance that some application features won't work on 2008 that did work on 2005?
I was told that sql 2008 was 100% backward compatible with 2005.
No release of SQL is 100% backward compatibile. With every release, there is a well documented list of deprecated and disconetinued features, and SQL Server 2008 is no different. The list is published at SQL Server Database Engine Backward Compatibility. This contains a list of features no longer supported on SQL 2008, like:
In addition, no SQL Server feature is ever discontinued without ample heads-up warning. All features disconetinued in SQL 2008 were on the list of deprecated features in SLQ 2005. You can use SQL Profiler to monitor your SQL 2005 applicaiton and watch for the events:
The first event will fire every time your application uses a feature on the 'endagered' list. The second event will fire when you use a feature that will be discontinued in the next release.
Finaly ther eis the SQL Performance counter for deprecated features, see What deprecated features am I using?:
Between all the available documentation and infrastructure to monitor for deprecated features, you have plenty a means to figure out if your application is using any discontinued features.
Now that being said, unless you use features from dark ages of SQL Server 6.0. your application will likely run unnaffected on SQL Server 2008. There are plenty of reasons to upgrade, an primarily I always cite page compression, which is literarly a big performance boost at free cost (I exagerate for dramatic effect, but only slightly).
You should go ahead and upgrade, but do it smart:
So depending on how pretious your data is you want to apply all, some, or none of the above. I may say that if you were in the 'all' boat, you probably wouldn't have asked the question to start with ;) so probably some of the advice above applies to you.
There's no way we can tell you what your applications will do.
I think your best course of action is to have a second server which runs SQL Server 2008, migrate the databases to it (perhaps one at a time, slowly), and if there are any problems switch back to the SQL Server 2005 server.
This does require two servers (physical or virtual), but gives you perfect recoverability in either case.
One good test is to run the Microsoft SQL Server 2008 Upgrade Advisor. Microsoft SQL Server 2008 Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 in preparation for upgrading to SQL Server 2008. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.
You can download it for free here: http://www.microsoft.com/downloads/details.aspx?FamilyID=f5a6c5e9-4cd9-4e42-a21c-7291e7f0f852&displaylang=en
You can run this in production, but as was said above, a test environment is always best.