Being relatively new to ESRI ArcGIS Server I have a question regarding the storage of ESRI SDE Databases – AGS 10.0 Enterprise and ArcSDE 9.3.
Firstly the naming of the whole structure gets confusing. SDE as far as I know, is only the software which "spatially enables" a Database (in my case MS SQL 2008 R2). So when I create a geodatabase in MSSQL I have to do this through the post install program. Only then will I have the ability to view/update/delete in the geodatabase either with ArcEditor/Info.
Im my "inherited" system that I have to now continue using, I have some data coming from MSSQL Server and some from Geodatabases which are on another PC.
Question 1: What type of connection is that on the pc? Is that a so called "direct" sde connection?
Question 2: Some other colleagues have told me, that Rasters are delivered quicker in AGS Services if they are not in the MSSQL DB. Is this true?
Thanks for any help,
Robert
Best Answer
SDE is a middle tier software that translates calls from ESRI, or other, clients into SQL that is appropriate for a certain database. If you are connecting to a geodatabase, then SDE also manages a certain amount of metadata that is stored in that database's SDE or dbo schema. What is ArcSDE
For Vector data on SQL Server 2008, there are actually spatial types provided by Microsoft which you can be used to store your data. But by default, SDE will create tables using a storage type called SDEBinary or just Binary. This is basically storing the geometry as binary data in a side table (F-Table), and maintaining a pseudo-index table to improve performance (S-Table). I call it a pseudo-index table because it isn't an index at the database level.
I don't think Microsoft has provided a type that can store rasters in the database (other then a BLOB). SDE can add an ST_Raster type, but that type will not be added by default.
When you run the post-install program, or the enable or create geodatabase GP tools at later releases, you are adding the tables in the SDE or dbo schema that track feature classes and tables, along with other functionality such as versioning and archiving. Additional tables are added by the Geodatabase to track feature database, subtypes, topologies, etc.
The difference between direct connect(2-tier) and application service connection(3-tier) is where the SDE process is running. That is, where the translation between the SDE calls from the client and the DBMS calls from SDE are actually being translated. With a direct connection, that process is running within the client application (ArcMap, ArcCatalog, ArcIMS, etc). With an application service connection, that same process is started by a separate service called the giomanager. This giomanager can run on any machine, but is commonly run on the same machine that is hosting the database. An Overview of ArcSDE Geodatabase Connections
So, to answer your questions:
Question1: It depends on how you are connecting. If in your connection dialog box you are entering a port number into the instance field, you are running application service connection. If you are entering something like 'sde:sqlserver:myHostName', then you are running a direct connection. Creating spatial database connections - Scroll down to 'Adding a direct connection to a geodatabase in SQL Server'.
Question2: It depends. There are many things that can cause performance issues for both SQLServer, DBMS and file based rasters. But in general, file based rasters outperform those stored in databases. This is because the architecture of a database, and ArcSDE, isn't really designed to store and retrieve giant chunks of binary data. Similarly, large and externally complex vector data does not perform well either. Rasters stored locally will outperform those stored remotely. But all else being equal, I would expect that a raster stored in a remote file geodatabase to perform more slowly then one stored in an SDE instance, which would perform slower then one stored as a tiff in a remote directory, which would be slower than one stored in Image Server. Geodatabase and ArcSDE Raster Basics