Migrating DDOTGIS Oracle Migration to SQL Server
- James Graham (Deactivated)
About
We're moving!
As a part of our larger effort to standardize on one database platform, DDOT's GIS/IT team will be migrating from our Oracle GIS database to SQL Server on . Below is some information about what will (and won't) change. As always, if you have any questions about this change, do not hesitate to reach out to us with questions at ddotgis@dc.gov .
What's Changed?
- Moving the database platform. As the name suggests, we are continuing our migration of various apps from Oracle to SQL Server. This will allow our database team to focus and improve upon a single platform and continue to provide great support for DDOT data users.
- Schemas... reimagined! One of the other big changes has to do with the reworking of our database schema. Previously, our schemas were a little bit of a mess. Generally, they were created to organize data by DDOT agency name/division. The major problem: agency/division names (and acronyms) can change! For example:
TOA (Traffic Operations Administration) has been reorganized into 2 or more Divisions. However, production data in our database is preceded by a schema of 'TOA.<tablename>'.
UFA (Urban Forestry Administration) is now UFD (Urban Forestry Division)
Solution: choose names that are more descriptive of the asset/feature/type. This will allow us to better group and classify data in our database. See the table below for a good breakdown.
- Names. There is a slight difference in how tables are named in SQL Server vs. Oracle. In the next section we provide some examples of what you can expect.
Names will change slightly
In the new database, the tables and GIS features will show the database name + the schema name (instead of just the schema name).
- You are required to include the full name to connect to a table or feature class.
- The table or feature class name will be identical to the original in Oracle.
Example:
| Oracle (Old) | SQL Server (New) |
|---|---|
|
|
Here's a complete table showing what's coming:
| New Schema | Old Schema |
|---|---|
| Planning | PPSA |
| Snow | PPSA |
| Freight | PPSA |
| Parking | PPSA/TOA |
| ADA | DDOT_ADA |
| Roadway | IPMA |
| Bridge | IPMA |
| Sidewalk | IPMA |
| PubSpace | PERMIT |
| Bicycle | PPSA |
| TrafficSafety | TOA/PPSA |
| Transit | PTSA |
| Forestry | UFA |
| TrafficOperations | TOA |
| Sign | TOA |
| Streetlight | TOA |
Install the SQL Server database client
The recommended client to access the new server is SQL Server 2016 Client.
- Install the new SQL Server Client. The new database requires the latest client software (this allows you to connect to the database from your PC).
- Microsoft ODBC Driver 17 for SQL Server - https://www.microsoft.com/en-us/download/details.aspx?id=53339
Once the file is downloaded, right-click the .msi file and Run As Administrator
When installing, accept all the default recommendations
- Once the installation is complete, restart the PC
- You should now have the ability to connect to the upgraded SQL Server.
- Log in to the wiki to see the connection details below
If you would like assistance setting up/configuring this to work, please don't hesitate to send us a request on Jira
Connecting to the New Database
What if I just use map services? Does this affect me?
No!
What if I use ArcMap or ArcGIS Pro to connect to GIS data? Does this affect me?
Maybe! Use the connection information above to update your connections. If you need help recreating/reconnecting, you can submit a request and we'll help reconnect you!
Any other questions?
As always, if you have any questions about this change, do not hesitate to reach out to us with questions at ddotgis@dc.gov .