I'm having difficulties with implementing Pro2SQL CDC replication for multi-tenant OpenEdge database. I'm using OpenEdge 11.7, Pro2 version 5.5. Running on windows server 2012 R2.
Let's say I have OpenEdge database called DB and it has tenants from A through D. I'm only interested in replicating data from tenants A and B. The data should be replicated to the same target SQLDB so that I can identify which tenant the records are coming from in the target SQLDB. I have embedded the repldb in a OpenEdge database (not the same where I replicate data).
The documentation says that I should have two separate installations of Pro2. I created two installations of Pro2 in C:\Progress\Pro2A and C:\Progress\Pro2B. When opening the administration tool from tenant A it also shows the mapping etc. from tenant B. This is a problem as I have not connected to tenant B from the tenant A installation of Pro2. This also means that the replication is not working as the log files show that it is missing the DB connection from tenant B and dropping out to reconnect. After failing a few times, the replication shuts down. I have also made sure that the paths, .pf files, pro2_env are in pointing to the corresponding tenant.
I also did a different setup where I connected to tenants A and B from the same Pro2 installation, but it seemed that the replication didn't work for both tenants at the same time. For example I made a change in tenant A and the Pro2 admin tool identified the source database for the change as tenant B and tried to use tenant B credentials to connect and of course couldn't find the changed records from tenant A using tenant B credentials. Also with this setup I had to write the tenant credentials to the .pf files, which is not really a secure way to connect.
The source database is the same for both tenants (same port and name) but I'm using different logical names and different uid and pwd to connect to these tenants. I also have different schema holder dbs for both tenants with different names and both are in the corresponding C:\Pro2\db.
So my question is, how should I setup Pro2 for a multi-tenant OpenEdge database?
- Should I use a single or separate Pro2 installations?
- How should I fix the problems that are currently occurring with these setups?
As far as I know Pro2 does not currently support multi-tenancy (as well as tabplepartitioning). This is because the _Cdc-Change-Tracking table is not a multi-tenancy table.
You need to contact with this task to a professional service of Pro2.
>>The documentation says that I should have two separate installations of Pro2.
By the way, in which documentation you found about this? Can you post link? Maybe something has changed and I don't know about it yet.
Russian Progress User Group
Pro2 does support multi tenant database replication without having CDC feature.
With Pro2, you can configure a multi-tenant database. Each tenant in a multi-tenant database must have its own installation of Pro2, a schema holder, and an ODBC connection. However, for all the tenants, the logical name of the ODBC connection can be same, and the schema holder name can be same as the Pro2 installation folders are different for each tenant.
Can you please refer Page#17 of Pro2 Config Guide_5.5.pdf for detailed steps.
Okay maybe I will contact Progress about this.
The documentation is the Pro2 5.5 Configuration guide that came with Pro2 installation (in docs directory) as Kunal stated. I don't think it is available online as it came with the Pro2 installation. The documentation regarding multi-tenancy is about setting up a secure connection to a multi-tenant database. It states that I need to create a separate folder for each tenant. It is not stating anything about CDC and I just assumed that it would also work with the same logic using CDC.
I referred the config guide when configuring the Pro2 installations. The problem is that even with the own installations for each tenant, the Pro2 administration tool is always showing the mapping etc. to all the tenants even if they have their own installations. As I stated, Pro2 is trying to connect to both tenants A and B even if I start the admin tool from tenant A install location of Pro2. The replbatch.bat won't run because it cannot connect to the other tenant B because that tenant is not to be connected in the installation location of tenant A. I guess this is CDC problem with multi-tenancy as the CDC policies are for the database which is the same for both tenants and Pro2 is using these policies and it messes up the Pro2.
However, If I have only one installation of Pro2 for one tenant, the replication is working fine. So if there could be a way around the problem so that I could somehow "force" the Pro2 tool to not to try to connect to the other tenants when replicating. I can see from the CDC monitor that it is correctly getting the changes but the problem is with the replication as it won't run because of the problem I explained above.
As you stated that Pro2 does support multi tenant database replication without having CDC feature. So I guess Pro2 doesn't support CDC for multi tenant database and one should use triggers instead...