Using a highly available Microsoft SQL Server database for ArcGIS service data can avoid downtime for your services.
SQL Server offers several high availability solutions. ArcGIS supports connections to the following types:
- AlwaysOn Availability Groups and Failover Cluster Instances
- Database mirroring
Microsoft has deprecated database mirroring. If you are using SQL Server 2012 or a later release, you should use 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 on the Microsoft Developer Network 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
- MULTISUBNETFAILOVER=YES or MULTISUBNETFAILOVER=NO
If you do not specify values for APPLICATIONINTENT and MULTISUBNETFAILOVER, the default values are READWRITE and NO, respectively.
In the following example, a connection is made to a read-only secondary database through the org_agl group listener.
Database mirroring
If you use database mirroring in SQL Server, you can provide connection information for both the principal and mirror server connections for the source data used for your services. This is given <principal>;MIRROR=<mirror> in the format.
If the principal server becomes unavailable, ArcGIS for 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.
For example, if you create your connection from the Database Connection dialog box in ArcGIS for Desktop, your principal server is oak\prod, your mirror is oak2\echo, and the databases are datasquared, the connection would look similar to the following:
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.
For example, if your feature service will point to data that is copied to your managed database gisdata, which is on server willow and mirrored on server cottonwood, your registered geodatabase connection would look similar to the following:
Because this example uses default SQL Server instances, you could provide the IP address for each server instead of the SQL Server instance name. For example, if the IP address for willow is 10.10.100.10 and the IP address for cottonwood is 11.11.111.11, type 10.10.100.10;MIRROR=11.11.111.11 in the Instance text box. If either of the SQL Server instances is listening on a port other than the default database engine port of 1433, include the port number in the connection information. For example, if the willow instance is listening on port 50000, type 10.10.100.10:50000;MIRROR=11.11.111.11 in the Instance text box.