Understanding the Foundation: BMC Remedy AR System and its Architecture

Before we dive into performance tuning, it’s essential to understand what BMC Remedy AR System is and how it’s structured. The term “Remedy” and “Action Request System” are often used interchangeably. The reality is that the product was initially developed by Remedy Corporation and later acquired by BMC Software, leading to its current name: BMC Remedy Action Request System. This historical context is useful to grasp the evolution of the platform.

The AR System architecture is fundamentally a multi-tiered system designed for scalability and modularity:

• Client Tier: This is where users and administrators interact with the system. It includes applications like BMC Remedy User, BMC Remedy Developer Studio, and web browsers accessing the Mid Tier.
• Mid Tier: This acts as a bridge, running on a web server. It translates client requests into a format the AR System server understands, handles web service requests, and serves application content to web-based clients.
• Server Tier: The heart of the AR System. The AR System server manages workflow processes, controls access to data, and orchestrates the execution of server-side applications like the Approval Server, Email Engine, and Flashboards server. It also hosts various plug-in servers (Java, C).
• Data Tier: This comprises the database servers where all application data is stored and retrieved. The database server is the ultimate engine for data persistence and access.

The default administrator user for the AR System database is ARAdmin with a default password of AR#Admin#, and the database is typically named Arsystem.

Database Configurations and Performance Considerations

The choice of operating system and database plays a significant role in overall AR System performance. While BMC supports a variety of configurations, some are more prevalent and have demonstrated better performance characteristics for larger deployments.

Generally, the recommendation is to align with your organization’s existing IT infrastructure expertise. If you’re a “Windows shop,” sticking with Windows and SQL Server makes administrative sense. Conversely, a “UNIX shop” might lean towards Solaris or HP-UX with Oracle.

Based on support call volume and industry trends, common and popular configurations include:

• Windows / SQL Server (popular for mid-to-smaller companies)
• Windows / Oracle
• Solaris / Oracle
• HP-UX / Oracle
• AIX / DB2
• AIX / Oracle
• Red Hat / Oracle (increasingly popular for Linux-based deployments)

Larger enterprises often favor UNIX-based operating systems due to their robust performance and scalability features, while smaller to medium-sized businesses tend to find Windows/SQL Server a cost-effective and manageable solution.

Licensing and Login Behavior

Understanding AR System licensing is crucial for managing concurrent users. With 5 fixed and 5 floating licenses, an unlimited number of users can *log in*. However, only a maximum of 10 users can be actively using licensed features simultaneously. Subsequent users will have to wait for a license to become available. Floating licenses are indeed more costly and are often used to optimize resource utilization during peak operational hours or shift changes, making them a reliable choice for dynamic user needs.

Database Structure and Key Tables

During an AR System installation, several key tables are created in a specific sequence to establish the system’s foundational structure:

1. control
2. controlRecordIds
3. arschema (stores schema/form definitions)
4. schema_index (manages indexes for performance)
5. schema_group_ids

For workflow objects, the database tables are structured as follows:

• Active Link: dbo.actlink (followed by the action name, e.g., dbo.actlink_)
• Filter: dbo.filter (followed by the action name, e.g., dbo.filter_)
• Escalation: dbo.escalation

Core Fields: AR System reserves specific database IDs for core functionality:

• Request ID: 1
• Submitter: 2
• Create Date: 3
• Assigned To: 4
• Last Modified By: 5
• Modified Date: 6
• Status: 7
• SD (Short Description?): 8
• Status History (Hidden): 15

The AR System stores date/time values as the integer number of seconds since 00:00:00 GMT. This format supports dates from January 1, 1970, through January 18, 2038 (Unix time limitation).

Workflow Objects and Their Database Impact

Workflows are the automation engines within AR System, triggered by user actions or system events. Understanding their database implications is vital for performance tuning.

Active Links

Active links execute based on client-side operations or information displayed on the current screen. They are triggered by user interactions within the application, such as clicking a button or changing a field value. A key characteristic is that they cannot be directly invoked via API programs. If an active link with the same name already exists, AR System appends _c to the new name. Saving a form does not automatically copy its associated active links; however, renaming a form can inherit its active links if they are linked to the form’s identity.

