Problems with schema in ArcSDE

[NickAlexandrou1]

Hey guys bare with me this is going to be kind of a lengthy post.

Background information,

I just got hired to help implement an ArcGIS Online web presence. We have 2 license of ArcDesktop advanced and ArcGIS Server. Who ever set up our infrastructure before I arrived, no longer works for the company so I am left trying to put together the pieces, much without the help of our IT department because they do not really know very much about the IT side of ESRI products.

The problem,

I have a database connection to our SDE, I can view everything that is on the SDE, however. If I try to load in a new dataset, I get errors saying that the database username and current user schema do not match. I have been told by IT that my Windows authenticated username has been added to the SQL server that the SDE is housed, and that I have admin privileges. I don't understand why I am having difficulties. I have noticed that all the data stored on the SDE, is labeled SDE.filename.yadayadayada, which indicates to me that there was an SDE username created when that SDE was set up.

Is my problem that the SDE username, and my current username are not on the same schema, therefore I can not do anything to the data within the SDE?

What is the best method for fixing this?

I've tried adding a geodatabase user through arctoolbox toolset, but it immediately cancels out and tells me that I am not logged in under a username that has sysadmin privileges. What do you guys think? I need to be able to give the IT department at least something to work with, but I am rather perplexed at this situation. My logic has deduced that it is a problem with my username and the privileges or "schema" associated with it in relation to the SDE username who owns the DB.

PLEASE HELP!

[NickAlexandrou1]

Vince Angelo​ sorry to tag you, but I have no idea what is going on and feel like you might be my saving grace.

[RebeccaStrauch__GISP]

Nick, it would help to include the version of ArcGIS Desktop, ArcGIS Server, the type of Enterprise Database (SQL or Oracle) and the version, and if when you right click on the SDE database to look at the properties, if it says it needs to be upgraded or if it is at the same version as the desktop.

Older versions (~9.2?) uses to have an option to have an sde-schema which worked as a main control point for additional databases.  (The Multiple vs Single designation is like "Scale factors", it always seemed the opposite to me). Later versions (10.x) allowed each database to be a more, standalone table, without dependancy on thise SDE data

I'm sure Vince will have a more straight forward way of explaining this, but in the mean time, here are a few documents/thread that may help you determine what you have now

SDE/DBO Schema pros and cons?

ArcGIS Desktop Help 9.2 - ArcSDE for SQL Server dbo-schema geodatabases

ArcGIS Desktop Help 9.2 - Using multiple geodatabases within a DBMS

If I remember correctly, there was a command that allowed you to convert from the older version format to the newer format....or....

Are you using versioning, topology-rules, editing, replication, etc with the SDE database?  Do you have ArcGIS Services feeding from the ArcSDE databases?  If not, you can maybe just set up a new database, in the format/permissions that you would like, and copy the features to the new database...that is start out clean.  Might be a bit manual, but may give you a fresh database without any remnants from the older version.  This could be automated with python.

[NickAlexandrou1]

We are using ArcGIS Desktop 10.2.1 , I believe our ArcServer is also 10.2 and are using SQL 2008 Standard. I'm not at work right now, and IT hasn't set up my remote desktop access yet, but I am fairly certain that under SDE properties it says that it is able to be upgraded. To continue to answer your questions, the current SDE that is set up is using versioning, topology-rules, editing, replication, etc. Our Arc Server is being fed directly from the SDE that is set up.

I would be completely okay with setting up a new SDE database (if it is possible to link ArcServer to multiple SDE databases) as the current one does not have very much data on it anyway, and a fresh start would be nice (the current system as so much data redundancy and multiple files stored in various folders on different servers, its ridiculous, this whole set up is a mess!)

Thanks for the reply, hopefully these answers help

[RebeccaStrauch__GISP]

ArcGIS Server should have no issue accessing and/or "licensing" multiple ArcSDE databases.  I don't know if you have much experience in setting up current (or 10.2.1) ArcSDE databases, but it is so much easier then it used to be.

