Channel Strategies for the CIOUpgrading SQL Server
 |
||||
|
|
|||
|
||||
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 http://www.microsoft.com/downloads/details.aspx?familyid=1470e86b-7e05-4322-a677-95ab44f12d75&displaylang=en (at www.microsoft.com/downloads, 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:
- To access the splash screen, insert the CD if autoplay is enabled, or launch the screen from serversdefault.hta on the installation media.
- 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.
- 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).
- 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.
- 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.
FIGURE 9.9 The SQL Server 2005 installation Components to Install screen
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:
- 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.
- 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.
- At the Installation Prerequisite Check screen shown in Figure 9.11, click Begin TestDBUpgrade.
- 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 myITforum.com, 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.
FIGURE 9.10 Copying the SMS site 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
This was first published in November 2009
Join the conversationComment
Share
Comments
Results
Contribute to the conversation