SQL Server 2014’s new In-Memory OLTP, with its 2x-30x performance improvements, provides a pretty compelling reason to migrate to SQL Server 2014—in spite of the fact that SQL Server 2014 was released just two years after the last SQL Server 2012 release.
There are two basic strategies for upgrading to a new SQL Server release. Some people recommend performing a full backup followed by a clean install of SQL Server on a new operating system. Other people prefer performing in-place upgrade. Each different tact has their advantages.
Full Backup Followed by Clean Install
People who prefer the clean upgrade say you minimize the register and system corruption that eventually creeps into all Windows installation. Thereby giving you a more stable new installation. This is essentially the same as performing a new installation. However, this method is time consuming and costly. You need to restore all of your databases and in many cases need to buy a new Windows Server OS license. Then you need to restore any custom SQL Server settings and properties that you may have changed.
In-Place Upgrade is Much Faster
Alternatively, performing an in-place upgrade is much faster as you only need to install the need SQL Server version over top of the old one. There’s no need to backup and restore. Some database pros say this method is safer because you know you’ll be running with the same settings and they don’t like to change an otherwise stable system. I have performed both successfully many times in the past. In this article I’ll cover the steps for performing an in-place upgrade of SQL Server 2012 to SQL Server 2014.
You can perform an in-place upgrade to SQL Server 2014 using the graphical Installation Center or you can upgrade by running setup.exe from a command prompt using a configuration file. In this article I’ll guide you through the setup of migrating to SQL Server 2014 using the Installation Center. For more information about using a configuration file you can refer to Install SQL Server 2014 Using a Configuration File. The SQL Server 2014 installation process supports upgrading from the following versions of SQL Server
- SQL Server 2012 SP1
- SQL Server 2008 R2 SP2
- SQL Server 2008 SP3
- SQL Server 2005
You cannot perform an in-place upgrade for older versions of SQL Server such as SQL Server 2000, SQL Server 7.0, or SQL Server 6.5. In addition, you cannot add features during the upgrade process. In general, you can upgrade from your current edition to of SQL Server to the equivelant or higher editions. For instance, you can upgrade from the SQL Server 2008 R2 SP2 Datacenter edition (the highest for that release) to the SQL Server 2013 Enterprise or Business Intelligence editions. Likewise, you can upgrade for the SQL Server 2012 SP1 to the SQL Server 2014 Standard, Enterprise, or Business Intelligences editions. The Developer Edition can only be upgraded to the Developer Edition. For a complete list of supported upgrade paths to SQL Server 2014 you can refer to Supported Version and Edition Upgrades
SQL Server 2014 Hardware Requirements
SQL Server 2014 is designed to run on a wide range of computer systems from laptop and desktop systems to enterprise class systems. The minimum processing requirement for a 32-bit x86 implementation is a 1.0 GHz CPU, while the 64-bit x64 implementation has a recommended minimum of a 1.4 GHz processor. Microsoft’s recommended minimum processor speed is 2.0 GHz. The minimum memory requirements for SQL Server 2012 are also quite low. The low end SQL Server 2012 Express edition requires a minimum of 512 MB while the other editions require a minimum of 1 GB or RAM. Microsoft’s recommended minimum RAM for the SQL Server 2014 Standard edition and above is 4 GB. Disk space requirements vary depending on the features that you install. SQL Server 2014 requires a minimum of 6GB of free disk space.
SQL Server 2014 Operating System Requirements
SQL Server 2014 is designed to run in wide variety of situations and each different SQL Server 2014 edition has different operating system requirements. In addition, the 32-bit x86 versions and the 64-bit x64 versions of the different SQL Server 2014 editions also have somewhat different operating system requirements. The following table lists all the supported operating systems for the principle editions of SQL Server 2014.
|
SQL Server 2014 Edition |
32-bit |
64-bit |
|
SQL Server 2014 Enterprise |
Windows Server 2012 R2 64-bit Datacenter Windows Server 2012 R2 64-bit Standard Windows Server 2012 R2 64-bit Essentials Windows Server 2012 R2 64-bit Foundation Windows Server 2012 64-bit Datacenter Windows Server 2012 64-bit Standard Windows Server 2012 64-bit Essentials Windows Server 2012 64-bit Foundation Windows Server 2008 R2 SP1 64-bit Datacenter Windows Server 2008 R2 SP1 64-bit Enterprise Windows Server 2008 R2 SP1 64-bit Standard Windows Server 2008 R2 SP1 64-bit Web Windows Server 2008 SP2 64-bit Datacenter Windows Server 2008 SP2 64-bit Enterprise Windows Server 2008 SP2 64-bit Standard Windows Server 2008 SP2 64-bit Web Windows Server 2008 SP2 32-bit Datacenter Windows Server 2008 SP2 32-bit Enterprise Windows Server 2008 SP2 32-bit Standard Windows Server 2008 SP2 32-bit Web |
Windows Server 2012 R2 64-bit Datacenter Windows Server 2012 R2 64-bit Standard Windows Server 2012 R2 64-bit Essentials Windows Server 2012 R2 64-bit Foundation Windows Server 2012 64-bit Datacenter Windows Server 2012 64-bit Standard Windows Server 2012 64-bit Essentials Windows Server 2012 64-bit Foundation Windows Server 2008 R2 SP1 64-bit Datacenter Windows Server 2008 R2 SP1 64-bit Enterprise Windows Server 2008 R2 SP1 64-bit Standard Windows Server 2008 R2 SP1 64-bit Web Windows Server 2008 SP2 64-bit Datacenter Windows Server 2008 SP2 64-bit Enterprise Windows Server 2008 SP2 64-bit Standard Windows Server 2008 SP2 64-bit Web |
|
SQL Server 2014 Business Intelligence |
Windows Server 2012 R2 64-bit Datacenter Windows Server 2012 R2 64-bit Standard Windows Server 2012 R2 64-bit Essentials Windows Server 2012 R2 64-bit Foundation Windows Server 2012 64-bit Datacenter Windows Server 2012 64-bit Standard Windows Server 2012 64-bit Essentials Windows Server 2012 64-bit Foundation Windows Server 2008 R2 SP1 64-bit Datacenter Windows Server 2008 R2 SP1 64-bit Enterprise Windows Server 2008 R2 SP1 64-bit Standard Windows Server 2008 R2 SP1 64-bit Web Windows Server 2008 SP2 64-bit Datacenter Windows Server 2008 SP2 64-bit Enterprise Windows Server 2008 SP2 64-bit Standard Windows Server 2008 SP2 64-bit Web Windows Server 2008 SP2 32-bit Datacenter Windows Server 2008 SP2 32-bit Enterprise Windows Server 2008 SP2 32-bit Standard Windows Server 2008 SP2 32-bit Web |
Windows Server 2012 R2 64-bit Datacenter Windows Server 2012 R2 64-bit Standard Windows Server 2012 R2 64-bit Essentials Windows Server 2012 R2 64-bit Foundation Windows Server 2012 64-bit Datacenter Windows Server 2012 64-bit Standard Windows Server 2012 64-bit Essentials Windows Server 2012 64-bit Foundation Windows Server 2008 R2 SP1 64-bit Datacenter Windows Server 2008 R2 SP1 64-bit Enterprise Windows Server 2008 R2 SP1 64-bit Standard Windows Server 2008 R2 SP1 64-bit Web Windows Server 2008 SP2 64-bit Datacenter Windows Server 2008 SP2 64-bit Enterprise Windows Server 2008 SP2 64-bit Standard Windows Server 2008 SP2 64-bit Web |
|
SQL Server 2014 Standard |
Windows Server 2012 R2 64-bit Datacenter Windows Server 2012 R2 64-bit Standard Windows Server 2012 R2 64-bit Essentials Windows Server 2012 R2 64-bit Foundation Windows Server 2012 64-bit Datacenter Windows Server 2012 64-bit Standard Windows Server 2012 64-bit Essentials Windows Server 2012 64-bit Foundation Windows Server 2008 R2 SP1 64-bit Datacenter Windows Server 2008 R2 SP1 64-bit Enterprise Windows Server 2008 R2 SP1 64-bit Standard Windows Server 2008 R2 SP1 64-bit Foundation Windows Server 2008 R2 SP1 64-bit Web Windows 8.1 32-bit Windows 8.1 Pro 32-bit Windows 8.1 Enterprise 32-bit Windows 8.1 64-bit Windows 8.1 Pro 64-bit Windows 8.1 Enterprise 64-bit Windows 8 32-bit Windows 8 Professional 32-bit Windows 8 64-bit Windows 8 Professional 64-bit Windows 7 SP1 64-bit Ultimate Windows 7 SP1 64-bit Enterprise Windows 7 SP1 64-bit Professional Windows 7 SP1 32-bit Ultimate Windows 7 SP1 32-bit Enterprise Windows 7 SP1 32-bit Professional Windows Server 2008 SP2 64-bit Datacenter Windows Server 2008 SP2 64-bit Enterprise Windows Server 2008 SP2 64-bit Standard Windows Server 2008 SP2 64-bit Foundation Windows Server 2008 SP2 64-bit Web Windows Server 2008 SP2 32-bit Datacenter Windows Server 2008 SP2 32-bit Enterprise Windows Server 2008 SP2 32-bit Standard Windows Server 2008 SP2 32-bit Web
|
Windows Server 2012 R2 64-bit Datacenter Windows Server 2012 R2 64-bit Standard Windows Server 2012 R2 64-bit Essentials Windows Server 2012 R2 64-bit Foundation Windows Server 2012 64-bit Datacenter Windows Server 2012 64-bit Standard Windows Server 2012 64-bit Essentials Windows Server 2012 64-bit Foundation Windows Server 2008 R2 SP1 64-bit Datacenter Windows Server 2008 R2 SP1 64-bit Enterprise Windows Server 2008 R2 SP1 64-bit Standard Windows Server 2008 R2 SP1 64-bit Foundation Windows Server 2008 R2 SP1 64-bit Web Windows 8.1 64-bit Windows 8.1 Pro 64-bit Windows 8.1 Enterprise 64-bit Windows 8 64-bit Windows 8 Professional 64-bit Windows 7 SP1 64-bit Ultimate Windows 7 SP1 64-bit Enterprise Windows 7 SP1 64-bit Professional Windows Server 2008 SP2 64-bit Datacenter Windows Server 2008 SP2 64-bit Enterprise Windows Server 2008 SP2 64-bit Standard Windows Server 2008 SP2 64-bit Foundation Windows Server 2008 SP2 64-bit Web |
Windows Server Core Support
Like the earlier SQL Server 2012 release, SQL Server 2014 provide full support for running on Windows Server Core. Windows Server Core is the minimal Windows Server implementation which runs without the graphical shell—this provides a lower overhead and smaller attack and patching surface than a full Windows Server implementation. SQL Server 2014 is supported on Server Core mode in the following editions of Windows Server:
- Windows Server 2012 R2 64-bit Datacenter x64
- Windows Server 2012 R2 64-bit Standard x64
- Windows Server 2012 64-bit Datacenter x64
- Windows Server 2012 64-bit Standard x64
- Windows Server 2008 R2 SP1 Datacenter x64
- Windows Server 2008 R2 SP1 Enterprise x64
- Windows Server 2008 R2 SP1 Standard x64
- Windows Server 2008 R2 SP1 Web x64
SQL Server 2014 Software Requirements
SQL Server 2014 also has a few software requirements beyond the base operating system. The primary software requirements are:
- The .NET Framework 3.5 SP1 is required when you select Database Engine, Reporting Services, Master Data Services, Data Quality Services, Replication, or SSMS. This is not installed by the SQL Server setup process.
- The .NET Framework 4.0. This is installed by the SQL Server setup process.
- PowerShell 2.0 or higher is required if you have installed the database engine or SSMS.
- The Windows Installer service must be running.
For more information about the prerequisites for installing SQL Server 2014 you can refer to Hardware and Software Requirements for Installing SQL Server 2014.
Upgrading Using the SQL Server Installation Wizard
The SQL Server Installation Center supports direct upgrades to SQL Server 2014 from SQL Server 2012 / R2, SQL Server 2008 / R2, and SQL Server 2005. SQL Server 2012’s essential on-disk structures are the same as these previous versions and the setup program can successfully perform an in-place migration of all of these previous versions of SQL Server.
To initiate the upgrade to SQL Server 2014 you can execute the Autorun program from the SQL Server 2014 installation media or you can launch the setup.exe program found in the installation media’s root directory. These options will both start the SQL Server Installation Center which you can see in Figure 1.
Before performing the upgrade, it’s a good idea to install and run the Upgrade Advisor. Selecting the Install Upgrade Advisor option from the Planning page will install the Upgrade Advisor on your system. After completing the installation, you can run the Upgrade Advisor can check your SQL Server system configuration and code. The Upgrade Advisor will warn you about changes or depreciated features that might affect your system upgrade. You can optionally generate a report from the Upgrade Advisor that you can use to systematically address any potential upgrade problems.
To begin the actual upgrade process, click the Installation link in the navigation pane shown on the left side of the installation wizard. This will display the SQL Server Installation Center’s Installation page that you can see in Figure 2.
On the Installation page of the SQL Server Installation Center, click the Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2 or SQL Server 2012 link to begin the upgrade process. This will launch the SQL Server 2014 installation wizard. As with a clean installation, the first thing the SQL Server 2014 installation wizard does is to display the Product Key dialog box, allowing you to either choose to install the free Evaluation Edition or to enter your SQL Server 2014 product key. You can see the Product Key dialog box shown in Figure 3.
After entering the product key, click the Next button to display the License Terms dialog box that’s shown in Figure 4.
To proceed with the upgrade, click the I accept the license terms box. You can optionally choose to send product usage information to Microsoft. This doesn’t send any confidential data—it only sends feedback about the product features that you are using. Clicking Next will optionally display the Global Rules dialog that is shown in Figure 5. In Figure 5, the Show Details button has been clicked so you can see the checks that the Upgrade to SQL Server 2014 wizard performs.
If all of the setup rules are passed, the Upgrade to SQL Server 2014 wizard will continue on to display the Microsoft Update dialog shown in Figure 6. If there are any issues that would prevent the upgrade from completing, they would be listed in the Global Rules dialog with a red or yellow icon. You must correct any items with a status of Failed before you can continue the upgrade. Clicking OK from the Global Rules screen will display the Microsoft Update dialog that you can see in Figure 6.
The Microsoft Update dialog controls whether SQL Server 2014 will use the Microsoft Update service to deliver automatic updates to this instance of SQL Server 2014. Using Microsoft Update is recommended by Microsoft. Checking the Use Microsoft Update to check for updates (recommended) enables SQL Server 2014 to get automatic updates from Microsoft Update. Clicking Next displays skips the Product Updates step and displays the Install Setup Files dialog that you can see in Figure 7.
While this dialog is displayed, the setup process will copy the setup files from the installation media and will scan Microsoft Update for any recent updates to the product. If there are any updates available then, they will be downloaded and installed. You don’t have to take any action on this screen. When the installation for the setup files and any downloads are complete the Upgrade Rules dialog will be displayed. Clicking the Show Details button will display the Update Rules dialog box that you can see in Figure 8.
The Upgrade Rules dialog box checks for any problems that might interfere in the upgrade process. If there are issues that will prevent the upgrade, a yellow icon will be displayed in the Rule list and the status of Fail will be displayed. You must correct any pending issues before proceeding. If all of the upgrade rules are passed, the rules will all be displayed with green check marks as you can see in Figure 8 and you can proceed with the upgrade. Click Next to display the Select Instance dialog box that’s shown in Figure 9.
The Select Instance dialog box lets you choose the SQL Server instance that you want to upgrade. It’s possible to have multiple instances installed on the same system and to upgrade them separately. SQL Server supports up to 50 instances on the same system and each named instance is essentially a different installation of the SQL Server code. You can select different instances to upgrade by using the Instance to upgrade dropdown at the top of the dialog. As you can see in Figure 9, a default instance named MSSQLSERVER has been selected to be upgraded. Clicking Next displays the Select Features dialog box shown in Figure 10.
The Select Features dialog box shows you the installed features that will be upgraded. All of the installed options are checked by default—you can’t choose to just update some features. When performing an upgrade to SQL Server 2014, you must update all of the installed features in the selected instance at the same time. In addition, you cannot install any additional features. Additional features must be added after the upgrade process has completed. Clicking Next displays the Instance Configuration screen, shown in Figure 11.
The upgrade process will detect the installed SQL Server instances and all of the installed instances will be listed in the Installed instance box at the bottom of the screen. You can choose to keep the installed instance name or you can opt to change it. In most cases, you’ll want the upgrade process to use the same SQL Server instance name. In Figure 11, you can see that upgrade process will update the default MSSQLSERVER instance. As you can see in Figure 12, it also displays the required disk storage for each installation directory. If there’s adequate space to proceed with the installation, a green check mark will be displayed at the top of the screen. If there isn’t enough disk space to perform the upgrade, a red X will be displayed. Click Next to display the Server Configuration screen shown in Figure 12.
The Server Configuration dialog box enables you to change the default startup type and authentication information for SQL Server Integration Services, the Distributed Replay Controller, the Distributed Replay Client, and SQL Full-text Filter Daemon Launcher. You can set the authentication to a domain user account or you can accept the default values, which were pulled from the existing configurations. Click Next to proceed through the upgrade installation process. Microsoft recommends that you use a separate domain account for each service. If you installed the Full-text search feature on the SQL Server instance that you selected to upgrade, then you’ll see the Full-text upgrade dialog shown in Figure 13.
You use the Full-text Upgrade screen to specify how you want the upgrade process to deal with any existing full-text search catalogs. You can choose to import the existing catalogs without any changes, or you can choose to rebuild or reset the full-text catalogs. Importing is the default option and it leaves the full-text catalogs unchanged, but that means that they may not be able to take advantage of the enhanced SQL Server 2014 full-text search functionality. Rebuilding will rebuild the catalogs, adding the new functionality, but using this option can considerably lengthen the upgrade process. The Reset option clears the existing full-text catalog data, but does not rebuild it during the upgrade. If you select the Reset option, you can rebuild the catalog data manually after the installation. If you choose the default Import option, you can rebuild the full-text catalogs at some point after the upgrade has completed. Clicking Next displays the Feature Rules dialog shown in Figure 14.
The Feature Rules dialog box performs a final check for any conditions that might cause the upgrade process to fail. If any error conditions are found they will be shown in the Rule listing preceded with a red X. If all the rules have green check marks, as you can see in Figure 16, then you can proceed with the upgrade. If all of the upgrade rules are passed the upgrade wizard will automatically display the Ready to Upgrade dialog that you can see in Figure 15. If there are errors then you will need to correct them and click the Re-run button.
The Ready to Upgrade dialog box enables you to see a summary of the upgrade actions that will be performed. If you need to change anything, you can use the Back button to page back through the previous upgrade dialog boxes. Click the Upgrade button to launch the SQL Server 2014 upgrade process. The Upgrade Progress dialog that you can see in Figure 16 will be displayed showing the current action and status of the upgrade process.
After the SQL Server 2014 upgrade operation is finished, the Complete dialog shown in Figure 17, will be displayed. This process takes anywhere from 15 minutes to a half an hour depending on your system's perfromance. After that point, your SQL Server instance will have been upgrade to SQL Server 2014 and you can begin using the new SQL Server 2014 installation.
Verifying the Upgrade
You can verify the installation of SQL Server 2014 by querying the build number. To find the build number of your SQL Server instance, open SQL Server Management Studio (SSMS). The version number is listed immediately following the system name in the Object Browser. Following the upgrade your build number should be the following:
- SQL Server 2014 RTM – 12.0.2000
You can also find the SQL Server build number, the product’s production level and edition name by opening Query Editor and then running the following T-SQL queries:
SELECT @@VERSION
Which should return the following:
----------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3
Or you can run
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Which should return the following:
------------------------------------------------------------------------
12.0.2000.8 RTM Enterprise Edition (64-bit)
(1 row(s) affected)
Related: Detailed Migration Steps for SQL Server Upgrades, Part 1
