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

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
1
  • Actually there is an easier way (as I just noticed). See my answer...
    – Adrian Grigore
    May 29, 2011 at 21:50
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.

Improve this answer
8
  • 1. My sql servers are hosted on the same machines as my web servers, so I can simply monitor those without expsosing the database to the internet 2. This point is irrelevant. The DNS server does not even need to know what the A record it is serving points to. It just looks up an A entry and returns an IP address, plain and simple. SQL server is responsible for automatic failover, and that is completely independent of DNS lookups. Point 3 can be solved with a DNS entry has a very low TTL (60 seconds)
    – Adrian Grigore
    May 29, 2011 at 21:44
  • I'm not sure why this was voted down, but +1 from me for a very reasonable answer to an ambiguous question.
    – Tom Shaw
    May 29, 2011 at 23:50
  • @Adrian Grigore: Reg 1) internet-facing: OK, fair enough, your MSSQL isn't exposed to the Internet. But if it isn't, why are you looking to your public DNS host to provide MSSQL failover? Reg 2) fail-over handling: MSSQL can do high-availability in the most expensive editions; but you haven't said anything to indicate that you're doing that. Reg 3) low DNS TTLs, low TTLs are known to be problematic, see my link above and many other similar reports on Serverfault.
    – user2874
    May 30, 2011 at 7:18
  • @Jesper: 1. It's trvial to write a small web app that monitors the db server and returns it's current state. 2. I did mention that I am going to set up two SQL Servers with mirroring. MSSQL Mirroring always supports automatic failover. 3. Problems with low TTL can be avoided quite easily if only the web server instances have to honor it. I agree it can be problematic if relying on end user clients to honor the TTL, but that is not the case in my scenario.
    – Adrian Grigore
    May 30, 2011 at 9:05
  • 1
    @Adrian: Regarding your first answer. If you can "simply monitor" it yourself, why do you need then the DNS provider? I think as well that Tom and Jesper gave the right answer. DNS is not the right solution. Maybe the wording of your question was wrong. But regarding DNS their answer is correct.
    – Raffael Luthiger
    May 30, 2011 at 9:47
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.

Improve this answer
3
  • 1
    Didn't you say the servers were primary/standby? A client string won't change the server roles; you'd need to have multi-master servers for it to work (or only need read only access).
    – Tom Shaw
    May 29, 2011 at 23:48
  • @Tom: I'm sorry, but I don't understand your objection. Please see my edit above for more details.
    – Adrian Grigore
    May 30, 2011 at 9:20
  • 1
    Jesper and I both interpreted your original question in the same way: that you wanted your DNS provider to monitor your SQL servers directly and initiate a primary->standby failover of your SQL servers when the primary server went down. I suggest you take more care with how you word your questions in future.
    – Tom Shaw
    May 30, 2011 at 9:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .