Migrating to SQL Server 2012

The majority of businesses will deploy SQL Server 2012 by upgrading existing instances of SQL Server. Microsoft provides a couple of different methods for upgrading to SQL Server 2012. You can use the installation wizard, or you can use a configuration file. The SQL Server 2012 installation process supports upgrading from SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005. However, it can't be used to upgrade older versions of SQL Server, such as SQL Server 2000, SQL Server 7.0, and SQL Server 6.5. I'll walk you through the process of upgrading a SQL Server 2008 SP2 instance running on Windows Server 2008 R2 SP1 x64 to SQL Server 2012 Enterprise using the installation wizard. First, I'll cover the SQL Server 2012 hardware and software requirements. Then, I'll step you through the actual upgrade process.

SQL Server 2012 Hardware Requirements

SQL Server 2012 is designed to run on a wide range of computer systems—from laptop and desktop systems to supercomputer class systems—so its minimum hardware requirements are surprising low. The minimum processing requirement is a 1.0GHz CPU for a 32-bit x86 implementation and a 1.4GHz CPU for a 64-bit x64 implementation. Microsoft's recommended minimum processor speed is 2.0GHz.

The minimum memory requirements for SQL Server 2012 are also quite low. The low-end SQL Server 2012 Express edition requires a minimum of 512MB of RAM, whereas the other editions require a minimum of 1GB. Microsoft's recommended minimum RAM for SQL Server is 4GB.

These days, it's hard to buy even a desktop system with anything lower than a 1GHz processor and 1GB of RAM, so these hardware requirements shouldn't be a problem for most businesses. Of course, most production implementations will require more processing power and greater amounts of memory.

SQL Server 2012 OS Requirements

Each SQL Server 2012 edition has different OS requirements. In addition, the 32-bit x86 versions and the 64-bit x64 versions of the SQL Server 2012 editions have somewhat different OS requirements. Table 1 lists all the supported Windows OSs for the principal editions of SQL Server 2012.

Windows Server Core Support

One of the big changes in SQL Server 2012 is support for running on Windows Server Core. Windows Server Core is the minimal Windows Server implementation that runs without the graphical shell. This provides a lower overhead and smaller attack and patching surface than a full Windows Server implementation. SQL Server 2012 is supported in Server Core mode in the following editions of Windows Server:

SQL Server 2012 Upgrade Considerations

There are several limitations to be aware of before upgrading to SQL Server 2012. The most important limitations are:

For more information about the prerequisites for installing SQL Server 2012, you can refer to "Hardware and Software Requirements for Installing SQL Server 2012."

Supported Upgrade Paths

You can't simply upgrade from any edition of SQL Server to any other edition. Microsoft provides a set of supported upgrade paths. This is particularly important in SQL Server 2012 because the editions have been changed from the previous releases. For example, SQL Server 2012 dropped the Datacenter edition and added the new Business Intelligence edition. Table 2 shows the supported upgrade paths to SQL Server 2012 from the previous editions of SQL Server. For more detailed information, you can check out "Supported Version and Edition Upgrades."

Upgrading Using the SQL Server Installation Wizard

The SQL Server Installation Center supports direct upgrades to SQL Server 2012 from SQL Server 2008 R2, SQL Server 2008, 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 2012, you can execute the Autorun program from the SQL Server 2012 installation media or you can launch the setup.exe program found in the installation media's root directory. Both of these options will start the SQL Server Installation Center, which you can see in Figure 1.

Figure 1: Installing Upgrade Advisor from the Planning Page of the SQL Server Installation Center

Before performing the upgrade, it's a good idea to install and run Upgrade Advisor. Selecting the Install Upgrade Advisor option from the SQL Server Installation Center's Planning page will install Upgrade Advisor on your system. After completing the installation, you can run Upgrade Advisor, which will check your SQL Server system configuration and code. Upgrade Advisor will warn you about changes or deprecated features that might affect your system upgrade. You can optionally generate a report from 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 on the left side of the SQL Server Installation Center. This will display the Installation page, which you can see in Figure 2. On that page, click the Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2 link to launch the SQL Server 2012 installation wizard.

Figure 2: Launching the SQL Server 2012 Installation Wizard from the Installation Page of the SQL Server Installation Center 

As with a clean installation, the first thing the SQL Server 2012 installation wizard does is run the Setup Support Rules to check if there are any system problems that would interfere with the upgrade. Figure 3 shows the Setup Support Rules dialog box, with the Show Details option enabled.

Figure 3: Checking for Problems That Might Cause the Upgrade Process to Fail 

If there are any problems that will prevent the upgrade from running, they'll be displayed in the rules list preceded by a yellow icon and the status will be listed as Fail. You must correct the problems before you can continue the upgrade. If the system passes all of the setup rules, the OK button will be enabled. Clicking OK from the Setup Support Rules dialog box displays the Product Key dialog box shown in Figure 4. You can either install the free Evaluation edition or enter your SQL Server 2012 product key.

Figure 4: Entering the SQL Server 2012 Product Key 

After entering the product key, click the Next button to display the License Terms dialog box that's shown in Figure 5.

Figure 5: Accepting the License Terms 

To proceed with the upgrade, select the I accept the license terms check 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're using. Clicking Next will display the Product Updates dialog box shown in Figure 6.

Figure 6: Including SQL Server Product Updates as a Part of the Upgrade Process 

