When it comes to the high volume of event data prevalent in most environments, it is important to follow the guidelines for best practices with database setup in order to optimize performance.
ELM utilizes Microsoft SQL Server 2008 (or newer) to store event, log, and performance data efficiently and securely for both reporting and archiving purposes. The Primary Database is used by the ELM Server for storing data received from your monitored systems. The ELM Server also has a built-in database protection mechanism to prevent data loss in the event the Primary Database or connectivity is unavailable; the Failover Database. Once the ELM Server has reconfirmed the availability of the Primary Database, the temporary storage of data in the Failover database is merged for a seamless transition.
During an evaluation period of any software, it’s common to use a workstation or older hardware to see if the application will fit the need. Such is the case with ELM as we’ve learned from customers. During an evaluation period of ELM it is perfectly acceptable to have the SQL databases reside on the same hardware as the ELM installation to ease the setup and evaluation process.
However, once ELM is purchased and deployed in a production environment it is important to revisit the database configuration for optimized performance. The preferred configuration is to have the Primary Database reside on a separate server. It is acceptable to keep the Failover database on the same hardware as the ELM server.
If resources are available, further improvements can be recognized by locating the Failover Database on a separate server as well.
If multiple licenses of SQL Server 2008+ are not realistic for your operation, a free SQL Express edition may be a viable option for the Failover Database. SQL Server Express 2008 R2 now offers up to 10GB of storage per database. (Click here for more details on Microsoft SQL Server Express.)
Why do we recommend all the databases and different hardware? Good question – the driving force behind these recommendations has to do with disk contention and I/O due to the large amounts of event data we are talking about with real-time event log collection. In a busy environment that could be upwards of millions of events per day. Even the newest hardware will be taxed keeping up with this kind of activity. To optimize performance we’ve developed the best practices approach(es) above as well as the details listed below.
Additional SQL Server Database Best Practices for avoiding disk I/O bottlenecks:
- Separate the Operating System from the database files and the log files.
- Separate the database files from the log files.
- For database files, performance increases with more spindles included in your RAID configuration.
- Use SATA with TCQ support or SCSI Drives, the faster the RPM the better.
- For better recoverability, use a SCSI interface instead of SATA and IDE.
- For large bandwidth demands on the I/O bus, use a different bus for the transaction log files.
- (If there is a DBA on staff, defer to the DBA.)
ELM also includes an automatic database maintenance routine. Each night it will perform integrity checks on the database, backup the transaction log, rebuild indexes to optimize the database, and backup the database. With ELM version 6.5, security improvements allow EM to schedule and run the maintenance plan without the need for the SQL Agent. (However, if you have other database maintenance strategies or policies in place, the ELM database maintenance plan can be disabled.)
ELM Version 6.5 also introduced a new procedure for deleting old data. With the new database structure it will delete data table-by-table, vs. row-by-row, which is much faster and more efficient.
If you have any questions about the best approach for your individual database management strategy in conjunction with ELM, don’t hesitate to contact our technical support department.
We hope that you found this article on Best Practices With ELM Databases – Evaluation, Testing and Production informative and useful and wish you continued success with ELM.