Migrating repositories from ORACLe to SQL Server

[ASousa]

Hello,

One of our clients is currently doing an assessment for switching the storage medium of the repositories (they are using ORACLE and want to switch to SQL Server).

(I assume that this migration must have been done in at least in one of the many clients using MEGA).

They kept the current system running (using ORACLE) and created a completely separated environment (using SQL Server), in order to do a migration of the data and check if everything goes smoothly.

MEGA Corporate issued a temporary license to allow the usage of both repositories (the "old" license referring ORACLE and a "new" temporary license referring SQL Server and set to expire in a couple of months).

After the assessment they will decide if they move permanently to SQL Server or if they keep ORACLE.

The current state of the tests are:

1 - Current System using ORACLE is up and running.

2 - New System using SQL Server is also up and running.

3 - MUST licenses in both systems configured.

4 - First attempt at migrating data from the ORACLE repositories to SQL Server repositories.   --- FAILED

In the first attempt done at migrating this data, they took the easiest route and simply did an export and import of the objects they wanted to migrate.

This did not work very well, so the second attempt was to do a logical backup of the repository (from the Administration tool) and importing it to the destination repository.

That did not work well also.

After the import, the destination repository was full of errors and missing objects or object properties were blank (I attached printscreens with some of the errors shown).

The third attempt was to do the procedure described in the "Repository - RDBMS Installation Guide MEGA 2009 SP5" guide, specially the topic “Migrating Your Data from One Storage Support to Another”.

From what we could see in the manual, that seems to be the proper method for migrating data in our scenario (One storage medium to a different storage medium).

We are now doing that procedure, but some steps were not obvious to me.

First question was the one regarding our setup.

We have a server with an installation of MEGA, the respective MEGA environment and the repositories to migrate (the one using ORACLE).

We then have another server with another installation of MEGA, another MEGA environment (the ones that will use SQL Server).

Is this the ideal setup for doing the data migration we intend to?

If not, what would be the ideal setup?

Also, what would be the best way of creating the target repository?

Create the repository first, and then use it in the steps described at the RDBMS installation guide?

Have the migration create the target repository and after the migration add the repository to the target MEGA environment (using the "Create Reference" option in the Administration tool)?

Is this the proper way of doing this migration?

Are there any other procedures to be done, or another method for achieving the intended result?

Thanks

André

[jhorber · Accepted solution]

Hello ASousa

From what we could see in the manual, that seems to be the proper method for migrating data in our scenario (One storage medium to a different storage medium).

--> Yes. I confirm the feature recommended for migrating data is the feature 'Reorganize repository' described p 60 of this document (Migrating Your Data from One Storage Support to Another)

Is this the ideal setup for doing the data migration we intend to?

--> what is expected is that the machine running the migration process

- has MEGA installation with a MEGA licence programmed with both products ('MEGA Repository Storage (SQL Server)' AND 'MEGA Repository Storage (Oracle)')

MEGA Repository Storage (SQL Server)

- has a connection to running instance or Oracle (source database server)

- has a connection to running instance or SQL Server (target database server)

You can check both connections by defining a connection in MEGA

See page 15 (Defining an oracle Connection) of the above document

See page 31 (Defining a MEGA SQL Server Connection) of the above document

Verify that:

- SQL server is configured with SQL server authentication

- You have a SQL server user account with the expected privileges

See page 30 of the above document (User privileges)

Also, what would be the best way of creating the target repository?

--> You can either have MEGA create the SQL server repositories or create SQL server repositories manually from SQL Server and have MEGA them. It mainly depends on the privileges you can have.

The first option is easier (have MEGA create the SQL server repositories).

If you use the second option (create SQL server repositories manually from SQL Server and have MEGA use them):

- Follow the naming rule: <Environment folder name>_SystemDb

- Select the option 'Use an existing SQL Server database' at repository creation