• Article

Installing SQL Server and SQL Server Analysis Services

Microsoft Office PerformancePoint Server 2007 requires Microsoft SQL Server relational engine and SQL Server Analysis Services to be used for storage purpose.

Note

In a multi-server PerformancePoint Server topology, you can have one or more computers running SQL Server and SQL Server Analysis Services.

SQL Server 2005 and Cumulative Update Package 3 for Service Pack 2

In a PerformancePoint Server deployment, SQL Server computers contain the relational databases for your Planning Server applications, as well as your PerformancePoint Server system database.

If you are using Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007 in your deployment, the SQL Server computers will contain configuration databases and content databases for SharePoint Products and Technologies.

Note

The terms Windows SharePoint Services and SharePoint Services are used collectively in PerformancePoint Server documentation to refer to Office SharePoint Server 2007 and Windows SharePoint Services 3.0.

Important

PerformancePoint Planning Server requires the cumulative update package 3 for SQL Server 2005 Service Pack 2. This cumulative update package 3 is also known as Build 3186.

The following programs and components must be installed on SQL Server computers in a PerformancePoint Server deployment:

  • SQL Server 2005 with Service Pack (SP) 2, relational components only. Standard Edition or Enterprise Edition only. The trial version is here (you will have to register to download it): https://go.microsoft.com/fwlink/?LinkId=87015

  • A cumulative update has been released for SQL Server 2005 SP2. This update is called "Cumulative update package 3 for SQL Server 2005 SP2" and is also known as build 3186. All Analysis Services computers in your PerformancePoint Server environment must have this update applied prior to your Planning Server installation.

    The update package can be requested by going to: https://support.microsoft.com/kb/939537.

  • SQL Server setup and support files

  • SQL Server Native Client components

  • OLE DB Provider for OLAP Services 9.0

  • Microsoft Core XML Services (MSXML 6.0), which is found here: https://go.microsoft.com/fwlink/?LinkId=78220

SQL Server 2005 Analysis Services

Computers running Analysis Services in your PerformancePoint Server deployment provide the online analytical processing (OLAP) cubes that contain your Planning Server business data.

The following programs and components have to be installed on SQL Server Analysis Services computers in a PerformancePoint Server deployment:

  • SQL Server 2005 Analysis Services

  • SQL Server 2005 Service Pack 2

Installing and configuring SQL Server 2005 for PerformancePoint Server

To install SQL Server 2005, you must be logged in to a Windows Server 2003 computer as an administrator with "Privileged User" access. Do not install Analysis Services on your SQL Server computers that are to be dedicated to relational databases.

The following sections cover best practices for installing and configuring SQL Server 2005 to work with PerformancePoint Server. We highly recommend that you use SQL Server 2005 Enterprise Edition with the PerformancePoint Server system as Planning Server uses some features that are only available in the SQL Server Enterprise Edition.

SQL Server 2005 installation

SQL Server installation requires that you choose a version to install, consider the hardware required, select components to install, set your startup service account choices, and set collation options. The following sections discuss these steps and options.

Choosing a SQL Server version

Choosing the correct version of SQL Server to work with PerformancePoint Server is important. Consider the following:

  • SQL Server 2005 Enterprise Edition is often your best choice. If you plan to use the full features of Planning Server, then Enterprise Edition is your only choice.

  • Choose either 32-bit or 64-bit; both are supported.

  • SQL Server 2005 Developer Edition has the same features as Enterprise Edition, except that it cannot be deployed as a production system.

  • SQL Server 2005 Standard Edition does not have the high-availability feature. It also does not have certain Analysis Services features that are used by the Planning Server.

  • The Trial Edition is supported as well, but it expires after 180 days. Other than the expiration, it is the same as the Enterprise Edition. You can get the Trial Edition by clicking the register link on this page: https://go.microsoft.com/fwlink/?LinkId=87015

Hardware recommendations

