Udostępnij za pośrednictwem

  • Artykuł

Prepare SQL Server to host an application database

Updated: 2009-04-09

As you prepare your computer running Microsoft SQL Server to host an application database, your database administrator (DBA) needs to properly set up the database server that will host the application databases.

First, the DBA needs to manually run two SQL scripts that will update the sys.messages table on the master database.

  • utl_pop_sysmessages.sql

  • utl_pop_sysmessages_lang.sql

These two SQL scripts are located on the computer that hosts the Planning System Database; the directory is C:\Program Files\Microsoft Office PerformancePoint Server\3.0\DatabaseScripts. The DBA needs sysadmin permission to execute these two scripts in the master database on this Application database server.

To update the sys.messages table on the master database

  1. In SQL Server Management Studio, load utl_pop_sysmessages.sql from the directory mentioned above.

  2. Run the script against the master database.

  3. Load utl_pop_sysmessages_lang.sql into SQL Server Management Studio.

  4. Run the script against the master database.

Second, you need to verify that your service identity accounts are set up properly. The scripts shown below ensure that error messages are captured in the application event logs and that the correct role has been applied to each service identity account. See Planning Server Application Pool Identity and Service Identity Account Considerations for additional information.

Note

This article is only applicable to Planning Server distributed deployment. It is not applicable to a stand-alone deployment.

If your application database will be hosted on a computer running SQL Server that does not contain the Planning System Database, then you must run the following script for each service identity account on each application SQL Server computer. The purpose of the following GRANT statement is to allow SQL Server's error messages from RAISERROR to be logged in the application event log.

use [master] 
GO 
GRANT ALTER TRACE TO [Domain\ServiceIdentityAccount] 
GO

The next script is a security measure that grants the dbcreator role to a service identity account. You must execute this script for each service identity account on each application SQL Server computer if one of the following cases is true for you:

  • You want to select Automatically execute application creation scripts option during Create Application from Planning System Administration Console

  • You want to use the Outbound Database feature in the Planning system

The purpose of the following statements is to grant proper permission to the service identity account so that the application, staging database or outbound database can be created by the service identity account within the Application Database server. This extra manual step is included to make your Planning system more secure. If you want, you can revoke this dbcreator role from the service identity account after the application, staging database or outbound database is created.

use [master] 
GO 
EXEC master..sp_addsrvrolemember 
      @loginame = N'Domain\ServiceIdentityAccount', 
      @rolename = N'dbcreator' 
GO

Note

See the PerformancePoint Server 2007 Operations Guide article named Maintaining Planning Server databases for details about Planning Server Databases and their physical storage design guidelines. The "Planning Server Physical Database Storage Design" sectionof that article contains information about database data and log-file design, tempdb placement, and file-groups design. This physical database storage design information is important for the deployment process.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Downloadable content for PerformancePoint Monitoring Server.