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

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.

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

Download this book