Best Practices for Upgrade MEGA with RDBMS

[SVanSchoonlandt]

Are there any best practices documented for upgrading MEGA while using RDBMS installation.

We are running on SP 4 CP10 and are migrating to SP5 R7. Data is stored in SQLServer.

My main concerns or questions are:

- Fallback scenario => if I read the documentation, it is possible that we need to perform a technical conversion of the SQLServer database, if this is the case it is not possible anymore to connect with the old client version to the database. So if we need to perform a fallback scenario which are the steps we need  to do for the SQLServer database then if a technical conversion of the database has been performed? Just back-up of old version and put that one back in place?

- With the file system of MEGA, it was rather easy to reapply the conversion on an environment by copying over the files again. However, how do we do this best under SQLServer?

Any other best practices or experiences, feel free to share.

Thanks

stijn

[jhorber]

Let me add this precision

Installing a pre-build systemdb without prior agreement with customer can lead to difficulties (with GBMS or RDBMS) regarding systemdb updates.

Systemdb updates are of course updates regarding elements stored in the systemdb (metamodel, queries, document templates, web site templates, users, profiles, descriptors, RFC, system diagrams...)

Let us imagine this case

On January 1: you perform a SQL Server backup of a systemdb on customer site

During January: you rebuild an environment, upgrade it and perform a SQL Server backup of the upgraded systemdb

On January 30: you restore the SQL Server backup of the upgraded systemdb on customer site

All the systemdb updates between January 1 and January 30 are lost because the snapshot was dated January 1

There should be an agreement with the customer that such systemdb updates would be lost. Customers should avoid creating such data or accepting the loss.

Such agreements enable to speed the migration processing on customer site and therefore reduce the service interruption.

This works quite well with MEGA 2009.

However note that more and more data are saved in the systemdb and it becomes difficult to skip such updates for a while. This will be even truer with future version such as MEGA 2013

[SVanSchoonlandt]

Jerome,

thanks this is exactly the information (and confirmation) of what I need.

Regards

stijn

[jhorber · Accepted solution]

Hello Stijn

If I understand, the subject is 'How to restore the initial situation if the upgrade goes wrong?'

• Restore previous version of programs
• Restore previous version of data

An subsidiary question is 'How to replace a systemdb?'

I imagine you have worked on a copy of the customer data in back office and want to install the premigrated systemd

Here are several dimensions

1) Restore previous version of programs

RDBMS format does not change anything. You can work as you did previously

2) Restore previous version of data

In SQL Server, each MEGA repository (systemdb, adventure...) is mapped to a SQL Server database

You need a backup (.BAK file) of each SQL Server database

a) You can restore it in the orignal  SQL Server database (SQL Server feature). Data are initialized

Note that certain files (.emv files, megaenv.ini files) is not consistent because contains references to the new version which was cancelled in the end. You can restore manually the previous version of such files.

b) You can also consider to restore the backup in a new  SQL Server database and then create a new environment in MEGA with the restore option (MEGA feature). Data are duplicated

In production installation, you need to delete the reference of the previous environment and create a reference for the new environment (MEGA feature).

You need to delete references to repositories using 'Remove not referenced repositories' the create them again

You need also to add additional files/folders (Mega_usr...)

More details about the option 'Restore'

http://community.mega.com/t5/custom/page/page-id/mega-kb-solution?sid=501D0000000zMH1IAM

More details about the menu 'Remove not referenced repositories'

http://community.mega.com/t5/custom/page/page-id/mega-kb-solution?sid=50120000000mvmrAAA

3) Replace a pre-build systemdb

As we said, in SQL Server, each MEGA repository (systemdb, adventure...) is mapped to a SQL Server database

You need a backup (.BAK file) of the SQL Server database of this pre-build systemdb

See 2)

I hope this helps

[SVanSchoonlandt]

Dear edarolle,

thanks for your reply.

I'm aware which steps I should follow during an upgrade, but my main concern is what do I need  to do when something goes wrong during the upgrade in eg Production.

We need to describe a fallback scenario, however I have no idea what the impact of running on SQLServer has on the fallback scenario. DBA ensured me that they do not only duplicate data but also the structure of the database, so I guess that will be sufficient to return to SP4 CP 10 in case SP5 migration would fail.

In the past we had to perform a rollback, so we like to be prepared.

Currenly we foresee the following little process:

1. Re-install MEGA SP4

2. Put back-up of SQLServer DB back in place (SP4 CP 10 format)

3. Log-in Admin tool, reference environments/repositories if necessary

4. back online in the old version

In the past, something else what we did to limit the work during the conversion is prepare a systemDb in the new format and copy thoses file over the systemDb of every environment, which prevented us of execution the conversion for every environment seperatly. Can this also be done by copying the systemDb table in SQLServer from a UAT machine over the production one.

Regards

stijn

[edarolle · Accepted solution]

Hello Stijn,

You are right, when using MEGA on a RDBMS (here SQL Server), a mirgation must be prepared to avoid any trouble with the data. 

A good way to secure this operation is to physically duplicate your environment in SQL Server.

(you could also save the .mgr of your repositories and extensions of the environment before proceeding in the very unlikely case that there is a data loss during the process)

Here are the steps to follow:

    1. Ask the DBA to duplicate all the databases of your environment

A MEGA database in SQL Server is named like this : <Environment name>_<Repository name>.

The duplication must be made indicating for every copy a name with the new environment name* : <Environment new name>_<Repository name>.

* (the reason for using a new name is that SQL Server does not allow many DB's with the same name). 

The SystemDb is the "root" repository of your environment, don't forget it.

    2. Create the references for the new environment and its repositories

For the following part, refer to the technical article « Repository - RDBMS Installation Guide MEGA 2009 SP5.pdf »

- Using the same MEGA installed for production ( SP4 in your case ), follow what is indicated in the paragraph : « Restoring a Mega repository from formatted data ». Start with the « Restoring an environment (SystemDb repository) » part (using a new name for the environment to be restored.

- When this is done, open Administration.exe then open the environment that you've just restored.

- Right-click on the just-restored environment and click on the menu entry « Remove not referenced Repositories » so that you will be able to re-reference the environments repositories by restoring them too.

- go then to the « Restoring a data repository » part and retsore every data repository of the environment following the paragraph's instructions. When this is over, you should have 2 identical environments referenced but with different names.

- Delete the reference of the environment that has just been restored.

- Open Administration.exe of the target MEGA version (SP5 R7 in your case).

- Create a reference to the environment that was restored in the SP4.

- Before you start the migration, you have to take care of the technical conversions of all the environment starting with the SystemDB (see paragrap « Handling of Mega RDBMS repositories specific internal format »). It meens that you will ignore all the messages inviting you to upgrade your environment before this step is complete.

- you can now follow the steps for upgrading the environment

edarolle