Proper DB management ensures the availability, integrity, and confidentiality of your information, as well as ensuring the quality of stored data and improving the performance of your back-office systems. Therefore, we want to share some tips regarding good practices to follow for the administration of your DB, keep in mind that the suggested practices do not focus on any Database technology (Oracle, SQL server, or other), but on the management executed to ensure the performance, reliability, and security of your database.
Configurations:
It is important that you perform proper management in the administration of the resources assigned to your database system. For this reason, we offer some configuration actions that you must keep in mind to ensure optimal performance of your database:
- Distribute the data files on multiple disks, this will help you get better response times.
- Constantly monitor resource usage such as CPU, RAM, Network, and Storage to avoid database service unavailability.
- Prevent 100% use of CPU and RAM resources on your database server.
- Make sure that the number of data files in your TempDB database is equal to the number of processors your DB server owns.
- Avoid sharing credentials on your database system and set only the permissions required to execute your activities.
Maintenance:
Remember to perform periodic tasks that guarantee the security and performance of your database, below, are the most relevant ones:
- Validate your database backups, to achieve db service continuity in case of failures.
- Run backup restore tests to estimate system downtime if a restore should need to be performed.
- Periodically update your database statistics, and run index defragmentation tasks, to ensure proper execution plans on your database management system.
- Constantly track the size of your data files, to decrease the likelihood of incidents on the DB once the storage limit is reached.
- Be sure to perform periodic integrity checks on your database.
Tuning:
Improve your database response times by:
- Consider partitioning and/or compressing tables more transactionally, to reduce DB system response times and make better use of resources.
- Try to migrate information that is infrequently accessed to historical systems.
- Set up automatic maintenance plans that perform the administrative tasks that do not cause unavailability in the DB.
- Periodically debug the tables that store log information.
Finally, note that it is essential to have a competent DBA who has good management foundation and is willing to promote good practices in managing your database.
