Multiple GDBs vs Single GDB

[mpboyle]

Our production enterprise system is currently 1 sde gdb (Sql Server 2014) with several (45 to be exact) datasets and 9 editable versions (based on department). The sheer number of datasets makes it a little cumbersome for users to find what they may be searching for.

I was wondering if anyone has tried breaking down a large sde gdb into several smaller gdbs, perhaps 1 sde gdb per department?  If so, is there is a performance advantage of having 1 large sde gdb versus several smaller sde gdbs?  Is response time better for smaller gdb versus larger gdbs?  Would reducing the number of versions have a benefit on performance?

I'm hoping to get some pros / cons of having 1 large sde gdb which is like a one-stop-shop for any GIS needs or several smaller sde gdbs meant for departmental needs.

Thanks!

[MattWilkie1]

<raises hand>Wait, cache? what cache? how do you build, and then distribute them? I've never seen mention of caches in ArcGIS outside of Server. Clue me in!

[DougBrowning]

Start here An overview of the Tile Cache toolset—Help | ArcGIS for Desktop 

It is basically just a zip file that has all the tiles in it. You can side load this into Collector or 123.  But what is cool is you can also unzip it and then point to it on say a network drive in ArcMap.  Then everyone can use your cache with no server needed (image server used to cost $).  Much faster than server also since it is just a file.

[MattWilkie1]

Oh, okay. That's basically the same thing as what we're doing now -- geotiff's with pyramids -- with a different (albeit more robust) mechanism handling the administration of the sections/tiles. I should make a tile cache of them and see if there's any difference performance wise in our advironment.

[DougBrowning]

For me it was 10x faster and 95% reduction in file space.  Moved 14 GB off the main server which made IT happy.  Also easier to distribute and we could use them for mobile apps 

[JustinNgan]

Hi Matthew,

Absolutely there would be benefits!  The benefits are not just in performance but also in management and data recovery in case of 'problems'.

First, from a performance perspective, it is relevant because it sounds like you are using a 'versioned' environment for multi-user editing and enterprise dataflow.  As such, you are likely well aware of the numerous 'DB' 'States' that are created with numerous 'Add' and 'Delete' tables. Strategically dividing the SDE into multiple SDEs would let you break up the data so that when you do a DB compress, you can return each to as best the base 'state' as possible.

Second, because you have numerous areas editing data and each area will presumably have a different 'data editing schedule' and 'transactioning type' (e.g. long v. short), you can minimize data loss if short transactions are not all mixed in with long transactions. So, an editing session that takes weeks to complete, say updating complex engineering data, can only be reconciled and compressed over weeks. In the meantime, short transactions involving a simple field or location edit can be reconciled followed by a DB compress much sooner.

Let me know if these suggestions help and if you'd like to discuss further.

[RebeccaStrauch__GISP]

Justin....welcome to the fold of geonet...looks like that was your first post.

I agree with your comments, that from a management and administrator aspect, it helps to have multiple, logically organized databases....both for function and type.

I keep all my master files (only three) that have to be involved in the Topology/versioning/editing, in on SDE database.  I copy (or replicated if needed) to a FGDB and then use scripts to separate these (i.e. many selects) into more user friendly subsets...both for staff and for ArcGIS Server services.

I have other SDE or FGDB for the data that doesn't change often, and usually have these grouped into logical groupings...whether in separate gdb's or datasets, as needed.  I'm lucky that I only have three main master files....I know others have MANY more...but from an administrator standpoint, this works for me...again, especially going along with your points.

[GregCarlino]

Justin,

You may be able to skirt the entire system architecture issue If helping your users find the right data is your primary concern.  You should look into using the 'NPS Theme Manager for ArcGIS' from the National Park Service.  It is an application that functions both in conjunction with ArcMap and as a stand alone program.  I believe it was developed by the NPS, specifically for providing easy access to data sets without having to worry about complicated path directories and login portals.  Furthermore, It allows you to group large numbers of datasets into logical categories, or 'themes', while providing simple drag-and-drop adding functionality to any ArcMap document.  Plus, you can still implement all the usual database security settings behind the scenes without bogging down the end user.  We use it at our office, and maintain an enterprise system with over 250 datasets on one database, with no problem.

[MichaelSmith1]

There is a balance between having too many DBs to manage, and yet having enough spread to give you performance and redundancy you need.

In my previous job, we had a lot of editors.  So I put the editable data in one GDB, and had another which was read-only, a 'warehouse' which drew from the edit GDB using replication.  Finally, a third GDB had all the raster data in it - since it rarely changed it was only backed up when updated.

We learned this lesson the hard way about a decade ago when our Oracle environment was corrupted.  At that time all the data - edit, view, raster - was all in one big GDB.  It took a week to fully restore it since it went down 'hot'.

In my new job, I do most of the editing, so I just have two GDBs - one for vector and one for raster.

[JonMorris2]

Just counted the contents of our main GDB:

30 tables

30 relationship classes

4158 feature classes

16345 raster datasets

Too much? Definitely too much for SDE - it's all in one file GDB at the moment. SDE becomes unworkable once you get beyond 2-3000 datasets.

[VinceAngelo]

I've had clients with 12K feature datasets (and 150K tables), which, while not quite ideal, certainly weren't "unworkable".

The time for placing rasters in databases ended at ArcGIS 9.2, when cached map services were introduced.  There really is no reason to place the rasters in a modern database, which means that this set of <5K tables would be a large, but not very large enterprise geodatabase.

16+K rasters is quite a few.  Have you tried organizing them with mosaic datasets?

- V