Data migration to Outsystems cloud from a on-premise database

Data migration to Outsystems cloud from a on-premise database

  

Hello Everyone,

We have a customer who has OutSystems cloud setup - the database is MS SQL Server.

Now - they already have their database from their legacy application in the on-premises database.

This is a huge database (SQL Server) with about 350 tables. 

The below are the expectations:

  1. Copy the database schema as well as the data inside the tables to OutSystems cloud database from the legacy on-premises SQL Server.

I have thought some ways to achieve this:

1. We can create a database script - which will include both the DDL as well as the SQL statements for copying the data.

OR

2. We can create the same entities in the OutSystems cloud instance. Publish the entities. We will have to be careful so that these entities are exactly as same as that in database schema of the legacy application. Then we can create an SQL data import script to start the data import on the OutSystems cloud instance.


Problem with Approach 1:

If we go by this approach - the tables will be created outside of OutSystems. Hence they will not automatically appear as "Entities" in the Service Studio. Also, BPT events do not work such entities.


Problem with Approach 2:

This approach will allow me to use the entities in OutSystems as real, "native" entities. But - I will be required to create the 300 or so tables in OutSystems. I will have to create these from Service Studio. This seems like a lot of manual work. I will also have to ensure that all the referential relationships, the delete, ignore rules etc are followed while I am creating these entities.

After this - I can go on about copying the actual data using the scripts. But - I will have to make modifications - as the names given by OutSystems to the entities are autogenerated.

So - I see a problem with both the approaches. Please suggest a good approach for this very important scenario.


(To summarize - the problem is of migrating a on-premises database to OutSystems cloud whilst preserving all data integrity constraints and having the entities behave as "native" OutSystems entities.)

You can consume an external database by creating an extension or by setting it up on service center.

https://success.outsystems.com/Documentation/10/Extensibility_and_Integration/Integrate_with_an_External_Database

Ofcourse the Outsystems cloud environment needs to be able to access the target database/network, this might require some extra setup.

Joey Moree wrote:

You can consume an external database by creating an extension or by setting it up on service center.

https://success.outsystems.com/Documentation/10/Extensibility_and_Integration/Integrate_with_an_External_Database

Ofcourse the Outsystems cloud environment needs to be able to access the target database/network, this might require some extra setup.

Hello Joey,


Thanks for the reply. However-  consuming the external database does not solve the problem.

Consuming the external database would make those entities available in OS for sure. But - those entities would still be "external" and not really in the database of the Outsystems cloud.

We want those entities to be replicated in the OutSystems cloud.

Besides the entities which are created via external database do not work with BPT events.



Solution

Ahh I understand now, sorry!

Perhaps you can try Refactor?

https://www.outsystems.com/forge/component-discussions/496/Refactor

It lists functionality for creating Outsystems entities by Foreign entity

Support for converting a foreign entity (imported trough an extension) into a normal eSpace entity

Solution

Joey Moree wrote:

Ahh I understand now, sorry!

Perhaps you can try Refactor?

https://www.outsystems.com/forge/component-discussions/496/Refactor

It lists functionality for creating Outsystems entities by Foreign entity

Support for converting a foreign entity (imported trough an extension) into a normal eSpace entity

Thanks. The component description does say that "It can convert from foreign entities to OutSystems entities"

It may solve the problem.

Chetan Yewale wrote:

Joey Moree wrote:

Ahh I understand now, sorry!

Perhaps you can try Refactor?

https://www.outsystems.com/forge/component-discussions/496/Refactor

It lists functionality for creating Outsystems entities by Foreign entity

Support for converting a foreign entity (imported trough an extension) into a normal eSpace entity

Thanks. The component description does say that "It can convert from foreign entities to OutSystems entities"

It may solve the problem.


Let me know if this does indeed solve your problem! 

Hello Chetan

Check the following Forge component 


https://www.outsystems.com/forge/component/2812/infosistema-dmm/


Best Regards

Hello Chetan,


I think what you want is exactly what Alberto suggested or the Coolprofs one.

We're using the coolprofs one in production to move data from there to other environments or to replicate tenants (for testing or performance).


Initially what we did, was to create an application internally that exposes all tables and consumes them in the other end through web service and worked out really nice, problem was to maintain this during the amount of development so we dropped it, but could be a solution for 1 time only as you can manage the statics and all of that manually : )


Best regards

Cristiano Marques wrote:

Hello Chetan,


I think what you want is exactly what Alberto suggested or the Coolprofs one.

We're using the coolprofs one in production to move data from there to other environments or to replicate tenants (for testing or performance).


