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
Upgrading from MMC 3.4.1 (Only) for MS-SQL Server to the Latest Version of MMC for MS-SQL Server
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.
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.
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
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_TABLEfield (located in the only row of
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:
Locate the statement
MMC_PERSISTENCYwith 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
OPENJPA_SEQUENCE_TABLE is the same as
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
Help us improve with your feedback.