I have an application with a custom high availability (HA) feature that provides automatic failover from an active primary server that experiences a failure to a passive backup server. To date, this feature has included manual replication of all data between the primary and backup server on independent disks but with many headaches and performance problems as it is quite difficult to correctly and performantly implement full data replication. I am moving to a shared storage mechanism on SQL Server where I intend to make use of the more robust and performant HA capabilities of SQL Server and I need to modify my HA feature to work in that environment.

My question is how can I ensure that only one of either the primary or the backup server is writing to the database at a time? Obviously, in the case where the primary server fails catastrophically, there is no chance that it will be writing to the database at the same time that the backup server picks up. There are, however, less catastrophic scenarios in which certain systems on the primary may have gotten into a “bad state” and the backup needs to assert control over the primary which may still be partially functioning. In that case, there is a chance that both servers could attempt to write to the database with the potential result of data corruption. I don’t think my particular situation is unique. As an example, SQL Server in a Failover Cluster configuration writes to shared storage such as a SAN and I would assume it also has to address this problem in failover scenarios. Are there any patterns or common approaches for handling this problem?

Through Google searches and the like, I haven’t turned up any techniques that would work across machine boundaries. The one idea I had was configuring the primary and backup servers to connect to the database using different logins. In the case of failover, the first action of the backup server would be to disable the login of the primary server. While that might work, it seems like a hack to me and would also require administrative privileges on the database, something that end users are understandably hesitant to grant to individual applications. Any pointers to known patterns or other suggestions would be appreciated.