Initially what we did, was to create an application internally that exposes all tables and consumes them in the other end through web service and worked out really nice, problem was to maintain this during the amount of development so we dropped it, but could be a solution for 1 time only as you can manage the statics and all of that manually : )


Best regards


Do the Coolprofs or the Infosistema one create Entities from external sources aswell? 

If so, then Chetan can definately use this!

Joey Moree wrote:

Cristiano Marques wrote:

Hello Chetan,


I think what you want is exactly what Alberto suggested or the Coolprofs one.

We're using the coolprofs one in production to move data from there to other environments or to replicate tenants (for testing or performance).


Initially what we did, was to create an application internally that exposes all tables and consumes them in the other end through web service and worked out really nice, problem was to maintain this during the amount of development so we dropped it, but could be a solution for 1 time only as you can manage the statics and all of that manually : )


Best regards


Do the Coolproffs or the Infosistema one create Entities from external sources aswell? 

If so, then Chetan can definately use this!

Hey Joey,


Maybe I misunderstood but I think he wants to move between OutSystems environments (cloud to onpermise).


That being said it will be the same behavior as moving from any other type of environment as long as the data base entities are the same.

Coolprofs solution allows for that and even manipulate the export and import of the data separately .


Best regards

Cristiano Marques wrote:

Joey Moree wrote:

Cristiano Marques wrote:

Hello Chetan,


I think what you want is exactly what Alberto suggested or the Coolprofs one.

We're using the coolprofs one in production to move data from there to other environments or to replicate tenants (for testing or performance).


Initially what we did, was to create an application internally that exposes all tables and consumes them in the other end through web service and worked out really nice, problem was to maintain this during the amount of development so we dropped it, but could be a solution for 1 time only as you can manage the statics and all of that manually : )


Best regards


Do the Coolproffs or the Infosistema one create Entities from external sources aswell? 

If so, then Chetan can definately use this!

Hey Joey,


Maybe I misunderstood but I think he wants to move between OutSystems environments (cloud to onpermise).


That being said it will be the same behavior as moving from any other type of environment as long as the data base entities are the same.

Coolprofs solution allows for that and even manipulate the export and import of the data separately .


Best regards


Maybe I misunderstood when he said Legacy Application with on-premise database, I assumed a different system from Outsystems entirely (since this is the case for me haha).

We have some experience with Coolprofs and I must say after watching the demo in Amsterdam last week I am quite exited to start using this product of them aswell!


Let's wait for Chetan to reply! :)

Joey Moree wrote:

Cristiano Marques wrote:

Joey Moree wrote:

Cristiano Marques wrote:

Hello Chetan,


I think what you want is exactly what Alberto suggested or the Coolprofs one.

We're using the coolprofs one in production to move data from there to other environments or to replicate tenants (for testing or performance).


Initially what we did, was to create an application internally that exposes all tables and consumes them in the other end through web service and worked out really nice, problem was to maintain this during the amount of development so we dropped it, but could be a solution for 1 time only as you can manage the statics and all of that manually : )


Best regards


Do the Coolproffs or the Infosistema one create Entities from external sources aswell? 

If so, then Chetan can definately use this!

Hey Joey,


Maybe I misunderstood but I think he wants to move between OutSystems environments (cloud to onpermise).


That being said it will be the same behavior as moving from any other type of environment as long as the data base entities are the same.

Coolprofs solution allows for that and even manipulate the export and import of the data separately .


Best regards


Maybe I misunderstood when he said Legacy Application with on-premise database, I assumed a different system from Outsystems entirely (since this is the case for me haha).

We have some experience with Coolprofs and I must say after watching the demo in Amsterdam last week I am quite exited to start using this product of them aswell!


Let's wait for Chetan to reply! :)


Yes, that is correct. The Legacy Application with on-premise database is different system (not built in OutSystems).

And I need to migrate this data to Outsystems Cloud database.

I have figured that the Refactor component would be useful to me.

I have also had correspondence with OutSystems technical team, so now I have a high level approach ready - and I am positive it will work.

We would be trying out the data migration on smaller scale (with some tables) with the approach we have come.


Describing the approach:

1. We will first use external database connection from an extension and connect to legacy app database from Outsystems cloud.

2. Then we will have those external entities available in Service Studio

3. Then we will copy those to a local espace in Outsystems. So - now we will have two sets of entities. One which are external and other are native. Difference is the external entities have the data while the native ones don't at this point.

4.  Then we will use Refactor component and migrate the data from external entities to Outsystems created native entities.