Omada Identity Data Warehouse database maintenance
This section provides some general guidelines for maintenance of the Omada Identity Data Warehouse, but it should not replace a strategy developed for specific requirements and environments.
The following recommendations cover the Omada recommendations for a production environment. Test and development environments may have other maintenance requirements.
Database recovery options
When you define a backup strategy for the Omada database, there are many factors to consider. At the very least, you must decide how often your system should do database backups and whether to use the Full, Simple, or Bulk-logged recovery model.
Omada recommends that you use the bulk-logged recovery model for the Omada Identity Data Warehouse and Omada Identity Data Warehouse staging databases and the full-logged recovery model for the Omada Identity Data Warehouse Master database.
If you change the recovery models, Omada does not support any issues that could have been avoided by using the recommended models.
Defragmentation of indices
Database indices can become fragmented over time, which leads to lower performance. So, you should defragment database indices regularly to avoid such a situation.
Omada recommends that you defragment the ObjectID indices, for example, IX_Account_ObjectID, and that you defragment indices weekly.
You can use the standard report Index Physical Statistics in SQL Server Management Studio to discover the level of fragmentation.
There are two options for defragmenting indices in SQL Server: Reorganizing and Rebuilding.
Microsoft recommends that an index is reorganized when the fragmentation is between 5% and 30% and rebuilt when the fragmentation is higher than 30%.
For more information, see Microsoft documentation.
Improving performance
After the initial import
To improve the performance of ODW, you can perform two tasks. You should perform both tasks after the initial import and after you have imported a new source system for the first time. These two tasks are rebuilding indices and backing up the database to clear log space.
Rebuilding indices
Omada recommends that you rebuild all indices after the initial load of data. The initial load of data causes high levels of fragmentation. The system performs better for future imports with little or no fragmentation.
Backing up the database
Omada recommends that you make a backup after the initial import of data into ODW. A backup clears the transaction log. As the transaction log uses up to 80% of the disk space, clearing the log frees up a lot of space.
Cleaning up of history
To optimize disk space usage, you should clear the import logs, old backups, and another history in the database on a regular basis. This lowers disk space requirements, especially if you have set the databases to use the full recovery model.
Omada recommends that you keep the history for up to four weeks.
Scheduling common tasks
Maintenance plans allow you to schedule common tasks in SQL Server Management Studio. Omada recommends that you add maintenance plans for the following tasks:
- Database backups
- Defragmentation of indices
- Cleanup of history