November 15, 2007 at 6:05 am
Hello folks,
I'm berto and work for Amadeus GmBh in Erding near Munic in Germany. I'm polish beside that I leave in Germany :).
I'm responsible for migration and I started this project from gathering requirements from users (SQL Server edition, platform and licensing model and compatibility with SQL 2005).
Some of them returned to me with requirement to leave database after migration in old compatibility level.
I have question for you what impact will have changing compatibility level for database? Can I always return to old one without loosing anything? Why some software vendors requires to stay with old compatibility level?
Thanks for your comments and welcome in community 🙂
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
November 15, 2007 at 6:38 am
Hi Berto,
There are some breaking changes in higher compatibility modes. For example SQL Server 2005 no longer accepts =*, and there are some new reserved keywords, e.g. "PIVOT"
You can read about these on http://msdn2.microsoft.com/en-us/library/ms178653.aspx
When you upgrade SQL Server does not check your database. E.g. the old syntax in stored procedures is not checked, so you may experience that things that worked in compat mode 80 no longer work, but you will only find it out when you execute the stored procedure. You can fix it by switching back to compat mode 80.
Regards,
Andras
November 15, 2007 at 6:40 am
it's generally a bad idea to leave the database in backward compat and I'd ask for the actual reasons. You won't be able to use the performance dashboard for instance or run some reports.
I'd want to have some very good reasons for not upgrading.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 15, 2007 at 7:05 am
You will have to change the mode to 90 to have full features of SQL 2005 to be effective in your database.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 15, 2007 at 8:24 pm
You can run the Microsoft SQL Server 2005 Upgrade Advisor to see what issues, if any, are present in the databases to migrate. This will tell you if leaving the database in an older compatibility mode is necessary or not. Generally speaking, if you can put the database in the latest compatibility mode, you want to do so.
Download: Microsoft SQL Server 2005 Upgrade Advisor
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply