Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Upgrading to SQL Server 2012 Express


By:   |   Read Comments (4)   |   Related Tips: More > Upgrades and Migrations

Attend these FREE MSSQLTips webcasts >> click to register


Problem

Sometime back one of my friends was asking about upgrading SQL Server 2008 R2 Express to SQL Server 2012 Express.  As you may know, the SQL Express edition is free and is often used for smaller database applications and most times there are not full-time DBAs supporting these installs, so I decided to share some online resources and steps to do the upgrade.

Solution

Microsoft provides the upgrade option in the 'SQL Server Installation Center' which makes the upgrade task straightforward. We can upgrade instances of SQL Server 2005 / 2008 / 2008R2 to SQL Server 2012. We need to know the supported version and the edition matrix for the upgrade to SQL Server 2012 before we start the upgrade.

Supported Upgrade Paths

An in-place upgrade to SQL Server 2012 Express can be considered for:

  • SQL Server 2005 SP4
  • SQL Server 2008 SP2 and
  • SQL Server 2008 R2 SP1
  • earlier versions cannot be upgraded to SQL Server 2012

System Requirements

  • Processor
    • 32-bit systems : 1GHz or faster Processor
    • 64-bit systems : 1.4GHz or faster processor
  • Memory
    • Minimum of 512 MB (recommended 1 GB or more)
  • Disk Space
    • Minimum off 2.2GB
  • .NET Framework
    • 3.5 SP1 and 4.0 ( if you only install the Database Engine then either one is sufficient)

Note: SQL Express 2012 edition is limited to use only one processor and 1 GB of memory.  The maximum database size is 10GB.

Download

Microsoft SQL Server 2012 Express can be downloaded from this link.

Checklist

An in-place upgrade is straight forward most of the time, but it is always good to have an upgrade plan and checklist in place before you begin the upgrade. The risk with an in-place upgrade is if the SQL 2012 upgrade is unsuccessful, we will need to uninstall SQL Server 2012 and reinstall the previous version and then configure the databases.

  • Make sure the system meets the hardware and software.
  • Backup the System and User databases and document rollback requirements.
  • Run the Upgrade Advisor and any issues for upgrading the current databases or any deprecated function usage. The below image shows that there is no unresolved issues found in the current system for the upgrade to SQL 2012.

  • The SQL Express 2012 edition is limited to one processor and 1 GB of memory
  • For more info on Upgrade Advisor refer to this MSDN link. Also review Deprecated SQL Server Features in SQL Server 2012 and make database changes to avoid using these features.
  • Post Upgrade:
    • Check data consistency by running DBCC CHECKDB for all user databases.
    • Update statistics for tables in all user databases.
    • Consider implementing new SQL 2012 features / functionality. The complete list can be viewed in this Microsoft link.
    • Here are a few new features you should look into:
      • Sequence: Numeric values in an ascending or descending order at a defined interval which can be used with different tables in a database.
      • Enhancements in Exception Handling by introducing THROW keyword.
      • Conversion Function: PARSE, TRY_CAST, TRY_CONVERT, TRY_PARSE.
      • Logical Function: CHOOSE and IIF.
      • String Function: FORMAT and CONCAT.
      • Date and Time functions: DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS, DATETIME2FROMPARTS, SMALLDATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS and EOMONTH.

Upgrade to SQL Server 2012

I downloaded Microsoft SQL Server 2012 Express for this tip. Now, let me share a few screenshots to give you some ideas about the upgrade process.

The Upgrade option is available under 'Installation' in the 'SQL Server Installation Center'.

When you click on the 'Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2', The 'Setup Support Rules' process will check for the setup support files and necessary actions like restarting the computer, account Privileges and .NET framework updates. The report will be shown as the below screen letting you know the status.  For my upgrade everything passed the checks.

click on the 'Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2'

In the Next screen, the Product Updates will be checked and display all the latest update files for installation. This process can be skipped if we are sure about the latest updates in the system.