If there are available updates for SQL Server 2012, they'll be displayed in the Product Updates dialog box. You can optionally include these updates as a part of the migration process, ensuring that you complete your upgrade with the most current software updates. In Figure 6, you can see that the SQL Server 2012 SP1 Setup Update is available. Selecting the Include SQL Server product updates check box will install this update as a part of the upgrade process.

After you click Next in the Product Updates dialog box, the Install Setup Files dialog box shown in Figure 7 will be briefly displayed. While this dialog box is displayed, the setup process will download the requested product updates and install them. You don't have to take any action on this screen. When the download and update process is complete, the Setup Support Rules dialog box will be displayed.

Figure 7: Watching the Installation Status of the Setup Support Files 

Once again, the SQL Server 2012 installation wizard runs the Setup Support Rules to check for any problems that might interfere in the upgrade process. Clicking the Show Details button will display the results. If there are problems that will prevent the upgrade, they'll be displayed in the rules list preceded by a yellow icon and the status of Fail will be displayed. You must correct any problems before proceeding. If all of the setup 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.

Figure 8: Checking Again for Problems That Might Cause the Upgrade Process to Fail 

Click Next to display the Select Instance dialog box shown in Figure 9.

Figure 9: Choosing the SQL Server Instance to Upgrade 

In the Select Instance dialog box, you specify 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. 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.

Figure 10: Reviewing the Installed Features That Will Be Upgraded 

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 update only some of the features. When performing an upgrade to SQL Server 2012, you must update all of the installed features in the selected instance at the same time. Clicking Next displays the Instance Configuration screen, which Figure 11 shows.

Figure 11: Specifying the Name of the Installed Instance 

The upgrade process will detect all the installed SQL Server instances and list them in the Installed instances 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 the upgrade process will update the default MSSQLSERVER instance. Clicking Next displays the Disk Space Requirements dialog box shown in Figure 12.

Figure 12: Reviewing How Much Space Will Be Used 

The Disk Space Requirements dialog box shows the disk space that will be required by the upgrade process, as well as the available disk space for the volume. 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 dialog box shown in Figure 13.

Figure 13: Changing the Default Startup Type and Authentication Information 

The Server Configuration dialog box lets you change the default startup type and authentication information for three SQL Server services: SQL Server Integration Services, SQL Full-text Filter Daemon Launcher, and SQL Server Browser services. You can set the authentication to a domain user account, or you can accept the default values, which are pulled from the existing configurations. Microsoft recommends that you use a separate domain account for each service. Click Next to proceed through the upgrade installation process.

If you installed the full-text search feature on the SQL Server instance that you're upgrading, you'll see the Full-text Upgrade dialog box shown in Figure 14.

Figure 14: Specifying How to Deal with Existing Full-Text Search Catalogs 

You use it to specify how you want the upgrade process to deal with any existing full-text search catalogs. You can choose to:

After selecting the option you want to use, click Next to go to the Error Reporting dialog box shown in Figure 15. In it, you can elect to send SQL Server error reports and usage information to Microsoft. This is optional. By default, the upgrade process won't send error reports to Microsoft. Click Next to display the Upgrade Rules dialog box, and then click the Show Details button.

Figure 15: Electing to Send SQL Server Error Reports and Usage Information to Microsoft 

The Upgrade Rules dialog box reports the results of a final check for any problems that might cause the upgrade process to fail. If any error conditions are found, they'll be shown in the rules list preceded with a red X. If all the rules have green check marks, as you can see in Figure 16, you can proceed with the upgrade.

Figure 16: Performing the Final Check for Problems That Might Cause the Upgrade Process to Fail 

Clicking Next displays the Ready to Upgrade dialog box, which Figure 17 shows.

Figure 17: Reviewing a Summary of the Upgrade Actions That Will Be Performed 

The Ready to Upgrade dialog box allows 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 dialog boxes. If you don't need to make any changes, click the Upgrade button to launch the SQL Server 2012 upgrade process. The Upgrade Progress dialog box, which you can see in Figure 18, will be displayed showing the current action and status of the upgrade process.

Figure 18: Watching the Current Action and Status of the Upgrade Process 

After the SQL Server 2012 upgrade operation is finished, the Complete dialog box, shown in Figure 19, will be displayed.

Figure 19: Reviewing the Upgrade Results 

At that point, your SQL Server instance has been upgraded to SQL Server 2012 and you can begin using the SQL Server instance. (In some cases, restarting the computer is necessary.)

Verifying the Installation

You can verify the installation of SQL Server 2012 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. After the upgrade, your build number should be one of the following:

You can also find the SQL Server build number and the product's production level and edition name by opening Query Editor and running the T-SQL query:

SELECT @@VERSION

It should return the following:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)Feb 10 2012 19:39:15   Copyright (c) Microsoft Corporation   Enterprise Edition (64-bit) on Windows NT 6.1    (Build 7601: Service Pack 1) (Hypervisor)

Alternatively, you can run the T-SQL query

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY  ('productlevel'), SERVERPROPERTY ('edition')

It should return the following:

11.0.2100.60    RTM    Enterprise Edition (64-bit)

In addition, SQL Server 2012 uses a number of services. The actual services that are in use will depend on the features that you've selected. To verify that the services you selected are running, select Administrative Tools on the Start menu and choose the Services option. Table 3 lists the different services used by SQL Server 2012.

Knowing What to Expect

Following the steps outlined here will help you know what to expect during your migration to SQL Server 2012, making your migration easier and helping you avoid errors. As always, be sure to back up all of your important databases before performing an upgrade.