Configuring a failover partner for Sitecore

Question

I´m currently facing a really weird (at least for me) problem regarding the configuration of a failover partner in Sitecore.

Environment:

• .NET Framework 3.5
• Windows Server 2008 R2
• IIS 7.5

This is one of my connection strings:

<add name="master" connectionString="user id=user;password=password;Data Source=PRIMARY_SERVER\INSTANCE,60000;Failover Partner=FAILOVER_SERVER\INSTANCE,60000;Database=Sitecore_Master" />

As you can see, I´ve configured a failover partner, which accepts the same credentials as the primary database server.

If I stop the primary database during operation, I´ll receive a SqlException which says:

 Login failed for user 'user'

Any further request will show the same Exception, unless the AppPool is recycled. Now the application point to the failover database...

Shouldn´t the application switch the database itself?

The only idea I had was something like this, but it feels very, very dirty and I haven´t even tested it:

public class FailoverSqlDataProvider : SqlServerDataProvider
{
    public FailoverSqlDataProvider(string connectionString) : base(connectionString)
    { }

    protected override LanguageCollection LoadLanguages()
    {
        try
        {
            return base.LoadLanguages();
        }
        catch (Exception ex)
        {
            if (ex is SqlException)
            {
                Trace.TraceError("(FailoverSqlDataProvider) An error occured: {0}", ex.ToString());
                HttpRuntime.UnloadAppDomain();
            }

            return new LanguageCollection();
        }
    }
}

Answer 1

If you are using SQL Server with mirroring, the failover will only occur automatically if you are using a Witness server, otherwise you have to handle the switch over manually.

You can use a SQL Server Express as a witness server, and could run it from the webserver if there are no other servers available.