After a few screens we will get the 'Select Instance' screen where we need to specify the instance which needs to be upgraded. Here I want the 'DEV_SQLEXPRESS' instance to be upgraded to SQL Server 2012 Express.

DEV_SQLEXPRESS' instance to upgrade to SQL Server 2012 Express

In the 'Select Features' screen, the options are automatically selected to upgrade. The options cannot be modified. If we want to add or remove features we need to do as additional steps after this upgrade process.

the 'Select Features' screen

Based on our inputs, the 'Ready to Upgrade' screen will display a summary list and details on the configurations.

the 'Ready to Upgrade' screen will display a summary list

Finally, the 'complete' screen will provide the feature list and the installation status as below.

the 'complete' screen will provide the feature list
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Murali Krishnan Murali Krishnan is a Lead Consultant with vast experience in Database/BI Design, Development and Administration.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, January 14, 2014 - 3:19:54 AM - Asif Mohammed Back To Top

Hi,

I have upgraded SQL server from 2008R2 to 2012 Enterprise. My 2008R2 instance name was "SQL2k8R2". So i had selected this "SQL2k8R2" instance in the select instance name part specified instance name as "SQL2012ENT" in the specify name box. after the successful upgrade to 2012 without any eror, i had open the management studio console. When i tryed to open database engine, i havenot fount any instance name with "SQL2012ENT" but i found "SQL2k8R2". so i have selected "SQL2k8R2" and connected with the earlier sa password. I have connected to the DB and can access all the database. 

My concern is, i can still see "SQL2k8R2" instance name for 2012 as well as it is replaced by 2012 filesystem? or the instane "SQL2k8R2" which i have selected was not properly upgraded? also i cant see any instance name "SQL2012ENT" which i had specified during upgrade? I can open and access instance "SQL2k8R2" from both SQL 2008R2 and 20012 ent console?

What should i do to minimize confution and use only 2012 ent. and remove 2008R2 from the system.

 

Please advice


Wednesday, December 04, 2013 - 9:45:32 AM - Patrick Lambin Back To Top

I have forgotten one little thing about your tip : it is only excellent , well documented with useful screenshots which are easily readable ( for my poor old eyes : a real bonus )

I provide the link towards the thread where Mike ( I owe him nearly everything I know about SQL Server Express ) explained the reason of the impossibility to upgrade SSMSEE :

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2b630866-f21b-4916-9b0d-790bd2cc8f16/management-tools-release-date?forum=sqlexpress

Last thing : thanks and I hope you will go on to provide so good tips.


Wednesday, December 04, 2013 - 8:06:08 AM - Murali Back To Top

Fine and Thanks for sharing the experince Patrick Lambin.

 

 


Tuesday, December 03, 2013 - 6:11:42 PM - Patrick Lambin Back To Top

Have you tried to upgrade a SQL Server 2005 Express to 2008,2008 R2,2012 with a SSMSEE ( SQL Server Management Studio Express specific to 2005 as SSMSEE has been replaced by a SSMS edition nearly the same as SSMS for 2008 , for example , Standard ) ?

I met the problem of a SQL Server 2008 Developer impossible to install because of a SSMSEE already installed. It is a well-known bug which has been discussed on the MSDN/Technet SQL Server Express forum ( en-US ). The reason was given by Mike Wachal : SSMSEE has not been fully finished so it prohibited any upgrade to a higher version ( Mike Wachal has been the main writer on the SQL Server Express Blog , official blog from the Microsoft team who has developed SQL Server Express ).

I knew that a SQL Server Express 2005 without advanced services and SSMSEE is possible to upgrade to a new version as I succeeded towards a SQL Server 2008 Developer but I have before uninstalled the Client Tools, drivers,... but it was log to do as I preferred to restart after each uninstall ), but I have failed twice with 2005 with advanced services fully installed , so twice , I got so many problems that I had to reinstall my XP , my VS 2008 and so on. A bad experience which let me a bad remembrance.

Have a nice day


Learn more about SQL Server tools