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

Sams

1 2 3 4 5 Page 3
Page 3 of 5

Verifying Minimum Hardware Requirements

Whether you are installing SQL Server 2008 in a lab or production environment, you need to ensure that the hardware chosen meets the minimum system requirements. In most situations, the minimum hardware requirements presented will not suffice; therefore, Table 1.1 provides not only the minimum requirements, but also the recommended and optimal system requirements for the hardware components.


Note

This book is tailored toward the Standard and Enterprise Editions. As such, the minimum hardware and software requirements documented in Table 1.1 and Table 1.2 of this chapter only cover “core editions” of SQL Server 2008, also known as Standard and Enterprise Editions. To review the hardware and software requirements for the “specialized editions,” refer to the section on “Hardware and Software Requirements for Installing SQL Server 2008” in SQL Server 2008 Books Online (BOL).


Table 1.1  SQL Server 2008 Processor and Memory System Requirements

SQL Server 2008 Enterprise Edition (64-bit) IA64

Component

Minimum Requirements

Recommended Requirements

Processor

Itanium

1.0GHz or higher: Itanium

Memory

512MB

2.048GB or above

SQL Server 2008 Enterprise Edition (64-bit) x64

Component

Minimum Requirements

Recommended Requirements

Processor

1.4GHz: AMD Opteron, AMD Athlon, Intel Xeon EM64T, and Intel Pentium IV EM64T

2GHz or higher: AMD Opteron, AMD Athlon, IntelXeon EM64T, and IntelPentium IV EM64T

Memory

512MB

2.048GB or above

SQL Server 2008 Standard Edition (64-bit) x64

Component

Minimum Requirements

Recommended Requirements

Processor

1.4GHz: AMD Opteron, AMD Athlon, Intel Xeon EM64T, and Intel Pentium IV EM64T

2GHz or higher: AMD Opteron, AMD Athlon, IntelXeon EM64T, and IntelPentium IV EM64T

Memory

512MB

2.048GB or above

SQL Server 2008 Enterprise Edition (32-bit) x64

Component

Minimum Requirements

Recommended Requirements

Processor

1.0GHz: Pentium III

2GHz or higher: Pentium III

Memory

512MB

2.048GB or above

SQL Server 2008 Standard Edition (32-bit)

Component

Minimum Requirements

Recommended Requirements

Processor

1.0GHz: Pentium III

2GHz or higher: Pentium III

Memory

512MB

2.048GB or above

The minimum disk space requirements differ depending on which SQL Server 2008 feature will be installed. Table 1.2 depicts these minimum disk space specifications itemized by feature.

Table 1.2  SQL Server 2008 Minimum Disk Requirements

SQL Server 2008 Feature

Minimum Disk Space Required in MB

Database Engine and data files, Replication, and Full-Text Search

280

Analysis Services and data files

90

Reporting Services and Report Manager

120

Integration Services

120

Client Components

850

SQL Server Books Online (BOL) and SQL Server Compact Books Online

240


Note

When designing and selecting the system specifications for a SQL Server implementation, even the optimal system requirements recommendations from Microsoft might not suffice. It is a best practice to assess the server specifications of the planned server role while taking the load during the time of deployment and future growth into consideration. For example, a SQL Server 2008 system running 50 instances of the Database Engine will require much more than the recommended specification of 2GB of RAM to run adequately. In addition, SQL Server 2008 running on a Windows 2008 server that is providing business intelligence solutions for 10,000 users might require 32GB of RAM. Therefore, size the system accordingly and test the load before going live into production.


Examining SQL Server 2008 Software Prerequisites

Before installing SQL Server 2008, it is also important to get acquainted with the software prerequisites, as many of these prerequisites outline best practices. As such, you should take the time to review the prerequisites before implementation to ensure installation or upgrade success. The SQL Server 2008 software prerequisites include

  • .NET Framework 3.5

  • Windows Installer 4.5 or later

  • Microsoft Data Access Components (MDAC) 2.8 SP1 or later

  • Internet Explorer 6 SP1 or later

  • Latest version of PowerShell

  • Latest Windows Server hot fixes are recommended

  • If SQL Server 2008 will be virtualized, then Hyper-V is required and supported.


Note

