Arguing why schema changes to SDE databases can�??t be managed like non-SDE databases

[RandyKreuziger]

We are a SQL Server shop.  The business side of our IT shop likes to put out policies without vetting them beyond one or two people and they would like all database schema changes to be made only through T-SQL scripts provided to the DBAs.  So before the ink is dry I need to argue when and where ArcSDE databases are different.  SDE tables and feature classes cannot be created and/or modified through pure Transact-SQL scripts.  So I think they will then argue that all changes still need to be scripted and should be done in python. 

There is a lot we can do through python arcgis scripting but are there still some things that can only be manipulated through ArcObjects in the realm of schema changes? 

I'm wondering how many shops insist that only the DBA can make schema changes to their SDE database?

It shouldn't matter but we are running SQL Server 2008 and SDE 9.3.1.

[JoeWeyl]

We are a SQL Server shop.  The business side of our IT shop likes to put out policies without vetting them beyond one or two people and they would like all database schema changes to be made only through T-SQL scripts provided to the DBAs.  So before the ink is dry I need to argue when and where ArcSDE databases are different.  SDE tables and feature classes cannot be created and/or modified through pure Transact-SQL scripts.  So I think they will then argue that all changes still need to be scripted and should be done in python. 

There is a lot we can do through python arcgis scripting but are there still some things that can only be manipulated through ArcObjects in the realm of schema changes? 

I'm wondering how many shops insist that only the DBA can make schema changes to their SDE database?

It shouldn't matter but we are running SQL Server 2008 and SDE 9.3.1.

Hi -

This used to be a fight I had as well, until my company fired the old DBA and promoted me. So I have the benefit of being both the SQL Server DBA and the ArcSDE Admin.

Anyhow, here is the logic. Since Spatial Databases managed with ArcSDE have to recognize any changes to the underlying table schema you have a choice to make 1) Handle this within the framework that is supported by ESRI or 2) Don't use a supported method.

Now for a supported method of changing table schemas there are not any ESRI specific tools that I am aware of that will do this for you, outside of doing it the tedious way in ArcCatalog. I requested this as an enhancement request at version 9.1, but as of yet have not seen anything.  I don't know of any Python Tool that has been created either. But I am sure, if someone was to do this with either ArcObjects or Python with the ArcGIS module loaded, that would be a supported method.

ESRI has specific pieces of it's API that run the stored procedures and triggers that manage the column registry inside the SDE tables in each GeoDatabase. I think that the two most important tables used by the sp's and triggers are SDE.SDE_COLUMN_REGISTRY and SDE.SDE_TABLE_REGISTRY. If these tables get out of sync with your data because someone edited the table "behind" ArcGIS's back, then your GDB will be corrupt.

Now as far as an unsupported way to do this, I have done one method for years and always get "pinged" by ESRI for this when I call support, but it works. Of course that it helps that I handle both Admin roles and can set the rules and know when changes are done.

After any scripts are run in SQL Server to change Spatial Data Table Schemas (either tables or spatial views), I immediately open ArcCatalog and  "touch" the Feature Class in question. This will then run the sp's and triggers to keep the SDE_COLUMN_REGISTRY in sync.

Good Luck.

Joe