Developed against MSSQL 2005 - host only has MSSQL 2008

Question

Hey, I've been developing my ASP.NET application against SQL Server 2005. The web host I intend to go with only supports SQL Server 2008. Will there be any changes I have to make in my database so that it works with 2008? My database doesn't do anything special, but are there any pitfalls that I should look out for? The host says that there should be no changes to be made, but I'd like to make sure from people who don't have a selling agenda :)

Thank you

Answer 1

There are two things you can do.

1) Take a look at the Microsoft list of Deprecated Database Engine Features in SQL Server 2005. This lists all the stuff in SQL Server 2005 that Microsoft intends to stop using in the next (SQL Server 2008) and future versions of SQL Server.

2) Install Microsoft SQL Server 2008 Upgrade Advisor. This will analyze your SQL Server 2005 installation and tell you if anything will cause issues when you upgrade to SQL Server 2008.

Answer 2

There are unlikely to be issues, especially if you are not doing anything much fancy SQL-wise, but this is not guaranteed so I would be inclined to test against SQL2008 to be sure - if only on express edition (which won't cost you).

If you don't retest on 2008, at least ensure that all new databases have the 2005 compatibility level (90 - see here for more info). Compatibility level doesn't guarantee complete issue free running though if you your code (intentionally or accidentally) depends upon any "undefined" behavior that has always been the same in SQL2005 (and perhaps prior) by chance.

Regarding "the host says that there should be no changes to be made": the host is unlike to be a SQL expert, they are a hosting company not advanced DBAs or developers - the most they can legitimately say with any guarantee (IMO) is "we've had clients upgrade to 2008 and they have not had any problems that they reported to us". It also would not be in their interests to say anything like "yeah, you might have to test to ensure no problems with SQL2008" if 2008 is all that they offer...

Answer 3

I've been developing locally on 08 and scripting my database, then running said script on 05 to recreate the DB on our production server. I'm avoiding using 08 enhancements (hey boss! an upgrade would be nice!) and haven't had any issues, so I doubt that there would be problems going the other direction.

Answer 4

You should ideally

• Re-run your entire auto-test suite if possible against the MSSQL 2008
• Run comparative performance testing on production-grade hardware in your test lab to check for performance regressions from MSSQL 2005 to 2008
• If possible sample a lot of queries from production and run them, in parallel, against identical hardware instances of 2005 and 2008, checking for differences in result, error code (when an error is generated), and performance

Any anomalies need to be investigated on a case-by-case basis to see if they are a cause for concern, or require a modification to your application.

Once you've made all the apparently necessary modifications to your application, do the above AGAIN, and if no more anomalies are found, you can then be reasonably sure that you won't have any issues.

Of course I'm assuming your app has decent coverage on its auto-test suite; many don't, in which case you will have to do a lot of manual testing as well.

Answer 5

There should be no issues.

Edit: Not to say that you shouldn't test, rather just that there are no inherent "gotchas". SQL 2k5 is fully compatible with 2k8.