For more information and consolidating and virtualizing SQL Server 2008 on Windows Server 2008 Hyper-V, refer to Chapter 21, “Consolidating and Virtualizating with Hyper-V.”


The SQL Server installation wizard will first verify if these software prerequisites are already installed. If they are not, don’t panic—the SQL Server 2008 installation wizard is very intuitive and will most likely prompt, and then install all of these software prerequisites automatically. Therefore, you won’t have to spend hours conducting Google searches, trying to nail down the appropriate downloads, including versions.

Choosing the Appropriate SQL Server Edition

SQL Server 2008 comes in a variety of editions that are tailored to suit the needs and requirements of different organizations and applications. The SQL Server 2008 Editions include the Enterprise, Standard, Workgroup, Express, Compact, and Developer Editions, as described in the following sections.

SQL Server 2008 Enterprise Edition

The SQL Server 2008 Enterprise Edition is the complete feature set of the product and is designed to support the needs of the largest enterprises. It includes all the features for scalability, performance, high availability, enterprise security, data warehousing, business intelligence, and enterprise manageability. The Enterprise Edition is fully 64-bit capable, is optimized to run on 64-bit platforms and can support all the processors and memory found in the operating system.

Some other new features only found in the Enterprise edition include Partitioned Table Parallelism, enhanced database mirroring features, Resource Governor, Backup Compression, online operations, Hot Add CPU, Performance Data Collector, Extensible Key Management, Failover Clustering, Transparent Data Encryption, and Change Data Capture.

SQL Server 2008 Standard Edition

The SQL Server 2008 Standard Edition includes the core set of functionality needed to support data warehouses, electronic commerce applications, and line-of-business applications. It is designed to support the needs of small to medium organizations. The Standard Edition is fully 64-bit capable and can support a maximum of four processors. It is worth mentioning that two nodes of failover clustering is also supported within the Standard Edition.

SQL Server 2008 Workgroup Edition

The SQL Server 2008 Workgroup Edition is designed for small organizations and includes the core database features needed for applications.

SQL Server 2008 Express Edition

The SQL Server 2008 Express Edition is the free edition that is designed to support small or targeted applications with a core set of secure database requirements. This edition replaces the Microsoft SQL Server Desktop Engine (MSDE) platform available in SQL Server 2000 and augments the Express Edition in SQL Server 2005.

SQL Server 2008 Compact Edition

The SQL Server 2008 Compact Edition is the free edition that runs on mobile devices as well as desktops. This provides a single lightweight database platform for client applications. This edition replaces the SQL Server Mobile product and augments the SQL Server 2005 Compact Edition.

SQL Server 2008 Developer Edition

The SQL Server 2008 Developer Edition provides all the same features and functionality as the Enterprise Edition but is licensed only for development purposes.

The following link includes the full list of features supported based on the Editions of SQL Server 2008.

Choosing the Appropriate Windows Operating System Version and Edition to Support the SQL Server Installation

SQL Server 2008 can run on a number of Windows operating systems. SQL Server 2008 can run on top of Windows Server 2008, Windows Server 2003 SP2, Windows VISTA, and Windows XP. When referring to Windows Server 2008, either the Windows Server 2008 edition with or without Hyper-V can be utilized. Please note that SQL Server 2008 does not support running Windows 2000 or Windows NT 4.0.

Benefits of Running SQL Server 2008 on Windows Server 2008

