Maintaining Planning Server databases
Updated: 2009-04-30
In this article:
Background of Planning Server databases
Application databases in Planning Server
Staging databases in Planning Server
Outbound databases in Planning Server
Analysis Services databases in Planning Server
Planning Server physical database storage design
This article is intended for Planning Server database administrators. It outlines some database implementation areas that are specific to Microsoft Office PerformancePoint Server 2007. When you are preparing for your production system implementation, we recommend that database administrators read this document.
Background of Planning Server databases
Physical database storage design directly affects database performance. In general, Planning Server customers have a degree of flexibility when designing the physical storage attributes of the system databases. Presented here are design guidelines for maintaining Planning Server databases to achieve optimal server system performance.
Planning System Database and Planning Service Database
For each Planning Server installation, there is one Planning System Database (PPSPlanningSystem) and one Planning Service Database (PPSPlanningService).
The Planning System Database contains the following components:
Planning system security data
Planning type library data
Planning system-level configuration data
Planning application-level metadata
The size of both the Planning System Database and the Planning Service Database is small and will remain relatively small.
The Planning System Database and the Planning Service Database can be created either manually or while running Planning Server Configuration Manager.
If you choose to have Planning Server Configuration Manager create these two databases for you, both databases will be placed in the primary file group with the default data file size set to 50 MB, to autogrow by 50 MB. The default log file size will be set to 20 MB, to autogrow by 20 MB.
If you choose to create these two databases manually, you can choose the file group and change the default settings of the initial database and log file size.
Application databases in Planning Server
A Planning system can consist of multiple Planning applications. There is one Planning Application Database for each Planning application. This application database contains all Planning application data, which includes Planning application metadata, reference data, fact data, workflow-related data, and service-broker data. This database can grow quite large, depending on your data retention policy and the number of model sites and the models that are inside the Planning application.
The application database is created during the Create Application process. You can select either a manual or automatic method to create an application database.
From the Planning Administration Console, you can select the Manual Execution option from the Create Application user interface so the database administrators can customize CREATE DATABASE/CREATE TABLE in the application creation process. Specifically, database administrators can add file group information and specify the initial data file size and initial log file size when they create the application database. After Microsoft SQL Server 2005 scripts are generated in the Application Creation process, database administrators can edit CreateAppDB.sql and TypeLibMasterSchema.sql and add the file group information and data and log file sizes into these scripts before they execute them manually.
The other method is to select the Automatic Execution option from the Create Application user interface. The application database will be created for you with the default initial data file size set to 50 MB, to autogrow by 50 MB. The default log file size will be set to 20 MB, to autogrow by 20 MB.
Staging databases in Planning Server
There is one Planning Staging Database for each Planning application. This staging database either can be created during the Application Creation process or can be created manually at a later time. The staging database must be on the same database server as its counterpart application database for version 1 release.
Outbound databases in Planning Server
The Planning Outbound Database contains Planning Server data that is available for other purposes. You can use Planning Administration Console to create or register databases as a Data Destination.
Analysis Services databases in Planning Server
A model site for a Planning Server application always corresponds to an individual Microsoft SQL Server 2005 Analysis Services database. The Analysis Services database name is automatically generated by Planning Server. The default name is <application label>_<model site label>.
You can configure all model sites for your Planning application to point to the same Analysis Services server while each model site points to a different Analysis Services database. You can also set your configuration to have any of your model sites within your Planning application point to an Analysis Services database residing on a different Analysis Services server. You can manage these configurations by using Planning Administration Console and navigating to the Model Site Maintenance window. Enter the value in the Analysis Services server name field for each model site. Refer to Planning Administration Console Help for complete details.
Note
If you delete a model site or a subsite, you will need to manually delete the Analysis Services cubes.
Planning Server physical database storage design
Follow the database storage design topics in SQL Server Books Online when you design physical storage for Planning Server databases. The physical database storage design is crucial for the overall performance of the Planning Server system. Good physical database implementation will lead to better performance and better system health.
This section discusses the physical database storage design areas: database data file and log file placement, initial file size, properly configured log file for good performance, file group design, good TempDB design for the Planning Server system, and database recovery models. Many of these common design guidelines are covered in SQL Server Books Online.
Database data and log files
SQL Server 2005 maps a database over a set of operating system files. Data and log information are never mixed in the same file, and individual files are used only by one database. For more information database data files and log files, see SQL Server Books Online.
For all Planning Server databases that are automatically created by the Planning server, the default initial data file size is set to 50 MB, with autogrow by 50 MB.
For application databases and staging databases, we suggest that the customer’s Planning Server database administrators perform capacity planning and use the organization’s data and data retention policy to determine a reasonable size for the initial data file. For example, determine how many models they expect to have in each model site and how many model sites they expect to have in the application.
Some general guidelines when designing database data file and log file include the following:
Enable database data and log files to grow automatically.
Allocate reasonable initial sizes to database and log files.
Set maximum size for data files so that you do not run out of space when you have no disk space (especially important if you have multiple databases).
Set data file growth increment to reasonable sizes (preferences: fixed increment less than or equal to 1 GB, significantly helped by instant file initialization).
Consider allowing instant initialization for data files.
Consider RAID technology for data files and log files.
Allocate only one log file.
Isolate the log file to a separate drive (for better performance, you should have log files on a separate physical disk, rather than data files).
Monitoring the log file is also important. You can monitor log file status by running the following query:
select * from
sys.dm_os_performance_counters
where counter_name like '%Log%'
and instance_name = 'Alpine_Ski_House_AppDB'
For more information, see SQL Server Books Online.
Preallocate the log file size
To minimize the autogrowths on the log file, we recommend that you preallocate the log to an appropriate size. The size of the log file depends on two factors: the frequency of the log backup and the activity of the Planning Server system.
Although the general guideline is to preallocate a log file to 10 or 15 percent of the database file, the actual log file size depends on the frequency of log backup.
If you back up your log file every five minutes, and you have normal Planning activity, we recommend that you allocate your log file initial size as follows:
Planning System Database: 50 MB
Planning Service Database: 200 MB
Planning Application Database: 1 GB
Planning Staging Database: 1 GB
Planning Outbound Database: 400 MB
You can change these numbers, based on the frequency of your log backup. For example, if you back up your log every 10 minutes, you need to make the initial log file size bigger. If you back up your log file every two minutes, you can allocate a smaller log file size.
In addition to the proper initial log file size, we recommend that you set your log file to autogrowth with a fixed amount for autogrowth (not a percentage), and that you also set a maximum limit on how much the log can grow. (Do not set the size for unrestricted growth.)
Minimizing the Virtual Log File (VLF) is also important for SQL Server performance. For more information about performing this task, see SQL Server Books Online.
Back up the log file
It is important that you back up your log file regularly. In your production system, it is highly recommended that you schedule the computer that is running SQL Server to do log backups periodically (such as every 5 or 10 minutes) to avoid data loss. If your database recovery mode is Full and you do not back up your log for a long time, it will continue to grow until you get “log is full" error.
There is minimal overhead on the system when doing the log backup, so frequent backups are not costly. The more fragmented your log file is, the more costly your log backup will be. That is why it is important to preallocate a reasonably sized log file for your system; it will result in better performance for log backup.
When a log file is full, the only thing you can do is to back up the log. Backing up the log will clear the inactive log and the log file will shrink in size. Backing up the log will not clear the active logs because the transactions are not committed yet.
In a nonproduction environment where you do not concerned about data loss, you can truncate the log to clear it. However, you should only do this with the prototype, development, or testing systems when data loss is acceptable.
In either production or nonproduction systems, you must take care of the log file (either backup or truncation); otherwise, the log file will grow fast and will affect the performance of your Planning system.
Sample scripts
This section includes sample scripts to perform log backup or truncation. It is important that you schedule the computer that runs SQL Server to run the following script. If you are working in a test or prototype environment and you do not want to spend time handling this log backup or truncate issue, set the database recovery mode from the default Full mode to Simple mode by modifying the database property page in SQL Server Management Studio.
Important
Simple mode should never be used in the production system. For more information about database recovery models, see SQL Server Books Online.
-- Truncate Log sample script
-- Use only if you are in testing environment and do not care about DB backup.
BACKUP LOG 'Alpine_Ski_House_AppDB WITH NO_LOG
GO
BACKUP LOG 'Alpine_Ski_House_AppDB WITH TRUNCATE_ONLY
GO
USE 'Alpine_Ski_House_AppDB
GO
EXEC sp_helpfile
GO
-- get the log file name for this DB
-- now shrink the log file
USE 'Alpine_Ski_House_AppDB
GO
DBCC SHRINKFILE(Alpine_Ski_House_AppDB_log, TRUNCATEONLY)
GO
-- Backup log sample script
-- For any DB that you care about data loss, you should back up DB and the
-- log, that is the only good way to clear the inactive logs.
-- Create dump devices first
EXEC sp_addumpdevice 'disk', 'ServiceDBData',
'C:\work\ServiceDBData.bak';
GO
EXEC sp_addumpdevice 'disk', 'ServiceDBLog',
'C:\work\ServiceDBLog.bak';
GO
-- Back up database and log file
USE PPSPlanningService
GO
BACKUP DATABASE PPSPlanningService TO ServiceDBLog;
GO
BACKUP LOG PPSPlanningService TO ServiceDBLog
GO
DBCC SHRINKFILE(PPSPlanningService_log, TRUNCATEONLY)
GO
Important
You must either truncate the log in a nonproduction system or set up the computer that runs SQL Server to back up the log periodically to clear up the log file size for better performance and to prevent data loss. If you let your log files grow too large, the performance of Planning Server will suffer significantly. The growing log file will also eventually consume a tremendous amount of your disk space over the course of time.
TempDB
The size of TempDB can affect the performance of your system. For example, if the size defined for TempDB is too small, part of your system processing load might be taken up with autogrowing the database to the size that is required to support your workload every time you restart the SQL Server (MSSQLSERVER) service. You can avoid this overhead by increasing the size of TempDB.
General recommendations for the physical placement and database options set for TempDB include the following:
Permit TempDB to automatically expand as required.
Set the original size of TempDB files to a reasonable size to avoid automatically expanding the files as more space is required. If TempDB expands too frequently, performance can be affected.
Set the file growth increment percentage to a reasonable size to avoid the TempDB files from growing by too small a value. If the file growth is too small, compared to the amount of data being written to TempDB, the database might have to constantly expand. This will affect performance.
Put TempDB on a fast input/output subsystem to guarantee good performance. Stripe TempDB across multiple disks for better performance. Put TempDB on disks that are different from those used by user databases. For more information about how to move TempDB to a new location, see SQL Server Books Online.
When SQL Server is restarted, the TempDB size goes back to the initially configured size and it grows based on the requirements. This can lead to fragmentation of TempDB and can incur overhead. This can impact the performance of your workload. It is recommended that you preallocate TempDB to the appropriate size.
Because Planning Server databases use "read committed isolation" by using the row versioning feature, TempDB should be set to a reasonable large size for better performance. Set TempDB initial size to at least 500 MB for better performance. Set the initial size of TempDB to 1 GB for even better performance.
It is important to monitor the free space in TempDB. For more information, see SQL Server Books Online.
File groups
You should group database objects and files together in file groups for allocation and administration purposes.
The Planning System Database and the Planning Service Database can be created during Planning Server setup or they can be provisioned manually by customers before they install Planning Server software. If you let Planning Server Configuration Manager create these two databases for you, you will not have a chance to specify a file group for them. These two databases are relatively small in size, and the need to use a file group for them is minimal.
The Planning Application Database is created during the Create Application process. There are two options when creating application databases. Customers can specify the Manual Execution option from the Create Application user interface in Planning Administration Console so that database administrators can customize CREATE DATABASE/CREATE TABLE in the application creation process. Specifically, database administrators can add file group information when they create the application database. After SQL Server scripts are generated in the Application Creation process, database administrators can edit CreateAppDB.sql and TypeLibMasterSchema.sql and add the file group information into these scripts before they run them.
Note
You can create file groups from CREATE DATABASE or ALTER DATABASE. You can specify file group to tables from CREATE TABLE. When you create new file groups, make sure you add the files to your new file groups before the new file groups are used.
For more information about file groups, see SQL Server Books Online.
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 Planning Server.