Disaster Recovery (or moving database to another server) on SQL Server process

963
1
11-04-2020 02:04 PM
Labels (1)
BrettSanders
New Contributor III

In the case of a relational database server failure, ESRI recommends that we restore geodatabases from backups on new server, make all new connection strings to new database Instance (machine name), fix every database connection error in every map and/or ArcGIS Pro project that is connected to an Enterprise database (SQLServer/Oracle, etc.), republish every service on your Enterprise ArcGIS Servers and potentially check/fix all maps and their popups.  This process, to me, sounds like potentially a ton of work to get your Enterprise back up and running again.  

 

I proposed that there is, potentially, another solution that cuts the work down to a few hours at best.  Our servers are virtual on VMware.  This approach still would work with stand alone physical machines used as database servers as well. 

  1. I propose that it is possible to create a server with the same machine name for your Enterprise database server (and for this document, we'll just use SQL Server as an example), leave it off your Domain.
  2. Add OS and any typical software and install SQL Server, taking the machine name as the Instance (in effect having the new server with the same name and Instance as your existing server),
  3. Then the magic...Take old server off the Domain and add the newly created one to your Domain and restore all geodatabases from their backups
  4. Verify user accounts are still there or authorize the same user accounts set up on old server, and in theory, everything is back up and running at that point.  All of your old connections should now still work, including Services and Maps.

 

I could not get confirmation form Tech support that this approach works as they have apparently never had anyone with a failing DB Server to confirm this method, so I thought I would ask the Community if this seems to be a way around this type of disaster recovery without all of the work.  

 

Please let me know if anyone else has had a failing DB Server and tried another method.

 

Thank you,

Brett.

1 Reply
MarceloMarques
Esri Regular Contributor

Yes, it will work but you can also do something better that will make things easier in the future and also minimize impact during upgrades or migrations of databases from one server to another in the long run.

Create a new DNS Alias for your Database Server and use the alias in the Geodatabase Connection Files.

Then publish all map services and feature services with the Geodatabase Connection Files that uses the DNS Alias.

Example:  hostname = mydbserver1.acme.com   --> DNS Alias = mygisdb.acme.com

Hence, if you loose the server mydbserver1.acme.com you can create a new server with any new hostname that you want, and then restore the backups, when the new database server is ready just change the DNS Alias "mygisdb.acme.com" to resolve to the new database server, the map services, feature services etc. will not break because the geodatabase connection files are still the same thus the arcgis server data store connections also did not change, hence everything will work as expected without having to republish services. 

But there is a more robust solution for High Availability with SQL Server, you can look to implement SQL Server Always On with a minimum of 3 nodes, yes, you can use Always On with VMWare VMs as well.

I hope this helps.

| Marcelo Marques | Esri PS Products | Principal Product Engineer |  

| Cloud & Database Administrator | OCP – Oracle Certified Professional |

| Esri | 380 New York St | Redlands, CA 92373 | USA |

| Esri Enterprise Mapping and Charting Databases Best Practices |

 

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
0 Kudos