topic Re: Tips for migrating geodatabase to new SQL Instance in ArcGIS Enterprise Questions

Tips for migrating geodatabase to new SQL Instance

Jay_Gregory — Mon, 04 Jan 2016 16:10:46 GMT

We are doing some SQL consolidation, and are migrating our current enterprise geodatabase (the current data store for Server and Portal for ArcGIS 10.3) to a different SQL Instance. Our DBO is comfortable migrating the database, users, etc. to a different instance under the same database name and setting, but I am worried about the ArcGIS for Server services, since the services seemed to be tied to the SQL Server / Instance. Am I going to have to change all the data sources in my Map Documents and republish services once the database is migrated?

Thanks!

Re: Tips for migrating geodatabase to new SQL Instance

JakeSkinner — Mon, 04 Jan 2016 16:21:44 GMT

Hi Jay,

You should just have to change the instance name in the Database Connection file. Your map documents will not have any broken data sources, and your map services will read the data correctly.

One thing I would double check is that the Data Store validates successfully to the new instance.

Re: Tips for migrating geodatabase to new SQL Instance

Jay_Gregory — Mon, 04 Jan 2016 16:31:08 GMT

Thanks Jake - I'll give it a try and see if that works. The reason I'm concerned is because if I look in ArcGIS Server Manager at the Service Workspace of one of my map services, I can see it is tied to the SQL Instance (see attached screenshot showing SQL Server name / instance name). I can change the instance name in the Database Connection file in ArcCatalog, but I don't see how that would change the setting in ArcGIS for Server without republishing the services via the map documents.

Thanks!

Re: Tips for migrating geodatabase to new SQL Instance

JoeHershman — Fri, 05 Apr 2019 15:30:10 GMT

Jay Gregory‌ did the process described above work? We need to do the same task and wanted to know if it is as simple as described. Once a service is published I don't think it still references the original map document so I question the Jake Skinner solution would work

Thanks

-Joe

Re: Tips for migrating geodatabase to new SQL Instance

JakeSkinner — Sun, 12 Dec 2021 04:49:10 GMT

I don't think my initial solution will work with just changing the instance in the database connection file. I believe the database connection information is stored within the layer within the MXD. You could use the following script to update your MXDs with the new database connection to the new SQL Server instance:

import arcpy, os
from arcpy import env
from arcpy import mapping

# Parent directory to MXDs.  Script will iterate through Child directories
env.workspace = r"D:\Temp\Python\MXDs"

# New Enterprise Geodatabase
newDataSource = r"Database Connections\SQLSERVER - DBO@GIS.sde"

env.overwriteOutput = True

for (path, dirs, files) in os.walk(env.workspace):
    for file in files:
        if ".mxd" in file.lower():
            mxd = os.path.join(path, file)
            print(mxd + " is being processed")
            mxd = mapping.MapDocument(mxd)
            for df in mapping.ListDataFrames(mxd, "*"):
                for lyr in mapping.ListLayers(mxd, "*", df):
                    try:
                        if lyr.supports("DATASOURCE"):
                            if lyr.serviceProperties['Database']:
                                print("\tUpdating " + str(lyr))
                                lyr.replaceDataSource(newDataSource, "SDE_WORKSPACE", "")
                                print("\tSuccessfully updated data source")
                    except AttributeError:
                        pass
            mxd.save()

del mxd‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The services will have to be re-published afterwards as well.

Re: Tips for migrating geodatabase to new SQL Instance

JoeHershman — Fri, 05 Apr 2019 16:05:07 GMT

I already have a script that changes the maps data source of maps, so that parts not an issue. Even have a script to republish the services. But what we really were hoping is to be able to make the database move without the need to republish services.

One big issue is that in Portal when you do a republish of a service the item id changes (so it is actually creating a new Portal Item, event though the service endpoint doesn't change). So Web Maps will break because they reference the Portal Item not just the service endpoint, we have not tried in 10.7 so maybe that is no longer the case, but in previous versions that is what we saw happen. That means all our web maps need to be re-created, which would be a lot of work

Thanks

-Joe

Re: Tips for migrating geodatabase to new SQL Instance

Jay_Gregory — Fri, 05 Apr 2019 16:05:25 GMT

At this point I kind of forget what I did. I might have just republished everything, although at this point I wouldn't be able to do that because it would generate new item ids for all my Portal content (we had JUST started using Portal three years ago so it wasn't as much of an issue). I could involve Esri support if possible.

Re: Tips for migrating geodatabase to new SQL Instance

JakeSkinner — Fri, 05 Apr 2019 16:08:04 GMT

One big issue is that in Portal when you do a republish of a service the item id changes

This should not occur if you are overwriting the service when you republish. What version of Portal and Server are you using?

Re: Tips for migrating geodatabase to new SQL Instance

JoeHershman — Fri, 05 Apr 2019 16:58:17 GMT

I was incorrect in my description after discussing with someone involved in the process. If during a republish you change a property of the service it would seem it creates a new item. What does occur on republish is that settings get overwritten. So if you have configured the popups this all needs to be redone. We do have a tool to make these changes external to Portal and update the items, however, it will be a significant pain to do with all our web maps

Re: Tips for migrating geodatabase to new SQL Instance

BillFox — Fri, 05 Apr 2019 18:40:35 GMT

AGOL vs Portal but this issue may be involved.

https://community.esri.com/thread/219119-arcgis-online-overwrite-feature-service-from-arcgis-enterprise-and-new-layer-does-not-show-up-in-agol-web-map