In conjunction with a new database schema which greatly reduced the storage requirements for events, ELM version 6.5 also introduced a sleek new database settings dialog to simplify setup and archiving. In this tech tip article well take a closer look at these settings and walk through the powerful new and easy to use features.
ELM requires two databases, a primary and a failover database, and optionally a third archive database. These databases can be in any combination of:
- Microsoft SQL 2008, Microsoft SQL 2008 Express, Microsoft SQL 2008 Express R2, Microsoft SQL 2008 R2 (the same instance or separate instances)
- Local to the ELM Server computer or on a computer available on the network
- Default instances or named instances
ELM will need write permissions so that it can create the databases. With a given instance and permissions, ELM will automatically create the database, tables, and indices for you as well as the constraints required.
To open the Database Settings, right click on the ELM Server computer name and select Database Settings from the menu.
Connections
The first tab on ELM Database Settings is where we configure the database server connections and authentication. The new version in ELM 6.5 combines all of the database fields onto one single dialog tab, simplifying creation and testing of your databases.
When entering the SQL Server name for the ELM databases, use the default (just the name of the SQL server as shown) or one of 3 possible alternate formats as described below.
1) For a default instance listening on a custom port use the format: servername,portnumber
2) For a named instance listening on default port 1433, use the format: servernameinstancename
3) For a named instance listening on a custom port use the format: servernameinstancename,portnumber
Note
This syntax for SQL Server name can be used for all 3 ELM databases: Primary, Failover, and the optional Archive database.
ELM can authenticate to the database using either Windows Authentication (recommended) or SQL Authentication. With either type of authentication, the ELM Server service will need DDL (Data Definition Language) permissions such as create databases, tables, and views, and DML (Data Manipulation Language) permissions such as select, insert and delete records. These permissions are inherited when the db_owner role is assigned to a user account in SQL Management Studio.
Retention Policy
The next tab we’ll look at is the Retention Policy where we’ll configure archiving and deleting dated or unneeded records (referred to as “pruning” in previous versions). In order to archive data, an archive database must be setup on the Connections tab shown previously.
The first piece we’ll go over is the Event Data Retain field. Here we’ll enter the amount of time to keep data in the ELM Primary database. Once this field is entered, the Archive All Events filter will appear below and be selected by default. We can then add, edit or delete event filters that our archiving strategy is based on. For more details on Event Filter Criteria, please refer to the Help File.
When archiving is enabled, records will be copied to the Archive Database and then ELM drops those partitions from the Primary database. If the check box next to “Archive events matching the below filters” is grayed out, this means that an archive database has not yet been configured on the Connections tab.
For licenses that support Performance and SNMP Data monitoring, you also have the option to archive both of these data types using similar settings to Event Data. To see which licenses in ELM support Performance and SNMP collection, please refer to the Feature Comparison Page.
The last option on this dialog is the Archive Now button. This will initiate the archiving process based on the settings on the retention policy tab. This process may be fairly resource intensive so be mindful when you launch it. (It is scheduled by default to run at midnight.)
Archive
The archive database is optional as mentioned, and can be used to reduce the size of the ELM primary database, improving responsiveness of the ELM console. There is a rollover option to provide generational archives based upon a default 1 Month time frame. So keeping 12 databases archived every 1 months provides one year of monthly archives. Rollovers can also be set by size, the default being 20GB.
By default, users have two choices as to when the Archive DB will rollover and create a new database: either once a month or once every 20 GB. It is possible however to change to different time periods or database sizes. Refer to the Help File on “how to edit the databaseSettings.xml file” for more details.
Once the archives are created, the ELM Console can be connected to these historical databases seen listed for ad hoc reports or forensic investigation. The Server can be a local or remote Microsoft SQL instance.
You can also see more details of each archive database available by adding fields to the window. To do this simply right click on one of the archive databases available to view the different information fields available to view.
Properties
The final tab of ELM Database Properties shows a quick overview of all of the settings covered in the previous steps. These can be copied and pasted which may come in handy for future technical support requests if needed.
Maintenance Microsoft SQL Job
The last thing we’d like to mention involves database maintenance. An optional database maintenance plan is now enabled by default for the ELM primary database to run at midnight every night. The plan runs in the ELM server process and will perform integrity checks on the database, rebuild indexes to optimize the database, and backup the database. These settings are also located in the Help File on databaseSettings.xml file.
We hope that you found this article on Mastering the Retention and Archive Database Settings in ELM informative and useful and wish you continued success with ELM.