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.