How to Set Up a Reward and Online-Time System on Your MU Server

Overview and Goals

A reward system tied to online time is one of the most effective tools a MU Online server administrator can deploy to improve player retention and daily engagement. Instead of relying solely on events or manual GM giveaways, an automated system tracks how long each account remains connected and delivers incremental prizes — Zen, items, or custom tokens — at defined milestones.

This guide walks through the full implementation: database schema design, stored procedure logic, scheduling the reward job, and connecting the output to the in-game economy. All examples use Microsoft SQL Server, which is the standard backend for Season 6 and Season 9 MU Online server software.

Database Schema for Time Tracking

Before writing any logic, you need two supporting tables. The first records raw session events; the second stores the aggregated totals used for reward calculations.

-- Session event log
-- → Captures login and logout timestamps per account
CREATE TABLE AccountTimeLog (
    LogID        INT IDENTITY(1,1) PRIMARY KEY,
    AccountID    VARCHAR(10)  NOT NULL,  -- → references MEMB_INFO.memb___id
    CharName     VARCHAR(10)  NOT NULL,
    EventType    CHAR(1)      NOT NULL,  -- → 'L' = login, 'O' = logout
    EventTime    DATETIME     NOT NULL DEFAULT GETDATE(),
    ServerCode   TINYINT      NOT NULL DEFAULT 0
);

-- Reward accumulation table
-- → One row per account, updated by the reward job
CREATE TABLE AccountRewards (
    AccountID       VARCHAR(10)  NOT NULL PRIMARY KEY,
    TotalMinutes    INT          NOT NULL DEFAULT 0,
    LastRewardTier  INT          NOT NULL DEFAULT 0,  -- → milestone index already claimed
    PendingZen      BIGINT       NOT NULL DEFAULT 0,
    PendingTokens   INT          NOT NULL DEFAULT 0,
    LastUpdated     DATETIME     NOT NULL DEFAULT GETDATE()
);

The EventType column uses single-character codes to keep the log compact. Your server's login/logout hooks write to AccountTimeLog; the scheduled job reads from it and updates AccountRewards.

> [!TIP] > Add an index on AccountTimeLog (AccountID, EventTime) immediately. Without it, the aggregation query will perform a full table scan every time the job runs, and performance will degrade rapidly as the log grows.

Calculating Accumulated Online Time

The core aggregation logic pairs login events with the nearest subsequent logout event for the same account. This approach is robust against crashes: if no logout exists, the calculation falls back to the current timestamp as the session end.

-- Stored procedure: calculate and credit online minutes
-- → Run on a schedule (every 10 minutes recommended)
CREATE PROCEDURE sp_UpdateOnlineTime
AS
BEGIN
    SET NOCOUNT ON;

    -- Step 1 → Compute session durations from the log
    ;WITH SessionPairs AS (
        SELECT
            l.AccountID,
            l.EventTime AS LoginTime,
            ISNULL(
                (SELECT MIN(o.EventTime)
                 FROM AccountTimeLog o
                 WHERE o.AccountID  = l.AccountID
                   AND o.EventType  = 'O'
                   AND o.EventTime  > l.EventTime),
                GETDATE()           -- → treat still-online as session in progress
            ) AS LogoutTime
        FROM AccountTimeLog l
        WHERE l.EventType = 'L'
    ),
    SessionMinutes AS (
        SELECT
            AccountID,
            SUM(DATEDIFF(MINUTE, LoginTime, LogoutTime)) AS EarnedMinutes
        FROM SessionPairs
        GROUP BY AccountID
    )

    -- Step 2 → Upsert into AccountRewards
    MERGE AccountRewards AS target
    USING SessionMinutes   AS source ON target.AccountID = source.AccountID
    WHEN MATCHED THEN
        UPDATE SET
            TotalMinutes = source.EarnedMinutes,
            LastUpdated  = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (AccountID, TotalMinutes, LastUpdated)
        VALUES (source.AccountID, source.EarnedMinutes, GETDATE());
END;

Note that this procedure recalculates the full lifetime total from the log each time. For servers with high player counts, you may prefer an incremental approach: record the last processed LogID and only aggregate new rows since the previous run.

Defining Reward Tiers and Delivering Prizes

With accumulated minutes stored in AccountRewards, a second stored procedure compares each account's total against a tier table and delivers prizes for newly crossed thresholds.

