Sql-server – Updating a compatibility mode 80 database for SQL Server 2014

compatibility-levelsql server

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?

Best Answer

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.

Related Solutions

Sql-server – Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that’s in 2000 (80) compatibility mode

This may produce some false positives (e.g. you may have *= in a comment), but should be a good start:

SELECT 
  obj = QUOTENAME(SCHEMA_NAME(o.[object_id]))
      + '.' + QUOTENAME(o.name),
  o.type_desc
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.[object_id] = o.[object_id]
WHERE m.definition LIKE '%=*%'
OR m.definition LIKE '%*=%';

Sql-server – Tool for identifying stored procs with deprecated syntax

Run Upgrade advisor or you can run server side trace to Identify deprecated SQL Server code -By Aaron Bertrand.

It will show you - what will break when you upgrade to new version of sql server and change the compatibility mode to the upgraded version.

Also, when upgrading from SQL Server 2000 to 2012, consider using both the older 2008 R2 Upgrade Advisor and the new SQL 2012 Upgrade Advisor, as this will provide a more complete picture of issues you may need to fix.

Best Answer

Related Solutions

Sql-server – Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that’s in 2000 (80) compatibility mode

Sql-server – Tool for identifying stored procs with deprecated syntax

Related Question