Update ArcGIS Server Services after SQL Server Instance Change

[#1]

After moving your SQL Server instance to a new server, this guide will show you how to update your ArcGIS Server services. Additionally, it will demonstrate how to update file-based services that use data from a file geodatabase, such as map and image services.

Scenario

It might be necessary for you to move your SQL Server geodatabase to a different server. The 2022 instance, for instance, might be on a different server when you upgrade from SQL Server 2019 to SQL Server 2022. With a database backup and restore, moving the geodatabase is simple. However, what about updating your ArcGIS Server services? The services will use the SQL instance name if they are referencing information from your enterprise geodatabase. This can be fixed by re-mapping the data sources in your Pro Projects/MXDs, and then re-publishing your services.  But what if you have dozens, or hundreds, of services?  This can be extremely time consuming.  The below video will show how you can update all of your services with a couple of python scripts.

Note:

Before executing the scripts, be sure you have made a copy of the \arcgisserver\config-store\services and \arcgisserver\directories\arcgissystem\arcgisinput directories.

[JakeSkinner]

@ShelbyZelonisRoberson correct, that is not the case.  The easiest way to update your services is to update the existing Data Store connections, either in ArcGIS Server Manager, or in Portal, depending on how it was registered.

In either case, you would create a new SDE connection file to the new database instance and re-upload that.  Ex:

ArcGIS Server Manager

Portal

Once the database connection is updated, the services are pointing to the new SQL instance.  However, if you hover over the service workspace information, it will still state the previous instance:

You can run the script above on any machine that has ArcGIS Server or ArcGIS Pro installed to update this information.  If you update the existing data store connections, you don't have to worry about updating the previousDSID or newDSID variables, only the previousInstance and newInstance will matter in this case.

[ShelbyZelonisRoberson]

Wow. Okay! This morning we tested changing the password on one of my connections. We stopped a service published using that connection, changed the password, imported the new connection, restarted the service and it failed. I guess that's because we need the additional step of running the script. I just went from feeling defeated to optimistic! Can I give you my ticket number so you can internally update the tech support analysts? He was adamant that there is no workaround. 

[JakeSkinner]

@ShelbyZelonisRoberson 

We stopped a service published using that connection, changed the password, imported the new connection, restarted the service and it failed.

Did you update the existing connection when you stated you imported the new connection?  Or, did you create a brand new Data Store connection?

[MattiasEkström]

We just did this and all that was required was to update the data store in the portal (in our case) as Jake described above. All services using that data source now read from the new database without having to restart them or anything. Easier than I could have hoped for 😀

[ShelbyZelonisRoberson]

@JakeSkinner  we updated the existing connection to the Data Store in Server manager. 

[JakeSkinner]

@ShelbyZelonisRoberson the service should have started up successfully.  Were there any errors reported in the logs when attempting to restart the service?

[ShelbyZelonisRoberson]

@JakeSkinner Yes we got the error in this screenshot. The support analyst I was working with said that this happens in 90% of cases that he sees and his conclusion was "existing services are likely to break if any of the following parameters are changed during migration: Database machine IP address, database instance, database name, username and password."

[JakeSkinner]

@ShelbyZelonisRoberson that is strange.  What changed from the old data store connection to the new one?  i.e. username, password, instance name?

Are you connecting with Operating System Authentication?  If so, does the ArcGIS Server service account have read privileges to the feature class/?

[ShelbyZelonisRoberson]

@JakeSkinner 

We changed the password as a test. It is SQL Server authentication and yes the ArcGIS Server account has privileges to the entire database. 

[JakeSkinner]

@ShelbyZelonisRoberson I could not reproduce this.  Here are the steps I did:

1.  Create a database connection with SQL Server authenticated user called gis

2.  Registered database with Server

3.  Published a service

4.  Changed the gis user's password

5.  Created a new database connection with the updated password

6.  Updated the data store connection with the new database connection

7.  Restarted the previously published service

[HubertGimpl1]

@JakeSkinner we have to upgrade postgres single to flex, that is why we also have to change DB instance and username values.
Some of our DB connections were registered with ArcGIS Server Manager, some of them via ArcGIS Pro (Portal item).
Can Portal DB connections be changed in general - the button is just called ("Update password")?

Since the changed connection information to the new DB we configured on ArcGIS Server Manager connection and Portal item DB connection switch back to the original one, once we restart the ArcGIS Server Service, we also have to think about using your script.

Our next try is to create new DB connections referencing to the new DB and changing the reference of the services. Unfortunately we can not find any "previousDSID" in the extracted msd, neither in the Portal item DB connections, nor in the ArcGIS Server Manager DB connections.
Do we miss anything?

[ShelbyZelonisRoberson]

Hi @JakeSkinner 

I just tried the workflow again (Created new user, published service, changed password, restarted service) and it worked! So, can I assume that if I change the IP address in the connection file that it will work the same way? When does this script need to be run? 

[JakeSkinner]

@HubertGimpl1 

Our next try is to create new DB connections referencing to the new DB and changing the reference of the services. Unfortunately we can not find any "previousDSID" in the extracted msd, neither in the Portal item DB connections, nor in the ArcGIS Server Manager DB connections.
Do we miss anything?

Are you updating the existing Data Store connection (either is ArcGIS Server Manager, or through the Portal item > Update Password > Import a connection file with an updated password)?  If you do this approach, you don't have to worry about the DSID variables.  These can be ignored.

[JakeSkinner]

@ShelbyZelonisRoberson after you update the Data Store connection(s), you can execute the script.  Since you updated the existing Data Store connection, you won't have to worry about the previousDSID and newDSID variables.  You will just have to update the previousInstance and newInstance variables.  

[ShelbyZelonisRoberson]

Awesome. Thanks @JakeSkinner ! I don't have the new machine yet but once I do hopefully this transition goes smoothly! 

[HubertGimpl1]

@JakeSkinner thanks for your answer. In the meanwhile we talked to our ESRI distributor consultant. Since this is not a supported way to change db connections by changing the jsons directly, we need to fetch all mapx files on the server and republish them again after having set the new db connection in ArcGIS Pro.

[Laura]

Looking for some clarification - I have talked to support about needing to upgrade my SQL (express) so that I can upgrade to Enterprise 11.3 when needed.. They are saying I need to republish my services (100s). Is this accurate? I hope to upgrade my whole GIS Server in the coming year but currently just looking to upgrade my SQL. 

• Note: If services are published from the older instance of the geodatabase make sure to republish them from the newer instance of SQL server

[JakeSkinner]

@Laura, no, you will not have to republish your services.  If you are have a new version of SQL on another server, you can create a backup of your database(s) and restore to the new instance.  You can then follow the steps of this document to update your services.  Optionally, you don't have to create new data store connections as this document mentions.  You can update the existing connections by importing a new SDE connection file that references the new SQL instance via ArcGIS Server Manager.

[LouisDECOMBE]

Hi @Laura , I confirm what @JakeSkinner said. I’ve successfully updated the existing connection by importing my new SDE connection file, and everything is working well. 

[Scott-Shavnore]

@JakeSkinner what considerations and script modifications do you think may be needed if the ArcGIS Server config-store directory is hosted in AWS DynamoDB/S3?

[EdgarWIparraguirre]

Thank you Jake Skinner! Nice to know that I was not the only one developing a solution on the same lines as you did. Mine was triggered by the need to port AGS 10.9.x/11.x to a whole new environment, different paths, different database engines, etc.

For us the mayor issues have been caused by - no offense - esri's lack of consistency in the structure used in configuration files (inasmuch contained within a MSD file as under config-store/services/<service> or directories/arcgissystem/arcgisinput/<service>).  But not only this, the MSD's we have came along have diverse origins, like ArcGIS Desktop 10.[5-8].x (AGD), ArcGIS Pro (Pro) 2.x and 3.x, so there is quiet a variety of strings to be replaced.

Though the best way when moving to whole new environments is to publish every thing from scratch,  it  is not always feasible - mainly from the customer's side requirements/wishes/etc.  So, we have had (and sill have) to resolve to scrips lijke yours and mine.

About the lack of structure consistency (my opinion), examples are:

• AGD v101 published services sometimes do not contain (in the INSTANCE's connection string element) a reference to a DSID but direct to the a database. Of course, one can substitute the whole connection string having in mind to not include an ENCRYPTED_PASSWORD_UTF8 element and that the INSTANCE value looks like INSTANCE=&quot;DSID=<DSID_value>&quot; 
• Pro: the most annoying part here is - very probably depending on the Pro version at publishing time - to find xml files where  the content is either proper XML or just plain JSON (yes, xml as file extension but with a JSON content).
• The migration tool to be found in AGS 10.9.x (and probably AGS 11.1) does only change the provider string for a service, but it does nothing to cater for a transition to the newest structures (specially those to be found inside an MSD).
• AGD'originated v101 folders contain MXD-files, Pro 3.x's p30 Mapx ... but Pro 2.x's p20 neither mxd or aprx or mapx ...

[JakeSkinner]

@Scott-Shavnore the scripts will iterate through directories and update the necessary files, so I think the only thing that would change is how to iterate through an AWS DynamoDB/S3 bucket.

[Evan_LocalGov]

This could be a game-changer! I'll be using this exact workflow in the next couple of months.

When I first found this topic, it was a post detailing this workflow with much more information, including step-by-step instructions and screenshots (not just the video). Does anyone have a link to the original post?

[JakeSkinner]

@Evan_LocalGov the workflow changed slightly.  The previous workflow walked through registering a new Data Store connection to the new SQL instance.  This new workflow simplifies things by updating the existing Data Store connection.  The scripts have also changed as the previous ones missed updating some of the backend files.  If you get stuck, just comment on this article and I can assist.

[Joshua-Young]

@JakeSkinner does this error mean anything to you?

Traceback (most recent call last):
File "Update-Enterprise-Geodatabase-Data-Stores.py", line 313, in <module>
readJSON(filePath)
File "Update-Enterprise-Geodatabase-Data-Stores.py", line 190, in readJSON
if originalSQLInstance.lower() in filedata['featureTable']['dataConnection']['workspaceConnectionString'].lower():

KeyError: 'dataConnection'

The script was trying to read a mosaic dataset stored in a file geodatabase. Here is the connection string it was trying to read.

"workspaceConnectionString":"DATABASE=\\\\<servername>\\<sharedfolder>\\Elevation\\nationalmap\\usgs_elevation_1m.gdb","workspaceFactory":"FileGDB",

[Joshua-Young]

@JakeSkinner I got past my earlier issue only to run into another one. Is this supposed to change hosted feature services too? Our SQL Server instance and ArcGIS Data Store are on the same machine and the script changed all my hosted feature services to point to the new server running our new SQL Server geodatabases. That broke all of our hosted feature services. Thankfully I backed up the folders as clearly stated and was able to swap the old files back to fix everything.

I am on Enterprise 11.4 if that helps. 

[JakeSkinner]

@Joshua-Young thank you for bringing this to my attention!  I've updated the scripts to excluded the Hosted folder when updating services.