My latest task was to upgrade SQL 2005 Express version to SQL 2008 Standard version. The SQL 2005 instance was installed by Office Communicator Server 2007 R2. I thought it would be a straightforward task since I have done both SQL edition and version upgrades before. Oh how wrong I was!
There are two ways to get from SQL 2005 Express to SQL 2008 Standard. Both require two upgrades. One way is to first upgrade SQL 2005 Express to Standard and that to SQL 2008 Standard. The other way is to upgrade SQL 2008 Express and do an edition upgrade to Standard.
First I tried to go the Express-->Standard-->SQL 2008 route and that's where I hit the first problem. The SQL 2005 Express version had SP2 applied already. That meant that running the SQL 2005 setup.exe with the SKUUPGRADE=1 switch gave me the following error: "Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. "
Ok. No big deal. I'll just get the SQL 2005 files with SP2 or SP3 already included.
Unfortunately the only install files we had did not have a separate Setup.exe which is required to do an edition upgrade for SQL 2005. So I abandoned this route.
Let me try to upgrade to SQL 2008 Express first then. I didn't have the install files so I had to download it from Microsoft. I had a little trouble with that because I kept coming across with the web installation option as opposed to finding the old-fashioned download page. Finally our server admin suggested I'd search for the redistributable SQL 2008 Express so I found it. I feel a dummy for not thinking about it but I rarely have to download from Microsoft as we have everything on a network share. Here is the link if you need it: http://www.microsoft.com/downloads/details.aspx?FamilyID=58ce885d-508b-45c8-9fd3-118edd8e6fff&displaylang=en
Finally I have the exe to run but when I run it I don't get the option to select an instance to install. What on Earth is happening? After searching for this and searching for that I came across someone mentioning that you can't upgrade 32-bit version to 64-bit. Hmmm... Sure enough the SQL 2005 Express that the Office Communicator Server 2007 R2 installed is the 32-bit version even thought Office Communicator Server 2007 R2 can only be installed on a 64-bit server. Nice job, Microsoft. So I downloaded the 32-bit version of the installation file and now I was finally be able to upgrade from SQL 2005 Express to SQl 2008 Express.
I'm halfway there now let me do an edition upgrade. I run the Setup.exe file for SQL 2008 and under the Maintenance I select edition upgrade. On the system check there is a error: "Upgrade architecture mismatch" The upgrade is running in 64-bit so it can't upgrade the 32-bit version of SQL. You are trying to tell me that the upgrade can't detect what version I want to upgrade? OK. Onto some more searching. Aha! I found it. On the main screen before selecting the edition upgrade there is an Options section on the left side of the window. Clicking on that I can tell the upgrade process what architecture to use. I select x86 and the edition upgrade us finally successful!
The original task has been completed but as a good DBA I need to set up some maintenance plans for this instance. However, SQL Agent is missing. I go to SQL Server Configuration Manager and I see that the Agent is there but disabled. Oh it will be easy! I just have to enable the Agent and start it. I should have learned this by now but nothing is easy anymore. I can't start the Agent because of the following error: "SQLServerAgent could not be started (reason: Error creating a new session)." Urgh! Back to searching for a solution. I found it on a KB article page: http://support.microsoft.com/kb/955763 Since the server is running Windows Server 2008 I had to specify a user for the Agent and gave it sysadmin rights in SQL Server. I am not happy about the last one but there is not much I can do about it.
So what should have taken me just a couple of hours at most took me more than a day. Learn from my example: run "Select @ @Version" before you do any upgrades and note both the service packs applied and architecture it is running on. If I had paid more attention to that information I could have saved some time for myself.