nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Look for Enterprise Geodatabase Upgrade guidance, tips, etc

GangWang

We are planning to migrate/upgrade Enterprise GDBs – from Oracle 11g to SQL Server 2016, from SDE to DBO Schema, from 10.4.1 to 10.7.1. Due to the platform change, I assume migration at database level such as detach/attach and backup/recovery isn’t applicable or will cause a lot trouble, but has anyone ever succeeded this way, maybe with help of other scripts and softwares? We have multiple Enterprise GDBs with thousands of user schemas and tons of data so migrating/upgrading at Geodatabase level such as exporting/importing via SDE connection file is not practical. Does anybody know how to make this process smoother, faster, and less painful? Not tend to looking for definite answers, but I appreciate any ideas, comments, etc.

Find more posts tagged with

Accepted answers

All comments

George_Thompson

I would say that using Copy/Paste from the old Oracle to the new SQL Server would be the easiest, but if you have 1000's of user schemas (data owners) in the old one, that would take a lot of time.

Do all of the user schemas own data?

If the Oracle geodatabase is versioned you would need to reconcile/post/compress and delete all the versions out so all the edits are flushed to the base tables.

You could use scripting to migrate/copy the data from the Oracle schema to the SQL Server geodatabase. Do you plan on having as many data owners in the new SQL Server?

Are you only going to us O/S authentication with the DBO schema?

GangWang

Just roughly counted and there could be over 2000 user schemas in all ours sdes across the global. Not all of users schemas own data, but we need to migrate all users from Oracle to SQL Server. We are using SQL authentication in SQL server. Thanks for bringing Copy/Paste idea. is there a corresponding ArcTool for that operation?

George_Thompson

Are you planning on having all the data owned by DBO?

That is a TON of users. I know that the C/P function would also carry over some of the advanced functionality (network dataset, topology, etc.) and that certain tools do not catch that.

I would look at Copy - https://pro.arcgis.com/en/pro-app/help/data/geodatabases/overview/copy-feature-datasets-classes-and-tables-to-another-geodatabase.htm as it might be able to bring over the advanced functionality.

Copy feature datasets, feature classes, and tables to a geodatabase - https://pro.arcgis.com/en/pro-app/help/data/geodatabases/overview/copy-feature-datasets-classes-and-tables-to-another-geodatabase.htm

One item to note is that there is already a feature class/table with a certain name from another schema in there it will append "_1" to the end. That may lead to duplicate FC's.

GangWang

The user owns their data such as land.parcels, exploration.wells, etc. Not a lot but some of our regions do use these advanced funtionalities. Do you think Export/Import XML worksapce document are able to bring over these funtionalities?

George_Thompson

If the user owns data it will create it like this: <DBName>."Domain\Login".TableName

https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/comparison-geodatabase-owners-sqlserver.htm

https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/comparison-os-db-authentication-sqlserver.htm

I would not use the Export/Import XML for this as I have seen too many issues. You would be better served with the Copy tools referenced above.

GangWang

One last question. Let's say if I make a back up of SDE in Oracle and restore it in SQL Server 2016. Can I still make a connection to the restored SDE in SQL server? Will the SDE funtion normally? Will upgrading to 10.7.1 succeed? What is underlying problem with this way? such as gdb storage type difference?

George_Thompson

I am not aware of how to make an Oracle backup restore to SQL Server?

One thing that I could think of is that the SQL running the geodatabase would be different between Oracle and SQL Server. I am not sure that would be an advisable workflow. If you did get it to work, it could have issues down the line that may be related and not possible to correct.

The safest approach would be to create a new SQL Server GDB and copy over the data (via ArcGIS Pro and/or Python scripts).

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.