Latest Contributions by SherrieKubis

‎01-09-2019

Thanks Jonathan. I'm researching as well, will update if I find anything. Sherrie

‎01-07-2019

Hi Jonathon, thank you for the response. We have been doing SDE (I still cal it by this name, sorry), for a very long time, but always in Oracle. Now we are migrating to SQL Server. Our SDE Administrators are considered trusted users, they own their data and are very knowledgeable. In Oracle they don't have DBA or SYSDBA, but can do their work without issue. They do a lot from ArcGIS, but they also do a lot in TOAD, as they write ETL scripts that join spatial and tabular data. When moving them to MS SQL, they need the same access, which means a 'sa' or 'dbo' user. Having a DBA or another account perform the grants it's feasible. I've been looking for grants that would allow them to see users and what permissions have been granted to them, and allow them to travel around investigating schema data with SELECT access. They have a lot to do with this migration, over what they normally do, and I want to make it as smooth for them as possible without giving away higher privileges. That means allowing them to see users, privileges assigned to users, and schema data for investigative purposes. Any other insights would be appreciated ... but I fear the worst in that they are going to need db_owner. Sherrie

‎01-03-2019

SQL Server Enterprise 2016 ArcGIS 10.5.1 We are moving from Oracle to SQL Server and have installed under the 'sde' schema, not dbo. Following our security model, we have a data owner, data editors and data viewers. The Data Owner called 'SDECREATOR' owns data in the geodatabase, and then assigned permissions as needed to editors and viewers. The data owner is a sql login and database user that has CONNECT, CREATE PROCEDURE, CREATE TABLE, CREATE VIEW. The account owns data, but inside of SSMS, it can't see users to grant to, or see what grants have been given. For example, the viewing account is SDEVIEW. It can issue GRANT SELECT ON MYFC to SDEVIEW, and it works, but looking at properties doesn't show anything. I believe this to be related to now having the correct permissions. If I had the role db_owner to SDECREATOR, it can see what it needs to. However, since this role an do a lot more than own and manage data, I really don't want to do that. Is there another way around this? Sherrie

‎12-11-2018

Gotcha. No, we have always used alter system kill session How To: Kill direct connect processes to the ArcSDE databaseBut we've been doing it for so long I don't remember exactly where it came from, although I'm sure it was from ESRI. We'll give Arcpy a look and see what it does. At this point, I'm way past the production issue but will keep this in mind.

‎12-10-2018

Yes. The user doing the ALTER SYSTEM KILL SESSION has DBA privileges. The issue isn't that the sid,serial# can't be killed, it's that it doesn't exist, it's already been disconnected. When the session in V$SESSION and v$PROCESS has been killed, the corresponding entry in SDE.PROCESS_INFORMATION should be deleted but it's not. It persists so it appears that there are SDE connections when there aren't. At the time of this issue, there were NO sessions other then DBSNMP and Oracle background processes connected. However, SDE.PROCESS_INFORMATION table had 196 rows. Sherrie

‎12-07-2018

