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:
- DUMP and LOAD statements
- BACKUP LOG WITH NOLOG/BACKUP LOG WITH TRUNCATEONLY
- BACKUP TRANSACTION
- DBCC CONCURENCYVIOLATION
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?:
select instance_name as [Deprecated Feature] , cntr_value as [Frequency Used] from sys.dm_os_performance_counters where object_name = 'SQLServer:Deprecated Features' and cntr_value > 0 order by cntr_value desc
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:
- Run the upgrade advisor, read it's advice
- Have a test environment, deploy your application on test first
- Have a comprehensive battery of functional tests to perform, validate that your product still does work as advertised. If you can measure test code coverage try to reach some 85-90% coverage
- Have an exit straegy if things go bust. Keep in mind that an upgraded database can never be downgraded back. So keep safe copies of the SQL 2005 database files in case you are forced back. Have a technology plan for how to apply post-upgrade transactions back to 2005 if needed, or have a business plan to explain why transaction were lost (I'm not kidding, it may be a perfectly acceptable solution).
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.