Maintaining Planning Server databases

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.

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.

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.

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

-- 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

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.

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.

For more information about file groups, see SQL Server Books Online.