topic Re: How do we Migrate Oracle SDE Versioned Data to MS SQL in Data Management Questions

How do we Migrate Oracle SDE Versioned Data to MS SQL

AnthonyDaniels — Fri, 18 Mar 2011 13:59:12 GMT

We are trying to Migrate all our Oracle SDE date including our versioned data to Microsoft SQL server. I know that ArcCatalog will move all the "default" version data from Oracle to SQL, but how can we move the other versions in Oracle to SQL. We have to keep these other versions seperate from the default version so we can not reconcile them.

Thanks

Skip Daniels

Re: How do we Migrate Oracle SDE Versioned Data to MS SQL

KishorePerla1 — Sat, 19 Mar 2011 22:59:16 GMT

Even i tried it many times and finally concluded that....... is is not possible to carry the versions when we are changing the underlying DBMS.

Re: How do we Migrate Oracle SDE Versioned Data to MS SQL

VinceAngelo — Sun, 20 Mar 2011 03:33:49 GMT

I guess it depends on how complex the original state tree is. The first step would be to
identify all the branches in the state tree. Then to determine the numbers of features in
each segment which are inserts and deletes along each segment. From there you can
make stateN-stateM-mode exports out of the source database, and reapply the edits in
the same order into the target database. This would of course be complicated by custom
behaviors and object properties. It's not a pleasant task, but it probably isn't impossible
(unless you require the same objectids and state numbers in the target database).

In theory you could even use sdeexport/sdeimport to transfer the data (since sdeimport
can specify a target version), but you'd need a way to identify the features in a form
that a where clause could evaluate to make the exports (because sdexport doesn't expose
access to the full range of version difference rules that the ArcSDE API and ArcObjects
provide).

I'd have to defer to the replication gurus on whether this is a simpler task from their
standpoint, but the source database would have to be replication-ready (or a clone of
the original made that way) to make that work.

- V

Re: How do we Migrate Oracle SDE Versioned Data to MS SQL

MohammadIshfaq — Mon, 07 May 2012 05:13:35 GMT

Hello Vince,
Though, this thread is a little older but I found it useful and would like to request your help if you can elaborate how do we make stateN-stateM-mode exports and what would be the activities workflow in this regard? A particular reference to an API with relevant example would be really appreciated in this regard.

Secondly, usage of sdeexport, sdeimport is not very clear for me. In this case append is the only option i can use but it would over-write all existing records in the parent version. However, what in your opinion would be the impact if all these records are over-written from child to parent version in the version tree. If there is not any significant impact then I can go for it.
For sdeimport update option I can not build a where clause for about 60 versions and each containing about 270 object classes. Some of these feature classes contain more than a million recoreds and some with complex topology rules which I am not sure if we can migrate using sdeexport/sdeimport.

Finally, i think it can not be achieved with replication mechanism as my source environment is not replication-ready.

My work environment is ArcSDE 9.3.1 on windows with 64bit Oracle 11g R2. I am thankful for your time and support.

Re: How do we Migrate Oracle SDE Versioned Data to MS SQL

VinceAngelo — Mon, 07 May 2012 11:26:37 GMT

There are several ways to accomplish this, at least one for each avaliable API ('C', Java, and
ArcObjects). The se_toolkit 'sdequery' utility can export features based on a state or difference
of states, but there is no se_toolkit tool to diagram the version state tree (I began one once but
didn't have funding to continue), and no way to get exported ASCII data back into versions
('asc2sde' is not yet version-aware)

The key to attempting this resides in the data. If you have versioned feature datasets, where
the content of more than one table is dictated by the same state, then it would not be possible
to reconstitute the data without custom coding.

Even if you ignore the complex topologies (which can't be preserved with non-geodatabase tools),
you're looking at several hundred to possibly thousands of hours of effort (not including the time
it takes to master the versioning model and API of choice).

Since every ArcSDE install is replication ready, I suggest you research this option more fully.

- V

Re: How do we Migrate Oracle SDE Versioned Data to MS SQL

MohammadIshfaq — Tue, 08 May 2012 15:16:14 GMT

Thanks Vince for your quick response. I don't think using se_toolkit to export each state is a solution for me. Its reason is because I am working with legacy data and because of poorly designed business workflows I have seen versions with more than 250000 changes and as you said if a state is referencing changes from more then one feature classes this would be a nightmare.

As you said sdeexport won't be an option here because it works only with simple features, I think ArcObject is the only option which I am familiar with and can work with. But I am really not sure the exact path / objects and objects activity flow to accomplish it. First of all I can construct the versions ancestor tree using a simple sql statement. Then I can use arcobjects to identify and extract the version differences for each feature class. What I really have not done so far is delete a child version, recreate it again after a feature class/feature data is recreated in the target gdb environment and populate extracted changes into this newly created version and all related child versions. Just to keep in mind, some of the feature classes are participating in different topology rules. Before, I can get my hands real dirty with it, do you think it is a viable option?

I am suffering from the legacy data as I converted it 99% into replication ready datasets i.e., it should be versioned, should have global ids and be of high precision. Though, all these conditions are met, I am still facing a very strange error with just 1% of remaining data stating 'The spatial reference z values do not match'. Here is the reference link to my question on details about this issue on this forum 'http://forums.arcgis.com/threads/57008-The-spatial-reference-z-values-do-not-match'. I wander, if I would be able to get a solution to it forever.

Re: How do we Migrate Oracle SDE Versioned Data to MS SQL

VinceAngelo — Tue, 08 May 2012 16:14:00 GMT

At some point, Forums queries become Tech Support calls, and then beyond that,
sometimes Tech Support drifts into situation where a Services contract is needed
to untangle matters. You're probably perched in the grey area between the TS
and PS; either way, it's past the point where I could provide effective Forums
commentary.

Re: How do we Migrate Oracle SDE Versioned Data to MS SQL

MohammadIshfaq — Tue, 08 May 2012 19:39:08 GMT

Dear Vince,
My company already has a dedicated services contract with ESRI and I have escalated this issue at all levels but so far did not get any effective response on it. So, I decided to try posting it here but seems that even it might not help. Thanks for your kind help and support so far.