-- Reward tier reference (adjust values for your server economy)
-- → TierID | MinutesRequired | ZenReward | TokenReward | Description
-- →      1 |             60  |    500000 |           1 | 1 Hour Online
-- →      2 |            300  |   2000000 |           5 | 5 Hours Online
-- →      3 |            720  |   5000000 |          12 | 12 Hours Online
-- →      4 |           1440  |  10000000 |          25 | 24 Hours Online

CREATE TABLE RewardTiers (
    TierID          INT    PRIMARY KEY,
    MinutesRequired INT    NOT NULL,
    ZenReward       BIGINT NOT NULL DEFAULT 0,
    TokenReward     INT    NOT NULL DEFAULT 0,
    Description     VARCHAR(50)
);

-- Stored procedure: grant rewards for newly reached tiers
CREATE PROCEDURE sp_GrantOnlineRewards
AS
BEGIN
    SET NOCOUNT ON;

    -- Find accounts that have crossed new tier thresholds
    UPDATE ar
    SET
        PendingZen    = ar.PendingZen    + rt.ZenReward,
        PendingTokens = ar.PendingTokens + rt.TokenReward,
        LastRewardTier = rt.TierID
    FROM AccountRewards ar
    INNER JOIN RewardTiers rt
        ON rt.TierID = ar.LastRewardTier + 1   -- → next unclaimed tier
       AND ar.TotalMinutes >= rt.MinutesRequired;
END;

The pending columns act as a queue. Your game server reads PendingZen and PendingTokens at login or via a polling loop, credits the character, then resets those columns to zero. This decouples the database job from the live game process and prevents double-delivery.

> [!WARNING] > Never write Zen or items directly to character columns from an external job while the game server is running. The game engine holds in-memory state for online characters and will overwrite your changes on the next save cycle. Always use a pending/queue pattern and let the game process consume rewards safely.

Scheduling the Job with SQL Server Agent

Open SQL Server Management Studio, expand the SQL Server Agent node, and create a new job with two steps:

• Step 1 — Execute sp_UpdateOnlineTime
• Step 2 — Execute sp_GrantOnlineRewards

Set the schedule to repeat every 10 minutes, starting at server startup time. Name the job something descriptive such as MU_OnlineRewardJob so it is easy to identify in the job list.

If you prefer a script-based approach for repeatability:

-- Schedule the job via T-SQL
EXEC msdb.dbo.sp_add_job
    @job_name = N'MU_OnlineRewardJob';

EXEC msdb.dbo.sp_add_jobstep
    @job_name   = N'MU_OnlineRewardJob',
    @step_name  = N'UpdateOnlineTime',
    @command    = N'EXEC MuOnline.dbo.sp_UpdateOnlineTime';

EXEC msdb.dbo.sp_add_jobstep
    @job_name   = N'MU_OnlineRewardJob',
    @step_name  = N'GrantRewards',
    @command    = N'EXEC MuOnline.dbo.sp_GrantOnlineRewards';

EXEC msdb.dbo.sp_add_schedule
    @schedule_name      = N'Every10Minutes',
    @freq_type          = 4,   -- → daily
    @freq_interval      = 1,
    @freq_subday_type   = 4,   -- → minutes
    @freq_subday_interval = 10;

EXEC msdb.dbo.sp_attach_schedule
    @job_name      = N'MU_OnlineRewardJob',
    @schedule_name = N'Every10Minutes';

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'MU_OnlineRewardJob';

Maintenance and Monitoring

Once the system is live, routine maintenance keeps it accurate and performant.

Log archival. The AccountTimeLog table will grow continuously. Add a monthly cleanup job that moves rows older than 90 days to an archive table or deletes them outright if historical reporting is not required.

Tier adjustments. Reward tiers live in RewardTiers, so you can adjust Zen and token values, or add new milestones, without touching the stored procedures. Changes take effect on the next job execution.

Player-facing display. Consider exposing accumulated minutes and the next reward threshold through your server website or an in-game NPC using a read-only query against AccountRewards. Transparency increases motivation and reduces support tickets asking "when do I get my reward?"

> [!TIP] > Log each reward delivery to a separate audit table with a timestamp and the tier granted. This gives you a clear history for resolving player disputes and helps you identify whether tier thresholds are correctly calibrated for your server's average session length.

With these components in place — schema, calculation procedure, reward delivery procedure, and a scheduled job — your server has a fully automated, database-driven online-time reward system that runs without manual intervention and scales cleanly as your player base grows.

Perguntas frequentes