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!
Thanks for your reply. Is there any online reference to maximum "usable" limits? At what limit will people run into problems? Thanks!
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
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.
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
Thanks. What strategy would you recommend to implement row level security using Microsoft SQL Server with ArcSDE?
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
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.
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
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
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!
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
That's good info. Can you provide any Esri on-line references to support this pretty clear conclusion?
There probably are documentation references to the limitations of views, but I've never bookmarked any.
Here is some information for 10.3.x and views in a database/geodatabase (specifically in SQL Server):
Views in an enterprise geodatabase—Help | ArcGIS for Desktop
View database data in ArcGIS—Help | ArcGIS for Desktop
Hope this helps with any clarification.
-George
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
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?
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.
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.
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.
If we're looking at plain ArcMap and a smallish number of tables and users - yes, by all means. On the other hand, I recently came across a situation (no customer of ours) where this approach had resulted in the number of tables being more than 100,000, with thousands of ArcGIS Server services on top. Surprisingly, the system still seems to work...
In the context of our application, this is not an option: The application structure and constraints imposed by Schneider Electric ArcFM prohibit the use of multiple sets of tables in one database. Believe me, we have investigated this rather thoroughly.
Martin
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