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?
1 Answer 1
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.
-
-
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– Mark Wilkinson2014年05月21日 11:29:15 +00:00Commented 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– Mark Wilkinson2014年05月23日 22:42:53 +00:00Commented May 23, 2014 at 22:42