Use the following information as guidelines for recommended hardware:

  • Use the NTFS file system. Do not use drive compression.

  • Use more smaller/faster disks instead of fewer/larger disks.

  • The operating system should be on redundant disks, if possible.

  • SQL Server installation should be on redundant disks, if possible.

  • Transaction logs should be on fast, redundant, and preferably isolated disks.

  • TempDB should be isolated and, if on a multiprocessor computer, you should use multiple files.

  • If possible, spread TempDB files on multiple disks.

    Note

    READ_SNAPSHOT_COMMITTED ON is set by default. The temporary database's correct size and placement is very important for performance. The online rebuild index feature is also used, which requires a large amount of temporary database space. Refer to the PerformancePoint Planning Operation Guide for details on the TempDB size recommendation and database physical storage design guidelines.

Choosing components to install

Pick all components. Notification Services is not required but may be selected.

Install the following:

  • SQL Server Database Services

    Important

    It is recommended to choose only the Database Services component if you are installing SQL Server on a computer that will only contain relational databases in your PerformancePoint Server deployment, such as the system database or Planning application databases. Do not choose to install this component if you will be installing Analysis Services on this computer.

  • Analysis Services

    Important

    It is recommended to choose only the Analysis Services component if you are installing SQL Server on a computer that will not contain relational databases for your PerformancePoint Server deployment and will only contain OLAP cubes for your PerformancePoint Server deployment.

  • Reporting Services

  • Integration Services

  • Workstation Components

  • Books Online

  • Development Tools

    Note

    If you have to install SQL Server sample databases, select Advanced and then select the samples to install.

Setting startup service account choices

Selecting the service account to run SQL Server or Analysis Services is important for your system security. In a multiple-server deployment scenario, the security settings between the SQL Server computer(s) and Analysis Services computer(s) will depend on the startup service account chosen here.

The following accounts are available for use with SQL Server 2005:

  • Dedicated domain account (not local administrator): the best choice

  • Network service account (this is a shared account; other services may also use it): not recommended

  • Local service (other services may also use it): not recommended

  • Local account that is a local administrator: not recommended

  • Domain account that is a local administrator: not recommended

Avoid using the local system administrator or domain administrator accounts for use with SQL Server 2005, as they are not as secure as dedicated accounts.

The service account used for the SQL Server Agent must be a SQL Server system administrator.

In multiple server deployment, if Analysis Services and SQL Server are installed on separate computers, use the same dedicated domain account as the startup service account for both SQL Server and Analysis Services to save you one security configuration step.

Use Windows authentication for the highest level of security.

Selecting collation settings

Collation settings affect searches, look-ups, and ORDER BY commands. The character data is also affected by ASCII and Unicode character sets. We recommend that you use the default collation settings for SQL Server 2005.

If you install SQL Server 2005 and Analysis Services on the same computer and with English-language Windows collation, the default SQL Server collation will be set automatically to SQL_Latin1_General_CP1_CI_AS, which is case-insensitive.

Note

Microsoft Office PerformancePoint Server 2007 requires case-insensitive collation be configured for its host SQL Server. If the host SQL server is configured to use case-sensitive collation, Setup will fail.

To enable meaningful string comparisons that use GB18030 characters (GB18030 is a separate standard used in the People's Republic of China for encoding Chinese characters), use the new SQL Server 90 collation version, signified by the 90 suffix added to its name. For example, instead of the Chinese_PRC collation, use Chinese_PRC_90. For more information, see Collation Settings in Setup (https://go.microsoft.com/fwlink/?LinkId=99685) in SQL Server Books Online.

Installing and configuring SQL Server 2005 Analysis Services for PerformancePoint Server

You must be logged in as an administrator or another account with "Privileged User" access to the Windows Server 2003 computer where you want to install SQL Server 2005 Analysis Services.

Follow the same instructions as described previously for installing SQL Server 2005 except when choosing components to install. Select Analysis Services instead of SQL Server Database Services.