How to Migrate and Optimize the SQL Database on Your MU Server

Why Database Migration and Optimization Matter

A MU Online server's database is the backbone of every game action: character data, inventory, guild records, castle siege logs, event states, and account credentials all live inside SQL Server. Over time, as players connect, disconnect, trade, level up, and die, the database accumulates fragmented indexes, bloated transaction logs, outdated statistics, and rows left behind by incomplete operations.

Without periodic maintenance, query performance degrades. Login times increase, item storage operations slow down, and the server process itself can stall waiting on I/O. Migrating to a newer SQL Server instance or a fresh machine compounds the risk — an unoptimized or inconsistently migrated database will carry all its problems to the new environment.

This guide covers the full cycle: pre-migration preparation, a safe backup and restore migration procedure, post-migration validation, and an ongoing optimization routine you can schedule as SQL Server Agent jobs.

> [!WARNING] > All procedures in this guide require stopping MU Server services before execution. Never run migration scripts or index rebuilds against a database that is actively accepting game connections. Data corruption is possible if a migration overlaps with live write operations.

Pre-Migration Preparation

Before touching the database, gather information about the current state of the instance and establish a clean baseline.

Inventory the Databases

Standard MU emulator installations use between two and five databases. Common names include MuOnline, AccountDB, EventDB, GuildDB, and LogDB. Run the following on the source instance to list them with their sizes:

-- List all MU-related databases with size information
SELECT
    d.name                              AS DatabaseName,
    d.compatibility_level               AS CompatLevel,
    mf.size * 8 / 1024                  AS SizeMB,
    d.recovery_model_desc               AS RecoveryModel,
    d.state_desc                        AS State
FROM sys.databases d
JOIN sys.master_files mf
    ON d.database_id = mf.database_id
    AND mf.type = 0                     -- data files only
WHERE d.name NOT IN ('master','model','msdb','tempdb')
ORDER BY SizeMB DESC;

-- Check index fragmentation on MuOnline before migration
USE MuOnline;
SELECT
    OBJECT_NAME(ips.object_id)          AS TableName,
    i.name                              AS IndexName,
    ips.avg_fragmentation_in_percent    AS FragPct,
    ips.page_count                      AS Pages
FROM sys.dm_db_index_physical_stats(
        DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i
    ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
  AND ips.page_count > 100
ORDER BY FragPct DESC;

Note any tables with fragmentation above 30% — those are the highest priority targets for an index rebuild immediately after migration.

Shrink the Transaction Log

Before taking the backup, switch to SIMPLE recovery (if not already set) and shrink the log file to avoid carrying gigabytes of unused log space to the destination:

-- Run for each MU database that uses FULL recovery model
USE MuOnline;
ALTER DATABASE MuOnline SET RECOVERY SIMPLE;
DBCC SHRINKFILE (MuOnline_log, 64);   -- shrink log to 64 MB
ALTER DATABASE MuOnline SET RECOVERY FULL;
-- Repeat for AccountDB, EventDB, etc.

> [!TIP] > Switching to SIMPLE recovery for the shrink and then back to FULL is a safe maintenance pattern. If you do not need point-in-time restore capability (most private servers do not), leaving the databases in SIMPLE recovery model permanently reduces log growth and simplifies maintenance.

Taking a Full Backup

Use BACKUP DATABASE with COMPRESSION and CHECKSUM to produce verified, smaller backup files. Compression is available from SQL Server 2008 R2 Standard and above.

-- Full compressed backup → local path before migration
BACKUP DATABASE MuOnline
TO DISK = 'D:\Backups\MuOnline_premig.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 10,
    DESCRIPTION = 'Pre-migration full backup 2026-07-04';

-- Verify the backup immediately after
RESTORE VERIFYONLY
FROM DISK = 'D:\Backups\MuOnline_premig.bak'
WITH CHECKSUM;
-- Expected output → "The backup set on file 1 is valid."

Repeat the BACKUP DATABASE command for every MU-related database. After verification, copy all .bak files to the destination server before proceeding.

Restoring on the Destination Instance

On the new SQL Server instance, restore each database with MOVE clauses to place data and log files in the correct directories for the destination server layout:

-- Restore MuOnline on the destination instance
RESTORE DATABASE MuOnline
FROM DISK = 'D:\Backups\MuOnline_premig.bak'
WITH
    MOVE 'MuOnline'      TO 'E:\SQLData\MuOnline.mdf',
    MOVE 'MuOnline_log'  TO 'E:\SQLLog\MuOnline_log.ldf',
    REPLACE,
    STATS = 5;
-- → Restore completed for database 'MuOnline'.

After restoring all databases, set the compatibility level to match the destination SQL Server version:

-- SQL Server 2019 → compatibility level 150
ALTER DATABASE MuOnline SET COMPATIBILITY_LEVEL = 150;
ALTER DATABASE AccountDB SET COMPATIBILITY_LEVEL = 150;
-- Repeat for remaining databases

Post-Migration Validation

Before starting MU Server services, confirm data integrity and connectivity.

Run DBCC CHECKDB against each restored database:

DBCC CHECKDB (MuOnline) WITH NO_INFOMSGS, ALL_ERRORMSGS;
-- Clean result → DBCC results for 'MuOnline'. ...0 errors.

Then verify that the SQL login used by the MU Server process exists on the destination instance and is mapped to the correct databases:

-- Create login if it does not exist on destination
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'muserver_user')
BEGIN
    CREATE LOGIN muserver_user WITH PASSWORD = 'YourStrongPassword!';
