Manage Learn to apply best practices and optimize your operations.

Upgrading to SQL Server 2005 and SC Configuration Manager 2007

Use these steps to test a SCCM 2007 database upgrade, and run Upgrade Advisor to learn how to set up and prepare for a SQL Server 2005 upgrade.

Solutions provider takeaway: This example of SQL Server upgrade can be a reference point for solutions providers who need to find the best methods for preparing and setting up for a SQL Server 2005 upgrade and System Center Configuration Manager 2007 migration.

Upgrading SQL Server

About the book:
This chapter excerpt on Migrating to System Center Configuration Manager (SCCM) 2007 (download PDF) is taken from the book System Center Configuration Manager 2007 Unleashed. This book is an all-inclusive guide to SCCM 2007. You will learn best practices for such tasks as designing, deploying, configuring and securing SCCM 2007 and backing up its components. This book will also guide you through all of the different aspects of SCCM 2007, including patch and compliance management, site maintenance, how to set up the Data Center Manager and configuring asset intelligence.

SMS 2003 SP 2 and above versions provide support for both SQL Server 2000 SP 4 and SQL Server 2005. If your site database server is running SQL Server 2000, you will need to upgrade to SQL Server 2005 and apply Service Pack 2 before you can upgrade your SMS primary site. The next sections of this chapter step through a sample SQL Server upgrade. For additional information about upgrading SQL Server, you can refer to the SQL Server documentation and the release notes on the product installation media.

Running the Upgrade Advisor

Before upgrading SQL Server, download and run the latest SQL Server 2005 Upgrade Advisor from (at, search for SQL Server 2005 Upgrade Advisor). After the download is complete, execute the SQLUASetup.msi Installer package to install the Upgrade Advisor. Once it is installed, you can launch the Upgrade Advisor from Start -> Programs -> Microsoft SQL Server 2005. The welcome screen shown in Figure 9.7 includes the option Launch Upgrade Advisor Analysis Wizard.

FIGURE 9.7 The SQL Server 2005 Upgrade Advisor installation welcome screen

The Analysis Wizard allows you to select the components and databases to analyze, and it generates a report. You can view the report from the launch report button when the wizard completes, or you can use the Launch Upgrade Advisor Report Viewer link on the Upgrade Advisor welcome screen. Be sure to investigate any potential problems indicated in the Upgrade Advisor report.

The report shown in Figure 9.8 identified an issue with one of the extended stored procedures registered by SMS. The links in the report indicated that the affected object was the extended stored procedure xp_SMS_notification. Following the instructions on the "Tell me more about this issue and how to resolve it" link, the issue was corrected by executing the following SQL queries to re-register the procedure with the full path:

FIGURE 9.8 The SQL Server 2005 Upgrade Advisor report

sp_dropextendedproc xp_SMS_notification
sp_addextendedproc xp_SMS_notification, 'C:WINDOWSsystem32smsxp.dll'

Performing the Upgrade

After preparing your database server for the SQL Server upgrade, you can launch the Setup from SQL Server installation media splash screen. Perform the following steps:

    1. To access the splash screen, insert the CD if autoplay is enabled, or launch the screen from serversdefault.hta on the installation media.
    2. Setup first installs the Microsoft SQL Native Client and Microsoft SQL Server 2005 Setup Support Files. You can install the server components after this step completes. In most cases, you will simply choose to upgrade the default SQL Server instance and select all options to match your existing configuration.
    3. You will have the option to install additional components, as shown in Figure 9.9. For example, you might want to install the Reporting Services component if you plan to install a reporting services point at the site and use SQL Reporting Services for your reports. Chapter 18, "Reporting," discusses Configuration Manager reporting and the use of SQL Reporting Services (SRS).

FIGURE 9.9 The SQL Server 2005 installation Components to Install screen

  1. When Setup completes, you can run the optional Surface Area Configuration Tool. You may want to run the tool at this time to enhance security by reducing the attack surface of your SQL Server. Chapter 20, "Security and Delegation in Configuration Manager 2007," describes the Surface Area Configuration Tool.
  2. After upgrading to SQL Server 2005, you will need to download and apply Service Pack 2. It is generally a good idea at this point to visit the Windows Update site and apply any recommended SQL Server 2005 post--SP 2 updates.