Environment Oracle 12.1.0.2 SDE 10.4.1 Connection Methods Direct connects using tnsnames.ora are used from desktop and servers. Kill connections method Construct an ALTER SYSTEM KILL SESSION (‘SID’,’SERIAL#’) dynamic sql statement from joining V$SESSION and V$PROCESS with SDE.PROCESS_INFORMATION. This is how we gather information, there is a next step of constructing and executing the ALTER SYSTEM that is not shown here. WITH sp AS ( SELECT s.audsid, s.LOGON_TIME, s.MACHINE, s.MODULE, s.OSUSER, s.PROCESS, s.PROGRAM, s.SERIAL#, s.SID, s.TERMINAL, s.USERNAME, p.PID, p.PNAME, p.SPID FROM v$session s, v$process p WHERE s.paddr = p.addr AND s.TYPE != 'BACKGROUND' AND s.program IS NOT NULL AND s.username NOT IN ('SYS', 'SYSTEM', 'DBSNMP') ), pi AS ( SELECT p.audsid, p.sde_id, p.server_id, p.start_time, p.owner, p.nodename FROM sde.process_information p ) select sp.LOGON_TIME, sp.MACHINE, sp.MODULE, sp.OSUSER, sp.PROCESS, sp.PROGRAM, sp.SERIAL#, sp.SID, sp.TERMINAL, sp.USERNAME, sp.PID, sp.PNAME, sp.SPID, pi.owner, pi.server_id from pi, sp where pi.owner = sp.username and pi.server_id = SUBSTR (sp.process, 1, INSTR (sp.process, ':') - 1) and pi.audsid = sp.audsid order by sp.logon_time;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ Scenario 1 We periodically need to update data during our maintenance window requiring all connections to be disconnected. After all sessions were disconnected, the V$SESSION and V$PROCESS tables showed no connections for SDE. But SDE.PROCESS_INFORMATION had 196 rows, and SDE.TABLE_LOCKS had over 3000 rows. We bounced the database, but SDE.PROCESS_INFORMATION and SDE.TABLE_LOCKS returned the same. Ultimately from SQL we deleted all rows in SDE.PROCESS_INFORMATION and SDE.TABLE_LOCKS, and update work progressed. After the maintenance window, desktop ArcMap, ARCSOC sessions came in, everything seems to be working fine. Scenario 2 I believe that SDE.PROCESS_INFORMATION should always have an entry for an SDE connection. Right now if I pick a USERNAME/OWNER and compare V$SESSION has 9 rows, but there are 11 rows in SDE.PROCESS_INFORMATION. I’ve also seen cases where there are entries in SDE.PROCESS_INFORMATION that don’t exist in V$SESSION. ISSUE Prior to ArcGIS 10.5 sdemon was used to clear and rectify SDE process entries with Oracle sessions, even with direct connections. There isn’t an sdemon now, or a method (that I know of) to manage connections. There is something for locks, but not connections. This is why we resort to the brute force ALTER SYSTEM KILL SESSION method. Something is not quite right, but I don’t know why. It’s hard to test because this is production. Why do entries persist in SDE.PROCESS_INFORMATION? Why do sessions show in V$SESSION that are not in SDE.PROCESS_INFORMATION? It feels dangerous to manually monkey with SDE metadata tables. I see many posts about using the ALTER SYSTEM KILL method, so it seems widely accepted and recommended by ESRI. At this point, things are working okay, but it’s uncomfortable not to have processes and connections matched up. And I can’t guarantee this won’t happen again during the next maintenance update. Any insights or suggestions are welcome. Sherrie

‎03-08-2018

No solution, it continues to happen with SDE 10.4.1, but I get no complaints from anyone. I only see the error in the alert log, just about every day.

‎10-26-2017

We'll still have an Oracle presence for our spatial data as well for the next five years or so. Over the years, we've had different iterations with 8K, 16K and even 32K (for rasters) db_block_sizes. For the last migration to Oracle 12.1 we went with 8K straight across the board, and haven't noticed any performance impacts, and have gained some space back. It's simpler as well. I think blocks sizes for Windows volumes might be like that, where larger data would be stored in a filegroup with a larger block size, etc. But in the end, we have good hardware, a good SAN, and the added complexity maybe not worth it. If it gets to a point and we need to start tuning for performance we might consider looking at it. I came away from my conversation with an associate feeling like this was something I missed the boat on and really needed to change before we even get started, but I think we'll wait on it. Sherrie

‎10-26-2017

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) I have separate drives from C through K, for system, datafiles, logfiles, tempdb, etc. Our database server is a VM, and the storage I am presented is from the underlying SAN storage of the VM. The drives were created with the MS default of 4K, but it has been suggested to me that our datafiles, logfiles and tempdb should have a block size of 64K. I don't see many references to this, nothing in ArcGIS documentation. I suspect the answer is 'it depends' on data size and use. Does anyone have any insights, experience, rules of thumb for this? Sherrie

‎10-26-2017

As it turns out, this issue is not caused an object's storage characteristics, but the datafile. I created the datafile at a reasonable size and allowed autogrowth just in case, but at a percentage instead of size. Each time space is added, it's an exponential size, but there was unused space in there, and each time it was more unused space. Everything I've read MS SQL uses a Global Allocation Map to find an unused block, but that didn't seem to be happening, just a bunch of white space everywhere with bad performance. We started over and I made the datafile bigger with an autogrowth of 1MB and the behavior went away. This is staging, when we have a handle on how much storage is required for each of our databases, development through production will be adequately sized from the get go. The motto is don't use a percentage autogrowth size ... Sherrie

‎10-21-2017

