Chapter 1: Installing or Upgrading to the SQL Server 2008 Database Engine

Sams

1 2 3 4 5 Page 5
Page 5 of 5
  1. Log in to the server and insert the SQL Server 2008 media. Autorun should launch the SQL Server 2008 Installation Center landing page; otherwise, click Setup.exe.

  2. On the SQL Server Installation Center landing page, first select the Installation link, and then Upgrade from SQL Server 2000 or SQL Server 2005.

  3. On the Setup Support Rules page, review the outcome of the System Configuration Checker. Ensure that all tests associated with the operation passed without any failures, warnings, or skipped elements. Alternatively, you can review a standard or comprehensive report by selecting the Show Details button or View Detailed Report. Click OK to continue with the installation.

  4. On the Setup Support Files page, the wizard will prompt whether or not additional setup support files are required for the installation. If additional files are required, review the status of the files required and click Install.

  5. The Setup Support Rules page will be displayed again and will identify any outstanding items that may hinder the installation process associated with the SQL Server cluster. Review and correct failures and warnings before commencing the installation. If failures are not displayed, click Next to start the installation.

  6. On the Product Key page, enter the SQL Server Product Key and click Next.

  7. On the License Agreement page, accept the Licensing Terms, and then click Next.

  8. On the Select Instance page, use the drop-down menu and specify a SQL Server instance to upgrade. Click Next as displayed in Figure 1.8.

  9. Figure 1.8

    Specify the SQL Server instance to upgrade.


    Note

    The Installed Instances section displays all the instances installed on the system. In addition, to upgrade only SQL Server Management Tools, choose the option Upgrade Shared Features Only in the Instance to Upgrade drop-down list.


  10. Review the features to be upgraded in the Select Features page, and then Click Next.


  11. Note

    It is not possible to modify the SQL Server features being released during an upgrade.


  12. Review the name and InstanceID for the SQL Server instance being upgraded and click Next.

  13. The next page is the Disk Space Requirements. Review the disk space summary for the SQL Server components and features selected to be upgraded, and then click Next.

  14. On the Full-Text Upgrade page, specify an option of how the existing Full-Text catalogs will be processed after the upgrade. Click Next as displayed in Figure 1.9.

  15. Figure 1.9

    Specifying the Full-Text Upgrade option.

    • Import—Full-Text catalogs are imported “as is” and are ready to serve queries. This process is much faster than rebuilding the Full-Text catalogs; however, the import does not leverage the new Full-Text features and functionality associated with SQL Server 2008.

    • Rebuild—When this option is selected, the Full-Text catalogs are rebuilt using the new and enhanced word breakers associated with SQL Server 2008. This process is typically slower, and a significant amount of processor and memory will be required to tackle this operation.

    • Reset—The final option is Reset. The Full-Text catalogs will be removed; therefore, after the upgrade is complete, the catalogs will remain empty until they are manually processed with a full population.

  16. On the Error and Usage Reporting page, help Microsoft improve SQL Server features and services by sending error reports and feature usage to Microsoft. Specify the level of participation, and then click Next.

  17. The final check will take place to ensure that the upgrade process will not be blocked. On the Upgrade Installation Rules page, review for any outstanding errors or warnings and then click Next to continue.

  18. Before commencing the SQL Server 2008 upgrade, review the features to be upgraded on the Ready to Upgrade page, and then click Install.

  19. When the upgrade process starts, you can monitor its progress accordingly. When the upgrade setup completes, review the success status based on each SQL Server feature. Click Next as illustrated in Figure 1.10.

  20. Figure 1.10

    Reviewing the results of the SQL Server 2008 upgrade.

  21. On the Complete page, review the location of the SQL Server summary upgrade log file and items in the Supplemental Information section. Click Close to finalize the upgrade.

  22. To conduct post-installation tasks, review the upcoming section “Finalizing the SQL Server 2008 Installation or Upgrade.”

Finalizing the SQL Server 2008 Installation or Upgrade

Once the installation or upgrade of SQL Server 2008 is complete, it is beneficial to review the following tasks in order to finalize the installation.

Reviewing SQL Server 2008 Logs

When the installation or upgrade is complete, it is best practice to review the setup log file, review the Windows application log, and review SQL Server logs for any issues or warnings. As noted earlier, the location to the SQL Server 2008 installation setup file can be found on the Complete page during the final stages of the installation or upgrade.

Downloading and Installing Updates

Even though the Windows Server 2008 system may be configured to automatically obtain server updates, it is still a best practice to check for missing SQL Server 2008 and Windows Server 2008 service packs and critical fixes. These outstanding service packs and critical fixes can be installed with Microsoft Update or a software distribution tool such as System Center 2007 Configuration Manager.

Hardening the SQL Server Installation

Another important step to finalize the SQL Server 2008 installation or upgrade is hardening the SQL Server implementation. There are a number of tasks that should be completed in order to harden the SQL Server installation. Some of these tasked include using Policy Based Management to reduce the surface attack area, enabling a Windows Server 2008 advanced firewall, and leveraging the SQL Server Configuration Manager Tool to disable unnecessary protocols and features.


Note

The SQL Server Surface Area Configuration Tool has been deprecated in SQL Server 2008. Therefore, Policy Base Management and sp_configure should be utilized instead, in order to harden and configure the surface area of a SQL Server installation.


Items to Consider After an Upgrade

This section describes additional items to take into consideration after an upgrade to SQL Server 2008 is complete.

Running SQL Server Management Studio for the First Time

After the upgrade is complete and you launch SQL Server Management Studio for the first time, you will be prompted to import customized user settings from SQL Server 2005 Management Studio. Click Yes or No and be aware that some SQL Server 2008 default settings might be changed after you import your customized settings.

