Select to view content in your preferred language

Migrating to new SQL machine

3166
6
10-18-2021 07:23 AM
HeatherScroggins
Frequent Contributor

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?

0 Kudos
6 Replies
ReeseFacendini
Esri Regular Contributor

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.

0 Kudos
ChristopherPawlyszyn
Esri Contributor

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...


-- Chris Pawlyszyn
0 Kudos
Brian_Wilson
Honored Contributor

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.

0 Kudos
HenryLindemann
Esri Contributor

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

HenryLindemann_0-1634620809848.png

 


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.

HenryLindemann_1-1634620874872.png

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

 

0 Kudos
HeatherScroggins
Frequent Contributor

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

0 Kudos
HenryLindemann
Esri Contributor

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

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

HenryLindemann_0-1634658923898.png

test service is working 

HenryLindemann_2-1634659120310.png

 

HenryLindemann_1-1634659085994.png

Here you see the old connection

HenryLindemann_3-1634659168589.png

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

Here is the new connection

HenryLindemann_4-1634659223953.png

 

 

HenryLindemann_5-1634659237895.png

 

HenryLindemann_6-1634659284374.png

 

The records are pulling thru

 

HenryLindemann_7-1634659875406.png

 

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?

 

HenryLindemann_8-1634660154526.png

 

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

Kind regards 

Henry

 

0 Kudos