1

I need to set up automatic failover with two SQL Server Instances and mirroring. There are several DNS hosters (such as DNS Made Easy and Netriplex) out there providing automatic failover. The are monitoring your server instances every minute and if primary goes down the domain name resolves to the secondary server's IP.

My problem is that I also need to switch server roles when in case of an automatic failover and this is not supported by my current DNS hosting provider (DNS Made Easy).

In other words: Assuming I have two database servers - A and B. A is the primary server and B is just standing by in case that A goes down. When A goes down, B takes over and becomes the new primary server. When A comes back up, it is the new secondary server and stands by until B should fails. When B fails, A takes over and becomes the primary the server again.

Is there any DNS hoster that offers this kind of functionality?

Thanks,

Adrian

Adrian Grigore
  • 1,082

3 Answers3

3

I don't think DNS is the appropriate mechanism for this kind of failover. In my experience, database failover is normally handled by:

  • A) Cluster software that migrates a floating IP address between the different servers (while also automatically monitoring and migrating services as required), or
  • B) A load-balancer that provides a virtual IP address in front of the different servers (in the case of multi-master nodes).

The problem with using DNS for this is that DNS lookups can be cached at many levels between the end user and your server. Therefore even if you change the DNS entry immediately, it may be several hours before the change is propagated to the end user, and your service will be down for this time.

Tom Shaw
  • 3,790
2

Sorry but no, you can't do that.

SQL servers are typically not directly exposed to the Internet, so public DNS and SQL Servers together are a quite uncommon combination. For this reason you will not find any DNS hosts with automated health checks for SQL Server.

In principle, you can secure a SQL Server to a point were it can be Internet-facing. But even so; are you quite sure that's what you want, have you considered all the possible the security risks associated with fx a buffer overflow exploit in SQL Server? I don't know the specifics of your architecture, but at first glance this design seems wrong.

Next, SQL Server is a stateful thing. If the primary SQL Server crashes right in the middle of a long-running ALTER TABLE or a transaction, then it's not obvious what state the backup server is in. Handling fail-over of an SQL Server is often not quite possible to automate, you may need a DBA to bring the servers back to a consistent state. I would certainly not trust an external DNS host, who has no knowledge of my application domain, to handle database failover & data integrity correctly.

Lastly, DNS failover can take a long time to propagate. DNS caches, well, cache query results, and even if you set the DNS TTL low, real life transition times can be quite high.

Community
  • 1
0

As I just learned, there is a simple way to handle exactly this scenario: Specifying the failover partner in the connection string. Something like this would to the trick:

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

Edit: I'm not sure how to understand the objection in the comment below. Doesn't this schematic taken from MSDN describe exactly my scenario?

Failover partners and application code

Also, from this article:

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.

Adrian Grigore
  • 1,082