4

We have a SQL 2008 R2 server with a database on it that is running in compatibility mode 80. We are interested in upgrading 2008 R2 to 2014, but the upgrade adviser doesn't work because we can't force it to look for old syntax that works in compat 80 mode ( *= JOIN syntax for example), it just assumes the DB is a 2008 R2 DB, it ignores the compatibility mode.

What are our options here? We know it uses some deprecated syntax, and I can write some XQuery to check the cache for queries that use it, but is there anything else we can do that might be a little more concrete?

Improve this question

1 Answer 1

Reset to default
4

How about spinning up a SQL Server 2000 or 2005 server. Then script out the 80 database objects and create them on the earlier version SQL Server.

Then run the SQL Server 2008 R2 upgrade advisor to collect the 2000 and 2005 errors that might exist. Once you fixed those issues on 2008 R2, then you can run the 2014 Upgrade Advisor.

Improve this answer
3
  • Neat Approach...
    – eyoung100
    May 20, 2014 at 21:17
  • I like this approach. I would like to leave this unanswered for a bit to see what else people can think of though.
    – Mark Wilkinson
    May 21, 2014 at 11:29
  • This combined with an interrogation of the plan cache to pick up dynamic code should cover me.
    – Mark Wilkinson
    May 23, 2014 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.