Maintaining the Database

This topic outlines several recommendations regarding database administration, maintenance, and backup. A comprehensive approach to database maintenance is imperative for mission-critical business applications in production environments. The suggestions offered in this topic are general guidelines that are intended to provide a brief overview of SQL Server administration tasks.

Aptify does not recommend the use of integrated SQL Back Up agents that are provided with certain tape backup packages. The SQL Back Up agents do not generally support image or text data types. Aptify recommends using the built-in Microsoft SQL Server utilities to create disk backups that are later backed up to tape.

When creating backup jobs, the partition occupied by the operating system, and any other partitions containing a page file, should not be used as a backup location. This is related to the unknown size of the database backup. Not following this general rule can result in extreme system slow-down or operating system crashes.

Following the suggestions in this topc should be viewed as a good first step, but the Aptify system administrator must also have a thorough understanding of SQL Server administrative utilities. There are a number of technical publications that provide many more details on SQL Server maintenance, including SQL Server Books Online. The reader is encouraged to consult these publications while planning overall database maintenance and backup strategy for a production system. This chapter is not intended to serve as a replacement for a good general knowledge of SQL Server and associated Microsoft products and technologies.

This topic contains the following sub-topics:

• Recommendations for Backup and Restoration
• Backup Strategy for the APTIFY Database
• Creating a SQL Database Maintenance Plan

Recommendations for Backup and Restoration

This topic is an overview of system backup and restoration recommendations for the following elements of an Aptify environment:

• Aptify Database Layer
• SQL Server System-Level Databases
• Aptify Business Layer
• Aptify Presentation Layer
• IIS Configuration Files

Aptify Database Layer

On the Aptify Database Layer, the SQL Server database installed and used by the Aptify application is the APTIFY database. This is a single database that is added to Microsoft SQL Server during installation that stores all of the relevant Aptify objects. In addition, if the e-Business module is licensed and implemented, the SITEFINITY database will be in use. Likewise, if the Aptify Learning Management System (LMS) add-on application is licensed and implemented the AptifyLMS database will be in use.

