Using a highly available Microsoft SQL Server database for ArcGIS service data can avoid downtime for your web services.
SQL Server offers several high availability solutions. ArcGIS supports connections to AlwaysOn Availability Groups and Failover Cluster Instances.
Note that Microsoft has deprecated database mirroring. Consider using an AlwaysOn solution instead of mirroring.
The following sections explain what information you need to provide to connect from ArcGIS to highly available SQL Server databases:
AlwaysOn Availability Groups and Failover Cluster Instances
A Failover Cluster Instance provides a redundant SQL Server instance to which clients can connect if one instance fails. Availability Groups allow you to specify a set of primary databases and up to four sets of read-only secondary databases spread over Failover Cluster Instances. Be sure to read the SQL Server AlwaysOn documentation in Microsoft documentation before implementing this solution.
Once you have your AlwaysOn solution in place, you can connect to it from ArcGIS by specifying the availability group listener name instead of the SQL Server instance name. You can append additional conditions to the group listener, separating the group listener name and each parameter using semicolons (;). You can add the following conditions:
- APPLICATIONINTENT=READONLY or APPLICATIONINTENT=READWRITE
You must connect to an AlwaysOn Availability Group listener to use READONLY.
- MULTISUBNETFAILOVER=YES or MULTISUBNETFAILOVER=NO
If you do not specify values for APPLICATIONINTENT and MULTISUBNETFAILOVER, the default values are READWRITE and NO, respectively.
As noted above, Microsoft has deprecated database mirroring in SQL Server, but if you are still using it, you can provide connection information for both the principal and mirror server connections for the source data used for your services. Type the information in the format <principal>;MIRROR=<mirror>.
If the principal server becomes unavailable, ArcGIS Server retries the connection automatically. At that time, if the mirror server is available, the service's connection will switch to using the data on the mirror server.
Different scenarios for specifying a data mirror are described in the following sections:
Publisher and server machines use the same database
If the GIS resource you share as a service uses the same database as the published service, and that database is mirrored, provide instance information for both the principal and mirror server in the Instance field of the shared database connection.
Publisher and server machines use different databases
If your GIS resource and your published service will use different databases for their source data (either replicated geodatabases or a managed database), you will have two separate database connections defined. To ensure high availability for your service, be sure that the connection file defined for the publisher uses the mirroring syntax already described.