SQL Tips Geospatial Practioners Haven’t Heard Of

This blog post is going to be very boring to most people, but exciting to a small group of people. Today we are going to focus on enterprise geodatabases, which are comprised of Esri’s custom configurations on top of Microsoft’s SQL Server. As opposed to being a particularly structured blog, I am just going to brain dump a bunch of tips that I wish someone has told me when I started administering enterprise geodatabases.

-Ever wondered why you can’t kill locks? Have you checked on the SQL Server side that your user has “processadmin” enabled?

-Have you ever wondered why you still have terrible performance, even though you’re running the rebuild indexes tool in ArcMap/ArcGIS Pro? Well, I hate to break it to you – but you might need to actually go rebuild those indexes on the SQL Server side. It’s not hard – locate your table, expand it, find your indexes, and start right clicking them and going to “Rebuild”. You’ll get information on how fragmented the index is and you can decide if you’d like to rebuild it from here or not. I’ve found that the rebuild indexes tool in ArcMap/ArcGIS Pro can be faulty for a ton of difference reasons – best to do this at the source.

-Did you know that you’re not stuck with the default spatial SQL index settings? You can locate your spatial index on the SQL Server side and change its configurations – you can do this if you’ve noticed slow performance (though there’s a world of troubleshooting that can be done before this) or if you know that your data would perform better with a customized view.

-Did you know that SQL Server can recommend missing indexes to you? This is a big leap for the standard GIS administrator because it isn’t done at all on the Arc side of things – but it’s important, and can be accomplished fairly easily. Just right click your server, go to reports, go to the performance report, and go to recommended indexes. Now, you’ll want to start googling to determine which of these would be most impactful to your organization – don’t blindly implement them all. But good indexing is critically important, even in an enterprise geodatabase.

-An enterprise geodatabase is absolutely not a fancy file geodatabase – you’re talking about a full blown relational database, not a fancy wanna be file type.

-RDS, Azure SQL, and other managed database platforms are not good options for enterprise geodatabases – whether they run on SQL Server or Oracle or PostgreSQL. Having a managed product might sound good at first, but does not automate the real work that you need a database administrator for, and comes with a tremendous number of limitations that aren’t mentioned anywhere in the fancy sounding documentation.

-If you’re running an enterprise geodatabase, keep in mind that you’re running a fully features and amazingly robust relational database platform. You’re likely paying to license it (unless it’s PostgreSQL) – so why don’t you take advantage of it? Simple views can often fill the need for the pricey GeoEvent Server. You can do a tremendous amount of data conversion processing in SQL Server. You can create hugely beneficial automation from triggers and stored procedures. Use caution though – there are certain operations that should be done in ArcMap/ArcGIS Pro because a single operation there might set up a bunch of stored procedures / triggers in the database. For example, it’s best not to directly insert records into a versioned or archived table in an enterprise geodatabase from the SQL side, unless you really know what you’re doing.

-Remember web SQL! This is a cheaper alternative to SQL Server that you can purchase from cloud providers. It comes with most of the features of SQL Server Standard, but at a fraction of the cost. For most GIS practitioners, this version of SQL has a sufficient number of features.

-Remember that you can use SQL Server Express, which is free, for an enterprise geodatabase. However, you will get a modified licensing from Esri, Workgroup licensing, and it will come with some limitations.

-Also keep in mind that you can license all of your non-production SQL Server with SQL Server Developer Edition. This licensing type comes with all the features of SQL Server Enterprise, but is free and fine to use to license non-production workflows.

-It does matter if you choose SDE or DBO. Look into it. It does also matter if you have a user owned schema. Look into it.

-SQL Server upgrades are not as scary as you think. Endeavor to keep your SQL Server up to date with minor version upgrades, and try to keep up to date with major versions as well.

-If you can, hire a dedicated database resource. They don’t have to have experience as a SQL Server database administrator per se – a familiarity with administering enterprise geodatabases and a desire to learn will be enough to make a big difference.

I’ll keep expanding this list as time goes. I hope it saves someone some of the frustration and headache I’ve experienced!

Let me know what you think!