Filters

Filters are workflow objects that execute on the AR System server in response to form transactions (submit, modify, delete). They are crucial for enforcing business logic, data validation, and data manipulation. Filters can check form transactions as they undergo server processing.

Escalations

Escalations are similar to filters but are triggered at predetermined time intervals. They are used for tasks like sending overdue notifications, performing regular data clean-ups, or automating recurring processes based on data within the database.

These workflow objects, while powerful, can become performance bottlenecks if not designed efficiently. Excessive or poorly optimized workflows can lead to increased database load, slower transaction times, and higher CPU usage on the AR System server.

Data Types and Storage: Character vs. Diary Fields

The choice of data types for fields can subtly impact database performance and data integrity.

Character Fields

Character fields are designed to hold alphanumeric data. They can be overwritten, meaning that if you modify the data in a character field, the previous value is lost. These fields can have menus attached to them, providing users with selection options.

Diary Fields

Diary fields offer a more robust way to store historical text data. Unlike character fields, diary fields append new entries, typically prefixed with a timestamp and the user who made the entry. This ensures that all historical values are maintained and auditable. However, it’s important to note that web reports do not support diary fields, attachment fields, or attachment pools. While currency values are not directly supported in web reports, currency fields are.

The performance difference lies in how data is written. Overwriting a character field is a direct update. Appending to a diary field involves an insert operation, which can be slightly more resource-intensive over time, especially with very large amounts of data being appended frequently. For reporting, character fields are generally more straightforward.

In the database, diary field entries are stored in a format like: [timestamp in seconds -| User -| Data], with subsequent modifications appending to this string.

Attachments and Their Management

Attachments are a common feature in AR System, allowing users to associate files with records. Understanding how they are stored is key to managing database size and performance.

Attachment Pool: This is a data type for fields that store binary data. An attachment field has the data type attachment. The maximum size can be configured.

When attachments are used, AR System typically creates two related tables:

• A binary table (often a `BSchema_id` related table) where attachments are stored. This table might contain columns like C (file path), CO (original file size), and CC (compressed file size) in bytes.
• A table like Battachpoolid that links request IDs to the binary data.

Managing attachment storage is critical. Regularly cleaning up old or unnecessary attachments can significantly reduce database size and improve query performance. Techniques like defining retention policies and using archiving tools are essential.

Menus and Their Database Storage

Menus in AR System provide predefined lists of options for users. They can be static (hardcoded values) or dynamic (fetched from data sources).

Menu Types:

• Static: Character, Form Data Dictionary, Field Data Dictionary.
• Dynamic: Search, SQL.
• File menus can be of both types.

When a menu is associated with a field, deleting the field or form does not automatically delete the menu. The menu definitions are stored in tables like dbo.char_menu (which holds menu name, resolved name, menu ID, timestamp, owner, last changed, refresh rate, and menu type) and dbo.field_enum.

Refresh Rate:

For dynamic menus, the refresh rate dictates how frequently the menu’s contents are updated:

• On Connect: Retrieves the menu when the user opens the form. Requires form re-opening for updates.
• On Open: Retrieves the menu every time it’s opened. Can impact performance if used excessively.
• On 15 Minute Interval: Balances currency with performance. Retrieves on first open and then every 15 minutes. (Behaves as ‘On Open’ in browsers).

Static menus (character menus) are not refreshed.

Overlay Groups and Granular Overlays

BMC Remedy AR System emphasizes customization while promoting best practices to ensure smooth upgrades. Overlay groups are central to this strategy.

Overlay Group Setting:

• Set to 1: Users are restricted to working on overlay and custom mode objects. In Developer Studio, this means operating in “Best Practice Customization” mode.
• Set to 0: Users work on base mode objects, adhering to out-of-the-box definitions. In Developer Studio, this means “Base Developer” mode.
• Cleared: Provides unrestricted access to base, overlay, or custom objects.