Hands down, the Windows Server 2008 family of operating systems is the best choice for running SQL Server 2008. By combining the two products, the highest level of security, scalability, reliability, high availability and compliance can be achieved. Some of the major benefits of running SQL Server 2008 on Windows Server 2008 include the following:

  • Authentication—The Windows Server 2008 authentication mechanism provides the highest level of security for authorization when running Active Directory Domain Services. SQL Server can leverage the following: Active Directory role-based security for authorization and administration, two-factor authentication with SmartCard-based certificates and biometric devices, and integration with certificate services. Finally, Kerberos is now supported for all SQL Server protocols.

  • Encryption—By combining the encryption technologies included in both SQL Server 2008 and Windows Server 2008, it is finally possible to achieve encryption from an end-to-end perspective.

  • Minimized Footprint—Both Windows Server 2008 and SQL Server 2008 provide a modularized installation process that is very granular. Therefore, you only install what you need. This strategy minimizes the attack surface, which in turn, mitigates breaches and compromises.

  • Compliance—New features and functionality such as integrating Audit and Audit Specifications directly with the Windows Server 2008 event and security logs allows for stronger auditing functionality, which is a requirement of many major regulatory compliances.

  • Dynamic Hardware Partitioning—Allows for both CPU and RAM to be added to the SQL Server system on the fly, without causing a server outage.

  • High Availability Clustering—Windows Server 2008 supports up to 16 nodes within a SQL Server 2008 failover cluster. In addition, the requirement of having all nodes within the same subnet has been alleviated. Consequently, with the new quorum model and no subnet restriction, it is easier to achieve geographically dispersed clusters.

  • Policy Based Management—By leveraging the Windows Server 2008 group policy and the configuration management strategies, policies can be created to manage SQL Server databases settings and configurations.

  • PowerShell—The latest scripting technology geared toward effectively managing Windows Server and Microsoft applications has extended to SQL Server 2008. DBAs can use the powerful command-line scripting technologies to automate administrator tasks for both Windows Server 2008 and SQL Server 2008.

  • Performance Management—Windows Server 2008 introduces Windows Reliability and a newly refined performance monitor tool for troubleshooting and monitoring SQL Server system performance. In addition, the Windows performance framework has been augmented through the introduction of SQL Server 2008 Performance Data Collector. As a result, collecting, analyzing, and troubleshooting SQL Server data in a centralized solution for end-to-end monitoring can be achieved.

  • Consolidation and Virtualization—Hyper-V has been introduced with Windows Server 2008. Hyper-V is Microsoft’s virtualization technology. By using Hyper-V in conjunction with SQL Server 2008, you can consolidate SQL Servers into a virtualized environment, reduce SQL Server hardware, and reduce total cost of ownership within the infrastructure.

Understanding the Windows Server 2008 Family of Operating Systems

In the Windows 2008 family of operating systems, there are four main editions, and SQL Server 2008 can run on any of them. These editions include Windows Server 2008 Standard, Windows Server 2008 Enterprise Edition, Windows Server 2008 Datacenter Edition, and Windows Web Server 2008.

Organizations and DBAs must understand their workload needs and requirements when selecting the appropriate Windows Server 2008 operating system edition to utilize. In addition, the Windows Server edition selected must also coincide with requirements pertaining to the edition of SQL Server 2008 selected. For example, the Windows Server 2008 Enterprise Edition might be selected if there is a need to sustain an eight-node SQL Server failover cluster; the Standard Edition may be selected in order to save on licensing costs; or Windows Server 2008 Enterprise Edition with Hyper-V may be selected if there is a need to virtualize the SQL Server environment.

Each edition supports both the 32-bit and 64-bit processor architectures and the Standard, Enterprise, and Datacenter editions ship with or without Hyper-V. Hyper-V is the latest Microsoft virtualization technology based on Windows Server 2008.

Finally, when running SQL Server 2008 on Windows Server 2008, the maximum amount of RAM supported by the operating system on 32-bit systems is 4GB when running the Standard Edition and 64GB when running the Enterprise and Datacenter Editions. For a 64-bit system, the numbers increase as the Standard Edition can support 32GB and the Enterprise and Datacenter Editions can support up to 2TB of RAM. Hence it is strongly recommended to be using the x64 versions of the operating system whenever possible as it allows for greater flexibility and upgradability.


Caution

When installing Windows Server 2008, it is possible to select a Server Core installation. Windows Server 2008 Server Core is a stripped-down minimal installation that provides a low-maintenance environment through limited functionality. The present version of Server Core is not intended to be an application platform. Since there are SQL Server dependencies that are not part of the Server Core, SQL Server 2008 cannot run on a Windows Server Core installation.


For an overview of the editions and a complete list of Windows 2008 features and functionality, refer to the Microsoft Windows Server 2008 home page at the following link: http://www.microsoft.com/windowsserver2008/en/us/default.aspx.

New Installation, Upgrade, or Transition?

