topic Re: Multiple GDBs vs Single GDB in ArcGIS Enterprise Questions

Multiple GDBs vs Single GDB

mpboyle — Tue, 19 Jan 2016 16:21:06 GMT

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!

Re: Multiple GDBs vs Single GDB

DanPatterson_Retired — Tue, 19 Jan 2016 16:25:23 GMT

Not a proposal, but a concern. What about ... you lose one...you lose them all. That would be my main concern.

Re: Multiple GDBs vs Single GDB

mpboyle — Tue, 19 Jan 2016 16:32:35 GMT

Dan Patterson,

Do you mean database corruption, or something similar? If so, I would think having several smaller gdbs would be advantageous, not a disadvantage. If 1 gdb became corrupt, it's not a total loss of data. In terms of backups, our Sql backups are automated by our DBA, so, personally, I'm not terribly concerned with a db becoming corrupt.

If the server itself became non-functional, it wouldn't really matter.

Re: Multiple GDBs vs Single GDB

JoeBorgione — Tue, 19 Jan 2016 16:34:51 GMT

Sounds nightmarish to me; I'd be inclined to split them off. A little more work up front for you, but long term, I think easier.

Like Dan Patterson says: lose one lose them all.

I've gone the multi-version one db route. Hated it; left me wondering what the final edit should be with conflicts. Do you really have datasets pr do you mean feature classes? Actual datasets in SDE are in and of themselves nightmarish imho....

Re: Multiple GDBs vs Single GDB

JamesCrandall — Tue, 19 Jan 2016 16:44:02 GMT

Perhaps break it down by usage type instead (this is extremely simplified).

Publish (read-only, non-versioned)

Edit (versioned workspace)

The Publish data would be highly-available data that is not affected by versioning/editing overhead and easily consumed by data viewers and probably a better option if you intend to publish services off of the data.

The edit data would only be available to editors and internal applications that require edit and/or versioned processes.

You'd need to have some extract-load processes in place to move data between the two environments, but you'd de-couple the versioning/editing from the published data for enterprise usage. This way db admin tasks that require more involved/time consuming processes will not affect published, widely used data).

Re: Multiple GDBs vs Single GDB

JacobBoyle — Tue, 19 Jan 2016 16:44:37 GMT

In the 10 years I've been doing SDE administration, I've seen pros and cons for both configurations.

In a SQL environment, I prefer multiple databases. I find that it makes everything easier for the end user to understand. It gives you an additional level of organization for data as well. I also find user management easier by only adding who needs read/write to specific datasets to only the databases they need. Everything else is maintained with a read-only group. It really doesn't have an impact on performance as long as you right-size your environment. We are currently running over a dozen Enterprise Geodatabases.

Re: Multiple GDBs vs Single GDB

mpboyle — Tue, 19 Jan 2016 16:46:22 GMT

Joe Borgione,

Did you end up splitting up your multi-version one gdb? If so, do you find performance advantages?...administrative advantages?

...and yes, 45 datasets containing 278 feature classes.

Re: Multiple GDBs vs Single GDB

mpboyle — Tue, 19 Jan 2016 16:49:16 GMT

James Crandall,

We have thought about this approach as well, and already kind of have it implemented in our 1 production sde gdb. We have 'Publish' datasets that are non-versioned and read-only. These are mainly datasets that do not change over time or are feature classes that are produced from other business systems through views.

Our web side of things is a replicated file gdb on our app server. This is meant to separate our web environment from our production environment and has dramatically reduced the number of connections to our production sde gdb.

Re: Multiple GDBs vs Single GDB

AlexanderNohe1 — Tue, 19 Jan 2016 17:00:31 GMT

Dan Patterson - That's why we create backups. You could then store these backups elsewhere and then load the backup on a new machine if you need to.

Matthew Boyle - I think the main advantage to having it all on one SDE server is that it is centrally located and you do not have departments with duplicate files floating around in their database. This means that the water department can modify and update their mains while the planning department can use the latest datasets from the water department within their maps. You can furthermore, set permissions on the users of the database so they cannot modify datasets that their department does not have explicit permission over. As in my previous example, the planning department cannot modify the water mains, they can only view them. This allows the water department to be in charge of the creation and management of water mains while other departments can only use them.

As far as a performance increase from one database versus multiple, I cannot speak upon this. If its all on the same machine, I would assume that it would decrease performance but I am not sure.

Hope this helps!

Re: Multiple GDBs vs Single GDB

DanPatterson_Retired — Tue, 19 Jan 2016 17:03:25 GMT

Duh... I know about backups, hence there is no reason to pile everything into one in the first place (this of course assumes that people verify the validity of their backups... something I have seen "go-south" when a backup was needed)

Re: Multiple GDBs vs Single GDB

mpboyle — Tue, 19 Jan 2016 17:06:44 GMT

