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,072
  • 3
  • 21
  • 34

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,752
  • 16
  • 23
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
  • 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 '11 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 '11 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. –  May 30 '11 at 07: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 '11 at 09: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 '11 at 09:47
  • @Raffael: The web app I describes just tries to return the status of the local db. Tt does nothing with regards to switching the DNS record. That is what I need the DNS provider for. Have a look at this website if you are interested in the details: https://www.dnsmadeeasy.com/enterprisedns/dnsfailover.html – Adrian Grigore May 30 '11 at 09:53
  • @Adrian: a) Why do you need to switch the DNS anyway? Why does your application not switch automatically to the other IP address when the first one is not reachable anymore? and b) Why does your monitoring tool not make a call to their REST API https://www.dnsmadeeasy.com/enterprisedns/api.html and initiate the change itself. This way you can have the monitoring in exactly the way you want it. – Raffael Luthiger May 30 '11 at 10:18
  • @Raffael: a): because I did not know of the solution I write in my answer below at the time of writing this OP. b): Because the DNS made easy API does not support anything with regards to failover. It can only be used to change regular DNS records, but not to flip primary / secondary server addresses in an automatic failover configuration. – Adrian Grigore May 30 '11 at 11:17
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,072
  • 3
  • 21
  • 34
  • 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 '11 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 '11 at 09: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 '11 at 09:37