I am going to lead my answer by addressing a few of the suggestions you have considered and when it is appropriate to pull these out of your tool belt.
SQL Server Service Broker and Enterprise Service Integration (broker)
This is not a solution for ensuring High Availability nor is it a solution for SPOF.
Likewise Enterprise Service Integration as a system design tool doesn't itself make any guarantees or benefits in favor of Quality Attributes that address Redundancy, Availability, Scalability, Performance, and Reliability. In Enterprise Service Integration there are two primary patterns, source quoted from Wikipedia article on Enterprise Application Integration:
Here, the EAI system acts as the go-between or broker between multiple applications. Whenever an interesting event occurs in an
application (for instance, new information is created or a new
transaction completed) an integration module in the EAI system is
notified. The module then propagates the changes to other relevant
In this case, the EAI system acts as the overarching facade across multiple applications. All event calls from the 'outside world' to any
of the applications are front-ended by the EAI system. The EAI system
is configured to expose only the relevant information and interfaces
of the underlying applications to the outside world, and performs all
interactions with the underlying applications on behalf of the
A broker process is used in the Mediation pattern to receive messages from various applications, and then optionally perform transformation of that message for one or more targets. The Federation pattern is similar but instead acts like a Service Facade for an application so that applications have a common and encapsulated interface that shelters from changes in the target application. Both of these patterns address quality attributes like Interoperability and Reusability, but again, this as well does not address quality attributes commonly associated with High Availability and SPOF.
As far as Message Queue, this is less of a pattern in System Design and more of a technology that one would use to ensure 100% message delivery between applications over an aysnchronous channel (as opposed to HTTP web services which are synchronous, Request-Response model).
What you are looking for is a solution that utilizes Load Balancing. Essentially it is a highly available endpoint for a network resource, that will take requests, utilize some algorithm to determine which endpoint or server to send to, and then proxies a number of endpoints on behalf of the client by shuttling the request to an appropriate endpoint. This has a number of benefits. First is that a single network URI can be used by multiple clients to where they do not need to be aware of which server will ultimately serve their request. Second is that this gives a level of security to where the actual servers can be removed from visibility. Third this allows for seamless scalability to where additional servers can be added to meet load, and likewise servers can drop without the application being unavailable.
There are many patterns and forms that this can take in terms of technology. If you are developing an application for internal hosting in the enterprise, then often there would be a data center appliance (Eg. F5) that can be configured to help you achieve this. If you are hosting an application on the cloud, Microsoft Azure has Load Balancing built in as a feature that you can utilize for distributing load to multiple VM's.