Today I've been trying to upgrade a SQL2000 Failover cluster to SQL2005 on our HMC3.5 environment. According to the upgrade handbook, this should be an easy job…. Well, it's not… at least not in my case.
Shall I start with the upgrade advisor? Warning: Column aliases in ORDER BY clause cannot be prefixed by table alias, Object name: proc_GetTpPageMetaData, Database name: SharePoint_AdminContent_7bf5e9db-3139-4aec-9561-2a500fc13015.
Mmm, a WSS2.0 by Microsoft database not compatible with MS SQL2005? Oops! Well, ok, compatibility level on an upgrade is set to 8.0 and not to 9.0, so it's not a show stopper. But am I really the 2nd person on earth to have this problem?
Alright. Let's start the installation wizard. Select to Create a cluster, select an existing instance, next, next, ….. finish? uh, no! Error: SqlUpgradeMessage = SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft] [SQL Native Client] Shared Memory Provider: Shared Memory is not supported for clustered server connectivity .
Ok, the upgrade handbook says Shared Memory is not supported on clusters. So…. why does the installation wizard use it, while it knows it's upgrading a cluster!? Can't seem to find any notice in the handbook or on Google to manually disable Shared memory access before upgrading….
After I click ok, the setup is rolling back the installation….well, not entirely. I don't end up with a SQL 2000 installation. Instead, I have a running SQL 2005 service, with SQL2000 databases! Result: the SQL Agent won't start.
Ok, so I manually disable share memory connectivity and try to restart the setup using Add/Remove programs. Installation begins… but fails again, this time with the message "The group or resource is not in the correct state to perform the requested operation".
Mmm, would this be because of the failing SQL Agent? Alright then, let's remove the SQL Agent resource from the cluster group and try again. Hurray! It works! And I didn't even have to re-add the SQL Agent manually to the cluster group; the installation did it for me.