Thanks Tina, it's really about the ArcGIS objects in the datafiles, logfiles aren't the issue. I'm looking for other experiences when migration from Oracle to MS SQL with ArcGIS, and if anyone has seen a size difference -- good, bad, big or small. There are things to do in MS SQL like FILLFACTOR for indexes, compression, revising datatypes, shrinking the datafile to get to a target size, but the only think I'm looking at is FILLFACTOR, they others are not something I want to mess with. To me, they are different RDBMS systems with different storage, and what is required, is, well, required. But I can't help wondering why others haven't seen this, or at least I haven't found them.

‎10-20-2017

Hello Vince! Oracle's a beast, but I'm focusing on geodatabases and the spatial implementation. We use ST_GEOMETRY, nothing SDO. In MS SQL, we are using the native spatial type. In both Oracle and MS SQL we've not modified DBTUNE, but the default in there is PCTFREE 0. We are using Oracle ASM and ASSM Tablespaces. In MS SQL we also use the default DBTUNE and it uses FILLFACTOR 75. I'm going to look in that direction, that seems a likely place.

‎10-19-2017

Kevin, What size are your geodatabases? Do you have other enterprise data in SQL Server, or are you just concerned with spatial data? How has your conversion from Oracle to SQL Server worked, are you also seeing storage differences? Oracle on Oracle Linux is The Bee's Knees, I think it's straightforward, and mature, with the added benefit of opensource Linux. Oracle's Grid Infrastructure with its Automatic Storage Management is very efficient. It takes some expertise, and for a good implementation you need an Oracle DBA. Some points-of-view is that going to MS SQL means you don't really need a DBA (as much) because MS SQL is easier, and a lot is next-->next-->next. Databases that we have supporting COTS applications are like that -- if there is an issue, go to the vendor for support. I don't agree that a MS SQL DBA skills aren't needed for a large SDE implementation, where we have in-house applications, ETL, and a lot of other things hitting it. Money is usually the driving factor.

‎10-19-2017

HI Michael, no problem. We've been running ArcGIS/SDE with Oracle for about 22 years, I like our implementation, performance is great, and support has been good -- although we don't open many tickets for SDE because it's smooth sailing. Our management wants to move to SQL Server because of cost, it has something to do with we used to have a bigger Oracle footprint, then downsized, and now we can't renegotiate our contract (for some reason) and we are still paying a higher price. I'd love to hear experiences of anyone who has migrated from Oracle to SQL Server. I'm well-versed on the Oracle side, and many of the SQL Server concepts are the same, mostly reckoning back to older Oracle versions. Management was under the impression that SQL Server administration is easier, but so far my experience isn't that it's easier, it's just different. There will be growing pains. So it comes down to we are migrating not for a technical reason, but for a dollar amount reason. Sherrie

‎10-19-2017

Sorry, I did mean 10.4.1 - typo. Are you referring to the Transaction Log in SQL Server? That is not included in the size of the database file that I am describing. We allocate our T-Logs to as disk, and our database files to another disk. It is the database size that I am referring to. Your sizes look to be what I expected, a little more in SQL Server but not a great amount. That's why I think I'm doing something not quite right. I'm taking the defaults of DBTUNE. In Oracle, we use a pool of log file tables and pre-create the number that we need so that editors or viewers don't need the create table privilege. In our heavily edited database where sometimes the log pool can be exhausted, we create a nightly job that does an sdelog command line that 0's out the log pool and then recreates it at the number we need. I agree that SDE log files are better in MS SQL because they use tempdb. Do you take the defaults in DBTUNE or change something?

Online Status	Offline
Date Last Visited	‎11-11-2020 02:24 AM

My Ideas

Latest Contributions by SherrieKubis

Re: SDE in SQL Server and data owner permissions

Re: SDE in SQL Server and data owner permissions

SDE in SQL Server and data owner permissions

Re: SDE.PROCESS_INFORMATION entries persist

Re: SDE.PROCESS_INFORMATION entries persist

SDE.PROCESS_INFORMATION entries persist

Re: ORA-29904 on LOGOFF Error in executing OCIIndexClose Routine

Re: Block size for SQL Server geodatabases

Block size for SQL Server geodatabases

Re: Storage consumed in Oracle and SQL Server for geodatabase

Re: Storage consumed in Oracle and SQL Server for geodatabase

Re: Storage consumed in Oracle and SQL Server for geodatabase

Re: Storage consumed in Oracle and SQL Server for geodatabase

Re: Storage consumed in Oracle and SQL Server for geodatabase

Re: Storage consumed in Oracle and SQL Server for geodatabase

Re: Storage consumed in Oracle and SQL Server for ...

Re: arcpy delete_management error trapping when ta...