Organizations that have conducted a SQL Server implementation in the past may need to perform a new SQL Server 2008 installation, a side-by-side installation or upgrade their existing SQL Server system, which is commonly referred to as an in-place upgrade. Finally, organizations may choose to transition to SQL Server 2008 by first installing a new installation and then migrating SQL Server databases and objects from the legacy environment. There are benefits to each of these options. The next two sections detail the benefits.

Should You Perform a New SQL Server 2008 Installation?

The primary benefit of a new installation is that, by installing the operating system from scratch, you are starting with a known good server and a brand new SQL Server 2008 implementation. You can avoid migrating problems that might have existed on your previous server—whether due to corrupt software, incorrect configuration settings, or improperly installed applications. Moreover, a new installation provides an opportunity for housecleaning as legacy SQL Server items are not carried over.

For example, it is common for an old SQL Server system to have many outdated databases, packages, user accounts, and stored procedures that have not been touched in over 10 years. Keep in mind, however, that you will also lose all configuration settings from your previous installation. In addition, all SQL Server elements, such as databases, user accounts, packages, and so on, will need to be migrated/transitioned. Moreover, required applications on the legacy server will need to be reinstalled after the installation of the new operating system and the SQL Server 2008 implementation are complete. Make sure you document your server configuration information and back up any data that you want to keep.

When running SQL Server 2008, there may be situations where installing a new installation from scratch is the only option. For example, it is not possible to upgrade a legacy SQL Server Failover Cluster from SQL Server 2005 running on Windows Server 2003 to SQL Server 2008 Failover Clustering running on Windows Server 2008.


Note

When performing a new installation of SQL Server 2008, it is possible to install a new SQL Server 2008 instance on an existing system with SQL Server 2005. Therefore, a side-by-side installation is supported and a migration of existing data from SQL Server 2005 to SQL Server 2008 can be achieved all within the same server.


Should You Upgrade an Existing SQL Server System to SQL Server 2008?

Upgrading, on the other hand, replaces your current SQL Server binaries but keeps existing databases, components, features, packages, users, settings, groups, rights, and permissions intact. In this scenario, you don’t have to reinstall applications or restore data. Before choosing this option, keep in mind that you should test your applications and databases for compatibility before migration. Just because they worked on previous versions of SQL Server does not mean they will work on SQL Server 2008.

As always, before performing any type of server maintenance such as a SQL Server or Windows Server 2008 in-place upgrade, you should perform a complete backup of the SQL Server environment, any applications residing on the server, and data that you want to preserve. Do not forget to include the System State when backing up the SQL Server system. It is required when performing a restore if you want to maintain the existing Windows settings.

Table 1.3 lists the upgrade paths for SQL Server 2008.

Table 1.3  SQL Server 2008 Upgrade Paths

Previous SQL Server System

Upgrade to SQL Server 2008

SQL Server 2008

SQL Server version upgrades supported

SQL Server 2005

Yes, fully supported to like edition

SQL Server 2000

Yes, fully supported to like edition

SQL Server 7.0

Not supported

SQL Server 6.5

Not supported


Note

In-place upgrades can only be accomplished when using the same edition. For example, an upgrade from SQL Server 2005 Standard to SQL Server 2008 Enterprise cannot be achieved. Nevertheless, there is a way around this situation. It is possible to upgrade from SQL Server 2005 Standard to SQL Server 2008 Standard and then conduct an edition upgrade to SQL Server 2008 Enterprise.


Should You Upgrade the Operating System to Windows Server 2008?

On another note, when upgrading an existing system to SQL Server 2008, there may be situations when an organization would also want to upgrade the underlying operating system to Windows Server 2008. To upgrade to Windows 2008, you must be running a server-level operating system. You cannot upgrade Workstation or Home Editions of operating systems such as Windows XP or Windows Vista to Windows 2008. To upgrade your existing SQL Server’s operating system, you must be running Windows Server 2003. An upgrade from Windows NT 4.0 and Windows 2000 Server is not permitted. Table 1.4 lists the available upgrade paths to Windows 2008.

Table 1.4  Windows Server 2008 Upgrade Paths

Previous Operating System

Upgrade to Windows Server 2008

Microsoft Windows Server 2003 R2 Standard, Enterprise, or Datacenter Edition

Yes, fully supported to like edition

