Move Spatially enabled SQL data to SDE

Question

I have a spatially enabled SQL 2008 R2 database that has a table in it that I want to move to SDE 10.1 every night because the performance is dramatically better using the SDE server (SQL 2008 R2  as well) to present the data in my flex app.  It presents customer data, so it changes but a daily refresh is often enough.

Has anyone done this and how did you do it and would you do it this way again?

Thanks,
Mike

VinceAngelo · Answer

It all depends on how you want to proceed -- You can use an ArcPy script to make a
Query Layer request and extract the data from the source database, then insert it
into a truncated target table (after the service which would lock the truncate has
been stopped).  Or you can link the servers and query the export table directly
from the ArcSDE connection.  Or you can use Well-Known Text in an ASCII export
to repopulate the table with a command-line utility like 'asc2sde'.

Detecting change over time isn't all that difficult if you have access to a cryptographic
hashing function to generate {key,hash} pairs (based on rowid column, usually).
Two skinny tables and three views can be used to identify the insert, update, and
delete events.  You can even run the analysis in realtime to update the content
more frequiently than daily, and it wouldn't require any downtime.  All you need is
a location for the intermediary files.

I must point out that proper tuning of a Geometry layer could produce better 
performance than a SDEBINARY layer, so you might not be asking the right 
question.

- V

MikeSmith7 · Answer

There's at least a dozen ways to accomplish this, but it's not completely clear what
 you want as the end product.  Data does not ever reside "in" ArcSDE -- ArcSDE is a 
protocol used by ArcGIS to access data in databases.  There are several different 
possible geometry storage methods, which include native database types like
 Geometry and Geography, as well as the traditional SDEBINARY ("three table")
 storage option.

So is it the database or the storage format you're looking to change in this migration
 process? (It makes a large difference on how you would go about moving the data.)

Are you looking for unattended conversion processes?

Do the two machines share a single security domain?

Do you need to do further processing to manage the data accessed through ArcSDE?

How many edits occur in the data to be moved (expressed as a fraction of the total table)?
Would moving only the inserts, updates, and deletes be attractive in terms of availability,
or is it okay if the application is disabled during the maintence window?

- V

I would see the end result being in the traditional SDEBINARY format.  
Unattended is the goal of this.
Same domain.
No editing of the copied data will ever occur.
It is Ok for the data to go offline for a short period of time.  Since I will not know exactly what changed in the original data a complete replacement is likely easiest.