Pro2 multi-tenant OpenEdge db replication using CDC

Hi,

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.

Hi,

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.

Hi,

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...

Pro2 multi-tenant enablement is pretty simple but you first must address how the replqueue table is read in regard to multi-tenancy.

You may choose to A) embed the replication tables into your source database with all the same multi-tenant setup, or B) enable multi-tenant on Pro2 "repl" database.  If you choose to option B, it’s important for you to setup the same domain/user structure as is setup in the source database.  Personally I would set it up using method A.

One the replqueue stuff is setup, the rest of it is pretty easy. On disk, you do need two separate installation folders but there is no need to have separate “logical names” for each footprint.  The overall setup will actually be easier to maintain if you have everything in the setup configured the same (source names, schema holder names, etc.) but change the PF files to reflect each tenant to be replicated.

The easiest approach is to get one environment working/reading and replicating using one of the tenant domain/userid/password.  In your example, let’s start by getting C:\Progress\Pro2A functional.

For Pro2a, modify your source pf file to have -U -P tenant login for the OE database.  You could test it by starting a Pro2 editor and attempting to read source side data?  Can you tell the data is appropriate for domain/user Pro2A versus Pro2B?  Can you read repl* table data? If so, the admin tool, mapping, etc. should simply work.

Now you clone Pro2A to Pro2B, change the source pf file to have Pro2B tenant credentials.  You’ll also have to change shortcut pathing, etc in the Scripts folder.  Can you read data?.  Do all the same test as before.  If you are certain you are now reading Pro2B data, then you’re all set.

You can seed the Pro2B repl* tables by simply copying them from Pro2A to Pro2B, as long as the logical names being used are the same, then everything will simply work.  This was my point in the above statement “overall setup will actually be easier to maintain if you have everything in the setup the same…”

I apologize for the late response.  I was trying to get an example working, but enabling multi-tenancy is giving me fits on the VM I‘m using for testing.  So in the interest of time, I’m sharing the setup method and will continue to get my local environment working to insure I didn’t leave out a step.  The procedure above is close, but it’s been awhile since I setup a multi-tenant Pro2 environment and I wanted to validate it all before replying.

Once you get two environments working then it should all make sense.  As an FYI, you don’t necessarily have to have two completely separate on-disk environments. The magic is associated with calling the correct PF file. for the tenancyyou are attempting to replicate. If in each environment you are replicating ALL of the same items, i.e. the setup is identical, then really you can get by with only PF file modifications and a few duplicate shortcuts, although potentially this complicates the upgrade processes.  Just a thought.

I can get on a quick call to discuss the details and I’ll try to post a video when I get my MT environment up and running correctly.

In the meantime, feel free to drop me a note if you need further clarification.

Terry  (temays@progress.com)

[View:/cfs-file/__key/communityserver-discussions-components-files/248/MultiTenant_5F00_Setup.mp4:320:240]

Here is a quick video showing how to replicate from a multi-tenant enabled database.  Please be aware this is not "production ready" as I was trying to produce something quickly to get you going.   Over the next few days I'll add production edits and get it posted in the documents area.

You can can also view the video from here.

One last point.   I noticed in your original post that you didn't like adding the userid/password to the PF file.  This can be eliminated by using the db secure feature, see page 11 of the Pro2 Config and Admin Guide, "Setting up secure connection to a stand-alone database.

To turn this feature on, you first open the admin tool and go to Tools -> Generate Encrypted Password, specify the credentials (domain,user and password) and click OK. This process will generate an encrypted file called db_login.txt in the root level pro2 folder.   Note, you need to temporarily have the -U -P in the PF file for the db password generation to function properly.

Next edit predefs.i and do the following;

a. Uncomment &GLOBAL-DEFINE USERIDDB yoursourcedb, and replace ‘yoursourcedb’ with the name of the source database to which you want to connect.

b. Uncomment {bprepl/dbsecure.i}.

Now you can remove the user/password information from the PF file and everything should work using secure credentialing.

Hope this helps,

Terry

Thank you Terry for your comprehensive answer on how to setup pro2 for multi-tenant environment. I already had managed to proceed to the point where I have two installations of Pro2 as you had recommended. Also it was quite important to enable multi-tenancy on the repl tables even if they are embedded to the source db. I was able to do bulk loading from each tenant. 

However, there is still a major unanswered question for our problem.The original problem we have is with using CDC to replicate changes from multi-tenant database.

From the answers and discussion on this thread, one might conclude, that I cannot use cdc feature for replication from multi-tenant database.

So I just want to make sure of two things to make it clear for us:

- It is not possible to use CDC feature to replicate changes from multi-tenant database?

- We must use triggers to replicate changes from a multi-tenant database?

Thanks

-Yes. it is NOT possible to use CDC feature to replicate multi-tenant database changes. This is because of  "_Cdc-Change-Tracking" table is not a multi-tenant table. This is the limitation on the database side.

-Yes, use pro2 replication triggers against multi-tenant database to replicate the table changes.

Thanks,

Satya