topic Re: Enterprise ArcSDE Object Limit??? in Geodatabase Questions

Enterprise ArcSDE Object Limit???

JerryGarcia — Fri, 27 Mar 2015 13:37:38 GMT

What is the maximum number of tables/feature classes for an Enterprise GDB? Is there a similar reference for ArcSDE using SQL Server? http://resources.arcgis.com/en/help/main/10.2/index.html#//018s00000002000000 This is from Microsoft: https://technet.microsoft.com/en-us/library/aa933149%28v=sql.80%29.aspx Thanks!

Re: Enterprise ArcSDE Object Limit???

VinceAngelo — Fri, 27 Mar 2015 13:44:16 GMT

The system would be unusable long before you hit the table maximums (5.0e+05 tables, vice 2.1e+10 limit), so it doesn't really much matter.

- V

Re: Enterprise ArcSDE Object Limit???

JerryGarcia — Fri, 27 Mar 2015 13:52:45 GMT

Thanks for your reply. Is there any online reference to maximum "usable" limits? At what limit will people run into problems? Thanks!

Re: Enterprise ArcSDE Object Limit???

VinceAngelo — Fri, 27 Mar 2015 14:00:28 GMT

None that I know of. Had a user on a beefy box with 100k tables -- took 20-25 minutes to log in.

Databases are about rows, not tables. The tables should be kept as few as possible to prevent the high cost of inventory queries on the catalog.

- V

Re: Enterprise ArcSDE Object Limit???

JerryGarcia — Fri, 27 Mar 2015 14:07:29 GMT

Yes, we have over 100k tables based on an implementation mainly due to security requirements (no row level security). It takes a long time to connect to GDB and it does not fully compress. We are in the process of migrating to a more manageable number of tables and implementing an SOI to secure GIS services. I need to know if there are any published or non-published limits to the number of GDB objects. I was hoping this may help us determine before we hit the threshold or if we have already passed it. Thanks.

Re: Enterprise ArcSDE Object Limit???

VinceAngelo — Fri, 27 Mar 2015 14:33:56 GMT

Intialization time and compression failure are independent issues (the latter indicating geodatabase corruption).

I wouldn't recommend having more than 1000 tables, and 100 would be better. I can't imagine a business case for 100k tables. These limits are partially geodatabase, but mostly due to the the database cost of listing and permission requests against the RDBMS. Having a pretty UI box with a bunch of icons describing the table contents can be ruinously expensive, but you're not going to get a published source for worst case scenarios.

- V

Re: Enterprise ArcSDE Object Limit???

JerryGarcia — Fri, 27 Mar 2015 15:09:25 GMT

Thanks. What strategy would you recommend to implement row level security using Microsoft SQL Server with ArcSDE?

Re: Enterprise ArcSDE Object Limit???

VinceAngelo — Fri, 27 Mar 2015 15:16:15 GMT

ArcSDE is just a protocol for accessing spatial data from ArcGIS. Nothing in the API addresses security implementation (besides supporting GRANT requests as a pass-through to the RDBMS). You certainly can't implement row-level security in ArcGIS, though it may honor whatever the database provides..

- V

Re: Enterprise ArcSDE Object Limit???

JerryGarcia — Fri, 27 Mar 2015 15:25:17 GMT

The challenge we are facing is to implement feature level security, but this is not supported at the database level, and historically, at the GIS service level (or this has been very difficult). We intend to implement custom logic at the web or GIS server tier to implement security and enforce complex workflows for editing the GIS data; consolidate our features in the GDB. Overall, there are no published limits to objects in an Enterprise GDB like there are for FGDB.

Re: Enterprise ArcSDE Object Limit???

VinceAngelo — Fri, 27 Mar 2015 15:44:57 GMT

It would probably be easier to leverage an RDBMS that does support row-level security.

