SDE with SQL Server

Question

SQL Server 2016

SDE 10.5

Our SDE prototype to migrate from Oracle is ramping up.   We have experience with SDE in Oracle, but not in SQL Server.  Because this pilot will continue for the next year, we are going with the latest and greatest versions.  I'm looking for best practices, suggestions, tips on using SDE in MS SQL.

In Oracle, we have a master geodatabase and user-schema geodatabases in the same Oracle database.  We do versioned editing in the user-schema gdbs.

Can anyone share their experience?  I'm just starting, getting the terminology down and a staging SQL instance and database up and running.  There is a lot to learn.

Sherrie

SherrieKubis · Answer

Thanks Vince.  Old habits die hard!  We've been using 'SDE' since 3.2 and I still refer to it as that, but have been trying to use the correct terminology.

This is good info, we have a lot test and learn.  We have a MS SQL presence here, but for 3rd party applications.  The thought from those guys is that cross-database, or even cross-instance queries are eazy-peazy.  I was a concerned about cross-instance queries for performance reasons, but it's clear we have a lot to learn.

You're right, our old environment has all of our geodatabases and tabular data for relationships in the same Oracle instance, so every though ownership was different, joins were possible.  However, the user-schema GDB that was versioned was isolated enough that we could version and compress there, but the other user-schemas GDBs were all treated different.  In SQL Server my consideration is if I were to migrate these UGDBs over, would they each be a database?  If there were different schemas in a database I think that versioning isolation would be an issue.  But if cross-database joins aren't a good idea, that would definitely be an issue.

I want to get the architecture and possibilities straight in my head, but it's looking like we've got to just install it and play around, and see what we can do.

Your insights are appreciated.

Sherrie

VinceAngelo · Answer

Best practice is to give each user their own login, and use roles to manage access (create roles, grant table access to roles, grant roles to users).

I always use SDE-based (vice DBO) geodatabases, with one or more "headless" logins mapped to a user (and schema) with the same name to own tables.  If there's a web mapping application, I'll create a login for that as well.  Creating logins to match the users of user-schema GDBs wouldn't be a bad plan

Your old edit environment wasn't as isolated as it seemed, since the tables all lived in the same space.  You can isolate editing frameworks in separate databases, but doing so makes it nearly impossible to have the tables of different databases interact at the SQL level (cross-database queries are a violation of best practice, and not easily possible with ArcGIS).

Keep in mind that "SDE" no longer exists.  The current term of art is "enterprise geodatabase".  They can only be created by ArcGIS Desktop or Server.

- V

SherrieKubis · Answer

I need to update that we aren't planning on trying SDE 10.5 as I'm hearing that there are 'changes'.