One word to describe it; it was very easy to setup. We just completed setting up a 3-node configuration with Active-Active-Passive. Applied the SQL Server 2005 SP2, logged in-to server and found we can’t create maintenance plans or view existing ones.
So I though okay something went wrong in SP2 install; because it was working properly; I’ll just reapply it and I’ll be set. However, after trying it again same issue. I have other servers running SQL Server 2005 SP2 so I couldn’t figure out why it was crapping out.
Errors I was getting:
This error happens when you are trying to get a listing from the maintenance plans; because it is querying a system view called sysmaintplan_plans in msdb. Both these fields were added in SP2; so this view needed to be altered; but failed.
This error occurs when you actual create a new maintenance plan; this error happens because the field is missing in the table called sysmaintplan_subplans in msdb.
So after banging my head against the brick wall (reading self-support pages, Google, newsgroups, and whatever I can get my hand on); I decided to place a call with Microsoft.
Well we couldn’t figure out the issue there too or rather what happened; Microsoft tech got me to re-do the entire SQL Install to make sure I didn’t miss anything and after 6.5 hour long call; we still didn’t know what caused the issue.
We thought it might have been because of permissions and new security functionality with in Windows Server 2008, and the User Account Control (UAC, read about it here for its affect on SQL Server). Alternatively I though maybe SP2 x64 edition had some issues in the package. But Microsoft engineer said they have that running in their test environment without issues.
At the end of the call we still didn’t resolve the issue but had a work around. We went to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\ and loaded the sysdbupg.sql script which is part of SP2 and executed it manually. It resolved both those errors.
So I have a work around but I was not happy with this, thinking I just fluked out. So I decided to turn of UAC and made sure that I had SA rights in SQL server and did the install again but failed only way I can get it to work was with Work around. And looking at the log file I found there were 100+ components missed because they couldn’t be found by the package and the SQL Script was one of them.
I made the Microsoft Engineer back, and they are going to get their coders to look at the package.
So for now I am blaming SQL Server 2005 SP2 x64-Bit as faulty.
Further updates as I get them :).