If the current database is working right now with ArcGIS Server and the things that you need, I really would recommend creating and testing with the new. If you use model builder or a python script to create/copy and then test, test, test, it might be that you will find an improved format.  Having been thru many version of the Arc- software, it could very much be that the current format was state-of-the-art at the time, but like everything, SDE databases etc have also improved.

BTW, not that this is how you would want/need to do it, but for us, since we use version/topology/etc for editing our master SDE, but only need to update our services a few times a month, I actually run all my ArcGIS Server services from a FGDB that is a copy of my master files...and I have a series of python scripts that then create optimized feature classes for my various services.  That speeds up my services and prevents any locks or other issues.  Personally I find that services from FGDB are as fast or faster than reading from an SDE database now.   But if you need your services to be showing changes in realtime, then feeding from the SDE may be the way you need to set it up (although you could feed it to a one-way replica and refresh it on a more regular basis).  Just some things to think about.

[NickAlexandrou1]

Thanks Rebecca!

I think I am going to make a new SDE, copy all data to it, and connect it to our Arc Server, because I noticed that all data within the current SDE was published by the original GIS tech, who did it all as the original SDE username made when the DB was established. I'm fairly certain this is my problem in terms of schema because IT did not set my username up with the SDE schema, so we're just going to take an extra day or two and make a new SDE and just wipe the slate clean.

From what I have come to understand, since I do not have server admin privileges, I need my IT admin to create a database on the SQL server, when he creates this, he can make my username the SDE management account or the Database owner (DBO). From there I can execute the "enable enterprise geodatabase" tool. 

The information about FGDB is very interesting. We will be having edits made by maintenance out in the field daily so we will need to have a live-feed so the SDE is probably the way to go with that. (I was going to make a webapp on our ArcGIS Online account that feeds data in from our ArcServer that has been connected to SDE). An edit made in the field and saved, would automatically update our SDE files if I make sure I click the "sync data". But then I run the risk that our data potentially becoming messy if not upkept by users to standards we set, so I will probably make another DB to house our masters and run an update at the end of the week.

Some of our end users do not need such dynamic apps that have live feeds so I think the FGDB may be an excellent route for certain departments and I will probably go this route for a few who really don't make edits, just view and turn layers on and off.

Thanks again, this has been really helpful to bounce ideas off someone and get information. Let me know what you think about these ideas!

[VinceAngelo · Accepted solution]

Part of the issue is a thorough understanding of how databases work.  I realize it's hard to step in to some other admin's shoes, but Esri can't provide all the pieces in the learning curve.  I strongly recommend you find a book or class in SQL-Server administration.

For the record, it appears that you have an 'SDE' database (SQL-Server manages many databases within the RDBMS instance).  The full name of  any table in a RDBMS that supports independent databases (which is all of them, as of Oracle 12c) is in the form database.owner.tablename.  Databases almost never reference filenames, especially in table names, so the "SDE.filename.yadayadayada" reference is really owner "filename" and table "yadayadayada" in database "SDE".

It is true that it is against best practice to name the database "SDE", but that shouldn't prevent you from accessing the data. What could prevent such access is the security model of the database -- by default users can't see tables owned by other users unless they have been explicitly granted this access, either to the user, or to a role to which the user belongs (see the GRANT directive). In addition, ArcGIS enterprise geodatabases require that table owners have a schema name which is identical to the username within the database (it's a neat capability for a user to wear a different nametag in every database, but it leads to fiendishly complex (and slow) metadata queries to support tables which have a schema other than the owner username).  Non-owners who have a schema which does not match username should be able to access tables owned by users whose schema does match, though there may be quirkiness when working with selection sets of more than 1000 rows in a table.

There is no solution to the schema/owner requirement, but it's rarely a bad idea to re-implement a geodatabase using best practices during the transition of geodatabase administrators.  Unfortunately, learning best practice is not a short-term goal, so it might be necessary to use what is already in place a while, taking notes on what may be needed later in the transition process.  You'll also be well-served by establishing a working rapport with the IT team with respect to GIS database administration -- sometimes it's just as critical to get some understanding of GIS issues at the IT level as it is in gaining database admin skills at the GIS level.

- V