Dan Patterson - haha, unfortunately we've been bitten by the backup validity as well

Re: Multiple GDBs vs Single GDB

JacobBoyle — Tue, 19 Jan 2016 18:18:34 GMT

That's not a lot

That's just one of 12 databases.

Re: Multiple GDBs vs Single GDB

ChrisDonohue__GISP — Tue, 19 Jan 2016 18:55:52 GMT

Adding some more ESRI enterprise geodatabase folks in case they want to weigh in:

Jake Skinner

Vince Angelo

Asrujit SenGupta

Chris Donohue, GISP

Re: Multiple GDBs vs Single GDB

JoeBorgione — Tue, 19 Jan 2016 18:59:49 GMT

Didn't split up the db's, got rid of the versions instead; mine is a much smaller operation than what you and Jacob Boyle describe; just a few feature classes. I started with one version per user. That got old fast.

I do have a couple of databases now; an edit database and I replicate to a readonly SDE database for non-editing users.

Also, any and all published data comes from a File Geodatabase, that is a child from the edit SDE in a one-way, parent to child replication set up. Database replication is the best thing since hash browns imho....

Re: Multiple GDBs vs Single GDB

JoeBorgione — Tue, 19 Jan 2016 19:02:12 GMT

They better be paying you an awful lot! ( Jacob Boyle )

Re: Multiple GDBs vs Single GDB

VinceAngelo — Tue, 19 Jan 2016 21:17:49 GMT

Forty-five databases is quite a few. The rule of thumb I'd recommend is to focus on use patterns -- Keep stuff which is used together near to one another.

RDBMSes are designed to work well with lots of rows, but not so much with lots of tables. Databases are an excellent way to isolate the table count between objects that are never used together, but I expect there's a Law of Diminishing Returns with respect to Too Many Databases. You may find that bumping up the RAM on the database server is enough to keep up with a large pool of databases, but at some point, the additional worker processes are going to start impacting your available CPU.

Please do keep a close eye on best practices with respect to feature datasets: Using FDS as a "folder" solely for UI presentation is a burden on your geodatabase. There are a number of places where Desktop performance is linked to O(number_of_featuredatasets) and O(number_of_featuredatasets^2). I realize that presenting oodles of tables can be a burden as well, but clever naming policies can handle many of the "flat" presentation issues.

- V

Re: Multiple GDBs vs Single GDB

mpboyle — Tue, 19 Jan 2016 21:26:21 GMT

Vince Angelo,

That's interesting about the datasets. Would you recommend not using datasets unless complex functions (i.e. topology, geometric networks) are needed?

Re: Multiple GDBs vs Single GDB

VinceAngelo — Wed, 20 Jan 2016 03:43:27 GMT

Yes. It would be difficult to find a geodatabase SME who would recommend otherwise.

- V

Re: Multiple GDBs vs Single GDB

RobertWright1 — Thu, 21 Jan 2016 17:23:42 GMT

Great question,

I work for a medium size municipality and had tried a department split by database, so that our Utilities was in one, Public Works in another, Raster in another, and general edit database for everything else. Then replicate all to a production database that all others (non editors, public) could get current info from. It was all still on one SQL instance. This worked to isolate editing, so that utilities editors could edit utilities data and so forth, but limited use for other editors. It also increased the amount of management for all the databases.

We have since moved to the Local Government model for all edits. Similar set up to what James Crandall discussed. We have one database for all edits, that replicate to a production database (view only), with a static database for raster / contour info, a back up, and test database. The replication is handled on a nightly sync that is automated, so that production is only 1 day older than edit. All services are pulled form production. It is working well, with less management, and other editors are not limited by what they have access to. We have started utilizing table views to stream line what some users need in production. I will say its a bit slower than how it was, but for me only managing it in my spare time, it is a better fit. However we are thinking of now isolating our parcel fabric to its own database because of the sheer volume of records, and transactions. Hope this is helpful for you Matt.

Thank you Vince I was not aware how the RDBMS handle datasets I will consider this for future

Regards

Allen

Re: Multiple GDBs vs Single GDB

JoeSapletal — Thu, 21 Jan 2016 17:59:18 GMT

Matthew,

There is one part of your question that stands out to me beyond the organization and number of databases. No matter what ends up working best for your organization from a backup and database organizational stand point, the behind the scenes parts, is the focus on the customer. You said, "The sheer number of datasets makes it a little cumbersome for users to find what they may be searching for". We have a large vector database for view/query, a number of raster databases, and a few editing databases. But we guide staff away from "digging" in the databases. With the last upgrade to 10.3 we avoided even giving them connection files. We provide group layer files, by theme. In our case they go to an internal webpage and find the theme they have interest in and load that layer file into Desktop. These layer files also relate directly to a webapp we have for staff and citizens. So if you use one to explore data, you can use the other.