There are probably no published limits because it is not possible to get anywhere near the database limits, and the databases all publish their own limits. The closest ArcSDE has to a limit is 2^31-2 logical tables, and the databases pretty much have 2^31-1 physical table limits, so you'd be hard pressed to get within an order of magnitude of the Esri limit. The effective database limits are several orders of magnitude less, so the point is moot.

- V

Re: Enterprise ArcSDE Object Limit???

MartinAmeskamp — Wed, 17 Jun 2015 06:29:40 GMT

Hi, coming abit late into this discussion ...

Microsoft suggests implementing row-level security by using views: If you want to restrict access by, say, user, put a column USER on your tables and use a view with "where table.USER = CurrentUser()" to restrict access.

Question: Would this view be a first class citizen in ArcGIS? Adding data, publishing feature services, ...?

Thanks, Martin

Re: Enterprise ArcSDE Object Limit???

JerryGarcia — Wed, 17 Jun 2015 12:08:47 GMT

Good question. If you can do this, how would you do this with Windows AD: *EGDB SQL Server -> ArcMap *EGDB SQL Server -> ArcGIS Server -> Web Clients Thanks!

Re: Enterprise ArcSDE Object Limit???

VinceAngelo — Wed, 17 Jun 2015 12:48:59 GMT

There are so many things wrong with using user as a WHERE constraint that it's hard to take that suggestion seriously. And no, a view cannot treated as table by ArcGIS.

- V

Re: Enterprise ArcSDE Object Limit???

JerryGarcia — Wed, 17 Jun 2015 13:07:25 GMT

That's good info. Can you provide any Esri on-line references to support this pretty clear conclusion?

Re: Enterprise ArcSDE Object Limit???

MartinAmeskamp — Wed, 17 Jun 2015 13:22:56 GMT

OK - clear words. I'd taken this suggestion from the Microsoft SQL Server docs on row level security, and yes, the username wouldn't be my first choice, so forget about that.

What about an ID (i.e. project ID, customer ID). Could one take that seriously? If not, why? Any alternatives? I recently heard about Server Object Interceptors that could provide some sort of masking to implement selective access, and we hear that ArcGIS Pro has much better support for editing feature services, but that's still rather far away...

Martin

Re: Enterprise ArcSDE Object Limit???

VinceAngelo — Wed, 17 Jun 2015 13:25:57 GMT

There probably are documentation references to the limitations of views, but I've never bookmarked any.

Re: Enterprise ArcSDE Object Limit???

JerryGarcia — Wed, 17 Jun 2015 13:30:53 GMT

SOIs allow alteration of server requests; not at the database tier. This may work for ArcGIS Server clients. But, will not be a solution for editors working directly from ArcMap to Enterprise GDB. How many tables do you have? Just wondering... What industry are you working in? Where is this implementation?

Re: Enterprise ArcSDE Object Limit???

MartinAmeskamp — Wed, 17 Jun 2015 13:42:43 GMT

Yes, SOI is not a solution for ArcMap, but we've been looking at Pro a lot recently, and there seems to be a strong push away from direct GDB access, towards consuming feature services (for viewing and editing).

Our context is utility application. We have a rather complex custom solution on top of ArcMap (also using some Schneider Electric ArcFM functionality), and some our larger customers use their installation, expertise and manpower to provide network documentation as a service to smaller utilities. Right now, they need a separate database for each utility, since access to network documentation is fairly strictly regulated. Number of tables is fairly high, hundreds of feature classes.

Martin

PS Our main market is Germany.

Re: Enterprise ArcSDE Object Limit???

JerryGarcia — Wed, 17 Jun 2015 13:52:11 GMT

We hope to consolidate feature classes, restrict access by AD user for services via SOI, and use GDB check-out for individual editing. This is not optimal, but we need to reduce the number of feature classes in the GDB.

Re: Enterprise ArcSDE Object Limit???

VinceAngelo — Wed, 17 Jun 2015 13:53:52 GMT

There are too many intrinsic flaws in using a view for this purpose. If the goal is to give each user/project/anything a virtual set of unique tables, it's far better to give them an actual set of unique tables.