SQL Always on, TFS 2012/13 and SQL mirroring! Can they live together?

Vimal (Support Escalation Engineer) from our EMEA support team happened to work with a customer who was trying to bring back their SQL mirroring after upgrading TFS to 2012 from 2010 and also SQL to 2012 from 2008 and landed in sequence of issues and they were not able to connect to TFS from Visual Studio

We were receiving the following error on Visual Studio client boxes

TF246017: Team Foundation Server could not connect to the database. Verify that the server that is hosting the database is operational, and that network problems are not blocking communication with the server

When checked, the web application pool and windows services were working well

However when we look at the App tier event logs we saw the following exception stack trace:

===

Log Name: Application
Source: TFS Services
Date: <valid Date and time>
Event ID: 3008
Task Category: None
Level: Warning
Keywords: Classic
User: N/A
Computer: Team Foundation Server DNS Name
Description:

TF53010: The following error has occurred in a Team Foundation component or extension:

Date (UTC): <valid Date and time>
Machine: TFS DNS Name
Application Domain: <valid entry>
Assembly: Microsoft.TeamFoundation.Framework.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v4.0.30319
Service Host:
Process Details:
Process Name: w3wp
Process Id: 2272
Thread Id: 3000
Account name: Domain\TFSSERVICE

Detailed Message: TF30059: Fatal error while initializing web service

Web Request Details

Url: https://tfsserver.com:8080/tfs/DefaultCollection/Build/v3.0/BuildQueueService.asmx [method: POST]
User Agent: Team Foundation (devenv.exe, 10.0.40219.1)
Headers: Content-Length=554&Content-Type=application%2fsoap%2bxml%3b+charset%3dutf-8&Accept-Encoding=gzip&Accept-Language=en-US&Expect=100-continue&Host=tfsserver.com%3a8080&User-Agent=Team+Foundation+(devenv.exe%2c+10.0.40219.1)&X-TFS-Version=1.0.0.0&X-TFS-Session=433fa7a7-51bb-4c59-925e-c788d80314db&TF-Instance=433fa7a7-51bb-4c59-925e-c788d80314db&SOAPAction=http%3a%2f%2ftempuri.org%2fQueryBuilds
Path: /tfs/DefaultCollection/Build/v3.0/BuildQueueService.asmx
Local Request: False
Host Address: Server IP
User: not available [authentication type: not available]

Exception Message: TF246017: Team Foundation Server could not connect to the database. Verify that the server that is hosting the database is operational, and that network problems are not blocking communication with the server. (type DatabaseConnectionException)
Exception Stack Trace: at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.TranslateException(Int32 errorNumber, SqlException sqlException, SqlError sqlError)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.TranslateException(SqlException sqlException)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.MapException(SqlException ex, QueryExecutionState queryState)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.HandleException(Exception exception)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.Execute(ExecuteType executeType, CommandBehavior behavior)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.ExecuteReader()
at Microsoft.TeamFoundation.Framework.Server.ResourceManagementComponent.TryGetServiceVersion(String serviceName, Boolean& schemaExists)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationResourceManagementService.ReadServiceVersionFromDatabase(String connectionString, Boolean handleNoResourceManagementSchema, String serviceName, Boolean& resourceManagementSchemaExists)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationResourceManagementService.GetServiceVersionRaw[TComponent](String connectionString, ServiceVersionEntry& serviceVersionEntry, IComponentCreator& componentCreator, Boolean handleNoResourceManagementSchema, Boolean throwExceptions, ComponentFactory& factory, Boolean& resourceManagementSchemaExists)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationResourceManagementService.CreateComponentRaw[TComponent](String connectionString, Int32 commandTimeout, Int32 deadlockPause, Int32 maxDeadlockRetries, Boolean handleNoResourceManagementSchema, Boolean verifyServiceVersion, Boolean throwExceptions)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationCoreRegistryService.GetValueRaw(String connectionString, Int32 partitionId, String path, Int32 commandTimeout, Int32 deadlockPause, Int32 maxDeadlockRetries)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationCoreRegistryService.GetDeploymentValueRaw(String configurationDatabaseConnectionString, String path, Int32 commandTimeout, Int32 deadlockPause, Int32 maxDeadlockRetries)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationServiceHost.InitializeDataDirectoryRoot(TeamFoundationRequestContext requestContext)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationServiceHost..ctor(HostProperties hostProperties, TeamFoundationServiceHost parentServiceHost)
at Microsoft.TeamFoundation.Framework.Server.DeploymentServiceHost..ctor(HostProperties hostProperties, Boolean failOnInvalidConfiguration)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationApplicationCore.ApplicationStart()
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationModule.Module_BeginRequest(Object sender, EventArgs e)

Inner Exception Details:

Exception Message: Connecting to a mirrored SQL Server instance using the MultiSubnetFailover connection option is not supported. (type SqlException)

SQL Exception Class: 20
SQL Exception Number: 0
SQL Exception Procedure:
SQL Exception Line Number: 0
SQL Exception Server:
SQL Exception State: 0
SQL Error(s):

Exception Data Dictionary:
HelpLink.ProdName = Microsoft SQL Server
HelpLink.EvtSrc = MSSQLServer
HelpLink.EvtID = 0
HelpLink.BaseHelpUrl = https://go.microsoft.com/fwlink
HelpLink.LinkId = 20476

Exception Stack Trace: at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.Execute(ExecuteType executeType, CommandBehavior behavior)

====

When checked the last recent activity was, they employed SQL mirroring, after applying the mirroring they were not able to connect to Team Foundation Server (TFS)

Figured out that the MultiSubnetFailover setting in connection string is for SQLALwaysON, which was set to yes in web.config
Since customer has elected to use another SQL high availability option - Mirroring, hence the setting in web.config was in conflict with the mirroring

Solution

=======
As customer preferred to use SQL mirroring, went ahead and asked customer to set the MultiSubnetFailover=False in the web.config file for TFS web application.
The default location for this file is Drive:\Program Files\Microsoft Team Foundation Server 11.0\Application Tier\Web Services. Open the Web Services folder, which will be having the web.config file in it.
Important : Take a back up or create a copy of web.config file in case some issue happens. Also, there is no need to reset IIS as making changes to the config file will do that on it’s own
Now when checked everything works well even if mirroring is employed

Reason why this happened?

FYI! – This setting “MultiSubnetFailover=True” on the connection string in the web.config file is automatically added, if you select to use SQL Always on feature during the configuration of the TFS Administration console during upgrade process in the GUI.

The issue was happening because we cannot have SQL always on as a setting within TFS and Mirroring on SQL going together.

Also, when you are enabling the Check box for Always-on in TFS, ensure you are in fact using SQL Always on with the listener.

Content Created : Vimal Thiagaraj