Microsoft Windows Server 2003 operating systems with Service Pack 1 (SP1) Standard, Enterprise, or Datacenter Edition

Yes, fully supported to like edition

Microsoft Windows Server 2003 operating systems with Service Pack 2 (SP2) Standard, Enterprise, or Datacenter Edition

Yes, fully supported to like edition

Windows NT 4.0

Not supported

Windows 2000 Server

Not supported

Windows XP

Not supported

Windows Vista

Not supported


Note

For Windows Server 2008, in-place upgrades can only be performed to the same editions of Windows Server 2008. For example, an upgrade from Windows Server 2003 Standard to Windows Server 2008 Enterprise cannot be achieved.


Gathering Additional Information Necessary to Proceed

During the installation of SQL Server 2008, you will have to tell the setup wizard how you want your server configured. The wizard will take the information you provide and configure the server settings to meet your specifications.

Taking the time to gather the information described in the following sections before starting your installation or upgrade will likely make your SQL Server 2008 installation go faster, smoother, and easier.

New SQL Server 2008 Installation or In-place Upgrade

The first and most major decision when moving toward SQL Server 2008 is debating whether to implement a brand new SQL Server installation from scratch or to conduct an in-place upgrade. If you don’t already have SQL Server in your existing infrastructure, it is a “no-brainer,” and a new installation is warranted. However, if a legacy version of SQL Server resides in the infrastructure, the organization must decide between an in-place upgrade or a new installation. If a new installation is chosen, it is necessary to transition existing SQL Server data from the legacy system to the newly established SQL Server 2008 system. As mentioned earlier, each alternative has benefits and disadvantages.

New SQL Server 2008 Stand-alone Installation or Failover Cluster

Another major decision needs to be made in the planning phases: Should SQL Server 2008 be installed in a stand-alone system or should Failover Clustering be utilized? Failover Clustering provides high availability for a SQL Server instance and should be leveraged if an organization needs maximum availability, protection against server hardware failure, seamless failover that does not require DBA intervention, and finally, automatic client redirects. A stand-alone installation is also sufficient, as it is cheaper, easier to administer, and does not require specific failover clustering hardware.


Note

To install SQL Server 2008 Failover Cluster, review Chapter 12, “Implementing and Managing Failover Clustering.”


Single-Instance or Multiple-Instance Installation

For years now, discussions on the topic of single-instance versus multiple-installation have both engulfed and engaged the SQL Server community. Should you install a single-instance SQL Server installation and place all databases on one instance, or scale up and create a multiple-instance SQL Server installation and spread databases across each of these instances? This question continues to echo through every organization. Here are some best practices to assist in making such an arduous decision.

One of the main drawbacks of placing all databases on a single-instance installation involves the tempdb database. The tempdb database is a shared resource between all databases contained within the same SQL Server instance. Performance degradation may occur as the tempdb database is the single point of contention for all temporary database workloads. In multiple-instance installations, a tempdb database is created for each instance, minimizing contention and performance degradation.

Many DBAs implement multiple instances for other reasons, including regulatory compliance, administrator autonomy, different global security policies, global server settings, and compatibility requirements.


Note

Only one instance within the installation can maintain the default instance name. Therefore, if a default instance already exists, SQL Server 2008 must be installed as a named instance.


Side-by-Side Installations with Previous Versions of SQL Server

Organizations also have the option to install a brand new installation of SQL Server 2008 on a server that is already running a legacy instances of SQL Server 2005. Based on this methodology, more than one version of SQL Server will reside on the system.

Typically, the preference is to either conduct an in-place upgrade or install SQL Server 2008 on new hardware to minimize hardware contention and performance degradation. However, side-by-side installations are sometimes warranted. Let’s look at the situations that support this installation. SQL Server 2008 will coexist with SQL Server 2005 and SQL Server 2000. Unfortunately, SQL Server 7.0 is not supported, but hopefully the majority of the organizations out there have already transitioned out of SQL Server 7.0 because it is no longer supported by Microsoft.

Determine Which SQL Server 2008 Features to Install