Choosing the Database Compatibility Level After the Upgrade

When SQL Server systems are upgraded to SQL Server 2008, it is beneficial to understand how compatibility level settings affect databases. The compatibility levels include

  • SQL Server 2008—Version 100

  • SQL Server 2005—Version 90

  • SQL Server 2000—Version 80

If you select one of these options, the database behaviors are to be compatible with that specified version of SQL Server. This setting only affects a specific database and not all databases associated with a SQL Server instance.


Note

After the upgrade, SQL Server automatically sets the compatibility level to the earlier version of SQL Server.


The settings can be changed by right-clicking a database and specifying the compatibility level on the Database Options page or by using the ALTER DATABASE command. The following Transact-SQL sample illustrates how to change the compatibility level.

Alter Database <database name>Set Compatibility_Level =<80 | 90 | 100>

From a best-practice perspective, it is recommended to change the database to single-user mode before changing the database compatibility settings. This prevents inconsistent results if active queries are executed.

Additional Post-Upgrade Tasks

  • Update Statistics on all users and system databases.

  • Execute DBCC_UPDATEUSAGE on all databases to ensure that all databases have the correct row and page counts.

  • With SQL Server 2008, queries on partitioned tables and indexes are processed differently. Therefore, it is recommended to remove the USE PLAN hint from the query.

Managing SQL Server 2008 Installations

The following sections explain how to manage SQL Server 2008 installations.

Employing Additional SQL Server 2008 Instances

As mentioned earlier, many organizations decide on scaling up their SQL Server infrastructure by creating consolidated SQL Server systems with multiple-instance installations. To achieve the goal of installing additional instances on an existing system, a DBA must relaunch the SQL Server 2008 installation utility, and then select the option New SQL Server Stand-alone Installation or Add Features to an Existing Installation.

When the new SQL Server installation wizard begins, follow the steps in the earlier section “Installing a Clean Version of SQL Server 2008”; however, on the Installation Type page, select the option Perform a New Installation of SQL Server 2008, as displayed in Figure 1.11. Then on the Feature Selection page, select the desired features to be installed for the new instance. Finally, on the Instance Configuration page, provide the instance with a unique name and proceed with the installation.

Figure 1.11

Adding additional SQL Server instances to an existing installation.

Adding Features to an Existing SQL Server 2008 Installation

The process for adding and removing SQL Server features to an existing Installation is similar to the steps involved when adding additional SQL Server instances. The DBA must select New SQL Server Stand-alone Installation or Add Features to an Existing Installation from the SQL Server 2008 Installation Center’s Installation page. However, on the Installation Type screen, the option Add Features to an Existing Instance of SQL Server 2008 must be selected. Then on the Feature Selection page, select the features to be added and continue through the wizard.


Note

It is not possible to add features when upgrading to SQL Server 2008; therefore, this strategy should be used for adding additional features after the SQL Server upgrade is complete.


Changing SQL Server 2008 Editions

Another feature included with SQL Server 2008 is the potential to conduct an Edition upgrade after SQL Server 2008 has been installed. For example, if an organization is running the Standard Edition and decides that they want to leverage the features and functionality associated with the Enterprise Edition, they simply conduct an edition upgrade instead of formatting and reinstalling from scratch. Another advantageous scenario includes moving from SQL Server 2005 Standard to SQL Server 2008 Enterprise Edition. This objective would be achieved by first upgrading the SQL Server system from SQL Server 2005 Standard to SQL Server 2008 Standard, and then running the Edition Upgrade to upgrade the installation Enterprise Edition of SQL Server 2008.

To conduct an Edition Upgrade on SQL Server 2008, the Edition Upgrade must be selected from the Maintenance page on the SQL Server 2008 Installation Center landing screen.

Summary

The SQL Server 2008 installation process and deployment tools bear similarities to those found in previous versions of SQL Server. However, feature and performance enhancements associated with the new SQL Server 2008 Installation Center tool have improved the installation experience—whether you are installing a single SQL Server implementation from scratch or upgrading an existing system to SQL Server 2008.

Best Practices

  • The following are best practices from this chapter:

  • Verify that your hardware, devices, and drivers are supported by SQL Server 2008.

  • Stick to using the recommended or optimal hardware and software requirements when installing or upgrading to SQL Server 2008.

  • Leverage the planning tools and documentation associated with the SQL Server Installation Center when installing or upgrading to SQL Server 2008.

  • Run the System Configuration Checker tool as a prerequisite task when either installing or upgrading to SQL Server 2008.

  • Install and run the Upgrade Advisor to identify any upgrade anomalies when upgrading a system to SQL Server 2008.

  • When performing an upgrade, make sure you document your SQL Server system and database configuration information and perform a backup of any SQL Server data and objects that you want to keep.

  • Leverage Windows Server 2008 as the operating system when running SQL Server 2008.

  • Finalize a SQL Server implementation by hardening the system based on the best practices listed in Chapter 8.

  • Because the SAC tool has been deprecated, utilize Policy Based Management to configure the surface area of one or many SQL Server systems.

  • The Windows Server 2008 Advanced Firewall is enabled by default; therefore, review Chapter 8 to understand how to configure the firewall for SQL Server access.

  • Review Books Online if you need to upgrade other SQL Server 2008 features above and beyond the Database Engine.

  • Data, log, and tempdb directories should be on separate physical disks or Logical Unit Numbers (LUNs) for performance whenever possible.

Read author Ross Mistry's blog, written exclusively for Microsoft Subnet.

© Copyright Pearson Education. All rights reserved.

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2008 IDG Communications, Inc.

1 2 3 4 5 Page 5
Page 5 of 5
SD-WAN buyers guide: Key questions to ask vendors (and yourself)