Installing SQL Server and SQL Server Analysis Services
One of my basic questions was where to install PPS, so I figured I'd start with the "PerformancePoint Server 2007 Deployment Guide
" and more specifically, the "Installing SQL Server and SQL Server Analysis Services
" section, where you'll find these key prerequisites:
- Microsoft Office PerformancePoint Server 2007 requires Microsoft SQL Server relational engine and SQL Server Analysis Services to be used for storage purpose.
- SQL Server 2005 with Service Pack (SP) 2, relational components only. Developer Edition or Enterprise Edition only.
- 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.
- 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)
Going through that list, I verify that my ABCSQL01
server does have SQL Server Enterprise installed, and has SP2. I didn't think I'd installed the "Cumulative Update Package 3 for SQL Server 2005 SP 2." So I open up the Microsoft SQL Server Management Studio and I see that, according to the title of the top node in the left-hand pane, my build number is 9.00.3042—which is lower than Build 3186, the required build. So I visit the download link, but—as expected—that page simply tells me to submit a request to Microsoft Online Customer Services to obtain the cumulative update package. I do that, filling in a couple of fields to tell them I have SQL Server 2005, 32 Bit, English version, and give them my email address. Now I get to wait!
Nothing happened for two days, so I submitted my request again (who knows, it might not have gone through the first time) and finally, I get a link to download the hotfix (a file named 322810_intl_i386_zip.exe
) about a day and a half later. Now the fun can start!
The rest of this article details the steps I followed to complete the install. I've numbered the actual steps, and put other information you might need to know in between. As you read these articles keep in mind that the steps documented here aren't prescriptive, they're descriptive. In other words, these are the actions that I took to install the software, on my particular server configuration. I've documented the process so others will know what to expect. My hope is that readers will be able to leverage my experiences for planning their own installations, or to anticipate the undetaking.
- I start the hot fix install on my ABCSQL01 server by double clicking the executable. That simply extracts another .exe file, named SQLServer2005-KB936252-x86-ENU.exe. I double-click that and the SQL Server Setup loads.
- I accepted the defaults on the Feature Selection Window (see Figure 2). The installation then checks for locked files, and since SQL 2005 is running, tells me I'll have to stop SQL services to load it, which I do (see Figure 3 for the view of Services in Computer Management console after the SQL Services have been stopped). When you get a "Locked files not found. Press Next to continue" message, click Next.
|Figure 2. SQL Server 2005 Service Pack 2 Cumulative Hotfix: You should not change these installation options.||
|Figure 3. Computer Management: Stop the SQL-related services so the hotfix installation can proceed.||
- At that point, you can click Install, and the install should start (see Figure 4). So far, this seems pretty much the same as any other SQL Server 2005 Service Pack.
|Figure 4. Installation Process: Sit back, relax, and watch the blue bar grow.|
- When the install completes (it took about 20 minutes on my VMware SQL server), follow the on-screen directions to complete the install.
- I rebooted the server at this point just to make sure everything would start up properly, and there were no error messages on boot up, nor were there any unpleasant errors in the logs.
Now I make sure I have the other items on the prerequisites checklist. I'm pretty sure I have the SQL Server setup and support files and SQL Server Native Client components, because I installed everything on this server, but I'm not sure about the OLE DB Provider for OLAP Services 9.0 or Microsoft Core XML Services (MSXML 6.0), so I follow the link they provide
. Looks like the file I need is msxml6.msi
- I download msxml6.msi and try to install, but the installation fails, telling me I have a newer version . (6.10.1200.00) already installed, so I should be good there.
I suspect I also need the OLE DB Provider for OLAP Services 9.0. There's no apparent download on Microsoft's site, but a little browsing indicates that this is included as part of Analysis Services, which I do have installed. This page
mentions that the DLL associated with OLE DB Provider for OLAP Services 9.0 is msolap90.dll
- A quick file search on the SQL server finds msolap90.dll in C:\Program Files\Common Files\System\Ole DB, which I take as proof that I have the OLE DB Provider for OLAP Services 9.0.
OK, so I'm pretty sure I have met all the prerequisite criteria and can move forward.
|Figure 5. Server Properties from the SQL Server Management Studio: Verify that the Server Collation setting matches Microsoft's recommendations for case-insensitive collation, or the setup will fail.|
I glance through the steps for installing SQL Server 2005, which I've already done. The section on "Selecting Collation Settings" catches my attention, as I've been burnt by servers have the wrong collation settings.
- I verify my settings in SQL Server Management Studio, by right clicking on the top level node and looking under "General settings" (see Figure 5). Microsoft recommends SQL_Latin1_General_CP1_CI_AS, which is the default for a SQL install. I verify that this is in fact the Server Collation method being used. The "CI" in this collation refers to the fact that it is case insensitive, and as the note tells us, "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."
That's the end of the "Installing SQL Server and SQL Server Analysis Services" instructions, so I move back to the "PerformancePoint Server 2007 Deployment Guide
." The next link
in that document is for Planning Server.