Backup and restoration plans should be developed that protect the content and availability of these databases. SQL Server has various recovery models with the two common models being Simple and Full. The Aptify applications do not require a specific recovery model, but the recovery model should be well thought out and selected based on the business needs for data protection and availability. If the Full recovery model is used, a robust restoration plan can be developed that uses a database and transaction log backups that allow recoverability to point in time or point of failure with minimal or no data loss. A source for SQL Server information is the Microsoft Books Online documents, such as Backing Up and Restoring Databases in SQL Server  (http://msdn.microsoft.com/en-us/library/ms187048(v=SQL.100).aspx).

SQL Server System-Level Databases

The SQL Server system-level databases, master, model, and msdb are not specific to Aptify but are essential for the operation of a server instance. It is recommended that the content and purposes of the system databases be reviewed, and database backup and restoration plans developed for the system databases. See the Microsoft Books Online Document Back Up and Restore of System Databases (SQL Server) (http://msdn.microsoft.com/en-us/library/ms190190.aspx).

It is also recommended that the SQL Server Service Master Key be backed up as part of a complete SQL Server backup and restoration plan. The Service Master Key is the key at the root of all encryptions and needs to be protected. See the Microsoft Books Online document BACKUP SERVICE MASTER KEY (Transact-SQL) (http://msdn.microsoft.com/en-us/library/ms190337.aspx).

Aptify Business Layer

On the Aptify Business Logic Layer, the Application Server's typical install location is C:\Program Files\ [Your AptifyVersion] (for example, C:\Program Files\Aptify 6.0). There should be no need to backup files in this directory.  Changing objects should be in the database object repository and maintained by the Object Repository sync. 

Aptify Presentation Layer

On the Aptify Presentation Layer, if the e-Business or Aptify LMS applications are implemented, the directories here these Web Services are installed should be backed up. For e-Business, an example location might be C:\Program Files (x86)\telerik\Sitefinity 7. For LMS an example location might be C:\inetpub\wwwroot\courses.

IIS Configuration Files

Although not specific to Aptify, a good practice on the Web server would also include the backup of IIS Configuration files, see How To Back Up and Restore IIS (http://support.microsoft.com/kb/302573) and Back up System State Data (http://technet.microsoft.com/en-us/library/cc781353(WS.10).aspx).

Backup Strategy for the APTIFY Database

Following the recommendation of a daily full database dump, with hourly transaction log dumps, ensures that your system can always be recovered to the state of the last transaction log dump and that data loss is minimized. The following strategy is recommended:

• Database Backup: Daily from live database to hard drive (database dumps). Daily tape backups of database dumps are recommended.
• Dumping Transaction Logs.
  • Hourly: Append to existing log.

  • Nightly: Overwrite the existing log (use the INIT option if using T-SQL to define backup operation).

    Failure to run transaction log dumps during the day means that a hard crash during the day may result in the loss of ALL data since the last full backup.

Backup Strategy for the System Database

Aptify recommends that the system databases (Master, MSDB and Model) be backed up at the same time as the APTIFY Database.

Backup Tape Rotation Recommendations

Aptify recommends following a system using 4 generations of backup tapes: Daughter A, Mother B, Grandmother C, Great-Grandmother D. 
The sample schedule below shows a pattern to be repeated through the year.:

Week	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
1	A	A	A	A	B	C	-
2	A	A	A	A	B	-	-
3	A	A	A	A	B	-	-
4	A	A	A	A	B	-	-
5	A	A	A	A	B	C	-
9	A	A	A	A	B	C	-
52	A	A	A	A	B	C	D

Level D of the recommended backup schedule should be done once yearly.

To ensure tape quality for daily backups, replacing tapes on a periodic basis is recommended. The specifications for the tape media in use should state the recommended interval for tape replacement.

It is very important to validate tape backups on a periodic basis. These tapes are to be used to restore the full system to another server. If the full restore does not function properly in a test environment, the tapes cannot be counted on to provide full recovery from a catastrophic event. Aptify recommends a full tape verification on a periodic basis. The most conservative approach is to test each tape backup. However, most organizations do not want to devote the time needed to test the backup media each day. It is usually considered conservative to test the tape media each week by restoring the data to a test server.

Off-Site Storage of Backup Files

The greater the diversity of storage location for backup files, the less the risk of data loss.

Aptify recommends that all backup files (daily and otherwise) be kept off-site. Either automatically copy them to geographically diverse areas of our corporate network, or better yet, employ a secure Cloud backup service. Although inconvenient, the seriousness of data loss makes this step worthwhile.

Validating Backup Tapes When Updating the Test Server

If the Test Server is restored from the Production backup tape on a periodic basis, the integrity of the backup tapes may be monitored.

Before restoring data from the Production to the Test Server, be sure to back up any ongoing work on the Test Server and save it to another location to avoid the loss of test configurations or data.

Creating a SQL Database Maintenance Plan

This topic describes how to create a new database maintenance plan using the Microsoft SQL Server's Database Maintenance Wizard.

In general, you need to create a maintenance plan for each set of tasks that you want to perform as a group. For example, you need one maintenance plan for hourly transaction log backups, a second plan for the nightly transaction log backups, and a third plan for each recurring full database backup for the APTIFY database. (Note that you may also want to create a separate maintenance plan for backing up the system databases.) Here is an overview of the maintenance plans that Aptify recommends you create to handle database tasks:

• Maintenance plans to automatically perform a full backup of the APTIFY and system databases on the schedule described in Backup Strategy for the APTIFY Database.
• You can create separate backup plans for the APTIFY databases and the system databases or you can backup all of these databases in one plan.
• Maintenance plan to back up the APTIFY database's transaction log hourly (using the append to existing media option).
• Maintenance plan to back up the APTIFY database's transaction log nightly (using the INIT or overwrite existing media option).
• Maintenance plan(s) to perform general maintenance tasks on a regular basis (such as every week), including:
  • Check Database Integrity
  • Shrink Database

  • Reorganize Index

    The frequency of these tasks depends on the requirements of your organization.

Within Microsoft SQL Server Management Studio, you can create maintenance plans manually or you can use the Maintenance Plan Wizard. When you create a new maintenance plan, SQL Server automatically creates a corresponding Job for the SQL Server Agent.

Follow these steps to the use the wizard to create a new maintenance plan:

1. Open Microsoft SQL Server Management Studio.
   
   • If prompted, log in to your server using a system administrator account. 
2. Add your database server to the Object Explorer using the Connect menu option, if necessary.
   
   • If prompted, log in to your server using a system administrator account.
3. Expand your server's management tree.
4. Right-click the Maintenance Plans node and select Maintenance Plan Wizard from the pop-up menu. 
   
   
   
   • The Database Maintenance Plan Wizard begins with an introductory screen, explaining how the wizard helps create tasks to:
     
     • run database integrity checks
     • update database statistics
     • perform index maintenance
     • perform database back ups 
5. Click Next to continue.
6. Enter a Name and Description for your maintenance task.  
   
    
7. Specify the schedule options.
   
   • Identify if you want to define a single schedule for the entire plan or a separate schedule for each task in the plan.
   • Click the Change button to open the New Job Schedule dialog in order to specify when this plan should execute automatically.  
     
     
8. Check one or more maintenance tasks to execute in this plan.
   
   • The following figure illustrates a plan that will only have one task (Back Up Database (Full)). 
     
     
9. Click Next to continue.
10. If the plan will execute multiple tasks, specify the order in which you want to run these tasks (or accept the default order). Click Next to continue.
    
    • The following figure illustrates a plan with three tasks. If your plan has multiple tasks, you can specify the order in which the tasks are executed. 
      
      
11. Configure the options for each of the tasks you selected. When finished, click Next to continue to the next screen. (Each task in the plan has its own options screen.)
    
    • Specify the database or databases on which the task will be performed.
    • Specify task-specific options (if in doubt, use the default setting).
    • For back up tasks, configure the following options:
      
      • Select the APTIFY database. You can also specify the system databases (master, model, and msdb) to back up all four databases at the same time in one task.
      • Specify the location for backup files. As mentioned earlier in this appendix, the partition occupied by the operating system or any partitions containing a page file should not be used as a backup location. If you specify a network location, the SQL Server Agent that will execute this task must have read/write access to that network path.
      • Select the Verify Backup Integrity option. 
    • The following figure illustrates the Options screen for the Back Up Database (Full) task. 
      
      
12. Specify reporting options (such as the location for the storage of the reports generated by the maintenance plan and a list of email recipients for the report) and click Next.
13. Click Finish to complete the wizard and create the maintenance plan.  
    
     
14. Click Close when finished.
    
    • The SQL Server Agent will execute the specified tasks at the scheduled date and time.

Note that after you have created a maintenance plan using the wizard, you can edit the plan as needed by selecting Modify from the plan's right-click menu under the Maintenance Plan heading in the SQL Server Management Studio's Object Explorer.

To manually execute a maintenance plan, select the Execute option.