Granular Overlays: This advanced feature allows for more precise customization by enabling you to apply different overlay types to subcomponents of an object (like a form). This minimizes the need for manual reconciliation during upgrades.

• Additive Overlay (Extensions Overlay): Adds customized information to the origin object without replacing it. Additions are appended when the origin object changes.
• Overwrite Overlay: Replaces the entire origin object with the customized version. Useful for scenarios like removing permissions. This is the default behavior for non-granular overlays.
• No Overlay (Inheritance Overlay): The default for most object parts. It inherits properties directly from the origin object without any changes. This is crucial for maintaining out-of-the-box functionality.

Access Control: Groups and Permissions

Managing who can access what data is fundamental for security and operational control.

Types of Groups:

• Explicit Groups: Users are manually assigned to these groups. Access is granted based on the group’s permissions to objects and fields. Explicit groups are server-specific. Examples: admin, sub-admin, customize.
• Implicit Groups: Users belong to these groups based on specific conditions or circumstances, not direct assignment. Dynamic groups are a type of implicit group. Examples: Arsystem, public, assignee, submitter.

Group ID Ranges:

Group IDs are integers that uniquely identify groups. Specific ranges are allocated for different purposes:

• 0-1000: AR System groups and current AR System applications.
• 1000-13004 and 13007-14999: Regular and Computed groups (explicit).
• 13005-13006: CMDB groups.
• 14999-59999: Future AR System applications.
• 60000-60999: Dynamic groups (implicit).

Group Types (Permission):

• View
• Change
• None

Assigning Permissions: To grant common permissions to multiple forms, you can search for the relevant group, right-click, select “Assign permissions,” and then add the desired forms.

Performance Tuning Strategies and Troubleshooting

Optimizing database performance in AR System involves a multi-pronged approach, focusing on efficient querying, indexing, workflow design, and resource management.

Indexing:

Proper indexing on frequently queried fields is paramount. The schema_index table plays a crucial role here. Over-indexing can be as detrimental as under-indexing, so judiciously index fields used in search criteria and workflow conditions.

Workflow Optimization:

• Minimize workflow complexity: Break down complex workflows into smaller, manageable units.
• Avoid unnecessary database calls: Use server-side filters judiciously.
• Efficient querying: Ensure that active links and filters use optimized queries, especially when interacting with multiple forms or large datasets.
• Limit the scope of filters: Make sure filters only fire when absolutely necessary, by carefully defining their conditions.

Database Maintenance:

Regular database maintenance tasks are essential:

• Statistics updates: Ensure database statistics are up-to-date for the query optimizer.
• Index rebuilding/reorganizing: Maintain the health of database indexes.
• Purging old data: Implement archiving strategies for historical data that is no longer actively needed.

Garbage Collection:

The Xincgc (incremental garbage collector) setting can influence AR System server performance by managing memory efficiently.

Configuration Files:

The ar.cfg (on Windows) file contains AR System server configuration parameters. Changes made here are dynamically loaded. Be cautious when modifying this file, as incorrect settings can impact server stability.

Troubleshooting Common Issues:

• Slow Login/Form Load: Often caused by inefficient workflows, problematic database queries, or network latency. Check AR System logs and database performance monitors.
• High CPU Usage: Can be attributed to poorly optimized escalations, filters, or excessive concurrent activity.
• “ARERR [559] Character string exceeds maximum size allowed”: This error occurs when data exceeds the defined limit for a character field. Consider using diary fields or increasing the field size.
• Attachment Upload/Download Issues: May relate to file size limits, disk space on the server, or network bandwidth.

Interview Relevance

Conclusion

Database performance in BMC Remedy Action Request System is a continuous effort that requires a deep understanding of the platform’s architecture, workflow design principles, and underlying database technologies. By diligently applying best practices in indexing, workflow optimization, data management, and regular maintenance, organizations can ensure that their AR System deployments remain responsive, scalable, and capable of delivering the exceptional service management experience users expect.

For further in-depth information, refer to the official BMC documentation:

• BMC Remedy AR System Documentation
• BMC Helix Documentation (for newer cloud-native versions)