How Do I Use SQL Mirroring in ArcGIS?

[MikeChancey]

I am trying to setup a "highly available" ArcGIS database in Microsoft SQL.  I created an 10.3.1 enterprise database on the principal server, populated it with data, and then followed the standard SQL procedures to setup the mirror and witness copies on two other servers.  MSSQL is reporting everything working correctly.  I then setup this connection in ArcCatalog:

For testing purposes, w63-hydra-m is my principal server and w63-hydra is the mirror server.  The mirrored database is DevTest.

With this connection as long as the principal server is up, everything works perfectly.  If the SQL service on principal server is stopped, the connection fails.  If the SQL service on the principal is then restarted, the connect still doesn't work.  The only method I have found to get the connection working again is to stop and restart the service on the mirror so the orignal server is again shown as the principal, then close and reopen ArcCatalog.  This is not exactly a highly available connection.  Obviously I must have done something wrong here.  So what is it?

[ScottFierro2]

Setup the use of DNS aliases. We currently are building out 4 environments with 10.5 (2 are test and 2 are prod for different projects) all of which are using SQL 2016. We have played with mirroring and clustering in SQL and have found reasons/needs for both so just will depend on your need. In both cases we use DNS Aliases and setup all ESRI connections using the DNS. This makes it very easy when we want to do upgrades on single environments and not break existing configurations because we can architect, build and test new SQL releases, OS upgrades or anything else on the DB side we need and once we are ready simply swap the DNS over to the new servers.

Here's a quick simple article about 1 guy who used it and wrote blog. Google throws up many references for this if you search for SQL Mirroring using DNS.

http://www.allenkinsel.com/archive/2010/01/using-aliases-in-sql-server/

[KatieMcFarland1]

Hi Mike, 

Just wondering if you got any further with this issue? I am experiencing similar issues. We are using ArcGIS Server 10.4.1, and SQL server 2012. When the SQL server fails-over to the mirror database, any ArcGIS database connections stop working and map services published with these database connections are affected.

Thanks,

Katie 

[JonathanQuinn]

Did you install the following patch?

ArcGIS 10.4.1 for (Desktop, Engine, Server) SQL Server Quality Improvement Patch

There seem to be a few bugs in that patch that address this.  It may be good to make sure the patch is installed.

[KatieMcFarland1]

Hi Jonathan,

Thanks for your reply. Yes, we already have that patch and several others installed, but unfortunately that didn't seem to help.

Thanks,

Katie