Publish service definition with database connection

[ChrisAdams]

Is it possible to define the database connection at the time of publishing a service definition? Here is the desired workflow:

1. Create a service definition through ArcGIS Desktop with 'feature access' capability. 

2. Select 'no available connection', 'include data in service definition when publishing'

3. Move the service definition to another environment

4. Publish the service definition AND specify the database connection.

What is happening is that the data is being copied into the managed database. The ideal scenario is that it is copied into another referenced SQL Server database. Is this possible?

[ScottFierro2]

Just trying to follow this scenario. So build the service based off tables in SQL Instance A. Publish a service but have edits that are occurring via published service occur against SQL Instance B?

Any reason not to simple migrate a copy of the table(s) from SQL Instance A over to SQL Instance B. Build service in ArcMap based off connections to SQL Instance B and then be done?

Only reason I can think of for attempting your workflow is because SQL Instance A and SQL Instance B reside on 2 separate LAN's that have no ability to communicate thus preventing the pre-staging of the tables. It still almost sounds like running a SQL DB Backup and then loading the backup to the new location is much faster/easier/more reliable even if you had to build a new DB instance just to migrate a single table into, backup and load to new location over trying to migrate an ESRI .sd file.

[ChrisAdams]

You are correct that the instances reside on separate LANs. I am migrating a copy of the data and then publishing the MXD but was hoping to capture the service definition settings as well to reduce the chances of human error when publishing on the second environment.

[Anonymous User]

Hi Chris,

Is it possible to do that ?
if yes so how can we set data source of it 
can you please share more information.

[ScottFierro2]

I see. It's an Interesting use case and can't say I have had a need or heard of anyone attempting it before. Your logic behind it is sound I just am not sure what to expect at publishing on the other end because I would expect at publishing it would error out due to no database details and it wouldn't prompt for a connection.

If it did let you publish then it might prompt for the connection details but worse case you could always dive into the published directory folders and re-map the sources as needed.

[JonathanQuinn]

Take a look at the "The publisher's machine and the server are working with different databases" section within the documentation on registering your data with ArcGIS Server.  What will happen in that case is that the "publisher", (ArcMap), will be accessing one database through one database connection, but once the data is published, the connection string will be "swizzled" so the server, (ArcGIS Server), can access the data through a different database and another database connection.  Make sure you've already copied the data into that directory.  Another option is to register the database on the Server end as the "managed" data store.  Any data from the publisher that's published with feature access will be copied into the database registered as the managed data store.

[ChrisAdams]

As the "server" database connection is not reachable from the machine it is being published from because it is on a different LAN, it is not possible to set this connection. It would be good to either allow "server" database connections to be set even if the server is not reachable, thus allowing the connections to be swizzled once the mxds and data have been copied. Going back to my original post, I would like to set the database connection at time of publishing the SD with the data bundled inside it, or swizzle the connections at time of publishing.

[JonathanQuinn]

Chris, that's exactly what the section on "The publisher's machine and the server are working with different databases" is describing.  In that scenario, the Publisher's connection is not accessible by the Server and the Server connection is not accessible by the Publisher.  This is primarily used in Amazon environments where an on-premises geodatabase is not accessible by a Server deployment in the cloud.  Different database connections are used for ArcMap, (publisher), and the Server and the data is copied to the Server before hand manually, however possible, or even during the publishing process by packaging it up within the SD file.  When you use two different connections, the connection strings are swizzled once the SD file is unpacked on the Server end.

[ChrisAdams · Accepted solution]

There is a caveat to the section on "The publisher's machine and the server are working with different databases". You must publish a service definition through ArcGIS Server Manager on the Server machine. I was initially copying map packages and sd files across from ArcMap on my first environment, to ArcMap on my second disconnected environoment. Trying to publish from ArcMap will not work because you cannot set the publisher database connection for the swizzling to take place. ArcMap tries to validate both the publisher and server database connections and will fail to validate the publisher connection because the database is not reachable. Thanks to Jonathan Quinn for putting me right on this.

To answer my original question, it is not possible to do this but the easiest solution is to bundle the data in the sd files and push the data into a managed database.

[JonathanQuinn]

Just to clear up any confusion, you can publish a service definition through ArcGIS Server Manager on any machine, as long as you can actually reach the Manager page.  It doesn't have to be on the Server machine itself.

In Chris' environment, ArcMap on his first machine wasn't able to connect to Server on his second disconnected environment, so he wouldn't be able to publish directly from ArcMap on his first environment to Server in his second.  Additionally, ArcMap on his second machine couldn't connect to the database in his first environment, so publishing from ArcMap in general wasn't possible.

In regards to the connections, ArcMap and Server will both try to validate the database connection if the same connection is used for both.  On the other hand, ArcMap will try to validate the Publisher connection and Server will try to validate the Server connection if different connections are used, but ArcMap won't try to validate the Server connection and vice versa.