Update ArcGIS Server Services after SQL Server Instance Change
You may need to migrate your SQL Server geodatabase to a new server. For example, say you’re upgrading from SQL Server 2016 to SQL Server 2019, and the 2019 instance is on a new server. The geodatabase migration can be easy enough with a database backup/restore, but what about updating your ArcGIS Server services? If the services are referencing data from your enterprise geodatabase, the services will use the SQL instance name. To fix this, you can re-map the data sources in your ArcGIS Pro Projects and/or MXDs, then republish the services. However, what if you have dozens and dozens of services? This could be extremely time consuming. Instead, you can use the workflow below to update the services for you.
Scenario:
A SQL Server instance is being upgraded from 2016 to 2019. The 2016 instance resides on server PS0014736, and the new 2019 instance resides on server PS0014737. Services referencing data from the GISDATA geodatabase on PS0014736 have been published to ArcGIS Server:
Below is the workflow to follow so services do not have to be republished.
Workflow:
Before proceeding with the below steps, create a backup of ArcGIS Server, and make a copy of the \arcgiserver\config-store and \arcgisserver\directories\arcgissystem\arcgisinput directories.
1. Restore the GISDATA geodatabase to the new SQL Server 2019 instance on PS0014737.
2. Register the GISDATA geodatabase from the new SQL Server 2019 instance (PS0014737) as a Data Store
3. Log into ArcGIS Server Administrator to get the DSIDs of the previous (PS0014736) and new (PS0014737) Data Store connections
- Navigate to data > items > enterpriseDatabases
- Under Child Items, click on each child item for the data store connections:
c. Copy the ID value. This is the DSID
4. Stop the ArcGIS Server service
5. Update the # Variables in the below script
Variable Name | Description |
arcgisInputDirectory | ArcGIS Server arcgisinput directory |
arcgisConfigStoreDirectory | ArcGIS Server config-store directory |
previousInstance | Previous SQL Server Instance |
newInstance | New SQL Server Instance |
previousDSID | Previous SQL Server Instance DSID |
newDSID | New SQL Server Instance DSID |
tempDirectory | Temporary Director to write files to |
import arcpy, os, zipfile, shutil, re
from zipfile import ZipFile
# Variables
arcgisInputDirectory = r'C:\arcgisserver\directories\arcgissystem\arcgisinput' # ArcGIS Server arcgisinput directory
arcgisConfigStoreDirectory = r'C:\arcgisserver\config-store\services' # ArcGIS Server config-store directory
previousInstance = 'PS0021465' # Previous SQL Server instance
newInstance = 'PS0021475' # New SQL Server Instance
# named instance would be r'PS0021465\\\\gis'
previousDSID = '2cf24591-2e4a-4877-aac0-25ac4c2c0fd4' # Previous SQL Server Instance DSID
newDSID = 'c607988a-7494-472e-a590-26c200c7d2b8' # New SQL Server Instance DSID
tempDirectory = arcpy.env.scratchFolder # Temp Directory
# Function to zip MSD
def zipMSD(extractLocation, tempDirectory):
'''Function to zip directory to create new MSD'''
newMSDFile = os.path.join(tempDirectory, f"{msdFileName}")
with zipfile.ZipFile(newMSDFile, 'w', zipfile.ZIP_DEFLATED) as msdzip:
length = len(extractLocation)
for root, dirs, files in os.walk(extractLocation):
folder = root[length:] # path without "parent"
for file in files:
msdzip.write(os.path.join(root, file), os.path.join(folder, file))
return newMSDFile
# Function to update MSD
def updateMSD(service, xmlJSONFile):
servicesList.append(service)
# Open XML/JSON File and replace DSID
print("\tReading file and updating")
with open(xmlJSONFile, 'r') as file:
filedata = file.read()
filedata = filedata.replace(previousDSID, newDSID)
if re.search(previousInstance, filedata, re.IGNORECASE):
filedata = re.sub(rf'\b{previousInstance}\b', newInstance, filedata, flags=re.IGNORECASE)
# Write to new XML/JSON File
print("\tWriting new file")
with open(xmlJSONFile, 'w') as file:
file.write(filedata)
# Zip directory to create updated MSD
print("\tCreating new updated MSD file")
zipMSD(extractLocation, tempDirectory)
# Replace original MSD file with updated MSD
print("\tReplacing original MSD file")
msdFileLocation = os.path.dirname(msdFile)
shutil.move(os.path.join(tempDirectory, msdFileName),
os.path.join(msdFileLocation, msdFileName))
# Create list of services that are updated
servicesList = []
# Iterate arcgisinput directory to update MSD file
for root, dirs, files in os.walk(arcgisInputDirectory):
for name in files:
if '.msd' in name:
msdFileName = name
service = root.split('.')[0].split('\\')[-1]
msdFile = os.path.join(root, name)
extractLocation = os.path.join(tempDirectory, service)
os.mkdir(extractLocation)
with ZipFile(msdFile, 'r') as zipObj:
# Extract all the contents of MSD file in temp directory
print(f'\nExtracting {service} file to temp directory')
zipObj.extractall(extractLocation)
# If published from ArcGIS Pro
if 'p20' in root or 'p30' in root:
if os.path.isdir(os.path.join(extractLocation, 'map')):
mapDirectory = os.path.join(extractLocation, 'map')
else:
mapDirectory = os.path.join(extractLocation, 'layers')
# Update XML File
for root, dirs, files in os.walk(mapDirectory):
for name in files:
if 'map.xml' not in name and '.json' not in name:
xmlFile = os.path.join(root, name)
updateMSD(service, xmlFile)
elif '.json' in name:
for root, dirs, files in os.walk(extractLocation):
for name in files:
if '.json' in name:
jsonFile = os.path.join(root, name)
updateMSD(service, jsonFile)
# If published from ArcMap
elif 'v101' in root:
mapDirectory = os.path.join(extractLocation, 'layers')
# Update XML File
for root, dirs, files in os.walk(mapDirectory):
for name in files:
if 'layers.xml' not in name:
servicesList.append(service)
xmlFile = os.path.join(root, name)
# Open XML File and replace DSID
print("\tReading XML file and updating")
with open(xmlFile, 'r') as file:
filedata = file.read()
filedata = filedata.replace(previousDSID, newDSID)
# Write to new XML File
print("\tWriting XML file")
with open(xmlFile, 'w') as file:
file.write(filedata)
# Zip directory to create updated MSD
print("\tCreating new updated MSD file")
zipMSD(extractLocation, tempDirectory)
# Replace original MSD file with updated MSD
print("\tReplacing original MSD file")
msdFileLocation = os.path.dirname(msdFile)
shutil.move(os.path.join(tempDirectory, msdFileName),
os.path.join(msdFileLocation, msdFileName))
# Remove extracted content in temp directory
print("Removing extracted content")
shutil.rmtree(extractLocation)
# Update manifest.json
for service in servicesList:
for root, dirs, files in os.walk(arcgisConfigStoreDirectory):
for dir in dirs:
if dir in [f'{service}.MapServer', f'{service}.FeatureServer']:
serviceDirectory = os.path.join(root, dir)
for root, dirs, files in os.walk(serviceDirectory):
for name in files:
# Copy manifest JSON file
if name == 'manifest.json':
manifestJSON = os.path.join(root, name)
copyManifestJSON = os.path.join(tempDirectory, name)
shutil.copyfile(manifestJSON, copyManifestJSON)
# Read JSON file
with open(copyManifestJSON) as jsonFile:
data = jsonFile.read()
# If previous instance found in json, replace with new instance
if re.search(previousInstance, data, re.IGNORECASE):
print(f"\nUpdating {service} manifest.json with new instance info")
data = re.sub(rf'\b{previousInstance}\b', newInstance, data, flags=re.IGNORECASE)
with open(copyManifestJSON, 'w') as jsonFile:
jsonFile.write(data)
print("\tReplacing original manifest.json")
shutil.copyfile(copyManifestJSON, manifestJSON)
# Remove manifest.json in temp directory
os.remove(copyManifestJSON)
print("Finished")
6. Execute the script
7. After the script executes, start the ArcGIS Server service
Results
The services should now reference the new SQL Server instance (PS0014737):
To further demonstrate the service updated, parcels were deleted (northeast section) in the new instance before the script was executed. Shown below is a screen shot of the service before the script was executed and afterwards:
Service referencing original instance (PS0014736):
Service referencing new instance (PS0014737):
Video
A video of the workflow can be found at the following link.
Notes
It's not necessary to create a new Data Store connection. You can easily update an existing Data Store connection within ArcGIS Server Manager, or the Settings tab of the Data Store item within Portal if registered via ArcGIS Pro. However, the Service Workspaces will still show the previous instance information until this script is executed. For the newDSID variable, you would simply specify the same value as the previousDSID variable.
I ran through it in a debugger and tested.
Pretty interesting things going on down deep in the arcgisinput folder.
The re.search at line 127 and the re.sub would be a big problem for me since the current deployment is single machine and SQL Server runs in the same container. So replacing "cc-gis" with "cc-newserver" will also replace all strings referencing the other components, breaking everything.
Our new deployment will have "cc-gis" and other machines like "cc-gisserver" and "cc-gisdatastore".
I would need to use a more complete regular expression to avoid a problem. I have not found any manifest files directly referencing the database server so I cannot construct a proper regex. I think it might be using "sde.default" and mapping in the server elsewhere??? I do see references in the MSD files to the ID.
The Esri support guy told me I can change my SQL Server instance by creating an SDE connection doc that points at the new server and then import it on this screen. I will be trying that soon. What could possibly go wrong? I am thinking that if this updates the connection without changing the ID then none of the MSD files should need to change.
I would have made a terrible open heart surgeon. "Wait wait, we need to make a full back up first, and then we need to stop the patient while we do this! Let's get a snapshot and have a virtual copy of the patient ready in case this one fails." (I recall there is a John Varley story along these lines out there.)
Hi Jake,
One potential cause of our failure might be concerning the correct DSID of the previous Data Store, as it seems I have two: I'm assuming one is from before we upgraded from our old version of Enterprise (10.6.1), as it has no corresponding Data Store Item in Portal as layers were published from ArcMap; and one from afterward (post 10.7.1), which has a Data Store Item, evidently created once layers started to be published from ArcGIS Pro. How do we know which one is the correct one to use with the script? Does this imply that when layers are published, they are encoded with one DSID or the other? If so, can I run the script once for each DSID, or will that have a deleterious effect?
Thank you,
Greg
@AbiDhakal try the steps outlined in this document first. If for some reason it does not work, restore your snapshot. You can then update the existing data store connection with a SDE connection file pointing to the new SQL instance:
Your services will redirect to this new SQL instance at this point. However, updating the existing data store connection will not update the workspace information when you click on this option for the service. Though, it is in fact pointing to the new SQL instance.
Hi @JakeSkinner
Thank you for your excellent work. It's been very helpful in understanding what needs to be done.
In my case, I have many databases registered in ArcGIS Server, and I don't really want to create a new one for the new instance. I plan to simply update them using the SDE file. However, from what I understand, while the map services will continue to function, the information displayed in each map service will reflect the old data. This isn't too bad, right?
That being said, is there a method or script available that allows me to update the information in each map service after updating the database (without creating a new one), ensuring that the IDs remain unchanged?
Thank you for your advice.
@LouisDECOMBE for the script variables, you can update the previousInstance and newInstance. For the newDSID variable, simply specify this to the be the same as the previousDSID variable.
I tried, but it seems there was no change in the manifest or MSD file, even though AGS was stopped.
I have the new manifest in the scratch folder, but the information inside is the same as before.
Any ideas?
Thank you. I think I see where my problem is.
In the image below, the red boxes indicate what the script manages to update. The instance name is different from the SQL server name (blue box). Thus, the script only updates the parts outlined in red. Maybe I need to adapt it to also update the parts in blue. If you have any ideas, I’m all ears.
Additionally, the files in the config-store directory are properly updated. However, in the arcgisinput folder, only the p20/name_service.msd files are updated, not the manifest files. Could this be an issue?
In any case, thank you for your prompt response each time.
Have a great day!
@LouisDECOMBE I'm having trouble reproducing this with the latest script. Are you seeing the similar print statements in your IDE console when you execute the script:
