Porting existing MSSQL database to SDE

Question

I have an existing MSSQL2008R2 database with views each containing a single geometry field along with a big number of "attributes".

I would like to be able to do INSERTS, UPDATES, and DELETES on these views in an ESRI environment. As viewing the data is not enough, I suspect that the new methods for adding MSSQL-stored datasets to ArcMAP is not enough (they only read data as far as I can see).

I am imagining something like adding supporting tables for ArcSDE - would you reckon that is possible, and if so how would you reckon one could do that?

I have read that ESRI have reduced the number of metadata tables in the new ArcSDE, so I hope that I will somehow be able to add those - either using SDE or by hand - to my existing model, as the primary (non-ESRI) software still needs to operate on the same database.

Cheers,

Greg

Answer 1

The MSSQL environment can use the ESRI geodatabase when it is in native SQL. So to set this up, instead of trying to add the supporting tables, begin by creating a new SDE database on a dev machine. You will have to use the SDE installer. Make sure this is DBO owned, and don't bother creating an instance (service). Make sure your keyword is set to create native SQL geometries instead of SDE binaries.

Using the Query layer method of adding data in ArcMap, connect to the SQL data base. Export that data into your new SDE geodatabase. Replace the prod database with the dev database. If you choose to use ESRI versioning, you will need to use stored procs to expose the versions to the MSSQL environment.

OR Use QGIS instead of ArcGIS as it can write to SQL natively.