Background: We have a non-spatial reporting web application with a SQL Server backend. We would like to add a spatial component to this application: serve up some basemaps, serve up points and polygons for view and editing, build reports for selected polygons.
To that end, the plan is to use ArcSDE with SQL Server, and use ArcServer to serve up base layers and vector geometries. (something like WMS/WFS-T)
Problem: We may, at some point, decide we want to move away from expensive proprietary software, and use a FOSS alternative (e.g. GeoServer, or TinyOWS if/when it supports SQL Server). If possible, I'd like to avoid building the application irrevocably around ESRI technology.
My understanding of SDE is that it can either use ESRI specific spatial formats, or spatial formats native to the database system it's installed on. (I presume there is some performance penalty for using the native formats.)
My Question(s): If we were to set up SDE on this SQL server database using native SQL Server spatial formats, would we be able to set up say, GeoServer, to run against the tables that SDE was managing? What would it take to rip SDE out and use something else? (Also, a meta-question: is this a reasonable question to be asking? Is there something important I'm missing?)
Best Answer
A partial answer: I'm not a Geoserver user, but I have built a spatial-driven, non-Esri web app (ASP.NET) using the native geometry stored in an actual SDE featureclass (SQL Server 2008). As long as it isn't versioned (or it is versioned and you accept whatever lag exists for your edits to be moved to base), it should be no problem. My app was spatially driven, contained no map, but the spatial functions where heavily used in the SELECT statements and stored procedures.
I have found that ArcGIS can be buggy with native and/or Esri ST_GEOMETRY implementations and often will result in vendor finger pointing. See this question: How to allow more polyline vertices for a ST_GEOMETRY in Oracle/SDE? (short version: Oracle: "That's the way it is. Esri needs to deal with it." Esri:"That's a bug--wait until Oracle fixes it.")
It's not a reason to not use native geometry, but something to be aware of in case you are the first guy to stumble upon a bug (I also suggest having SDE_BINARY versions of your datasets in a test/dev env so you can confirm or rule out strange behaviour in both storage data types).