Disaster recovery... Replacing failing SQL Server with same machine name and Instance?

Question

SQL Server DB server disaster recovery process documented at ESRI to change all of your data connections in all of your maps and then republish all of your services.  I could not get a full answer from the ESRI Case worker as they can not test my approach, nor has ESRI ever come across an alternative to build another VMware server and SQL server instance with the same machine name and instance as the current DBserver, take the original server off of the Domain, connect the new server to the domain, install SQL Server and restore the DB backups.  This should make all of your database connections, services, Portal content (maps and apps) stay the same and nothing else to do...This approach would save potentially a few weeks based on your Enterprise usage.  Has anyone else had to recover or migrate to another SQL Server machine without the long and painful method?

ChristopherPawlyszyn · Answer

You are correct, if the plan goes off without a hitch then this is a viable approach for migration to a new DBMS instance, but it does fall outside of Esri's scope of support since all the moving parts are non-Esri products/technologies. If you are using the same hostname (FQDN) then you would not need to update any of the SDE connection strings within the registered databases.

I primarily wanted to expound a bit upon the supportability of such a maneuver, and outline some of the potential pitfalls along the way. In terms of recreating the user permissions/roles, the instance would have to be domain-joined to add OS-authentication users, but database users could be mirrored at any point in the process.

As you've already alluded to, make sure to have a sound back-out strategy for if things go awry.

bsanders69 · Answer

Thanks for the reply. We have full database backups run nightly and I would run new ones before the restore and switch. I was also aware of the need for the relative user accounts be moved to the new server, but there are not very many...I think there are perhaps 4 admin accounts that need to get created on new server. The Power user accounts are easy to put back in once it is put on our domain. I thought that the backup/restore might bring those over due to their association with the database? These Servers are all in a VMware virtual server environment. If I am not mistaken, a new server can remain off of our domain until it is in a state with the appropriate geodatabase and content admin user accounts and the databases restored.
Should there be a need to supply a new SDE connection if they are identical (the point of all this), the new server would be set up with a FQDN after the old server has been renamed and removed from the domain (it would still be available in case something does go wrong to switch back and reconnect it back to the domain. As far as anything else going wrong, we will have Snapshots and DB backups to roll back to. The way I see it (and I know there are a few things that have to be done in the correct sequence...we have a VMware expert on our IT team) nothing changes with the connection string, therefore, nothing changes once the new server is in place exactly mimicking the old SQL Server and nothing has to be remapped or re-published.
Let me know if I am missing something... I know this is a bit trickier on a SQL Server machine than, say... an ArcGIS Server, but we did successfully replace a ArcGIS Server (site owner) server in Enterprise with a very similar methodology.

ChristopherPawlyszyn · Answer

Hi @BrettSanders,

I think the major hurdle with the workflow you're suggesting is there are a number of areas where things can go wrong, and all the steps in the process are achieved outside of Esri software. While it is absolutely true that a new but "identical" SQL instance could run the services, there are a number of caveats including using the same FQDN (or SPN from an OS-authentication perspective), the same security/users/passwords configured on the instance, and no other underlying data changes compared to the original machine/database.

With that being the case, it's difficult from an Esri Support perspective to recommend workflows that are not officially documented, even if the workflow could technically work if implemented correctly. If you were to push forward with this plan, I would recommend having a plan to revert to the original stack in case anything along the way goes awry, which is made difficult since in an AD environment/domain having the same computer name twice within a domain is not possible.

There is another option to update the registered data store connection with a new SDE file when a new instance is stood-up and the data/security migrated to that new instance. The documentation only mentions password updates, but it works for hostname changes as well (documentation enhancement is in the works already). This would be a band-aid to prevent immediately needing to republish all referenced services, but the Service Workspace information would not be updated within Server Manager so it is worth republishing when the opportunity arises.

Register your data with ArcGIS Server using Server Manager—ArcGIS Server | Documentation for ArcGIS Enterprise
https://enterprise.arcgis.com/en/server/latest/manage-data/windows/registering-your-data-with-arcgis-server-using-manager.htm#ESRI_SECTION1_01665D7E33384062B6A57DE89A52C2FA

Hope that helps,

Chris