I need to migrate the database in a vendor-supplied client-server application to SQL Server 2008. The version of the application we run is supported on SQL Server 2000 SP4 and SQL Server 2005 SP2, but not 2008 - the reason, however, is mainly because the vendor wants us to upgrade to the current version (which is planned for 1Q/2Q 2014), and didn't want to "enable" us to stay on an old version of the app.
My question is, if I restore the database on SQL Server 2008 with compatibility level 80, and don't make any changes to it (it's a vendor database), what issues could arise? I understand that I can't restore the DB down to 2005/2000 - that won't be necessary. I'm more concerned about what may have been coded into procs that assumes 2005 or below - I've found one example so far, and amending the version statement (@@version = "SQL Server 2008") did the trick. What else is out there to think about? FYI - only standard database objects are being used - no jobs, DTS, SSRS, SSIS, etc. In addition, I'm done extensive application testing logged into 2008 with no issues so far.
Personally, I highly doubt anything in their app relies on 80 compatibility, and think it is highly unlikely that anything will break if you move to 90 or 100. This is usually just a way for a vendor to delay having to do elaborate testing of their old app against a newer version of SQL Server, especially if - as you say - they have a new version coming out.
However, there isn't much we can do to guide you on whether their app will break on a newer compat level. We have no idea what code they're using and what 80-specific features they're using. For example, they could be using old-style outer join syntax (
FROM a, b WHERE a.x *= b.x) which will break in > 80.I think the safest approach would be to take a full backup of the database, restore a copy to 2008, move it to 90 compatibility (since that is the latest version they support), or 100 if you are feeling frisky, and then test the heck out of a copy of the app. Chances are they are being overly conservative by not supporting the new version, but there won't be any negative outcome. If a full regression and compatibility test works out okay, then you should be able to upgrade your app.