Database Upgrade Tips and Tricks

Before upgrading an SMS 2003 primary site to Configuration Manager 2007, test the data-base upgrade to ensure there are no incompatibilities. To test the database upgrade, perform the following steps:

    1. Make a copy of your site database to use for the test upgrade. It is essential to test the upgrade with a copy of the database rather than the actual site database, because running the test upgrade against your production database could render the database incompatible with SMS 2003.

      You can copy the database using SQL Server Management Studio by right-clicking the site database and choosing Tasks -> Copy Database, as shown in Figure 9.10. This launches the Copy Database Wizard. The wizard is straightforward, and you can generally accept the default options. Note that the SQL Server Agent service must be running for the database copy to succeed. When the copy completes, you should record the size of the newly copied database files. You will need this information to estimate the space requirements for the database upgrade.


    Copying the SMS site database
  1. Test the database upgrade using the following syntax:

    setup /testdbupgrade <databasename>

    Run this from the smssetupbin<processor architecture> folder of the ConfigMgr installation media, where <databasename> is the name of your copied database and <processor architecture> is generally i386.

  2. At the Installation Prerequisite Check screen shown in Figure 9.11, click Begin TestDBUpgrade.
  3. When the test database upgrade completes, you should see a message that Configuration Manager was successfully upgraded. To verify there were no errors, review the log files c:ConfigMgrPrereq.log and c:ConfigMgrSetup.log. Resolve any problems indicated in the logs before upgrading your site to ConfigMgr 2007.
    About the authors:
    Kerrie Meyler is an independent consultant and trainer with more than fifteen years of experience in the IT industry. While in field technical sales at Microsoft, Meyler focused on infrastructure and management and presented at numerous product launches. She has also authored Microsoft Operations Manager 2005 Unleashed.

    Greg Ramsey, a Microsoft Most Valuable Professional for Systems Management Server, has been working with SMS and desktop deployment since 1998. Ramsey has written numerous articles for, including Removing Drivers From the Driver Store in ConfigMgr Using PowerShell.

    Byron Holt is an experienced IT professional and SMS and Configuration Manager engineer and has worked at Microsoft on the Active Directory and Enterprise Manageability support teams. Holt is presently on the security engineering team at McAfee Inc.

    You should also compare the size of the upgraded database files with the initial data-base size you recorded in step 1. This provides an estimate of the additional space required when you upgrade the database.

Before upgrading your database, export any custom objects you created outside of SMS. Any custom tables created in the SMS database, for example, are removed by the upgrade,

FIGURE 9.11 Launching the database upgrade test

although the upgrade preserves custom views based on the default SMS tables. You should also be prepared to re-create any customizations made to default SMS reports, because the upgrade process overwrites changes made to the default reports.

Any custom objects you have created within SMS, such as custom collections or cloned reports, are preserved through the upgrade.

Migrating to Configuration Manager 2007
  SMS 2003 to System Center Configuration Manager 2007 migration
  SMS 2003 to System Center Configuration Manager 2007 in-place upgrade
  Upgrading to SQL Server 2005 and SC Configuration Manager 2007
  Upgrading SMS 2003 sites to System Center Configuration Manager 2007
  SMS 2003 to SC Configuration Manager 2007 post-upgrade considerations

Printed with permission from Sams Publishing. Copyright 2009. System Center Configuration Manager (SCCM) 2007 Unleashed by J Kerrie Meyler, Byron Holt and Greg Ramsey. For more information about this title and other similar books, please visit Sams Publishing.

Next Steps

Are you ready for the approaching SQL Server 2005 end of life?

SQL Server 2005: How end of life could be advantageous for DBAs

Upgrading after SQL Server end of life? Consider these database trends

Dig Deeper on Server management, sales and installation

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.