Dedicated DB instance for your LRS?

JessePearson · ‎08-16-2019

Hello everyone, we are planning to switch our LRS to a dedicated LRS DB instance and I'd like to ask a few questions to the group

Here are my fun Friday questions: #lrs #esri roads and higwhays; #DB‌‌

If you have Roads & Highways installed in Production can you respond if you are using a dedicated DB instance for your LRS?

If you are using a dedicated DB instance what issues, if any, have you had?

What platform are you using? (Oracle, SDE, etc.)

Thanks,

Jesse Pearson

LRS Integration Portfolio Manager

Minnesota Department of Transportation

651-366-3881###

ScottFierro2 · ‎08-16-2019

Are you asking about technical performance related issues of the database? Or about the software's use of the database?

Also, depending on your Enterprise GDB the terms database, server and instance can be interpreted different ways. When you say instance I think of an Oracle instance which means a singular Oracle environment containing multiple schemas that may or may not live on physical server resources that are shared with other Oracle instances. So are you asking about a single instance on a shared machine or are you meaning a dedicated machine?

JessePearson · ‎08-16-2019

Hi Scott, Thanks for clarifying. What I mean is using a dedicated machine, as in a VM and that the LRS would reside only on that VM and nothing else.

Currently we have our LRS on an Oracle instance containing multiple schemas, which has caused us some issues. We are currently in discussions about putting our LRS on SDE on its own VM.

ScottFierro2 · ‎08-16-2019

Got it, ok. Sorry can't speak to that. We are running Oracle 12c over Linux servers but all our instances are on machines shared with other instances. We've seen performance issues at points but more so in 11g. I'd expect that a dedicated machine is the dream configuration because you are isolated so managing resources or troubleshooting are much easier since there is no parsing of what instance was doing things and when. Would prefer to move to an isolated machine but it's not cost effective to do with our current Oracle environments.

KyleGonterwitz · ‎08-16-2019

We do have three dedicated instances, one each for Dev test and prod SQL servers, designed for our roads and highways systems. I'm happy to announce here we have few databases in prod that interact a little bit with io... and many more databases in Dev.

Oracle having multiple schemas makes challenge geodatabase updates a challenge, is your issue related to that?

JessePearson · ‎08-19-2019

Thanks for the response Kyle. Yes, this does have to do with having multiple schemas and making updates for GIS Desktop when the rest of the agency isn't ready to move to the next version. Having a dedicated SQL server gives us more flexibility when we want to upgrade.

KyleGonterwitz · ‎08-19-2019

SQL Server definitely makes it easier to update a database as needed. I'm not sure, but I think Postgres gives the same update functionality as SQL server, while providing about the same native spatial query and function as Oracle, which is much greater than SQL server. Our DBA's of course would prefer we stick with SQL Server or Oracle.

KyleGonterwitz · ‎08-20-2019

I forgot to mention we have three more instances, dev - test- prod , each for a Roads and Highways "publication" database, where the idea is to routinely export simple features from the Location Referencing database. That's a total of 6 instances of SQL server. I also should mention we have three more instance environments for traveler information systems, two oracle instances (test and prod) for legacy GIS data, and two SQL instances for other application specific GIS Databases that are associated with ArcGIS server (processed and exported from legacy systems for ArcOnline and/or specific application/requirement needs).

Issues... as you see that's a lot of instances, let alone databases and database connections to manage as sa, sde, owner, and ad group logins. In our Roads and Highways instance, I do not have sa yet which I would really need in dev. Managing user security around SA is an interesting issue, as Scott Fierro mentions below, there are some specific privileges that need to be assigned to ad users/route editors in order to keep privileges minimized. In fact, keeping privileges to a minimum is still something we are trying to sort out and improve upon from the Oracle days where most connections were by the Data Owner or admin oracle database users. The issue is where we need to export/output a segmentation table for processing, we have a database named user for that, but we need to figure out how to better organize the output and processing tables so they are not included in the Roads and Highways database. Some of the output tables are created by database scripts, and as such are unregistered to the geodatabase.

A side effect issue of this is that we have so many objects in our roads and highways database in addition to the hundred plus event and registered LRS features and tables that using ArcPy to listFeatures or listTables takes about 20 minutes. I find it beneficial to keep a explicit list of events in a python config file to save time and add flexibility for listing event features.

KyleGonterwitz · ‎08-20-2019

I also run a postgresql instance in my desktop and a couple VM's for temporary development work and data processing, which I find works really well with memory-intensive functions.

ScottFierro2 · ‎08-20-2019

Ah now I see the conundrum. I agree with Kyle Gonterwitz‌ with regards to SQL Server because the general management of multiple independent databases within a single server instance on SQL is much more straight forward and easily allows for a slew of ESRI versions since they each contain their own SDE's. Oracle's managed much differently in that regard because Oracle DBA's still seem to heavily favor limited instances within a server and then utilize multiple schemas within that instance in the way that SQL DBA's would use a single database on a server. The catch always ends up being the single SDE schema limitation which bottlenecks lots of organizations flexibility in using Oracle.

I can say that relative to this situation you are asking then yes, we are using independent Oracle environments. We have Development, Vendor Development, Test and Production environments for our Roads & Highway needs spread across 4 different instances. All of those instances are shared instances with multiple schemas in them except for Production is its own true independent instance. The catch is we do zero ESRI work in our Oracle databases other than R&H so the shared instances are only shared with other projects systems for things like finance or projects data. This frees our R&H implementations from any dependencies on other needs for ESRI versions.

For your original question, we have seen solid performance out of our isolated Production instance at 12c despite the fact that the instance still shares a common Linux host with other instances. I can't say we've seen any issues thus far other than within our latest 10.7 efforts the ESRI permissions diagram for various users seems to be missing the fact that the role for out user accounts needs "Delete" on the LRS_Edit_Log table because we've seen errors during rec/post when that's not in place.