Depending on your organization, you may have a database administrator (DBA) who is already comfortable with maintaining SQL databases. This section outlines recommendations for maintaining the Project Server 2003 databases in SQL Server 2000 and is meant to be more of an overview and a checklist than a detailed how-to. Typical DBA tasks still apply.
Database Lists and Functions
A typical Project Server 2003 implementation includes three main databases. Table 25.2 lists these databases and their functions.
In addition to these databases, it is important to include your msdb and master databases in any backup strategy you implement. Without these two databases, it can be more difficult to recover from a complete system failure.
The majority of the defaults in SQL Server 2000 are sufficient for Project Server 2003. One important property to determine early is the recovery model. SQL Server 2000 provides three recovery models, shown in Figure 25.4, located in database properties under the Options tab: Simple, Bulk-Logged, and Full. Only the Simple and Full recovery modes are recommended for Project Server 2003.
Figure 25.4. The Options tab under database properties allows you to set the database recovery mode.
Simple Recovery Considerations
The Simple Recovery model is recommended for small implementations where database size and backup capabilities are a concern. Using the Simple Recovery model results in simpler backup and restore procedures. The main disadvantage is that you cannot restore to a specific moment in time but only to the time of the last backup. Organizations that choose this model typically back up nightly and can tolerate some data loss. In this model, when a restore is necessary, data entered since the last backup is lost.
Full Recovery Considerations
The Full Recovery method is recommended for larger organizations that can facilitate real-time backups throughout the day. The main advantage to this model is the capability to restore to a specific moment in time, which provides the capability to recover right up to the point of failure. The biggest disadvantages are the large transaction logs generated and the extra resources required to store, maintain, and confirm valid backups throughout the day. This method requires more thoughtful backup and recovery planning, or you will not have what you need, when you need it.
Database Maintenance Plans
SQL Server 2000 provides a wizard for creating maintenance plans. You should create at least two maintenance plans in Enterprise Manager to help maintain the Project Server 2003 databases. The first one should include at least the master and msdb databases and should contain an integrity check and backup schedule at the very least. The second should include the three main databases needed by Project Server, listed in Table 25.2, and contain an optimization schedule, integrity check, and backup schedule at a minimum. The following is a list of suggested settings for the Project Server maintenance plan:
There usually is no need to frequently Reorganize Data and Index Pages or Update the Statistics Used by the Query Optimizer for the Project Server databases unless you start noticing a decrease in performance. Figure 25.5 illustrates the options available under the Optimization tab.
Figure 25.5. Under the Optimizations tab, an important option is Remove Unused Space from Database Files.
You should Remove Unused Space from the Database Files on a regular basis. Although these databases are dynamic in design and will grow periodically to accommodate requests, they will not shrink unless you tell them to. Shrinking the database files allows you to free up resources for other databases to use.
The optimization portion of this maintenance plan does not need to run often. Depending on the size of your organization and the physical hardware of the server, running this job once a week or even once a month is sufficient. Optimizations can be resource intensive, so it is recommended that you run them sparingly and at low load times on the server.
Figure 25.6 shows suggested settings on the Integrity tab. Integrity checks should be run fairly frequently; typically once a week is sufficient. Checking the box to allow SQL to Attempt to Repair Any Minor Problems is recommended. "Minor problems" are not data errors, and this option will allow SQL to maintain the pages where the data is stored preventing fragmentation or other problems.
Figure 25.6. It is important to check the SQL logs after running an integrity check to identify potential database corruption.
The Complete Backup Tab
The Complete Backup tab, shown in Figure 25.7, allows you to schedule and verify database backups. It is recommended that you schedule a complete backup at least once a day during off-peak hours. Running a backup during peak hours can impact performance significantly. If you choose to run your Project Server databases in simple mode, this is the single most important backup you will make. Back up to tape or disk, but be aware that if you back up to disk you need to offload the backups to a different server or risk losing your backups as well as your live databases to a hardware or software failure.
Figure 25.7. Set your backup schedule to match your backup recovery mode with this tab.
Backup files can be similar in size to the actual database size. If you have physical hard disk limitations, be careful about keeping archived backups. Each time a complete backup is run, the job creates a new backup file appended with the date and time. This could cause insufficient disk space problems resulting in failed backups and production SQL errors. Storing backups off the production server is highly recommended.
Transaction Log Backup
Figure 25.8 shows suggested settings on the Transaction Log Backup tab. This backup is necessary only if you choose the Full database recovery option. In Full mode, backups become very large, dramatically increasing the physical hard disk requirements of the server.
Figure 25.8. The Transaction Log Backup tab is unnecessary if the database is set up to use the simple recovery mode.
Ensuring That the Maintenance Plan Worked
You can use the Maintenance Plan Wizard to create reports and record job status to a text file in the SQL Logs directory. Additionally, you can have the report emailed to an operator or sent to a remote server. Figure 25.9 illustrates some of the settings available.
Figure 25.9. The reports generated by this part of the maintenance plan should be reviewed regularly.
Maintenance plans are only a part of a total backup solution for the Project Server databases. They are meant as an aid for automating day-to-day maintenance activities, allowing the DBA to focus efforts on other business needs. Maintenance plans alone do not constitute a backup solution.