THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Upgrading from Express Edition to Standard Edition

Say you encounter an SQL Server which is Express Edition, and it really should have been some higher edition. Sounds familiar? It is common for me as a consultant to find plenty of SQL Servers at a customer's site. Many of the databases in these will be moved (typically using backup and restore) to a "real" SQL Server. But in some cases, this might not be desirable. You want to convert the whole instance, from Express to a "real" SQL Server edition.

I'm attending a great SharePoint course for Daniel Bugday this week, and the topic of how SharePoint is installed was discussed. Selecting "Standalone", SharePoint install will not only install all SharePoint parts on a single server, it will also install SQL Server Express Edition as the database engine. In short, SharePoint is one example where you might have an existing Express Edition which you want to make a "real" (higher) edition. There are other such scenarios, of course.

As of SQL Server 2008, you can let SQL Server's setup program change from one edition to some other edition by just selecting "Maintenance", "Edition Upgrade" in setup and specify the product key that came with the install media for the desired edition (which you have acquired first, of course). The cool thing here is that setup basically just registers the different product key and WHAM - you have the higher edition! This is what I wanted to test, and specifically going from Express to a higher edition. Below is my experience:

I have an Express edition which came with the SharePoint installation. It is SQL Server 2008 (not R2), with no tools (SSMS etc) installed. I want to make this a "real" Standard Edition. I have a Standard Edition 2008 installation media, with a product key.

The database engine 
I first ran setup from the installed SQL Server (Start, All Programs, Microsoft SQL Server 2008, Configuration Tools, SQL Server Installation Center (64-bit)). I selected "Maintenance", "Edition Upgrade" in setup and specify the product key that came with my standard Edition. This did not allow me to continue. I got an error (which is lost now) and I could not continue.
Next try was to run setup from the installation media (my standard Edition install media) instead. This did allow me to continue. The change was very quick (a couple of minutes). I then verified using SQLCMD that I now have Standard Edition. Great! The database engine done.

Tool-time
You might want to have the tools (a.k.a. Shared Components) as well on the machine. I refer to things such as SQL Server Management Studio, Integration Services etc. So, I just ran setup from the installation media and installed the desired shared components. Nothing strange here. I now have Management Studio, Books Online and whatever more I might want to have.

SQL Server Agent
SQL Server Express doesn't come with SQL Server Agent, right? Not really. It comes with agent, but the service is disabled and you cannot start it even if you try. But what about after an edition upgrade? I could still not start Agent. However, checking SQLAGENT.OUT revealed the problem. The first thing agent does at startup is to login to SQL Server and verify that it is sysadmin. The SharePoint setup installed Agent service account as Network Service, which was added as a login in SQL Server, but the login isn't sysadmin. Now, this is in general a good thing (don't run your services with higher privileges than necessary), but in this case it causes Agent to not start. Some of the SharePoint services are configured to use Network Service, and apparently the server roles securityadmin and dbcreator is enough for SharePoint. So, this needs to be taken care of. I could use some other service account for Agent, and make sure that service account is sysadmin in SQL Server. For my test, though, it is OK to make the login in SQL Server for Network Service as sysadmin. After that was done, Agent started just as expected. And just for fun, I created a Maintennance Plan for all databases, which also worked just fine.

What about the rest?
Obviously, you might want to check other things, such as database options, file size, auto grow settings, maintenance for the databases etc. But that isn't strictly related to the edition change, it is just general best practices. For my case, for instance, I might want to upgrade the SQL Server from 2008 to 2008 R2, while I'm at it. Specifically for SharePoint, it seems its setup program created the database files in the Hive (a SharePoint term for a folder in Program Files, Common Files), and you probably want to move the SharePoint database files to a more suitable location. But that is outside the scope for this post, and hopefully no sweat for your dba.

In short, my test show that is is easy to change from one edition to a different edition, even if you want to change from Express Edition.

Published Thursday, January 27, 2011 2:14 PM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

zee said:

Nice blog! Thanks!

zee

http://walisystems.com

January 29, 2011 2:49 AM
 

Jeff Bennett said:

Whoever built this into the toolset should get a raise.

Thanks

February 1, 2011 5:29 PM
 

Tibor Karaszi said:

Chances are that you rarely get to see the Express Edition of SQL Server if you mainly work with servers.

February 10, 2011 1:11 PM
 

Alexandros said:

Thanks mate for this good article!

March 1, 2011 6:34 AM
 

Daniel said:

This was a great help, glad I found your article. Cheers.

October 24, 2011 2:39 AM
 

Rich said:

I already had SQL 2008 Standard installed on the server when I installed the Stand Alone and whe I run the Edition Upgrade wizard it does not allow me to select the SHAREPOINT instance to upgrade. Even though I see it listed in the grid it is not in the drop down.

October 26, 2011 2:03 PM
 

LC said:

I originally had 2005 Express installed. Attempted to upgrade to 2008 R2 but installer said no components to upgrade. So I ran the upgrade to 2008 R2 Express and that worked fine. This is all on 64 bit. I tried doing a edition version upgrade from 2008 R2 Express to 2008 R2 Enterprise and the edition upgrade reports success, but when I run SELECT @@VERSION it still shows Express. I ran this query and it still showed Express. I did the edition version upgrade twice just to make sure but it never changes. The original instance of course was named SQLEXP_SOMETHING and its still there of course. I do not really care about the instance name but as long as I can get it up to enterprise.

This is the query from SELECT @@VERSION:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)   Jun 17 2011 00:57:23   Copyright (c) Microsoft Corporation  Express Edition on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (WOW64) (Hypervisor)

Any thoughts on why it is still showing up as express despite edition upgrade?

August 18, 2012 4:35 PM
 

mahendra said:

Thanks a lot .I have rarely read the thing related to express to standard or enterprise..

Really very helpful

August 7, 2013 4:15 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement