Two Database Mirroring cases in one week - one solution
Within a week we had two customers contacting us because of strange behavior they saw around SQL Server Database Mirroring. What led to this behavior was the same in both cases: The customers experienced a failover of synchronous mirroring to the mirror or manually failed over due to some operational reasons. Matter of fact the former mirror had the role of the principal. Both customers ran successfully in that configuration for a while. Re-starts of SAP processes created no problems since both customers had the Failover Partner defined in the default profile, Environment and Transport profile as described in OSS note #965908.
The real trouble started when out of different reasons the customers disabled Database Mirroring while still running on the server which formerly was the mirror and which got addressed in the profiles as the Failover Partner. In both situations the former principal was down or the SAP ERP database was not available. As mentioned we are talking about switching off or disabling mirroring and not about suspending mirroring. In the case of switching off mirroring both customers suddenly experienced an increasing amount of ABAP short dumps. Looking into the system log or the developer traces of the ABAP work processes, the following error text could be found:
‘The connection attempted to fail over to a server that does not have a failover partner’
What happened is that the SAP work process tried to establish a new connection or session to SQL Server. Reading the profile it first tried to connect to the former principal to realize that either the SQL Server instance or the database was not available. Hence SQL Server’s connectivity layer tried to connect to the server which was defined as Failover Partner. However the server which was supposedly the Failover Partner was not in a Database Mirroring Relationship anymore and hence declined connection attempts under the flag of a Failover Partner. I described a similar scenario here already. However the solution which we introduced was with SQL Server 2005 SP2 CU4 to resolve the problem with this scenario was not correctly described in that blog. I meanwhile corrected the blog and also added a section to OSS note #965908.
The solution itself is to upgrade to a SQL Server 2005 SP2 CU4 or a later build on the server side and on the client side (SQL SNAC). According to our recommendations any SAP customer using Database Mirroring should be on a more recent SQL Server build anyway. Since the solution of the scenario above required changes on the server coding and client coding, it is important to have SQL SNAC patched as well. However in order to have the solution working, the principal SQL Server instance and the mirror SQL Server instance should be started with trace flag 1449. The details also are documented in this KBA. The activation of the trace flag is the important point here. The traceflag needs to be used in combination with Database Mirroring on SQL Server 2008, 2008 R2 and SQL Server 2012. SQL Server 2012 AlwaysOn functionality does not require this traceflag anymore.