Migrating to new SQL machine

Question

Our IT team is in the process of upgrading/migrating our current SQL databases to a new machine with a completely new name. Since the machine is going to have a new name, all of our GIS connections are going to break. I'm not so worried about ArcDesktop, that's fairly easy to fix (if just a bit tedious). I'm really worried about our ArcGIS Enterprise deployment.

We have one Enterprise deployment that includes Server, Portal, and Datastore. And then we have one stand-alone ArcGIS Server. All are 10.7.1.

I've tried to search and I guess I'm just not using the correct keywords because I can't find the answers I need. What do I need to change on the backend of ArcGIS Server, Portal, and Datastore once the migration has happened to make sure they are pointed to the correct SQL machine and data?

HenryLindemann · Answer

Hi @HeatherScroggins, ok it is what it is :) .

so I created to Microsoft SQL 2016 instances and did the following tests

test service is working

Here you see the old connection

change data source in ArcGIS server shut down the old SQL, tested and the records pulled thru.

Here is the new connection

The records are pulling thru

But here is the crux the metadata does not update and you have broken mxd in mapx files in your arcgisinput folder (which if your server crashes is often used to rebuild the services or say you left and the new person needs to work with the data) for example if you work for a large company with a 100 SQL databases and say your datastore connection gets deleted and this is two years down the line with a new GIS Practitioner how will this person fix the problem?

I hope this gives you better insights and welcome further discussion on this:

Kind regards

Henry

CityofRichardsonGIS · Answer

@HenryLindemann unfortunately we can not keep the same name. They are merging several smaller SQL machines into one larger one, so a name change is inevitable.

But I'm confused now. Both @ReeseFacendini and @ChristopherPawlyszyn say that just changing the .sde file of the registered Data Stores on ArcGIS Server is enough to get everything working again. But @HenryLindemann is saying that it isn't and that I need to be worried every single .mxd and .aprx that was used to publish services.

Which is the correct answer?

HenryLindemann · Answer

Hi@HeatherScroggins , so try and convince the IT to maintain the name please because it will be a major headache to fix.

on desktop you are sorted and I think on server manager you are sorted just edit the connection and import the new connection

the problem is the services the "mxd and mapx (ArcGIS Pro)" that little database next to your service idicatd the data source so the change will break all you services.

You can run the script below on the server it will write the required metadata into a csv you look for onServerConnectionString if it points to your db then you will need to reconfigure and republish this mxd or mapx service.

the mxd ... will be located here for arcgis pro here e.g. where you installed ArcGIS Server "D OR C OR where you installed it :\arcgisserver\directories\arcgissystem\arcgisinput\province_lds.MapServer\extracted\p20\Updated_province_lds.mapx"

ArcGIS Desktop
"D:\arcgisserver\directories\arcgissystem\arcgisinput\province_lds.MapServer\extracted\v101\Updated_province_lds.mxd"

you pull out these services fix the data source and override the service.

let me know if you have a million services I have another script that can rewrite mxd data sources.

Hope it Helps 
Henry

arcgis_enterprise_audit.zip