topic Re: How to enable roll-back on SDE transactions in Data Management Questions

How to enable roll-back on SDE transactions

SebastianKrings — Tue, 15 May 2012 11:04:29 GMT

Hello,

I used Arccatalog to transfer Data from a FileGeodatabase in my network share to an ArcSDE Database (MS SQL Server) on another machine in the same network.
While the transfer was proceeded the infrastructure brokes and the connections were lost.
After recovering the infrastructure in ArcCatalog again 80% of the datasets, featureclasses and raster datasets were stored. One was shown in ArcCatlog but it was unable to open it with error ("Failed to open dataset"). I tried to delete it but another error tells me i cannot be deleted ("Failed to delete selected object(s)").

In the passt I still had this problem using a FileGeodatabase. Within an dataimport my computer went down. A dataset was stored and shown but I was not able to delete it.

I found threads like this one:
http://forums.esri.com/Thread.asp?c=2&f=59&t=116222

To delete rows and tables manually was the "solution" I used in the past were it happens on my own testsystem. But this is not a workaround for productive-systems.

Unfortunetely I can not find any useful information (from ESRI (documentation)) how to handle such problems (e.g. any recovery or roll-back mechanisms) and how to implement them in ArcSDE or ArcCatalog(?).
you should also consider that when the database machine went down immediately (e.g. due to a hardware-problem) those mechanisms also shall work when restarting the database.

Any help, advice, information and mybe field reports were grateful.

Thanks.

Re: How to enable roll-back on SDE transactions

VinceAngelo — Tue, 15 May 2012 11:34:49 GMT

The term "rollback" refers to transactional operations, but bulk-copying is NOT done in
a transactional context (it would be orders of magnitude slower if it were). If you lose
hardware during a transfer, it would be wise to start the entire copy over again (from
the state your production database was in during the backup snapshot you did just
before the bulk load).

Manually editing rows in the metadata tables is only supported in the context of being
asked to do so by Tech Support staff. You really ought to open an Tech Support incident
after catastrophic failure like this, since there are millions of failure modes, and it is not
possible to document everything that might happen.

- V

Re: How to enable roll-back on SDE transactions

SebastianKrings — Tue, 15 May 2012 11:42:46 GMT

Hey, mich thanks for this very fast reply (+1 point).

Well, in my case it still was abnother test-system but we are going for a productive one soon.

So you say when making bulk imports I need to backup the database manually first?
The problem I see is that every user with connection to the database is able to create data(sets) and if he fails because his machine crashes the whole system is affected by orphan-files. And also it is not possible to recopy this data due to errors.
But this way the system I think is weak versus careless users.

so are there ways to pretend users from making risky operations or to notify them to force them to make a database backup? Are there ways to make such a backup through ArcDesktop without remote-desktop-connections to the database machine?

Thanks.

Re: How to enable roll-back on SDE transactions

DeminHu — Tue, 15 May 2012 11:47:36 GMT

probably you could consider : DBMS backup and restore, or mirroring etc.

Re: How to enable roll-back on SDE transactions

VinceAngelo — Tue, 15 May 2012 12:12:43 GMT

Bulk loading isn't generally risky, and it still occurs in "transactions" (as all database operation do),
but it isn't possible to maintain the same level of multi-table referential integrity during a bulk load.
Generally speaking, bulk loads shouldn't be applied casually to a production system (*nothing* should
be done casually in a production system). It wouldn't hurt to make sure your RDBMS has a checkpoint
applied before starting a bulk editing session, though whether that requires a full backup is dependent
on the RDBMS (and the context and scale of the bulk edits).

- V

Re: How to enable roll-back on SDE transactions

SebastianKrings — Tue, 15 May 2012 13:01:47 GMT

So but I cannot avoid someone to drag and drop Geodata from any source into the database. (or did I missunderstand you "bulk loads shouldn't be applied casually to a production system "). Weren't this a feature which ESRI had to take care of by offering a function or workaround to achieve this?

Well my RDBMS is MS SQL Server 2008 R2. Since theres no security-feature by ArcGIS I have to implement within my RDBMS any option making snapshots of the database before any bulk will take place. Would you say this is the best workaround for preventing the database for such failures?

Thanks.

Re: How to enable roll-back on SDE transactions

VinceAngelo — Tue, 15 May 2012 14:20:27 GMT

Maybe we have a different working definition of Production Database -- As a production
admin, I don't give users the privileges neccessary to load data casually (all data is
owned by restricted set of "ownership" accounts, and read-mostly users access/edit
with their own accounts [if they're allowed to edit at all]). Staging databases are
a different issue, but if they get hosed, they're still not the production database.

- V

Re: How to enable roll-back on SDE transactions

SebastianKrings — Tue, 15 May 2012 15:17:57 GMT

Hey no I dont think we have a that different view of prodiction systems.

the 1st problem is, that by default everyone who can access a sde is able to create datasets (installation give public role such rights). By rightclicking in that dataset I can set privilegs as I am the owner and give me write settings. that way everyone is able to create data. (I still claimed about that in another thread).

the 2nd problem is that not every user who need write right (because he has some functions where he need those rights) but this user is not automatically as smart as a databaseadmin and does not have that knowledge. And by the way, the easiest way to copy data from a local fgdb to an sde is to drag and drop the data.

But when you say yes to "have a look for mechanisms on SQL server and not on ArcDesktop because there aren't any" then my question is mostly answered.

thanks to all.

Re: How to enable roll-back on SDE transactions

VinceAngelo — Tue, 15 May 2012 15:43:57 GMT

Feature datasets are not database objects that require "CREATE" permissions, just "INSERT"
(which can be revoked). I guarantee that my non-privileged Enterprise ArcSDE users can't
create feature classes on a whim, either inside or outside a feature dataset.

- V

Re: How to enable roll-back on SDE transactions

SebastianKrings — Wed, 16 May 2012 06:52:31 GMT

do you have a description for that where and what privilegs to revoke?
I am afraid of anything is not functioning when revoking privilegs because the sde user needed them at installation so public were granted that rights during the installation but they were not revoked after that. And what happens when installing a servicepack or running postinstallation again.

well this discussion may should be done in the right thread:
http://forums.arcgis.com/threads/49586-How-to-pretend-DataViewers-of-editing-deleting-Database-Contents

thanks

Re: How to enable roll-back on SDE transactions

VinceAngelo — Wed, 16 May 2012 10:27:43 GMT

If you are talking about the the 'sde' user, then that is your problem right there. The 'sde' user
should not ever own spatial data. It exists solely for administration of the instance (and the
password to it should be tightly controlled).

All other users (data owners or data viewers) should be granted the minimum privileges necessary
to what they need to do (I can't tell you exactly what because I don't have access to my SQL-Server
box right now, and those accesses change by release anyway). After 23 years as a SQL-Server
administrator (and Oracle, and Informix, DB2, PostreSQL, and a bunch of very odd RDBMSes), I no
longer need to follow rote directions on configuration, but I can't explain what I'm doing either.
An administration class would be a lot more beneficial than paying to have me try to brain-dump
the jumble of cross-linked lessons learned on database security.

- V