PeopleStage SQL maintenance

This guide explains how to set up a maintenance plan for the SQL Server database used by Apteco PeopleStage. The maintenance plan ensures the database functions properly on an ongoing basis, including rebuilding indexes and updating internal statistics. It excludes backup and archiving steps, which are covered in the recommendations for backing up PeopleStage.

Apteco advises having a backup plan for the PeopleStage, Web Service (control), and Response databases, as these hold important information about your PeopleStage system's structure and campaign history.

Background and database layout¶

The following diagram illustrates the components of Apteco PeopleStage.

User machines running the FastStats PeopleStage client communicate with the FastStats Web Service over a network. The Web Service places jobs from the client into a control database. The Service processes the jobs and returns results to the clients.

The FastStats Service connects to the FastStats Database and the SQL Server PeopleStage, smoke test, and control databases to process PeopleStage jobs. It also uses a response database for gathering email broadcaster responses.

The PeopleStage database has three main sections:

• Model—contains information about the PeopleStage diagram shown to the user
• Schema—contains information about all live PeopleStage campaigns in a format usable by the FastStats Service
• History—records the flow of people through the diagram and the communication history for each person in every campaign

The smoke test database is similar in structure to the PeopleStage database, but is used specifically for test runs of campaigns. The control database includes tables related to the general running of FastStats, such as Users and Sessions, as well as tables used by the Workflow Agent and Delivery Agent threads within the FastStats Service. The response database stores responses collected from email broadcasters.

Creating a maintenance plan¶

Prerequisites¶

Make sure the SQL Server Agent service is running:

1. Open Computer Management from Administrative Tools.
2. Check that the SQL Server Agent service is running and set to start automatically with a delay.

Creating the plan¶

1. Open SQL Server Management Studio.
2. Connect to the server that has the PeopleStage databases.

3. Open the Management folder, right-click on Maintenance Plans, and select Maintenance Plan Wizard.

   

4. Read the first page of the wizard, then click Next.

5. Enter a name for the maintenance plan (e.g. PeopleStage Maintenance Plan) and an optional description. Keep the default option Single schedule for the entire plan or no schedule and click Change... to set up a schedule.

   

6. Choose a time for the plan to run—pick a time when the server is not busy. The maintenance plan should take 10–30 minutes depending on the size of the databases. Running once a week is recommended. Give the schedule a name and click OK.

   

7. Click Next to reach the task selection page. Select the Rebuild Index task and click Next.

   

8. On the ordering page, accept the default option and click Next.

   On the rebuild index task page, select the databases where you want to rebuild indexes—these should be the PeopleStage, Web Service, Response, and Smoke Test databases (named PS_, WS_, RS_, and ST_ by default). Change the Free space option to keep 30% of each index page free. This ensures the database has enough space to insert new data into indexes without causing fragmentation between rebuilds.

   Note

   Some clustered index tables will be taken offline while being rebuilt, unless you select Keep index online while reindexing. This option may not be available in all editions of SQL Server.

9. Click Next.

   

10. On the report page, keep the log file as its default value and click Next.

11. The final page summarises your choices. Click Finish to apply the maintenance plan, then close the progress window. The plan now appears in the Maintenance Plans node in SQL Server Management Studio.

You can also expand this maintenance plan to include backup tasks. For more detailed recommendations, see Recommendations for backing up PeopleStage.

Recommendations for backing up PeopleStage¶

Without a backup in place, all PeopleStage data could be lost in the event of a hardware or database software failure. This data includes your PeopleStage system's structure as well as communication and response history for your campaigns.

Discuss the PeopleStage backup plan with your Database Administrator (DBA). Key considerations:

• By default, the PeopleStage database operates in Full recovery mode, which allows restoration to any point in time but means the transaction log grows with all data changes. To manage the size of the transaction log, it should be regularly backed up. As a starting point, consider:

  • Full database backup every 24 hours
  • Transaction log (.ldf file) backup every 4 hours, kept for 24 hours

  Note that taking transaction log backups during normal operational hours should not cause performance issues. In some cases, Microsoft and others recommend backups every 10 minutes or more frequently.

• The same considerations apply to other FastStats databases: the Web Service (control), smoke test, and Response databases. Note that the smoke test database does not contain live data, but holds information on test campaigns.

If you use the Backup options in the SQL Server maintenance plan, you can also use the Maintenance Cleanup Task to delete old backup files, such as transaction log backups older than 24 hours.