Contact Us 1-800-596-4880
  1. Homepage
  2. Mule Management Console (3.7)
  3. Upgrading the Management Console

  4. Upgrading from MMC 3.4.1 for MS-SQL Server to the Latest MMC for MS-SQL Server

Upgrading from MMC 3.4.1 (Only) for MS-SQL Server to the Latest Version of MMC for MS-SQL Server

Overview

MMC version 3.4.1 for MS-SQL Server allows you to persist MMC data to MS SQL Server instead of on MMC’s default internal databases. Users of this version of MMC should upgrade to the latest version of MMC, which includes support for MS-SQL Server.

Upgrading to the latest version of MMC requires that you run a migration script on MS-SQL Server. Optionally, you can also run an additional script to drop indexes not used in MMC versions later than 3.4.1. This page describes the procedure for running both scripts.

Before running the latest version of MMC, you will also need to perform the steps described in Upgrading to MMC with an External Database. Additionally, if your current MMC installation uses LDAP for user authentication, you must complete the steps described in Upgrading to MMC with LDAP.

Downloading the Migration SQL Scripts

There are two migration SQL scripts for MS-SQL Server:

  • The migration script, sqlServerCustomersMigrate_341_to_342.sql, required for all installations

  • The index drop script, sqlServerCustomersOptionalIndexDrop_341_to_342.sql, which is optional

Download these from the support portal or see the scripts for copy-paste

USE [MMC_PERSISTENCY]
GO
CREATE TABLE [dbo].[OPENJPA_SEQUENCE_TABLE](
    [ID] [tinyint] NOT NULL,
    [SEQUENCE_VALUE] [bigint] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[OPENJPA_SEQUENCE_TABLE]
SELECT 0, SEQUENCE_VALUE FROM [dbo].[OPENJPA_SEQUENCES_TABLE] WHERE ID = 'EVENTS'
GO
/* VERIFY SEQUENCE_VALUE IN OPENJPA_SEQUENCE_TABLE IS THE SAME AS SEQUENCE_VALUE IN OPENJPA_SEQUENCES_VALUE */
/* THEN DROP OLD TABLE */
DROP TABLE [dbo].[OPENJPA_SEQUENCES_TABLE]
GO

See the optional drop indexes script for copy-paste

USE [MMC_PERSISTENCY]
DROP INDEX [EVENT_PROPERTIES].[e_properties_value]
GO
DROP INDEX [EVENTS].[e_flow_name]
GO
DROP INDEX [EVENTS].[e_message_id]
GO
DROP INDEX [EVENTS].[e_server_id]
GO
DROP INDEX [EVENTS].[e_server_name]
GO
DROP INDEX [EVENTS].[e_timestamp]
GO
DROP INDEX [EVENTS].[e_type]
GO
DROP INDEX [EVENTS].[events_name]
GO
DROP INDEX [TRANSACTION_SUMMARIES].[t_s_processing_time]
GO
DROP INDEX [TRANSACTION_SUMMARIES].[t_s_status_ordinal]
GO

Running the Scripts on MS-SQL Server

Before running the scripts, follow these steps:

  • Ensure that no instance of MMC is connected to the MS-SQL Server – in other words, make sure that you have stopped your MMC 3.4.1, and that your new MMC is not started, or not connected to the database on the MS-SQL Server

  • In the MMC tracking (Business Events) database, back up the current value of the SEQUENCE_TABLE field (located in the only row of OPENJPA_SEQUENCES_TABLE)

  • Both scripts use the database MMC_PERSISTENCY. If your database for MMC data has a different name, modify the name in the scripts by following these steps:

    1. Locate the statement USE [MMC_PERSISTENCY].

    2. Replace MMC_PERSISTENCY with the name of your database.

After completing the above steps, run the migration script sqlServerCustomersMigrate_341_to_342.sql up to the following statement:

SELECT 0, SEQUENCE_VALUE FROM [dbo].[OPENJPA_SEQUENCES_TABLE] WHERE ID = 'EVENTS'

After running the above statement, verify that SEQUENCE_VALUE in OPENJPA_SEQUENCE_TABLE is the same as SEQUENCE_VALUE in OPENJPA_SEQUENCES_TABLE. After this, run the rest of the script.

If you have run the new MMC connected to MS-SQL Server before running the migration script, and OPENJPA_SEQUENCE_TABLE already exists, you can run the migration script if you skip the table creation statements and run from the INSERT INTO statement.

See Also