END

-- Map login to each database
USE MuOnline;
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'muserver_user')
    CREATE USER muserver_user FOR LOGIN muserver_user;
EXEC sp_addrolemember 'db_owner', 'muserver_user';
-- Repeat for AccountDB, EventDB, GuildDB, LogDB

Update the connection strings in your MU Server configuration files (typically DBConnect.ini, ConnectDB.ini, or equivalent) to point to the new server instance name or IP address.

Index Rebuilding and Statistics Update

With the migration complete, rebuild all fragmented indexes and update statistics before the first player connects:

-- Rebuild all indexes on MuOnline
USE MuOnline;
EXEC sp_MSforeachtable
    'ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF)';

-- Update all statistics
EXEC sp_updatestats;

A fill factor of 80 leaves 20% free space on index pages, reducing page splits during INSERT-heavy operations like item drop logging and guild activity tracking.

Scheduling Ongoing Maintenance

One-time optimization is not enough. Create SQL Server Agent jobs to automate the maintenance cycle:

-- Weekly index maintenance job step (run Sunday 03:00)
-- Reorganize indexes with fragmentation 10-30%, rebuild above 30%
USE MuOnline;
DECLARE @TableName NVARCHAR(256);
DECLARE @IndexName NVARCHAR(256);
DECLARE @FragPct   FLOAT;

DECLARE idx_cursor CURSOR FOR
    SELECT
        OBJECT_NAME(ips.object_id),
        i.name,
        ips.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN sys.indexes i
        ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.page_count > 100
      AND ips.avg_fragmentation_in_percent > 10;

OPEN idx_cursor;
FETCH NEXT FROM idx_cursor INTO @TableName, @IndexName, @FragPct;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @FragPct > 30
        EXEC('ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD');
    ELSE
        EXEC('ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE');
    FETCH NEXT FROM idx_cursor INTO @TableName, @IndexName, @FragPct;
END
CLOSE idx_cursor;
DEALLOCATE idx_cursor;

Schedule this script as a SQL Server Agent job step and configure it to run weekly. Add a second job for nightly BACKUP DATABASE operations to ensure daily recovery points are available.

Final Checklist Before Going Live

Before opening the server to players after the migration:

• Confirm DBCC CHECKDB returned zero errors on all databases.
• Verify the MU Server process can connect to the new instance using the configured credentials.
• Confirm character, account, and guild data is intact by spot-checking records directly in SQL Server Management Studio.
• Test login, character selection, and in-game item operations with a test account before announcing the maintenance window is over.
• Update any monitoring scripts, backup paths, or external tools that referenced the old server instance name.

A properly migrated and optimized database will handle player load more efficiently, reduce the frequency of deadlock events, and give you a clean foundation for future server updates.

Perguntas frequentes