Can I use SSIS to keep two SQL SDE databases in sync?

135
1
09-16-2021 05:21 AM
Labels (2)
HeatherM_JDI
New Contributor II

We have the need to keep feature classes from multiple SQL SDE databases synchronized on a schedule (nightly). We need to pull changes from 4 (current or current-1 version) databases and update an database at an older version to align with a partner-developed product that will always be a few versions behind. Has anyone leveraged an enterprise integration tool like SSIS to do this? 

Thanks! 

Tags (2)
0 Kudos
1 Reply
EduardoFernandez1
Esri Contributor

Hi Heather 

There is a lot to consider. 

Will you be truncating features in the older SDE sql server table and then appending new features into this same table? In this scenario, any configuration made in SDE will not be migrated across. SDE configuration in each database remain independent. How much backward compatibility between enterprise geodatabases is an important consideration here too. There are limits in how far back a version you can go between SQL Server and ArcGIS.

If you want to delete the older SDE sql server table completely and migrate a new copy as a fresh table - then any SDE configuration is deleted and you have to reconfigure these tables as feature classes\enterprise geodatabase artifacts. Also consider downstream impacts of deleting an artifact. For example, if you have a ArcGIS Server service registered this feature class as a data source and it gets deleted, then in many cases, a data outage occurs until you republish.

From my limited experience, SSIS doesn't 'out of the box' understand enterprise geodatabases concepts like feature classes unless you specifically include updates to SDE system tables. I strongly recommend not to modify SDE system tables directly.

I'm my opinion, other products like ArcGIS Data Interoperability, FME Workbench, even python using arcpy can handle interoperability between data model frameworks and formats and backwards compatibility of enterprise geodatabases much better and a bit more maturely than SSIS.

I would be interested to see what others have done with SSIS too. 

Cheers Ed