topic Re: Migrating to new SQL machine in ArcGIS Enterprise Questions

Migrating to new SQL machine

HeatherScroggins — Mon, 18 Oct 2021 14:23:26 GMT

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?

Re: Migrating to new SQL machine

ReeseFacendini — Mon, 18 Oct 2021 14:34:39 GMT

If the structure of the databases (i.e. database names, schemas, table names) will be staying the same and only the name of the actual server is changing, you only have to recreate the .sde file (same workflow as fixing the ArcMap connections) to point to that new machine then upload into ArcGIS Server Manager -> Site -> Datastores -> edit connection for SQL database. Portal and ArcGIS DataStore won't need anything updated to continue pulling the map services.

Re: Migrating to new SQL machine

ChristopherPawlyszyn — Mon, 18 Oct 2021 14:35:06 GMT

Hopefully this documentation covers what you are looking for, but let me know if you have any additional questions. ArcGIS Server is typically the only core component that would be connecting to the underlying databases directly.

Update information for a registered or managed database | Register your data with ArcGIS Server
https://enterprise.arcgis.com/en/server/latest/manage-data/windows/registering-your-data-with-arcgis-server-using-manager.htm#ESRI_SECTION1_01665D7E33384062B6A57DE89A52C2FA

Re: Migrating to new SQL machine

Brian_Wilson — Mon, 18 Oct 2021 15:12:40 GMT

We're at exactly the same stage you are at. We're migrating from a single virtual machine deployment and starting with SQL Server moving from the virtual machine to an existing enterprise grade SQL Server deployment.

My plan is to get a new database running and registered and test it for a few days until I am convinced it works, then lock the existing one and restore everything to the new one. After I am convinced the new one is functioning perfectly we shut take down the old one. It will probably sit around for a year before I finally delete it.

I've already confirmed with Esri that it's acceptable to do this both for licensing and technically.

After that I'm getting a 30 day temporary license so that the migration of Server and DataStore can go the same way, spin up the new components first and then after testing shut down the old ones.

10.9 has been sketchy for us, it's had some weird bugs bite us three times now. Our goal is to build a system divided into smaller pieces so it's easier to repair when it breaks again in the future. Right now it's all or nothing.

Re: Migrating to new SQL machine

HenryLindemann — Tue, 19 Oct 2021 05:32:00 GMT

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

Re: Migrating to new SQL machine

HeatherScroggins — Tue, 19 Oct 2021 13:10:32 GMT

@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?

Re: Migrating to new SQL machine

HenryLindemann — Tue, 19 Oct 2021 16:36:37 GMT

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