Migrating to SQL Server 2012.note¶
Source:
/Volumes/X9 Pro/ObsNotes/YoudaoYunNotes/Migrating to SQL Server 2012.note.pdfConverted: 2025-12-10 12:17:10
A step-by-step migration guideJan 14, 2013
| SQL Server Pro
The majority of businesses will deploy 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 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.
Migrating to SQL Server 2012
Michael Otey
IN
SHARE
TWEET COMMENTS
2
SQL Server 2012
SQL Server 2012 Hardware Requirements
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.
lists all the supported Windows OSs for the principal editions of SQL Server 2012.
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:
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 "
."
T
able 1
Windows Server Core Support
Windows Server 2012 64-bit x64 Datacenter
Windows Server 2012 64-bit x64 Standard
Windows Server 2008 R2 SP1 64-bit x64 Datacenter
Windows Server 2008 R2 SP1 64-bit x64 Enterprise
Windows Server 2008 R2 SP1 64-bit x64 Standard
Windows Server 2008 R2 SP1 64-bit x64 Web
SQL Server 2012 Upgrade Considerations
Windows Server 2003 isn't a supported OS. If your instance of SQL Server is running on a
Windows Server 2003 system, you must upgrade the OS before upgrading to SQL Server 2012.
A cross-platform upgrade isn't supported. You can't upgrade a 32-bit version of SQL Server to a
64-bit version.
You can't add new features as a part of the upgrade process. However, you can add new features
after the upgrade.
The Windows Installer service must be running.
Microsoft .NET Framework 3.5 SP1 and .NET Framework 4.0 are required.
Windows PowerShell 2.0 or later is required if you're upgrading the database engine.
IIS 7.0 or later is required for the Microsoft Management Console (MMC), SQL Server Data Tools,
Report Designer in SQL Server Reporting Services (SSRS), and HTML help.
Hardw
are 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. shows the supported
upgrade paths to SQL Server 2012 from the previous editions of SQL Server. For more detailed
information, you can check out " ."
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
Table 2
Supported Version and Edition Upgrades
Upgrading Using the SQL Server Installation Wizard
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 theInstalled
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
Import the existing catalogs without any changes. Importing is the default option. It leaves the full-
text catalogs unchanged. However, this means that they might not be able to take advantage of
some of the enhanced SQL Server 2012 full-text search functionality. If you choose this option,
you can rebuild the full-text catalogs at some point after the upgrade has completed.
Rebuild the full-text catalogs. When you choose the Rebuild option, the upgrade process will
rebuild the catalogs, adding the new functionality. However, using this option can considerably
lengthen the upgrade process.
Reset the full-text catalogs. The Reset option clears the existing full-text catalog data but doesn't
rebuild it during the upgrade. If you select the Reset option, you can rebuild the catalog data
manually after the installation.
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.)
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
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. lists the
different services used by SQL Server 2012.
Verifying the Installation
SQL Server 2012 SP1 - 11.00.3000
SQL Server 2012 RTM - 11.00.2100.60
Table 3
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.