Give serious thought to the SQL Server 2008 features before installing them. The modular setup of SQL Server 2008 is made up of many independent features, previously referred to as components, allowing for complete customization by organizations. This typically results in minimal surface area and more granularity compared with older editions of SQL Server. This improved modular installation process is said to be “slim and efficient” like other new Microsoft products such as Windows Server 2008 and Exchange Server 2007.

The following bullets depict the modular installation including shared features that can be selected during the installation of SQL Server 2008:

  • Database Engine Services—This is the core service for storing, processing, and securing data. It is designed to provide a scalable, fast, and high-availability platform for access and the other components. Two subfeatures within the Database Engine are

    • SQL Server Replication—Replication allows DBAs to copy databases to different locations and keep the copies synchronized. This can be used for data distribution, synchronization, fault tolerance, disaster recovery, load balancing, or testing. The Replication component manages database replication and interacts primarily with the Database Engine features.

    • Full-Text Search—The Full-Text Search engine populates and manages the full-text catalogs. The Full-Text engine also makes full-text searches easier by maintaining indexes, a thesaurus, noise words, and linguistic analysis of the full-text indexes.

  • Analysis Services—The SQL Server 2008 Analysis Services (SSAS) feature provides online analytical processing (OLAP) and data mining. OLAP is a modification of the original database concept of online transaction processing (OLTP). OLAP is designed to provide immediate answers to analytical and ad hoc queries from a multidimensional cube known as an OLAP cube. Data mining is the process of searching large volumes of data for patterns and trends. SSAS allows SQL Server 2008 to provide both these capabilities and is the core feature of business intelligence.

  • Reporting Services—The Microsoft SQL Server 2008 Reporting Services (SSRS) feature allows for the presentation and delivery of data in a variety of ways. The reports can include tables, matrices, and free-form data. The source data for the reports can be provided by the Database Engine component, the Analysis Services component, or any Microsoft .NET data provider such as ODBC or OLE DB to access data sources such as Oracle or file-based data.

  • Shared Features—Features designated as “Shared Features” include

    • Business Intelligence Development Studio—The Business Intelligence Development Studio is essentially Microsoft Visual Studio 2008 with some additional SQL Server 2008 business intelligence project types. It is an applications development environment that allows developers to build applications that include Analysis Services, Integration Services, and Reporting Services.

    • Client Tools Connectivity—This feature includes the installation of communication components between clients and servers.

    • Integration Services—The SQL Server 2008 Integration Services (SSIS) feature integrates data from different sources. This integration includes importing, exporting, and transforming data from disparate sources. The data can be copied, merged, restructured, and cleaned as part of the integration processing, which makes the integration services a powerful tool in the development of data warehouses. It is imperative to mention that the Integration Services component fills an important gap in the extract.

    • Client Tools Backward Compatibility—This feature was heavily requested by the SQL Server community. When Client Tools Backward Compatibility is installed, a DBA can manage legacy SQL Server systems.

    • Client Tools SDK—This feature includes the Software Development Kit containing resources for developers.

    • SQL Server Books Online—SQL Server Books Online (BOL) is Microsoft’s documentation for SQL Server 2008.

    • Management Tools Complete—When installed, SQL Server 2008 will possess all the management tools, including but not limited to Management Studio, support for Reporting Services, Analysis Services, Integration Services, SQL Server Profiler, and Database Tuning Advisor.

    • Management Tools Basic—This refers to the scaled-down version of the management toolset. It only includes management studio support for the Database Engine, SQL Server Express, SQL Server Command-Line Utility, and PowerShell.

    • SQL Client Connectivity SDK—This feature includes the Software Development Kit containing connectivity resources for developers.

    • Microsoft Sync Framework—This is a comprehensive synchronization platform enabling collaboration and offline of applications, services and devices with support for any data type, any data store, any transfer protocol, and network topology.

When installing the SQL Server 2008 Database Engine, the additional optional subfeatures to install include

  • Replication

  • Full-Text Search

  • Integration Services

  • Connectivity components

  • Programming models

  • Management tools

  • Management Studio

  • SQL Server Books Online

Installing a Clean Version of SQL Server 2008

The following sections depict the step-by-step instructions for installing a clean version of the SQL Server 2008 Database Engine feature including supplementary subfeatures such as SQL Server Replication, Full-Text Search, Integration Services, and Shared Components.

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