Why would I install ArcSDE with 10.1?

The documentation covers this.

Workgroup and Desktop geodatabases (which are the part of ArcSDE technology embeded

in Desktop) are implemented with a SQL-Server Express database (only). The GUI to access

these geodatabases is through the "Database Servers" connection type.

A full RDBMS like PostgreSQL requires an enterprise geodatabase (which is licensed by an
ArcGIS Server seat). The GUI for enterprise geodatabase creation is in ArcGIS for Desktop

(Standard and Advanced licenses only), and access is through "Database Connections".

You can also access spatial data in a full RDBMS using Query Layers, which do not use ArcSDE

technology (and therefore also don't support multi-versioning, archiving, and geodatabase behaviors).

- V

FilipKrál

Hi, I am glad to see somebody brought this up.

I was so pleased when Esri announced that ArcSDE will be part of both Desktop and Server products since 9.2 but to this day I haven't been able to use it the way I expected on Desktop. Vince Angelo‌‌, could you please explain how or to what extent is ArcSDE technology available in ArcGIS for Desktop?

I thought I would be able to, for example, turn my PostGIS database on localhost into an "enterprise geodatabase" and use it fully in ArcGIS while I could still benefit from full SQL (SELECT) flexibility in pgAdmin or through ArcSDESQLExecute‌ tool.

However, whenever I try to set up a geodatabase on a proper RDBMS, it turns out that actually I cannot do it with ArcGIS for Desktop alone and that I need ArcGIS for Server to do that. For example Enable Enterprise Geodatabase‌ tool requires some kind of authorization file from ArcGIS for Server Enterprise.

So is it possible to create a geodatabase in a proper RDBMS with ArcGIS for Desktop only or not?

Regards,

Filip.

First of all, you need to understand that there is no longer any such thing as "SDE". The technology was renamed "ArcSDE" at ArcGIS 8.0, and even "ArcSDE" isn't available as a product anymore (as of ArcGIS 9.2). The current term of art is "enterprise geodatabase," which encompasses all RDBMS, Object-Relational, and NoSQL storage of spatial data, as accessed by ArcGIS through a Database Connection.

There may or may not be performance benefits from storing spatial data in a enterprise geodatabase. Back in the infancy of SDE, some marketing reps were selling it as a "spatial data accelerator", but that practice has been stopped. There are benefits, but there are additional costs as well, starting with database installation and maintenance. Shapefile and File Geodatabase formats will still generally outperform an RDBMS engine on a "draw all features" query, precisely because of the principal benefits of a transactional RDBMS (Atomicity, Consistency, Isolation, & Durability). Enterprise databases usually allow for more sophisticated queries (because of the SQL language, or lack thereof) and more exotic indexing options, which often result in better performance with small query results from large tables.

"Several gigabytes" of data might not reach the conventional definition of "large" if you have a large number of wide string columns. The definition is also somewhat elastic, since 200,000 complex polygons might be large while 500,000 points might not. Certainly tens of millions of features would reach "large" or even "very large". Mostly it's a matter of how you need to manage the data to do something useful with it.

In order to help you decide whether an enterprise geodatabase would be of a benefit to you, you'd need to provide quite a bit more information on the numbers and kinds of tables, with row counts and width indicators, and the kinds of operations which are commonly performed against your dataset.

- V

RickWarner

So I am a single user 10.1, and don't share my files often or work in groups.

Are there any reason why I would use SDE for my work? I do use large databases, i.e. several gigabytes. Is there any speed enhancements for queries using SDE?

abureaux_0-1720791599639.png

srinivasrao1_0-1720792449992.jpeg

All is well. I FINALLY understand now. I get it. Turns out I was already using 2-Tier (Direct Connect).

Good, and I really hope you had a good look at the PDFs I created and pointed out to you in the other thread, as they graphically show what Vince has written in his last posts here (and many before in other threads).

Direct Connect still uses the 'C' API to interface with the RDBMS. Post-10.2,
Esri will no longer distribute the API SDK, but they will continue to use it, so it
really won't go away, just get pushed deeper into the software stack.

Not unexpected, but another nice insight you are sharing here for people to learn from...

The application server component was always the thinnest and least significant
part to wear an "ArcSDE" label, but for whatever reason, became the most
famous -- this may be fueling the confusion (and the outrageous length of
this thread).

So true...

Best,

Marco

You can uninstall the 10.1 SP1 ArcSDE application server software if you are not using it.
Just because the 10.x installer uses Direct Connect, and some clients may be using Direct
Connect, doesn't mean that all clients are using Direct Connect. If you never configured
your application server, then no one could use it, but if you did (e.g. there exists a running
service), then it would be wise to stop the service before removing it, just to see if anything
was using it.

ArcGIS Server is an ArcSDE client application. That's all it ever takes for any application
to claim that it has a "component of ArcSDE" installed. All ArcSDE clients have the ability
to make a Direct Connect connection to an enterprise geodatabase, including the Esri
command-line tools (distributed with and without the application server components)
and custom applications available elsewhere. All it takes to make that connection is
the Direct Connect DLL appropriate to the database release and the database DLLs
necessary for the Direct Connect DLL to connect to the database (both are included
within the ArcGIS Server install, though you need to unpack some database client
libraries according to the instructions in order to use them).

- V

Thank you Mr. Angelo! I uninstalled ArcSDE 10.1.1 my SQL Server Enterprise Geodatabase connections still work. All is well. I FINALLY understand now. I get it. Turns out I was already using 2-Tier (Direct Connect).

You can uninstall the 10.1 SP1 ArcSDE application server software if you are not using it.
Just because the 10.x installer uses Direct Connect, and some clients may be using Direct
Connect, doesn't mean that all clients are using Direct Connect. If you never configured
your application server, then no one could use it, but if you did (e.g. there exists a running
service), then it would be wise to stop the service before removing it, just to see if anything
was using it.

ArcGIS Server is an ArcSDE client application. That's all it ever takes for any application
to claim that it has a "component of ArcSDE" installed. All ArcSDE clients have the ability
to make a Direct Connect connection to an enterprise geodatabase, including the Esri
command-line tools (distributed with and without the application server components)
and custom applications available elsewhere. All it takes to make that connection is
the Direct Connect DLL appropriate to the database release and the database DLLs
necessary for the Direct Connect DLL to connect to the database (both are included
within the ArcGIS Server install, though you need to unpack some database client
libraries according to the instructions in order to use them).

- V

Does this mean I can uninstall ArcSDE 10.1.1 from my Server Machine. Based on how I created my Enterprise Geodatabases (using the "Create Enterprise Geodatabase" Tool in ArcToolbox in 10.1.1) I am not using the ArcSDE "Application Server" (3-Tier) I am already using Direct Connect (2-Tier)?

If I understand correctly, the ArcSDE download option from the Licensing/Provisioning Site is actually the "ArcSDE Application Server" which is only used for the 3-Tier Model. However, there is another "component" of ArcSDE, that IS NOT the same as "ArcSDE Application Server" that runs inside/behind-the-scenes/deep inside the "stack" with the 2-Tier (Direct Connect) Model AND this "component" is automatically installed when ArcGIS for Server is installed?

This is not an "either-or" situation. *EVERY* Direct Connect is *ALWAYS* an
ArcSDE connection.

As stated previously, the name "ArcSDE" applies to the technology used to
connect to enterprise geodatabases, the database components used to manifest
enterprise geodatabases, the 3-tier application server, and the 'C'/Java API.
The only part that is optional when working with enterprise geodatabases is
the application server.

Direct Connect still uses the 'C' API to interface with the RDBMS. Post-10.2,
Esri will no longer distribute the API SDK, but they will continue to use it, so it
really won't go away, just get pushed deeper into the software stack.

The application server component was always the thinnest and least significant
part to wear an "ArcSDE" label, but for whatever reason, became the most
famous -- this may be fueling the confusion (and the outrageous length of
this thread).

-V

Here is my situation:

I have been using ArcGIS Server 10.1.1 with SQL Server Express 2008 R2 for the past year. I have several Enterprise (Multi-User)
Geodatabases in SQL Server. I am using Versioning, Replicas, Post/Reconcile, and Archiving. All is working great. No problems.

I upgraded to 10.2.1 a few days ago. Again, all is working good.

Here are the facts:

- ArcGIS Documentation says there are 3 types of Geodatabases: Personal, File, and SDE. To me it seems to get muddled as to whether you are required to use ArcSDE or not. If ESRI says there is no need to use ArcSDE then they should say something like this under the SDE Geodatabase Help: "If you do not want to use ArcSDE, click here to use Direct Connect to a Multi-User Geodatabase".

- When I created my Enterprise Geodatabases I used the Geodatabase Tools in ArcToolbox combined with SQL Server Management Studio. I did not use the ArcSDE Command Line and have never used it.

- The default filenames of the Multi-User (Enterprise) Geodatabases I created in ArcCatalog end in .sde.

- I have ArcSDE 10.1.1 installed and never installed ArcSDE 10.2.1.

Question:

I find it very difficult to determine if I am using ArcSDE or Direct Connect? If ESRI says there is no need to use ArcSDE then should there not be a 4th Geodatabase Type maybe referred to as "Direct Connect Geodatabase"?

I could uninstall ArcSDE 10.1.1 and see what happens. If my Multi-User Geodatabases still connect to SQL Server then I know I am using Direct Connect and Not ArcSDE. What is the best way to determine if I am actually using ArcSDE?

My goodness what a long and confusing thread! do you need to install ArcSDE? well, yes, no, and maybe.

Here is the skinny I got out of all this mumbo-jumbo:

IF you are at 10.2/10.2.1 YOU do not need to install ArcSDE and you will NOT be given an option to install ArcSDE because it will not be there. ArcSDE is gone! kaput! no more!

As an addition to what Vince said: it would also be wise to refer to the ArcSDE Application Server, and the ArcSDE Command Line Tools instead of just generic "ArcSDE" in case you wish to refer to the components of the ESRI Geodatabase Framework that will no longer be available in the next major release.

ArcSDE is far from gone as I tried to illustrate in the PDF document I referenced for you in the other thread you started, and Direct Connect implements all of the geodatabase functionality accessible through the ArcSDE Application Server, while the new Python tools and GUI components for geodatabase management in ArcGIS for Desktop attempt, at least partly, to cover the ArcSDE Command Line Tools functionality. Where they don't cover it yet, ESRI seems to actively work on this and seeking user input.

and you will NOT be given an option to install ArcSDE because it will not be there.
ArcSDE is gone! kaput! no more!

Incorrect. It is still possible to run the application server at 10.2.x, though it would
certainly be wise to move to a Direct Connect model now, before the next major
release does take the decision away.

- V

My goodness what a long and confusing thread! do you need to install ArcSDE? well, yes, no, and maybe.

Here is the skinny I got out of all this mumbo-jumbo:

IF you are at 10.2/10.2.1 YOU do not need to install ArcSDE and you will NOT be given an option to install ArcSDE because it will not be there. ArcSDE is gone! kaput! no more!

At 10.2/10.2.1 you must use Direct Connect. This is your only option so use it.

For those of you NOT at 10.2.1 I say make it so.

KevinGooss

One could argue that the collection of metadata on the (potential) first record in a view as opposed to the true metadata in the (singleton) spatial column of the base table that was the progenitor of said view is synonymous with proprietary software manufacturer laziness.

-K

If there were major changes under way, I wouldn't be the one making the announcement,
fifty posts deep in the Forums.

- V

The dichotomy of the the two different interfaces has always been the issue. If there's no command
line and no API to make a command line, then the geodatabase takes over full ownership of all tables owned by the SDE user. At that point, your suggested change is a distinction without a difference.
The "SDEish" metadata tables have always been geodatabase metadata tables in fact, so I don't see
any major change.

X2, this was how I viewed it, but I was just wondering if any new major changes to ArcSDE (/ Geodatabase) Repository were due. I guess any future changes will be pretty much inconsequential for anyone sticking to official APIs like ArcPy, ArcObjects, just like the change to XML based tables for the geodatabase system tables at 10.x, as the APIs pretty much hide what is "behind the scene"...

The dichotomy of the the two different interfaces has always been the issue. If there's no command
line and no API to make a command line, then the geodatabase takes over full ownership of all tables
owned by the SDE user. At that point, your suggested change is a distinction without a difference.
The "SDEish" metadata tables have always been geodatabase metadata tables in fact, so I don't see
any major change.

- V

Hopefully, there will be a ArcPy tool that will register views with geodatabase metadata once
that which was once ArcSDE is gone.

"Hopefully, there will be a ArcPy tool that will register views with geodatabase metadata once that which was once considered ArcSDE is gone."

or

"Hopefully, there will be a ArcPy tool that will register views with geodatabase metadata once that which was once the ArcSDE command line toolset is gone."

might be more appropriate?... or are the ArcSDE system tables due for retirement too?

In fact, you have been encouraged to "register your view with ArcSDE". This is significantly
different from "use the command line tool to create the views." I strongly discourage anyone
from using 'sdetable -o create_view' ever again, particularly with native or ST_GEOMETRY
spatial columns.

In your particular use, registration of the existing view is necessary (it is not possible for a query
cursor to mine the database query plan to extract the information you expect it to have), but that
only requires 'sdelayer -o register', not recreation of the existing views (be sure to use 'sdelayer
-o describe_long' to extract the SRID of the base table for the -R flag in the register options).

Hopefully, there will be a ArcPy tool that will register views with geodatabase metadata once
that which was once ArcSDE is gone.

- V

KevinGooss

I would like to add my 2 cents to this post based on my experiences of the past few days.
In the past i relied heavily on sde views - take a spatial table, create a view on it, register that with sde using the command line tools and then modify the sql of the view to join in several other, non-spatial, tables.
The resulting 'sde view' is seen by esri in the same way a feature class would be.
This is a very effective tool and we use it extensively.
At 10.1, the idea was that sde command line tools are being mothballed. 'Most' everything you need to do will be available in Catalog, ArcMap and/or python.
Well that is not entirely true in the case of spatial views.

At 10.1 you can make a spatial view in ArcCatalog and it works great. So we did that, and we based ags services on map layers based on those views. Then a few days ago we started seeing issues. The services failed. ags error logs stated that ags could not recognize the geometry type of the layers in those services.
The issue is that our data expires over time and we remove it from the view via a sql where clause. When that view is empty ags/sde no longer recognize it as being a view - it is just a table. So our view sql may say the value in some field must be less than 4 hours old. If no data is added to that table over 4 hours it will be empty. The service will fail and our app throws errors. Not good.

The answer we are getting now is to use the command line tool to create the views and make sure they are registered with sde.
Apparently with the new style views the geometry metadata is gathered on the fly from the first record in the table.
That makes sense to me. But what about when there are no records? Seems like a better system would have been to get the metadata from the only spatial column in the view - the one in the base table - which is a feature class. A feature class can be empty and sde/ags still knows it is a feature class.

Anyway, sde command line is not ready for the closet yet.

StephanieSnider

I wanted to chime in incase this point had not been made clear. There's a difference between an enterprise geodatabase and ArcSDE application service. You can have an enterprise geodatabase (Example: SQL Server or Oracle) and also have ArcSDE installed for an application service. Or you can have an enterprise geodatabase and not have ArcSDE installed. Both options require an ArcGIS Server Basic license.

At ArcGIS Desktop 10.0, the default connection type to an enterprise geodatabase assumed the application service was installed - although ESRI recommended using a direct connect at "best practices". At 10.1 and above, ArcGIS Desktop uses the direct connection by default. You still have the option of connecting using an application service, but the setup is hidden in the ArcToolbox. After 10.2, ArcSDE will no longer be available...so you will have to use a direct connect to get to your enterprise geodatabase. Hope this helps.

MatthewWhitehead

- You don't need to install an ArcSDE Application Server (Also called "ArcSDE service" in ESRI documentation) if you want to connect to your geodatabase, use Direct Connect instead.

The ArcSDE Application Server will no longer be available after ArcGIS 10.2, so it is highly recommended to switch to Direct Connect at this point in time.

- You may need an ArcGIS for Server installation if you use it to create webservices (ESRI Map, Image, Feature Services or WMS, WFS or any of the other service types it supports). If you're not interested in serving your own webservices, you don't need ArcGIS for Server.

- Use the new geoprocessing tools in the Geodatabase Administration toolset in ArcGIS for Desktop for management and creation of your ESRI enterprise geodatabase. See for example the Blog article by ESRI's Melissa J linked below, and the second link to the Geodatabase Administration toolset:

Do This, Not That! �?? Alternatives to using SDE command line tools:
http://blogs.esri.com/esri/supportcenter/2013/10/04/do-this-not-that-alternatives-to-using-sde-command-line-tools/#sthash.YiYFmz5c.dpuf

Geodatabase Administration toolset
An overview of the Geodatabase Administration toolset

You may find it helpful to read these two PDF documents I created:

The ESRI Geodatabase Framework
http://forums.arcgis.com/threads/83644-quot-The-ESRI-Geodatabase-Framework-quot-PDF?p=295462&viewfull=1#post295462
The ESRI Geodatabase Framework - Future developments at ArcGIS 10.2 and 11
http://forums.arcgis.com/threads/83644-quot-The-ESRI-Geodatabase-Framework-quot-PDF?p=303021&viewfull=1#post303021

Thanks you. We use direct connections so no need for the application server. I have upgraded my 9.3.1 database to 10.1 successfully. I currently have server 9.3.1 installed on the same server the DBMS and geodatabases are stored on. I will likely retroactively upgrade to server 10.2 (as well as upgrading client apps to 10.2 and subsequently gdbs to 10.2) in the near future.

Do I need ArcGIS for Server 10.1 installed on my server? I assume the answer is yes but I'm not reading that explicitly in those steps. I just see to get rid of the old 9.3.1 install.

It's certainly not required (which is the main point of this thread). Whether you "need" it depends
on how bound you are to using an application server. It won't be an option after 10.2, so at some
point you'll need to switch over to Direct Connect exclusively.

- V

- You don't need to install an ArcSDE Application Server (Also called "ArcSDE service" in ESRI documentation) if you want to connect to your geodatabase, use Direct Connect instead.

The ArcSDE Application Server will no longer be available after ArcGIS 10.2, so it is highly recommended to switch to Direct Connect at this point in time.

- You may need an ArcGIS for Server installation if you use it to create webservices (ESRI Map, Image, Feature Services or WMS, WFS or any of the other service types it supports). If you're not interested in serving your own webservices, you don't need ArcGIS for Server.

- Use the new geoprocessing tools in the Geodatabase Administration toolset in ArcGIS for Desktop for management and creation of your ESRI enterprise geodatabase. See for example the Blog article by ESRI's Melissa J linked below, and the second link to the Geodatabase Administration toolset:

Do This, Not That! �?? Alternatives to using SDE command line tools:
http://blogs.esri.com/esri/supportcenter/2013/10/04/do-this-not-that-alternatives-to-using-sde-command-line-tools/#sthash.YiYFmz5c.dpuf

Geodatabase Administration toolset
An overview of the Geodatabase Administration toolset

You may find it helpful to read these two PDF documents I created:

The ESRI Geodatabase Framework
http://forums.arcgis.com/threads/83644-quot-The-ESRI-Geodatabase-Framework-quot-PDF?p=295462&viewfull=1#post295462
The ESRI Geodatabase Framework - Future developments at ArcGIS 10.2 and 11
http://forums.arcgis.com/threads/83644-quot-The-ESRI-Geodatabase-Framework-quot-PDF?p=303021&viewfull=1#post303021

MatthewWhitehead

I guess you meant to say:

"... do I have to uninstall the ArcSDE 9.3.1 Application Server from my database server..." ?

If so: yes. Of course, the geodatabase itself with the entire ArcSDE Repository and the ArcSDE and geodatabase system tables therein, should be left untouched, otherwise nothing is left to upgrade...

Don't forget you need to install an appropriate database client. Without client, you won't be able to connect any more once your Application Server is down!

There may also be an extra issue if you decided to migrate to a new server and in the process go from a 32 bit to 64 bit environment... There are descriptions for that in the Help, but it isn't an easy task.

I am upgrading from 9.3.1 to 10.1 and just want to make sure I have my ducks in a row. Are these the general steps I need to take:

1. Make backups, check permissions, etc, etc
2. Uninstall ArGIS Server 9.3.1 including ArcSDE from the server(and delete ArsSDE services)
3. Install ArcGIS for Server 10.1 on the server(?)
4. Upgrade from client via ArcCatalog

I guess my confusion is when reading through the steps on http://resources.arcgis.com/en/help/main/10.1/index.html#/Preparing_to_upgrade_a_geodatabase_in_SQL_Server/002q000000m6000000/
It says that I need to install the current release of ArcGIS client on a computer that can directly connect. I've got that and checked the connection. Do I need ArcGIS for Server 10.1 installed on my server? I assume the answer is yes but I'm not reading that explicitly in those steps. I just see to get rid of the old 9.3.1 install.

We're currently connecting via ArcReader and a few web apps which are already migrated to the Direct Connect method.

If you can already use Direct Connect in ArcReader on your local PC, than you must have the required database client installed. So you shouldn´t have to worry about it.

More info on this Help page:
http://resources.arcgis.com/en/help/main/10.1/index.html#//019v00000009000000

Again, note the database client is only required in Direct Connect, Application Server connections do not use the database client. But since ESRI plans to phase out the Application Server, the database client can better be assumed compulsory.

Wouldn't the database client simply be installed with ArcGIS Desktop or ArcReader?

The database client is a separate install, see the first Help page linked above. You can download it either from the database vendor, or the ESRI Customer Care portal.

But your IT department is likely to have already installed such a client in their default drive-images in case database connections are used by other non-GIS desktop applications in your organization.

DanPajak

Yes, my apologies, I meant the server. We are currently at 64-bit so no need to upgrade there. Regarding the database client I'm not sure what you mean? We're currently connecting via ArcReader and a few web apps which are already migrated to the Direct Connect method. Wouldn't the database client simply be installed with ArcGIS Desktop or ArcReader? Or is there something else I should be looking at? My main concern was what to uninstall. But, looking more clearly, I would have to uninstall ArcSDE anyway because we have a desktop install on our server as well which of course would also need an upgrade. Thank you for the clarification.

So when upgrading to 10.1 from 9.3.1, and not wanting to install the 10.1 ArcSDE Server Application, do I have to uninstall ArcSDE 9.3.1 from my existing Geodatabase ?

I guess you meant to say:

"... do I have to uninstall the ArcSDE 9.3.1 Application Server from my database server..." ?

If so: yes. Of course, the geodatabase itself with the entire ArcSDE Repository and the ArcSDE and geodatabase system tables therein, should be left untouched, otherwise nothing is left to upgrade...

So when upgrading to 10.1 from 9.3.1, and not wanting to install the 10.1 ArcSDE Server Application, do I have to uninstall ArcSDE 9.3.1 from my existing Geodatabase? Then simply run the "Upgrade Geodatabase" tool in ArcCatalog to get it to 10.1?

Don't forget you need to install an appropriate database client. Without client, you won't be able to connect any more once your Application Server is down!

There may also be an extra issue if you decided to migrate to a new server and in the process go from a 32 bit to 64 bit environment... There are descriptions for that in the Help, but it isn't an easy task.

DanPajak

So when upgrading to 10.1 from 9.3.1, and not wanting to install the 10.1 ArcSDE Server Application, do I have to uninstall ArcSDE 9.3.1 from my existing Geodatabase? Then simply run the "Upgrade Geodatabase" tool in ArcCatalog to get it to 10.1?

I have found it easier to do the following using SQL Server Management Studio 2008 R2:

In assigning users, roles, and permissions inside ArcCatalog, you get a dialog box, but no list of choices for the types of roles/permissions you can assign. In SQL Server Management Studio, you do this and their dialog box provides you a list of check boxes. You choose which box(es) to check for roles/permissions. It also verifies that you have the correct syntax and username does in fact exist on the network. Using ArcCatalog, you would never know this as it does not verify the user name to see if you typed it correctly or exists.

I found myself having to use a little of both together to manage our Geodatabases: SQL Server Management Studio and ArcCatalog Geodatabase Administration.

I only use ArcCatalog Geodatabase Administration for:

1) Creating my schema (using X-Ray Geodatabase, a great tool from ESRI!) - anyone who is managing Enterprise Geodatabase should use X-Ray Geodatabase!

2) Editing my Geodatabase (Includes Post and Reconcile operations)

3) Setting up Versioning

4) Creating/Managing Replicas

5) Archiving

5) Seeing who is connected, and identifying Locks

I only use SQL Server Management Studio to:

1) Set up my Instance

2) Create Database (name only, not the schema)

3) Assign Users/Roles/Permissions

I use SDE, but this runs in the background allowing me to do what I need to do.

Personally, I found the SDE Command Line to be obnoxious, exceedingly difficult, and cumbersome. We are a two person, very overworked GIS shop and do not have the time to learn the commands and syntax. I say use the dialog and check boxes it will make your life much easier.