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, sys, zipfile, json, 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 = '09f639cc-9c27-4cf8-be12-0645876b59de' # 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:
mapDirectory = os.path.join(extractLocation, 'map')
# 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.
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.
