Rotating the active database
- It is more difficult to manage one large database than multiple small databases.
- Performance is better with multiple small databases.
- Backing up, restoring, archiving, and deleting data all take significantly more time if you work with one large database.
- Database operations take very little time when you work with multiple small databases.
For audit and monitoring service, you can implement a database rotation policy by having the collector write data to a new database after a certain period of time. For example, the collector in site A writes data to the database
siteA-2015-11 in November, then write data to database
siteA-2015-12 in December and to the database
siteA-2016-01 in January. By rotating from one active database to another, each database stays more compact and manageable.