We have an application that use MSSQL, when we move to 2005, basically we recommend to our client to apply the backward compatibility package and that’s it, but somebody ask me what to do to be 100% compatible on 2005. I was thinking on 1. Compatibility level: SQL Server 2005 (90) 2. Remove any DMO reference, and replaced by SMO 3. Connect using ADO
I am not sure if this is the correct answer.
Actually you are talking about two different things.
One is to update the database to SQL 2005 level, to be able to use the functions only available there. It can be done easily by setting the compatibility level. However in this case SQL 2000 clients can have problems with it.
Second is to upgrade your application to use only SQL 2005 related libraries. This is also easy, basically the steps what you have written. In this case your application can also work with SQL 2000 databases (except if you use some SQL 2005 specific feature) because the libraries are backward compatible.
End words: you are either way compatible with SQL 2005, the only difference is that you can use SQL 2005 specific features or not.
Why not change to SQL 2008 already? Here is a thread about this topic.
To see if you are really compatible, the only way to find out is to run your app on an SQL 2005 server without the backward compatibility package. If you have an exhaustive test suite it will help a lot but you need to make sure that all your backend code gets hit and performs normally.
I have worked on an application that used SQL 2000 and SQL 2005 and the only bits of the app which needed version-switching were in some hairy reflection code which needed to use SQL Server's system tables to dynamically discover tables at runtime. The solution for us was to use dynamic SQL but you need to make the right choice for your app and users.
Your app is 100% SQL 2005 compatible if it will run correctly on an 'out of the box' SQL 2005 configuration without any backward compatiblity fixes applied.
If you have any DTS packages they will need to be converted to SSIS packages to be fully compatible.
If you really want to make sure that you are fully compatible, check out in the documentation which features are being deprecated and if you are using any of them, go ahead and fix them now.
YOu might also look at any code you have that could use the pivot feature that was not in 2000. The old code will work but the pivot code will be a better choice and probably more efficient. The same thing with other new features, check out how you were working around them and fix. One big one to consider is if you can get rid of your text fileds with the new datatype, varchar (max). Then look for places in your code where you were converting to a varchar in order to do comparisons.