Using a highly available Microsoft SQL Server database for ArcGIS service data can avoid downtime for your web services.
SQL Server provides several high-availability solutions. ArcGIS supports connections to AlwaysOn Availability Groups and Failover Cluster Instances.
Note:
Microsoft has deprecated database mirroring. Consider using an AlwaysOn solution instead of mirroring.
The sections below 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.
Note:
Read the SQL Server AlwaysOn documentation in the Microsoft documentation before implementing this solution.After you have an 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 using the Additional Properties settings on the Database Connection dialog box, or in the instance string by separating the group listener name and each parameter with a semicolon (;).
You can add the following conditions in the instance string or as properties and values under Additional Properties on the Database Connection dialog box:
- APPLICATIONINTENT=READONLY or APPLICATIONINTENT=READWRITE
Note:
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.
Database mirroring
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 must define two separate database connections. To ensure high availability for your service, ensure that the connection file defined for the publisher uses the mirroring syntax already described.