Easily Change ArcSDE Connection Parameters and Update MXD

[RichardA]

When one of the parameters in your ArcSDE connection changes (i.e. expired password, changed database server, etc.), it would be nice to have an easy way of updating an MXD with the new parameters.  If you have a lot of layers in your MXD (a map document to create a web map service could have upwards of a hundred different layers pointing to the same ArcSDE server), opening the MXD in ArcMap can take hours.  Even trying to update the connection params via "Set Data Source" in ArcCatalog sometimes hangs.

[RonnieRichards]

I agree SDE Connections need to be improved. The current procedure is difficult and does not work well for many reasons as the author explains. Why not create a data source connection manager in the MXD where we can manage the various data sources including multiple sde connections?

Many will say to use the Data Source manager tool in ArcCatalog by right clicking on the .mxd but even this tool does not work properly in 10.1. It has been around since 9.3 and still does not deal with SDE connections correctly.  

I did author a tool years ago that might help you. Several users have emailed me they use my tool to break their mxd's and fix the sources in the new mxd which is much easier than repathing. Unfortunately you would need to wait for the mxd to open to repath your data... 
http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=56FA061F-1422-2418-A076-D0B56...

[Nampreet_SinghKohli]

Would love a solution to this frustrating problem! Knowing that my data connections need to be changed, it's not acceptable to be waiting for hours to perform the simple task of changing the data sources in an mxd.

[JanZweep2]

A must have option!

In this case, it would also be nice to have the possibility to delete some layers in a mxd outside of ArcMap.

[PaulBurman]

I'm not sure this is the correct 'Idea' to attach this to, but here goes...

I would like to see as well as updating MXDs security information with details of a new/changed connection, what about the ArcGIS Server services with embedded connection details, that won't start due to a change in Password for the SDE Connection?

Can there be an automated process that updates the service security details for all services that used that now modified connection?

I hope that makes sense
Paul Burman

[Yih_TyngTan]

Allow map services to point to ArcSDE services instead of having to republish every time there is a password change. Hence changing the ArcSDE connection details would be sufficient and there is no need for republishing

[MelissaNorthey]

Just about seven years later and still a dream?  I spent a bit of time yesterday testing an ArcGIS Server service, changing the database connections behind my registered Data Stores in ArcGIS Server Manager (10.5.1) all to find out that each service hangs on to the database connections it was published with.  Our organization has 50 services by multiple publishers that need to be changed as part of a database upgrade project.  I thought for sure there had to be an easy-button for this.  This morning, ESRI Support confirmed that there is no such thing.  I'm glad we only have 50, we are a small city, I can't imagine the pain larger organizations deal with.  Sometimes upgrading your database in-place and keeping old server naming conventions are not options. 

[RonnieRichards]

Remember just changing the data store is not enough because each MXD/MSD is hosted in the ArcGIS Server publishing folders with the original workspaces. The registered data stores are just a check mark for the analysis of a service about to be published. 

There should be a database connection manager tool similar to other mapping applications so connections can be managed holistically at the map or project level and not the layer / table level which is where all wqorkspace information is stored. 

Regardless we gave up and I might suggest incorporating a DNS alias for your database server as long as the database names are the same. This way all connections point to the alias and when you are ready to change database servers just have DNS updated. This saved us for several SDE SQL Server upgrades from 2008, 2012, 2014 & 2016. 

[MelissaNorthey]

Yes, Ronnie, that's what I just got implemented with our server team.  I can't believe we've done it this long without.  I'm terribly excited that even the users' connections will not need to be changed again too!

[Arne_Gelfert]

Wanted to follow up with Ronnie Richards and Melissa Northey's last posts... No reported issues with using DNS aliasing when switching to a new DB instance? I'm going from one set of SQL Servers to a new set of SQL 2017 boxes and can't even imagine tracking down all the SDE connection files, etc. So I'm talking to DBA/Server team about setting up aliases.

[MelissaNorthey]

Arne - no issues encountered since.  I highly recommend it.  We also use this for a license server.  Now I can move the license server to another box and the only ones I have to worry with are those outside of our network that need the IP address.