topic Re: SDE with SQL Server in Data Management Questions

SDE with SQL Server

SherrieKubis — Mon, 28 Nov 2016 20:41:11 GMT

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

Re: SDE with SQL Server

JoeBorgione — Mon, 28 Nov 2016 20:54:28 GMT

I have no idea how Oracle works but I've been running SQL Server since version 2003 or so as the back end to our enterprise (pc for SDE) database.

I have one schema that all my users follow, and none of them own it. In SQl Server, if a user owns a schema, you can't delete the user.

I have one version, and I call it (drum roll) edit. All the users are a member of the (another drum roll...) edits group so I can assign privileges to the group instead of individual users. If a user wants to create their own version from the edit version they are free to do so. I have another group called datausers. Members only have get read privileges.

Can't say if these are best practices, but they've worked for me for quite a while.

Re: SDE with SQL Server

VinceAngelo — Mon, 28 Nov 2016 20:59:32 GMT

The biggest differences you will encounter are:

Oracle is the only RDBMS where user-schema databases are possible, so you'll need to choose between a different user or a different database for editing.
In Oracle, the default geometry is SDE.ST_Geometry, but an Esri type is not possible in SQL-Server, so the default geometry is Microsoft's Geometry type. It has a very different spatial indexing protocol, so you'll need to learn the new options.
SQL-Server allows for very-long names, but conforming to the Oracle 30-character column name limit will save you pain in the transition process, since names are still restricted to 31 characters by ArcGIS.

- V

Re: SDE with SQL Server

SherrieKubis — Tue, 29 Nov 2016 14:14:17 GMT

Thanks Vince.

My user-schema GDBs have different uses, for example, a regulatory UGDB is versioned and then compressed nightly, another UGDB uses disconnected editing. I have a master, SDE, that holds base map data, and the UGDBs are functional, with different schedules. And, I have a data owner user that is what the SDE Admins use. In previous versions, this account was the owner of all spatial data, with the latest release that's not possible with UGDBs, so there is mixed ownership. Overall, most users have use a shared viewing account that has read-only on all spatial data.

In the SQL Server world, would you make the compressed gdb its own database? I'm working through the security and functionality of sharing database between databases and between schemas, and the best way to handle data ownership.

Thank you for your help,

Sherrie

Re: SDE with SQL Server

SherrieKubis — Wed, 30 Nov 2016 20:30:39 GMT

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

Re: SDE with SQL Server

VinceAngelo — Thu, 01 Dec 2016 14:56:55 GMT

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

Re: SDE with SQL Server

SherrieKubis — Fri, 02 Dec 2016 19:21:44 GMT

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