Oracle Migration to SQL Server Modernizes Work at Houston
With a population of 2.1 million, the City of Houston, Texas is the fourth largest city in America. The City is undergoing amazing growth, which can easily be observed by counting the construction cranes from the 21st floor of the building at 611 Walker St. That building and floor is occupied by the Department of Public Works and Engineering GIS Department. With the dynamic nature of the City, the Department stays busy keeping the digital municipal infrastructure (Water, Wastewater, and Storm Water) up to date and reliable.
In September, 2015, the Department and SSP began the process of upgrading Houston’s Production System from their existing ArcGIS Version 10.0 Oracle environment to ArcGIS Version 10.2.1 SQL Server. The project also involved the cleanup of the data model and update/construction of custom ArcFM™ AutoUpdaters.
The complete procedure was first conducted and tested within the SSP environment. A procedural document was produced and utilized to guide a dress rehearsal implementation in the City’s test environment. Based upon that initial implementation, the document was modified to address necessary “tweaks” in the procedure to accommodate unanticipated onsite conditions and guide the final Production migration.
Migration from Oracle to SQL Server is not difficult, but there can be no outstanding Esri versions. Prior to migration, Houston had to either post or delete their versions. Once at State Zero and a SQL Server database created, the feature datasets can be copied from the source and pasted into the destination utilizing ArcCatalog. The City had several unused domains prior to the project. With the copy/paste method, any utilized domains and related objects “Go along for the ride” and copy to the destination database. Hence, removing unused domains and orphaned child objects in the process. Once a satisfactory migration was completed, the versioned state of the data was reinstated.
A sizeable portion of the project involved further data cleanup. The city identified and provided documentation of several feature classes and their associated fields with the following issues:
- Not used and/or unpopulated.
- Long text field lengths. Several text fields had lengths of 255 characters when a length of 10 (or so) would suffice.
- Incorrect field types. Text fields that should have a numeric field type.
- Renaming of fields for consistency to reduce confusion among staff.
SSP constructed Python scripts to automatically make these requested changes. In total there were hundreds of changes, but the effort’s end result was a more efficient database free from unused “baggage”.
The cleanup procedure was not completely “smooth as a gravy sandwich”. Testing revealed unexpected errors resembling a spatial nature when splitting linear geographic features. Turned out that several feature-linked annotation classes used expressions leveraging these altered fields. Once these label expressions were modified to reflect the changes, the feature classes were editable and functioning properly.
No effort was made to copy/paste the tables associated with the software (mm_TableName) with a similar procedure as described above. The destination tables were constructed via tools on the ArcFM™ Solution Tool bar within ArcCatalog and SQL Server Management Studio scripts.
The properties (i.e., model names, editability, visibility, field order, etc.) o
f the source Oracle database were exported as XML files using OOTB tools, and imported into the destination SQL Server database via the same. As expected, those fields that were renamed (at the City’s request) had to have their properties reassigned.
The ArcFM™ Stored Displays were exported from the source by saving each as a separate ArcMap Document (*.mxd). Each resultant ArcMap Document was opened and saved as a Stored Display within the Destination Database. The ArcFM™ Data Source Wizard was the used to remap each layer’s source within each Stored Display’s Table of Contents to the new location.
Custom ArcFM™ AutoUpdaters (AUs)
AUs are like gold. They are designed to automate certain tasks. Instead of a GIS User manually entering attributes representing the polygon region in which a point feature occupies, code can be written to do this automatically. This makes the User more efficient by not only improving digitizing/attributing speed, but also improving accuracy.
To their existing AUs (which were made to function in the new environment), the City requested two additional ones. The first was as described above. It automatically populates three fields based upon three polygon feature classes. It also automatically populates address fields based upon source feature classes.
The effort was completed in February, 2016. To conclude:
- The successful project resulted in a text book procedure for multiple processes (some of which have been previously documented in SSP published and future posts).
- Houston has an amazing underground tunnel system that resulted in my getting lost on multiple occasions (thoughts of The Shining maze come to mind). Seriously….I bet the reader hasn’t heard of their downtown tunnels. Click here for more information.