It shouldn't be that complex.
On the passive node upgrade the instance to SQL 2008 R2. Then reboot that node. Then failover. At this point the database will be upgraded to SQL 2008 R2.
On the now passive node upgrade the instance to SQL 2008 R2.
There's an article I wrote about upgrading a SQL 2005 cluster to SQL 2008. It's basically the same process.
Can you show the query? Just because it runs doesn't mean it's correct. :-) For example this is not legal, but it works:
SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY o.modify_date;
Technically, it should be as follows, since o.modify_date is not in the SELECT list:
SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY modify_date;
In this Connect bug, it was stated that this rule would be removed from the Upgrade Advisor. I suspect that one of the following (or both) happened:
- They forgot to remove the rule as they said they would
- They had other complications which prevented the above syntax from being blocked by the parser
That all said, there are some breaking changes the upgrade advisor will never catch. For example, this will work on 2005, 2008 and 2008 R2:
CREATE TABLE #foo(id INT);
IF OBJECT_ID('tempdb..#foo') > 0
BEGIN
DROP TABLE #foo;
END
GO
CREATE TABLE #foo(id INT);
This will break in SQL Server 2012, however, since #temp tables now get a negative object_id. The proper way to test is:
IF OBJECT_ID('tempdb..#foo') IS NOT NULL
I go over a few other issues in this blog post. A couple of other breaking changes:
If you use any of the memory-related DMVs there has been a major overhaul to some of the columns:
Again, most of these are not caught by the upgrade advisor, so running that tool and not doing any thorough testing could really put you in a bad spot.
And I agree with Eric's answer - you should try to track deprecated events. For completeness, here are three approaches:
Best Answer
The upgrade advisor will work, but you need to make sure to analyze a trace file of activity. Just pointing it to the database and running it will only analyze the TSQL code stored in the database and not any TSQL code within your application.
In addition to the upgrade advisor, you can run a Profiler trace to capture the events: "Depreciation Final Support" and "Depreciation Announcement". Both will identify code that will need to be evaluated because of syntax or reference to objects that don't exist in new version.
Check out Books Online for a decription of each of those events: Depreciation Event Category.