You are here

Oracle to SQL Server: What to Watch for When Migrating a GDB

When working with a geodatabase (GDB), it is inevitable that the data will need to be migrated at some point. Maybe the system is being upgraded, or things just need to be cleaned up a bit. With that said, data migration is normally between the same type of database system. If the GDB is already using Oracle, staying with it makes sense. However, sticking with the same system is not always an option, and switching over from Oracle to SQL Server may be required. Even though SQL Server and Oracle are similar in, there are key differences that may cause issues when migrating a GDB.

Oracle to SQL Server: First, What Happens to Format?

The biggest difference between the two systems is the format in which table names are specified. In ArcGIS, when using Oracle, only the schema and table name are specified. They follow this format: [SCHEMA].[TABLENAME]. 

When looking in ArcCatalog, all tables will appear like this:

Oracle to SQL Server ArcFM Designer Dataset as Displayed in ArcCatalog with an Oracle Database

SQL Server, on the other hand, specifies different table names: database name, database owner, and the table name.

This is because SQL Server uses the combination of database name and database owner in place of the schema that Oracle uses. In ArcCatalog when using SQL Server, you will see the tables follow the format of [DBNAME].[DBOWNER].[TABLENAME].

So if the database that contains the Designer dataset is named GIS, it will look like this:

Oracle to SQL Server ArcFM Designer Dataset as Displayed in ArcCatalog with SQL Server Database

With this being the case, we have to be aware of our table names when migrating from Oracle to SQL Server. To start, anywhere that references a table name will now need to be updated to include the name of the database, placed in front of everything else. (The schema from Oracle and the database owner in SQL Server should match, so this part should not need to be changed.)

Thankfully, the migration process itself should take care of prepending the database name in most situations. However, there are a few areas that need to be manually changed.

The first area is going to be any data in the tables that reference another table by name. These will not be changed during the migration. We can change them all by creating a script that updates these values. Without doing this step,  the references will not work.

Finally, there is one more area to manually update. These are any table name references in XML files that will be used. This could include the XML associated with CUs, ArcFM™ properties, ArcFM™ relationships, and Designer. If these are not changed, then the associated features, properties, and relationships will not appear or work.

Oracle to SQL Server: Second, What are my Different Data Types?

Aside from the different table-name format, moving from Oracle to SQL Server will require an understanding of their two different data types.

For the most part, the data in these two systems fit together just fine. This will prevent a lot of issues. However, there are a few cases of varying data to be aware of before migrating.

1. LONG RAW Data | Oracle

The LONG RAW data type in Oracle is a deprecated data type. It is included strictly for backwards compatibility, but it may be present if the Oracle system is an older one.

Since this data has been deprecated, trying to transfer a table that has a LONG RAW field from Oracle to SQL Server will fail. In order for this to work, all LONG RAW fields should be converted to BLOB (this should really be done whether migrating databases or not). Once you convert the fields to BLOB, the table will be able to be migrated from Oracle to SQL Server successfully.

2. TINYINT | SQL Server

Oracle actually only allows numeric data (with specifications for precision and scale). SQL Server, on the other hand, has many possible data types for numeric fields (e.g., decimal, numeric, float, real, and multiple types of integer). This variance can sometimes cause conversion issues. The level of issues depends on how the Oracle number is set up. So what can you do?

First, look for an issue where the precision of the Oracle numeric data is small. If this precision is small enough, SQL Server will create the field with the data type of TINYINT. This can cause a host of problems if the field has negative values. A negative value is just fine in Oracle, but the TINYINT data type in SQL Server only allows positive numbers. SQL Server's TINYINT allows numbers from 0 – 255, so the table will not be able to migrate due to having a negative TINYINT value.

The solution? For situations like this, it is best to manually check any field that is created as TINYINT to make sure that it is appropriate. When you find one that needs changing, change it from TINYINT to SMALLINT.

3. Decimal Data | Oracle

We find that if a number in Oracle does not have anything after its decimal, then that part will get truncated and not show up. This can cause the field to look like an integer even though it is actually set up as a double or float. When this field gets converted from Oracle to SQL Server, SQL Server will not truncate the decimal and trailing zeroes. Instead, the field will get converted to NUMERIC, with a matching precision and scale. This migration can cause problems with any programs or applications that are expecting integers. Luckily, you can easily change the data type in SQL Server once you migrate and identify these fields.

