Branch Versioning on Production Databases

[NickKoopman]

Our organization has been running an environment that has three different SQL Server databases

1. Editing
2. Viewing
3. Field Work

Editing is where we update our core asset information. It's only accessible within our firewalls, and it currently edited with connection files in ArcGIS Pro. It uses traditional versioning. We treat this database as our primary, and have historically limited access to only GIS staff.

Viewing is populated with data from Editing, which is replicated nightly. This database is used to create web services on ArcGIS Server for our viewer apps, which we share across the organization or to the public.

Field Work is also populated with data from Editing, also replicated nightly. But this database also has tables and views that support field operations (inspections, maintenance, etc). 

We have a new directive where we need to allow users to update our core assets in the field. I'm looking into shifting Editing from traditional versioning to branch versioning, and then serving that data through ArcGIS Server/Enterprise for edits. We also see the writing on the wall, that branch versioning is where Esri is focusing future development.

Are there any security or performance issues that I should be aware of before I go down that path? How would I handle replication?

[DavidSolari]

Security-wise, branch versioning hoists the main barrier to access from the database to ArcGIS Server and by extension your Enterprise setup. You publish the services out using database connections that use the data owner's authentication and then your portal's sharing options handles the rest. This is either a trivial change or a massive headache depending on how heavily your org uses Enterprise but as you've implied, ESRI is trying to make Enterprise the central hub for all remote GIS access so the sooner you bite the bullet the better.

Performance is where it gets tricky. Edits to branch versioned data are supposed to be more database friendly in the long term due to only having one table that doesn't need compression, although I haven't benchmarked anything myself. You also get an archive built up of edits to DEFAULT which has none of the headaches of doing versioning + archiving in the old model. The catch is all edits have to be made through a feature service, so your overall performance drops compared to direct connection edits unless you run your servers on some very powerful hardware. The one escape hatch is if you're the "owner" of the data, you can make direct connection edits using the append, calculate field & delete features/records tools; there might be others but this is your minimum CRUD set. I say "owner" because I can never tell if you need the database owner or if your Portal authentication plays a part somehow but either way you have an avenue for doing large updates without bogging down the servers.

I can't speak to DB replication, hopefully someone else can step in here.

[AyanPalit · Accepted solution]

@NickKoopman  Switching to branch versioning should have clear drivers and be a well-thought out process (plan, design, test, implement). During planning, revisit the architecture and stated database setup.

Some key considerations:

• Organization size and GIS data volume
• Purpose: What workflows will your data support
• User types: Office GIS analysts, mobile workers, public users, decision-makers — each has different performance and access needs.
• Data update frequency: Is it real-time, daily batch, or occasional?
• Performance: acceptable performance criteria
• System Integration
• Security and Access Control

 As stated by @DavidSolari branch versioned geodatabase is more efficient. Due to this many organizations have collapsed the data architecture layers. Small organizations use a single branch versioned geodatabase to support Editing, Viewing and Field Work.

Since branch versioned editing is done through feature services, the same database can be use for viewing through other map services. Field work can be supported using Feature Sync through Feature Services. You may not need the traditional geodatabase replication in a future state with slimmer data architecture. Consider these options, along with your organizations unique requirements to come up with a design that works well. 

[CalvinHarmin]

Just want to clarify that branch versioning is not applied at the enterprise geodatabase level, but at the dataset or feature class or table level. So you can have mixed Traditional and Branch versioned objects in an EGDB. 

[George_Thompson]

Correct, you can have both Traditional and Branch versioned data in the same EGDB. Once you version (either way) a dataset, you cannot make it the other one.

It is the connection files (.sde) that allows you to be Branch or Traditional for editing.

[AyanPalit]

Quoting Dr. Malcolm (Jurassic Park): "Your scientists were so preoccupied with whether they could, they didn't stop to think if they should."

@CalvinHarmin Technically it is possible to have mixed Traditional and Branch versioned objects in an EGDB. In my implementation best practice, I would not recommend a mixed transaction model. It adds to user confusion and system overhead with data maintenance as well as system integrations. 

[CalvinHarmin]

Thanks, that's a good consideration. When we start migrating to b.v. we will probably follow this advice and keep branch and traditional objects in separate databases to avoid confusion.

[NickKoopman]

Thanks Ayan! I'll be making a recommendation for our organization to move to branch versioning.

Just to confirm, you wouldn't see any security issues with exposing the Editing database outside our firewalls through feature services? My understanding is that the Web Adaptor does not allow direct access to the database, but handles the requests and responses. Is that correct?

[AyanPalit]

@NickKoopman Each organization's environment is unique. I can only provide generic guidance based on the limited information available. 

• Direct access to the database is not available/allowed  through ArcGIS Web Adaptor in typical branch versioned environment. Someone must explicitly open the database access through the firewall. 
• There are additional checks that may be implemented:
  • Implement reverse proxy in a DMZ
  • Protect Default Version in the enterprise geodatabase
  • Apply fine grained capabilities for external users to only edit in named version. It is possible to restrict users from creating version, deleting version, reconcile and post to Default version.     
• Good references at
  • ArcGIS Trust Center: ArcGIS Enterprise implementation guidance
  • ArcGIS Architecture Center: Working in secure environments

[CalvinHarmin]

One thing to consider/prepare for is that if you previously used Python workflows with arcpy geoprocessing tools including UpdateCursor and all the other tools that performed any editing operations on a traditional or non-versioned feature class or table -- those will no longer be able to perform those functions on a branch-versioned object, and you would need to re-write processes with ArcGIS API for Python or some other means. In my testing the ArcGIS for Python API methods may not be as efficient for certain workflows, but I didn't quantify it so I'm not sure if that would be a significant impact. But at least on my end, I have pushed the migration to branch versioning until I have time to be able to rewrite some rather complex automations that rely heavily on arcpy.