Is there a reason that you *must* use SQL for the reporting database? If there is, then you'll want to look into the OpenEdge triggers to keep track of the changes occuring. There are two sets of triggers - the normal triggers and the replication triggers. The replication triggers are specifically provided to do replication, so it's unlikely that the application vendor has put any application specific code in there.
The replication triggers are the right place for such replication logic. From there, they question of how you implement replication has many possible answers. You'll probably want to use a messaging based solution, which can guarentee not only message delivery, but also the delivery of the messages in the correct order.
Of course if you want to implement triggers in your application database, you should definitely talk to the application vendor. They may have an opinion as to whether such an approach might (for example) void your warranty or support contract.
All of that said, there's a much easier way and one that won't (usually) cause any problems for the application providers - that is to do your reporting out of an OpenEdge database. You can either use the application database itself (which might incur performance cost) or you could use the OpenEdge Replication solution to replicate the data in real-time (or near real-time) to a second database, where the reporting can then be run without affecting the performance of the production database.
Given that the OpenEdge Replication solution maintains an identical copy of the database to the production system, this approach can also be used as part of your business continuity plan.
Instead of using an SQL reporting solution, check out one that can do native Progress reporting:
http://www.cyberscience.com/cyberquery.html
Does "managed by a third party" simply mean that it is a packaged application and you pay them to create custom reports?
Or does it mean that they are hosting the database and they won't give you access to it?
If the first then you would almost certainly be better off to just learn how to setup ODBC/JDBC and use Crystal reports (or any "standard" SQL reporting tool) and create your own reports. The hard part of that is learning the ins and outs of the vendor's schema but you're going to have to do that regardless.
If you don't want to report off the live production database then you'll need to create a reporting database but that isn't very hard and there is no need to migrate to SQL Server to do that. The natural way to do that in a Progress environment is either via after image files and "log based replication" (cheap & easy but not real-time) or via OE Replication (more expensive, a bit harder to manage but real-time).
Third party means that the complete bundled package was purchased from a company and they also provide support and manage everything. They do not allow us to touch the infrastructure, and any new reports that are created are custom and are charged heavily.
I have a fair bit of experience in Crystal reports, however reports take a long time to run, presumably due to indexing issues (which has been proved in the past). We have asked for indexing to be fixed, however they deny any problems. Hence the reason I am looking at an in-house managed SQL server reporting solution...
If you are not allowed to touch anything, then they won't be happy to let you add any triggers to the database. Then you'd have to resort to a polling method to extract the data, which will certainly be *extremely* slow.
Crystal accesses the database through the SQL engine. To make effective use of the available indices from the SQL engine, the index usage statistics need to be updated. If you haven't done this, you should look in the documentation (http://www.psdn.com/library/entry.jspa?externalID=4804) for the UPDATE STATISTICS statement.
It could well be that on the ABL side, there are indeed no indexing issues. Updating the SQL statistics should give you similar kinds of performance results to what is seen on the ABL side (albeit with completely different query structures, so YMMV).
If you are not allowed to touch anything, then they won't be
happy to let you add any triggers to the database. Then you'd have
to resort to a polling method to extract the data, which will
certainly be extremely slow.
Perhaps we have different understandings of "not allowed to touch anything"