Oracle to SQL Server: Next, What About Process Tables?

Another area to watch out for when migrating from Oracle to SQL Server are the process tables.

Problems can arise with process tables if these tables are copied over using only the PX Framework Admin Tool. For the tables that have ID values (e.g., MM_PX_USER, MM_PX_USER_ROLES, MM_WMS_WORK_FUNCTION), the ID values can be reset by SQL Server. With nothing in them yet, they start at 1 and auto-populate as the rows are added. This will cause incorrect names to appear in forms, users not having roles they should, and all other kinds of issues.

This can be remedied simply by copying the tables over directly, as this will preserve the ID values. I found that linked servers are very useful for this. After the table is copied over, the table should work just as it did on Oracle.

Finally, there is also one process table that will require some special attention: the MM_WMS_DESIGN table.

One of the main jobs of the MM_WMS_DESIGN table is to maintain database connection information for designs. To do so, this information will need to be updated to match the new database environment. The two fields that will need to be edited are DATABASE_ID and WORKSPACE_DB_PROPERTIES.

1. Updating DATABASE_ID

The DATABASE_ID field is a unique identifier for Process Framework that shows the database from which the design originated. You can find the value that needs to be placed here in the MM_PX_DB_VERSION table under the same field name.

2. Updating WORKSPACE_DB_PROPERTIES

The WORKSPACE_DB_PROPRERTIES field holds the login properties for the database that is used to edit the design. This field will require a bit more editing. This field holds a varchar(255) value formatted as XML. 

The Oracle value stored in this field will look something like:

Oracle to SQL Server Workspace Database Properties for a design in an Oracle database

This entire value will need to be updated to point to the correct SQL Server instance. Doing so allows for the design to be edited used as normal. To do this, you have a couple different approaches based on how the SQL Server environment was set up.

The easiest way to find out what needs to be placed in this field is to create a new design and check this field for the new design. Since the design will have been created in the SQL Server environment, the value stored here will be exactly what is needed for all other designs. What you find in your test SQL design should be similar to what was there on Oracle, but some elements may have been added or dropped from the XML as well as being updated to point to the SQL Server environment.

Your new value should look something like this:

Oracle to SQL Server Workspace Database Properties for a design in a SQL Server database

Oracle to SQL Server: Finally, Help with Auto-Incrementing Fields

When it comes to fields that auto-increment, Oracle and SQL Server handle them differently.

In Oracle, these fields are maintained with a sequence and a trigger. However, in SQL Server, an identity is used for most of these fields (Oracle 12c does have identity). With this being the case, fields that auto-increment will have their seeds reset when they are migrated from Oracle to SQL Server.

For SQL Server identity fields, you can insert values into the field directly from Oracle using the identity insert property. This property can be set to on to allow you to directly copy over all data, then set up to set it back to its normal functionality. Once this is done, the identity will need to be reseeded with the same seed value from Oracle, and the table will be ready.

Please note: One situation where reseeding an identity will not work is with the process tables. This is because these process tables do not use an identity column to auto-increment. Instead, they have their own table, MM_PX_SEQUENCE, that keeps track of what the next value for certain fields should be. To keep process tables incrementing correctly from where they left off, this table will need to be copied over directly like other process tables.

Final Takeaways on Migrating a GDB from Oracle to SQL Server

When migrating a geodatabase, there will always be some hiccups that occur. That is the case with any project. When migrating to an entirely new environment though, these issues can become even more apparent. Some problems have obvious fixes, such as the formatting change from Oracle to SQL Server, but others require a much deeper dive into the inner workings of the environment. Hopefully this can help ease some of the headaches that can come when migrating to a completely different database environment.

Author Information

  • Corey Tokunaga-Reichert

    Corey Tokunaga-Reichert

    Corey Tokunaga-Reichert works as a Software Consultant at the Utility & Telecommunications GIS consulting company SSP Innovations in Centennial, Colorado. Corey has  worked with All Edits State Zero, GIS integrations and optimization of GIS integrations, and database migration.

    See all items created by this author >

Category Tags:

Corey TokunagaData

Add new comment