Repositive Recovery Model

The Microsoft SQL Server TechNet glossary describes a repository as “a database containing information models that, in conjunction with the executable software, manage the database.”

A database repository can be thought of as a higher-level organizational structure designed to manage the interactions between several other databases that need to work together. A large bank, for example, would likely have a repository database to manage the production databases associated with the traditional bank account division, the loans division, the foreign exchange division, the investment banking division and possibly others.

A database depository also needs to be included as part of a disaster recovery plan. You can set your repository recovery method to simple, full or bulk-logged. A repository recovery model  is typically set based on the model system database on the host SQL Server instance.

It might be necessary to change the repository recovery model in the following situations:

1. When you move SQL Compliance Manager into a production environment and therefore have to implement a full recovery model;

2. When you no longer need to back up transaction logs for the Repository database and can therefore get by with a simple recovery model.

Idera’s SQL Compliance Manager makes it easy to change the repository recovery model. All you have to do is click Auditing on the menu bar, and then select Configure Repository Databases, specify the appropriate recovery model and then click OK.

For more on using repository databases and configuring disaster recovery models, see Microsoft SQL Server Books Online.