topic Re: Copy ArcSDE Enterprise Geodatabase in Data Management Questions

Copy ArcSDE Enterprise Geodatabase

LeoDonahue — Mon, 15 Apr 2013 14:20:58 GMT

Is it possible to make a copy of an enterprise ArcSDE 10.0 geodatabase on SQL Server 2008 R2 and move it to another instance of SQL Server 2008 R2 and rename the copy to some other name? Using only SQL - no Arc products?

Re: Copy ArcSDE Enterprise Geodatabase

VinceAngelo — Mon, 15 Apr 2013 15:00:46 GMT

No. The "rename" part is where the process would fail.

- V

Re: Copy ArcSDE Enterprise Geodatabase

LeoDonahue — Mon, 15 Apr 2013 15:39:34 GMT

That is what I thought.

So if there are some people trying to hack the sde geodatabase by replacing all occurrences of the database name with a new name (simply by doing a find/replace in the triggers,tables, etc), that will still not work?

I'm about to feel somewhat vindicated here, but will hold off on my joy a few more moments...

Re: Copy ArcSDE Enterprise Geodatabase

VinceAngelo — Mon, 15 Apr 2013 15:56:47 GMT

While it's possible, rewriting the majority of the triggers in a database is not
a supportable activity. I certainly wouldn't attempt it, not when you can use
a named instance with the existing database name.

- V

Re: Copy ArcSDE Enterprise Geodatabase

LeoDonahue — Mon, 15 Apr 2013 17:28:30 GMT

Well, they did it anyway.

They didn't like the database name and wanted it named: databasenamedev

so it is going to work for them... ok... joy has left.

Re: Copy ArcSDE Enterprise Geodatabase

VinceAngelo — Mon, 15 Apr 2013 18:09:34 GMT

Cheer up -- it will likely fail in some inexplicable way down the road (or maybe
sooner, it they didn't take XML update into account 😉

In my book, wanting to change the database name is equivalent to wanting
to start database setup all over again. Doing anything else is asking for
system failure.

- V

Re: Copy ArcSDE Enterprise Geodatabase

LeoDonahue — Mon, 15 Apr 2013 18:17:54 GMT

I offered to install another sde instance for them, with a dev database name. They said they didn't need my help, so it's out of my hands. I wonder if this application they are trying to replicate in a development environment uses app server connections... hmm..

Re: Copy ArcSDE Enterprise Geodatabase

LeoDonahue — Mon, 15 Apr 2013 20:21:01 GMT

Hold on a second.

Even if the client makes a direct connection to this copy, without ArcSDE being installed, what is the point of the "service" property using: sde:sqlserver:databasename

Doesn't that still require ArcSDE 10.0 to be installed in order to use that?

Re: Copy ArcSDE Enterprise Geodatabase

VinceAngelo — Mon, 15 Apr 2013 21:00:01 GMT

Both flavors of access to the geodatabase require the same things to establish
connection. If the metadata in the SDE (or DBO) user tables no longer point
to tables, then there will be trouble, but no, ArcSDE (the software) isn't required
to be installed, because Direct Connect *is* an ArcSDE server.

- V

Re: Copy ArcSDE Enterprise Geodatabase

LeoDonahue — Tue, 16 Apr 2013 15:46:14 GMT

Connecting to this "backup, rename and restore" copy of SDE, via direct connect and without installing SDE on this SQL Server, fails.
<insert> I KNEW IT!! I TOLD THEM IT WOULDN'T WORK!! Muahhaaahaaaaa! lol. </insert>

Now that this is out of my system...

The error message I got in ArcCatalog making a direct connection to this "backup, rename, and restore" copy of SDE.

Failed to connect to the specified server.
This release of the GeoDatabase is either invalid or out of date.
DBMS table not found [Microsoft SQL Server Native Client 10.0: Invalid object name 'databasename.sde.GDB_Release'.][databasename.sde.GDB_Release]

Client and server are both at 10.0, the production geodatabase was not upgraded, I checked.

I can not say whether this is related to not having ArcSDE installed on this instance of SQL Server or whether the sde login needs to be resynced after the backup and restore.

I can say that the people administering this SQL Server are pointing to ESRI as the problem because they cannot log into the renamed geodatabase using the logins/passwords that the original production sde geodatabase had and they think that ESRI security is causing the problem. I don't even know how to respond to that.

Re: Copy ArcSDE Enterprise Geodatabase

VinceAngelo — Tue, 16 Apr 2013 16:31:03 GMT

Even after rewriting all the triggers, there's still two possible trip points:
Microsoft's security model (users, schemas, & logins and their ids)
The contents of the SDE.sde_* and SDE.gdb_* tables.

Renaming databases remains very unsupported.

- V

Re: Copy ArcSDE Enterprise Geodatabase

MarcoBoeringa — Tue, 16 Apr 2013 18:09:21 GMT

I can say that the people administering this SQL Server are pointing to ESRI as the problem because they cannot log into the renamed geodatabase using the logins/passwords that the original production sde geodatabase had and they think that ESRI security is causing the problem. I don't even know how to respond to that.

ESRI geodatabase security = RDBMS security = (most likely in case of SQL Server) Windows Authentication

So if you can't login after recreating your database and nothing else changed, you've taken the wrong path (as Vince already told you: you can't rename the database with an established geodatabase in it).

The path you probably should take if you really need to "rename" (note the quotes):

1) - Create a new database using the new name in your instance (or if desired an entire new instance)
2) - Re-create any database users / roles etc. of the old database you wished to "rename" in the new database
3) - Use the "Enable enterprise geodatabase" tool to create a new ArcSDE Repository in the new geodatabase (Alternatively you could have used the "Create enterprise geodatabase" tool to do steps 1 and 3)
4) - Copy over any datasets (Feature Datasets / Feature Classes) from the old geodatabase to the new one, or use One-Way Geodatabase Replication to do it. There may be some headaches here, especially if there are outstanding versions and editing is going on. See also Understanding distributed data

Vince can probably give you more details and correct/supplement what I wrote.