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.

[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-... 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-...

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-ow...

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

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).