ELM Database Guidelines

When installing the ELM Server, you must choose an existing SQL Server on which to store the collected data. The data structure, tables, and indices will be created automatically.

Choose one of the following approaches to estimate how large your primary database will be after you start monitoring Agents and collecting event data:

Approach #1

Create a test environment with one ELM Server and one or more Agents that are typical of your enterprise.

Configure the ELM Server to collect the event data and/or performance data and reports per your requirements.

Use the ELGEN.exe utility distributed with ELM to generate the typical number of events each day.

Examine the database size every day in order to determine its size and calculate the growth over the previous day. This will give you a reasonable idea of how much data the database will be required to store per server and aid you in making decisions about how large the database server must be.

Approach #2

Use the following formula to estimate how large your primary database will be after you start monitoring Agents and collecting event data:

Number_of_Agents * Number_of_Days_Events_Retained * Number_of_Events_per_Day(per Agent) * 515_bytes = DB_Size

Add 10% to the DB_Size value to account for database overhead.

If you are not sure how many events per day will be generated, use the local Event Viewer application to estimate the number. The event viewer displays the number of events currently in the log. Examine the date/time on the first event and the last event in the log to calculate the number of days recorded in the event log. Calculate the number of events per day. This will give you the average number of events per day. Perform the same operation on a sample of servers in your network to determine the average for your enterprise.

The formula above does not include calculations for other data ELM will collect, such as performance data or Alerts. It is difficult to determine the amount of space consumed by these items.

Sizing the ELM Server Database Hardware

Now that you know how large your database will be, the next step is to verify sufficient resources to run the database engine. Many hardware manufacturers include tools that can configure the appropriate hardware specifications for a server based on your answers to a few questions. HP/Compaq, Dell, and IBM have such tools. Below is an example of how to size a SQL Server using a tool from Dell called PowerMatch for SQL Server 2000.

Example Scenario

No. Agents	No. Consoles	No. Scheduled Reports/day	Events/Day	Event Retention
50	2	5	500,000	30 days

To use PowerMatch for SQL Server 2000

1. Execute the PowerMatch for SQL Server 2000 shortcut, or launch PmSQL.exe. The Welcome screen will appear:

2. Click Next to continue. The information and disclaimer screen will appear:

3. Read this information and click Next to continue. The Application Categorization screen will appear:

4. Select Application based on both queries and transactions and click Next to continue. The number of users/transactions screen will appear:

5. The number of users is the combined number of Agents and ELM Consoles in use. The ELM Server is considered a SQL Server user in this context. In this example, there is one ELM Server, 50 Agents and 2 ELM Consoles, for a total of 53 users. The number of transactions per day is equal to the number of events generated each day. In the example, there are 500,000 events collected each day. Therefore, 500 is entered as the average number of transactions. For the number of queries each day, double number of scheduled reports. The value should be doubled to account for Alert and Event View refreshes, which perform database queries. If are no scheduled reports, the number of queries is the number of manual reports and manual View refreshes you expect to do each day. Enter the appropriate values and click Next to continue. The load and user interactions screen will appear:

6. Given the nature of ELMs functionality, most organizations run ELM around the clock, with Agents on and reporting all the time. Consequently, with the exception of event storms and other anomalous activity, the ELM load on SQL Server should remain consistent. Therefore, enter 24 hours for the peak load and 100% for the user interactions value.

7. Click Next to continue. The database size screen appears.

8. Using the database sizing formula above, enter the expected size of the database. In the example scenario, the database size after 30 days will be nearly 400GB. Enter the estimated size for your SQL Server database and click Next to continue. The growth screen will appear:

9. Enter the amount of growth you expect during the next year. Adding Agents, additional Consoles, additional scheduled reports, etc., are examples of growth. Click Next to continue. The advanced features screen will appear:

10. If you are clustering your ELM Servers database, or using SQL Servers Log Shipping feature for increased availability, check the appropriate checkbox(es). Click Next to continue. The backup and restore screen will appear:

12. Select an optional backup/restore solution and click Next to continue. The recommendation screen will appear:

This guide illustrates only one way to size your ELM database server. Sizing an ELM Server and sizing its database server are two separate tasks when planning your ELM deployment. The above example centered on event data only, and did not include Alerts, performance data, or heavy reporting. In general, event data is more voluminous than performance data.

If you have any questions or comments about this guide, or if you would like assistance sizing your ELM Server or designing your ELM-based solution, please contact TNT Software's Product Support Group.