We have several applications that were developed using SQL Server 2000. Most of our customers run MSDE, some the full SQL Server 2000. We are in the process of making sure our applications will work properly in SQL Server 2005 / SQL Express.
I have a few questions regarding this issue:-
1. Should I set the compatibility level to 80 for our databases or should I aim to make our applications work the 90 compatibility level?
I've run the Upgrade Advisor against our databases and this has not flagged up any issues. Does this mean the databases are compliant with the 90 compatibility level.
Of course this hasn't verified the queries built into my VB application so presumably I need to test our applications thoroughly before we let our customers run them on SQL Server 2005? (Note: Our newest applications do approx. 95% of the queries through stored procedures, however our older applications are more like 50% - this I presume potentially means there are a lot of queries hard coded into our code that could potentially not work and we need to test the applications directly against SQL Server 2005?)
2. If the compatibility level is set to 80, does the guarantee the database functions EXACTLY the same as SQL Server 2000? Could anything not work that did work in SQL Server 2000? Note: I know the SQL Agent Service is not available in SQL Express - this is not my main concern - i'm more on about general stuff like queries etc here.
3. If the compatibility level is set to 80 do you still get some SQL Server 2005 benefits - e.g. running on a faster engine giving me performance benefits or does the database need to be set to compatibility level 90 to receive any performance benefits?
4. If you restore a SQL Server 2000 database into SQL Server 2005, does it automatically update the database so it no longer will work in SQL Server 2000? Can I for instance have the database set to compatibility level 80 with nothing upgraded and then restore the database back on a SQL Server 2000 machine. I don't believe this is possible, but need to check.
Thanks in advance,
Chris
Analysis Services Upgrade Issues|||
In this link it states..Mulhall wrote:
2&3 Check out this link and "Behavioral Differences Between Earlier Compatibility Levels and Level 90"
http://msdn2.microsoft.com/en-us/library/ms178653.aspx
"The sp_dbcmptlevel stored procedure affects behaviors only for the specified database, not for the entire server. sp_dbcmptlevel provides only partial backward compatibility with earlier versions of SQL Server. Use sp_dbcmptlevel as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility level setting. If existing SQL Server applications are affected by behavioral differences in SQL Server 2005, convert the application to work properly. Then use sp_dbcmptlevel to change the compatibility level to 90."
The above text implies compatibility levels should be used just as an aid until our application is fixed. I've read a lot of the upgrade advisor help file and can't see anything in there that our application would have trouble with.
Therefore, If i've run the upgrade advisor and I have no issues (and I can't see anything in the help file that would cause us problems) then presumably the next step is to restore our databases in SQL Server 2005 and change the compatibility level to 90 and start testing our applications directly?
|||There are noguarantees! :)In your place I would check whether I am aware of any points detailed in the above link that would impede your applications functionality or performance.
I'd then restore the database in SQL 2005 and start testing, if I hit issues that might be compability related, then I'd drop the compatability level to try to resolve them.sql
No comments:
Post a Comment