Ensuring that your database operates at its peak performance is crucial for any application. Neglecting this can lead to issues such as timeouts and slow response times. The SurePassID Authentication service is no different. If you are using the SurePassID Authentication service in the cloud or the SurePassID PCO (Private Cloud Option), this maintenance is automatically taken care of for you. However, if you have an on-prem deployment of SurePassID, it is necessary to establish database operations that regularly maintain the SurePassID SQL Server database in order to keep the system running at its highest level of performance.
There are several factors that can contribute to a slow-running database, but one of the most common issues is index fragmentation. This occurs over time as data is added, updated, and removed from database tables. It's important to address this fragmentation in order to maintain optimal performance for your SurePassID system.
There is a wealth of information available on the topic of index fragmentation and best practices for improving database performance. You can explore numerous blogs and articles by conducting a search query. Additionally, Microsoft provides a helpful article titled "Optimize index maintenance to improve query performance and reduce resource consumption" that offers further insights. It's a great resource to expand your knowledge on this subject.
To determine if your indexes are fragmented, you can periodically run the index fragmentation script (DatabaseMaint_FragmentationQuery.sql). This script will provide you with information on the level of fragmentation in your indexes.
Fragmentation can be fixed in one of two ways:
- Re-organize the indexes (DatabaseMaint_ReorgAllIndexes.sql) - This method provides a fast solution to address some of the fragmentation and can be executed while the database is actively running. It is recommended to perform this task once a week during non-peak hours.
- Re-build the indexes (DatabaseMaint_ReorgAllIndexes.sql) - The most effective method to address fragmentation is by rebuilding all the indexes. However, it is important to note that the database will be temporarily unavailable during this process, so it should be scheduled during off-peak hours. It is recommended to perform this task once a month or once a quarter during off hours.
To prevent future fragmentation and improve the speed of index rebuilds and response time, it is recommended to remove old log data that is no longer relevant or needed. Determine your data retention policy, such as keeping data for the last 12 months, and regularly remove data older than the retention policy. This maintenance task can be performed once a month or once a quarter during off hours. SQL Server Agent is one of the many options you have to schedule weekly and monthly maintenance jobs.
The database maintenance scripts we provide are designed to serve as a foundation for creating a comprehensive set of maintenance tasks. We recommend that you collaborate with your database administrator to review all the referenced scripts in this article and customize them to meet the specific needs of your company. This ensures that the maintenance jobs align effectively with your database requirements and optimize the performance of your SurePassID system.
Before executing any new database scripts, it is highly recommended to create a duplicate of the production database and test your maintenance scripts on this duplicate database instead of the actual production environment. By doing so, you can ensure that any potential issues or undesired outcomes are identified and resolved before implementing the scripts on the live system.
Another important consideration is to have a reliable daily database backup, recovery, and disaster recovery process in place. It is crucial to verify the effectiveness of this process before you actually need to rely on it. Additionally, ensure that your database backups can be easily restored and that they are stored in an offsite or air-gapped location. This will provide an additional layer of protection against ransomware attacks and safeguard your valuable data.
The database scripts are located in SurePassID Authentication Service 23.1 application folder. If you are using a version prior to 23.1 contact support for a download link.