Background:
Few years back I was assigned the task to replicate our piping design software (Bentley AutoPLANT & AutoCAD Plant3D) Projects between our offices.
The intention for this replication was, as explained to me by the Engineering CEO, is my engineer from office A will get sick I want the option that an engineer from office B across the world will be able to continue his work.
Function as collaboration goes without mentioning.
Back in these days the BIM360 was not that advanced, even today I’m not sure the solution is suitable for us since: it does not cover the Bentley environment, it does not cover pre-2020 products (the pre-2020 products are covered by the Autodesk Teams a lesser product), price of the licenses is very high.
The solution that we have built for us was divided into two components:
- File system replication base on the peer products package, the advantage of the peer products package is that it has a locking component preventing from the file to be open at the same time at both offices.
- Database replication based on the merge replication built into the SQL Server.
The advantage of the merge replication over the other types of replications is that this type of replication enable writing on both sides of the replication.
Challenges:
Over the time we found that this method has two major limitations that we must pay attention when configuring the database replication.
- None replicated tables – there is a list of tables that if we will enforce replication on them the software will not work properly, these tables should exist on both sides and since they content of these tables does not change there is no effect to the cancellation of the replication (see tables at the end).
- Newly born tables – the way AutoCAD PLANT3D works is that it’s starts with a minimal number of tables in each database and then creates needed tables for each new class added to the project.
This behavior is challenging with replicated databases since the replication process replicates only the tables existing in the database when the configuration initially configured.
Solution:
The solution we found and configured is stored procedure running weekly on our SQL server.
The steps of the stored procedure are as follows:
- Create a list of all None Replicated Tables.
- Create a list of all databases marked with the MergeReplication flag.
- For each replicated database from the list, check that none of the None Replicated Tables is check with the MergeReplication flag.
- For each replicated database from the list, check for tables without the MergeReplication flag (excluding tables from the None Replicated Tables list).
- After each change in the replication article list it is needed to rerun the replication agent.
Extras:
- The stored procedure is based on the MergeReplication flag in both system tables sys.databases & sys.tables, in some cases the MergeReplication flag is not clear from a database after deleting the replication, in this case it will have to be done manually using the commands described in the following article.
Manually remove replication – SQL Server | Microsoft Docs - Download Link to my Stored Procedure.
https://1drv.ms/u/s!Ak3_FVjLS7injZ9_jd9FrxGJpciEZA?e=dfSDZR - List of None Replicated Tables.
| Software | Table Name |
| BENTLEY AutoPlant | last_id |
| BENTLEY AutoPlant | LASTNUMERICID |
| BENTLEY AutoPlant | project |
| BENTLEY AutoPlant | sysdsheet |
| BENTLEY AutoPlant | System |
| AutoCAD PLANT3D | PnPTableAttributes |
| AutoCAD PLANT3D | PnPColumnAttributes |
Dudi Kurtz 