Hello community,
Earlier, we would not be able to access the database directly on Creatio SaaS cloud installations and would have to reach out to Creatio support for any assistance. Does this still continue or is there a roadmap to enable direct database access even in the Creatio cloud offering?
If we still do not have access,
- How does one approach enterprise level heavy volume data migrations in the context of the Cloud? Typically these are done directly through the Database either via SQL scripts or via ETL tools.
- We are aware of ETL plugins like Starfish ETL, we want to understand options besides that - where access & control at the database layer is required to execute these migrations. How have other customers handled this scenario?
Like
Dear Shrikanth,
Due to security reasons the direct access to the databases is not available. If there is a necessity to migrate some data from\to Creatio - the enterprise level customers use different integrations via webservices, OOTB API and Odata, DB server linking, ftp\sftp storages and different custom API integrations. SQL scripts can be also involved.
The direct connection with our cloud and customer's storages is never set up due to the security policies. Usually there can be some shared storage where customers send their data and it is imported into our cloud. As for Starfish ETL, it is also used but not frequently. Usually enterprises use their own integrations.
Regards,
Dean
Dear Amanthena,
This is typical on-site\non-cloud integration. It's basically looks like this:
Creatio database<--->buffer database<--->customer's storage.
Typically the buffer database is kind of shared storage for customer and Creatio used to import and export data to both sides. It is typically used on regular basis rather than one time data migration. It has some particular access rules assigned to the required tables. It is not usually used for migrating data into Cloud database due to some security limitations, however some enterprise customers have similar solutions where Web server is connected to similar storage and it pulls data into Creatio. Again, server linking is not really commonly used for cloud integration solutions, however, under some conditions, specific setup and prior negotiation with cloud team- it may be possible. If you are interested in it, I suggest you to discuss it with your account manager.
Regards,
Dean
I have done data migrations directly to the database on several occasions with no issues. The most challenging part is the creation of the access rights records (well, not really challenging once you figure out the RightLevel 0=read/1=edit/3=delete, Position, SourceId=user/role values, but something to be aware of). Each table has a separate table that stores access rights for the records, for example for Contact it stores it's access rights records in SysContactRight. For objects with record level permissions, you need to create these records to give read/edit/delete permissions to the records. However, in recent versions of Creatio the object permissions propagate to records nicely, so I would likely turn off record access rights for the migration, then set afterwords and let Creatio do that work.
In my scenario, I had a customer with multiple millions of records. They are cloud hosted, so I had support provide me with a database backup, I performed the migration, then provided the complete database loaded with data back to support as a backup and they put in place on the cloud again. This was significantly faster to load using SQL inserts from selects etc than loading record by record via the API. Keep in mind that if a customer is first a cloud customer, the backup you get from support is likely going to be a Postgresql database, not MSSQL. In another scenario, with Creatio on premise, creating views that combine data from other SQL databases and exposing as objects in Creatio was very straightforward as well.
Hope this helps.
Ryan