SQL Server Upgrade Advisor: Considerations when upgrading from SQL 2000 to SQL 2012

This article was contributed by Tony O’Grady, a Premier Field Engineer from the UK.

Are you currently running old SQL Server 2000 instances in your estate?

Are you planning to upgrade these to SQL Server 2012?

A direct upgrade from SQL 2000 to SQL 2012 is not supported, so chances are you’ll need to go via an intermediate version. Identifying issues we may need to fix - either before or after the upgrade - is one of the challenges with this technique.

SQL Server Upgrade Advisor:

To prepare for upgrades, Microsoft provides a tool called the SQL Server Upgrade Advisor. This tool analyses a SQL Server and creates a report which identifies issues we may need to fix when upgrading to a later version.

The Upgrade Advisor can be used for supported upgrade scenarios:

· The SQL Server 2012 version of the Upgrade Advisor can examine instances of SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.

· The older SQL Server 2008 R2 Upgrade Advisor can be used for older SQL 2000 instances.

Since a direct upgrade from SQL 2000 to 2012 is not a supported scenario, we cannot use the SQL 2012 Upgrade Advisor to examine SQL 2000 instances. This is explained in the SQL Server 2012 Upgrade Technical Reference Guide which offers the following guidance on Upgrading SQL Server 2000 to SQL Server2012:

· You cannot upgrade a SQL Server 2000 instance or database to SQL Server 2012.

· For a side-by-side upgrade, first restore the SQL Server 2000 databases to SQL Server 2005, 2008, or 2008 R2, and then restore the resulting database to SQL Server 2012.

Do we need to run multiple versions of the Upgrade Advisor?

Is it really necessary to run both versions of the Upgrade Advisor, or will it be sufficient to run the 2012 Upgrade Advisor to identify all potential issues?

To answer this question we have to consider whether the SQL Server 2012 upgrade Advisor will identifySQL 2000 features discontinued in SQL 2005.

One example of such a feature is the non-ANSI outer join operator “*=” which is only supported on SQL Server 2005 when the database is running in the older SQL Server 2000 (80) compatibility mode. For forward compatibility, it should be replaced with “LEFT OUTER JOIN” syntax.

Using this as an example on a development SQL Server 2005 instance, we created a test stored procedure which uses the “*=” join operator. We then evaluated the instance with both the SQL 2008 R2 and SQL 2012 Upgrade Advisor tools. A summary of this test is presented below.

Creating our test scenario:

On a development instance of SQL Server 2005 we created a test database called TestComp and set the compatibility of the database to 80. Setting the compatibility of the database to 80 ensured that the non-ANSI outer join operator “*=”would execute successfully. To facilitate testing, we also imported in a couple of tables from the AdventureWorks database.

CREATE DATABASE[TESTComp]

EXECUTEdbo.sp_dbcmptlevel @dbname=N'TESTComp', @new_cmptlevel=80

Next, we created a stored procedure called testJoin that used the deprecated “*=” join operator:

CreatePROCEDURE [dbo].[testJoin]

AS

BEGIN

SELECT*

FROM Employee LEFTOUTERJOIN

EmployeeAddress ON Employee.EmployeeID = EmployeeAddress.EmployeeID

SELECT*

FROM Employee, EmployeeAddress

WHERE Employee.EmployeeID *= EmployeeAddress.EmployeeID

END

Since the database compatibility mode was set to 80, the stored procedure executed successfully:

EXECUTE[dbo].[testJoin]

(290 row(s) affected)

(290 row(s) affected)

SQL Server 2008 Upgrade Advisor results

We then ran the SQL Server 2008 R2 Upgrade Advisor against the TESTComp database, and looked at the results:

As we can see, the report highlights the use of the outer join operator “*=”.

SQL Server 2012 Upgrade Advisor results

We tried the SQL 2012 Upgrade Advisor against the same TESTComp database, and got the following error:

The SQL 2012 Upgrade Advisor requires the database to be at a compatibility level of 90 or greater. Since this was a test database on my laptop, we went ahead and changed the compatibility level:

EXECUTE dbo.sp_dbcmptlevel @dbname=N'TESTComp', @new_cmptlevel=90

EXECUTE[dbo].[testJoin]

Msg 4147, Level 15, State 1, Procedure testAnsci, Line 12

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Changing the compatibility level to 90 caused the execution of the [dbo].[testJoin]stored procedure to fail. This was expected, so we continued on and ran the 2012 Upgrade Advisor:

As you can see from the report above, the 2012 Upgrade Advisor did not give a specific warning on the outer join operator “*=”. If you simply tried to upgrade the compatibility level and relied on the report alone, you might not have known that your database was no longer functioning correctly.

Conclusion:

The SQL Server 2012 Upgrade Advisor will analyse instances of SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 when the database is in a compatibility mode greater than 80. In our example above, we used a feature which is not supported when the compatibility level of the database is set to 90. The resulting report generated from the SQL 2012 Upgrade Advisor did not highlight this as something to fix, but this issue was clearly reported on the older SQL 2008 R2 Upgrade Advisor tool.

So, 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.

For additional information on upgrading to SQL 2012 see the MSDN library  and the SQL Server 2012 Upgrade Technical Reference Guide

Posted by Tristan “Mad King” Kington , MSPFE Editor. With fire.