Keeping your Insight databases running smoothly is essential in order to keep your system responsive and reliable. Ultimately, it is the responsibility of the local database administrator to understand and implement the methods and techniques necessary to keep the database environment running optimally. Listed below, in no particular order, are some recommendations and additional links to technical information to this end:
- Update your SQL Server to the latest available service pack. However, it is a good practice to first deploy the update in an Insight test environment before applying it to a production environment.
- If possible, do not run any other databases or instances on your production SQL Server other than those included with the Insight system.
- Develop and implement a backup strategy for your SQL Server databases. Consider backing up your databases to local additional drives using the SQL BACKUP DATABASE command and then using your backup software to back up SQL back files. Keep the latest copy readily available on your hard drive. Restoring from a hard drive is much faster than restoring from a tape or other backup solution.
Suggested reading: Backup Overview (SQL Server) at https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-overview-sql-server?view=sql-server-ver16 - Pay attention to the tempdb. The SQL Server internally uses this system database, and it can require maintenance and attention. It can have a significant impact on performance if not managed properly. Suggested reading: Optimizing tempdb Performance at http://msdn2.microsoft.com/en-us/library/ms175527.aspx
- As data is added, updated, and removed from database tables, indexes can become fragmented and lead to degraded query performance if not monitored and rebuilt. Cyncly recommends that you monitor index fragmentation and performance in your Insight databases on a regular basis, and rebuild indexes as needed.
Suggested reading: Resolve index fragmentation by reorganizing or rebuilding indexes at http://msdn2.microsoft.com/en-us/library/ms189858.aspx - If left unchecked, the volume of transactions in your database can grow to the point where system performance begins to decline. Cyncly strongly recommends that you manage the volume of data in your Insight databases by performing a regular purging and, optionally, an archiving strategy. The Insight application comes with built-in functions that you can use to perform these tasks.
- Carefully consider the growth options for your Insight databases. Cyncly generally recommends that you use manual file growth options. In other words, establish and preset a size large enough for your anticipated database growth. If you allow SQL Server to automatically grow the database when needed, it can choose to do this at an inopportune time and cause a significant disruption in database availability for your users.
Suggested reading: Using Files and Filegroups to Manage Database Growth at http://msdn2.microsoft.com/en-us/library/ms190970.aspx
NEXT TOPIC: Managing Application Workstations