Oracle Database to SQL Server

[RaymondLi]

Hi I am new to Geodatabase.

We have a database in Oracle for Geodatabase. We need to migrate it to the MS SQL Server. There is one column as data type "ST_GEOMETRY" in Oracle table. What should the data type in MS SQL Server to match it?

[George_Thompson]

How are you migrating the table from the Oracle Enterprise Geodatabase to SQL Server?

Are they at the same version?

That ST_Geometry field is specific to Oracle Geodatabases. What is the geometry that you are using in the SQL Geodatabase?

Is the data versioned?

To be honest, the best way to migrate (safest way) is to copy / paste from Oracle --> SQL Server with ArcGIS Pro client.

[RaymondLi]

Hi George,

Thank you for your reply.

I don't know the version of the Geodatabase, how can I check it.

I know that the Oracle Geodatabase is using ArcMap 10.4 to create. The MS SQL Geodatabase is using ArcGIS Pro 2.8 to create.

The problem for me is that our ArcGIS Pro has issue to connect to our Oracle database, that why we need to switch to MS SQL server.

[George_Thompson]

What does the sde.version table say in Oracle?

Using non-ArcGIS clients / tools to migrate from Oracle --> SQL Server may not work as expected.

What is the error connecting to the Oracle GDB from Pro?

[RaymondLi]

Hi George,

The sde.version in Oracle is "10.6.1 Geodatabase". It is a infrastructure issue that the location of  ArcGIS Pro computer cannot access the Oracle database.

[George_Thompson]

Ok, thanks for the update. Are you migrating the records via some sort of Oracle --> SQL Server migration tool?

[RaymondLi]

George,

We export the data into excel format or csv file. We plan to import the file directly to MS SQL Server.

[George_Thompson]

Is there a way to connect to the Oracle GDB via another ArcGIS client and export to a file geodatabase. Then move over and use Pro to load into the SQL Server geodatabase.

The shape conversion process may not work via CSV transfer method.

[RaymondLi]

George,

Thank you for your advise. I am able to export the table to shapefile. Then how can I import to the SQL server?

[George_Thompson]

Once you have the shapefile from Oracle, you can import via the Feature Class to Geodatabase GP Tool.

That will get everything in your SQL Server Geodatabase in the best possible shape. If you had domains / subtypes they would need to be created and configured again