FreeTDS Development Group

["Reid, Roger L." <roger.reid AT dpw.com>]

Using a CNAME in DNS is my practice as well.   In fact, I use a different 
CNAME per major application group - e.g.
        rdb-personnel  ->   SQL01
        rdb-clients   ->     SQL01
        rdb-tracking  ->    SQL02

If I decide to balance by moving personnel to the SQL02 server, the 
application does not need to know about the change.
        
Replication tools like XOSoft do the same thing, but handle the DNS update 
for you if you like.   

In my case, SQL01 and SQL02 are themselves CNAMES:
SQL01 ->sql_central_01.XXX.XXX.com
SQL02 ->sql_central_01.XXX.XXX.com

to failover to a recovery site, DNS is changed at that level:
SQL01 -> sql_remote_01.ZZZ.XXX.com

which effectively changes all the underlying ones.  NOTE!  Multi layer CNAMES 
violate the standards for DNS.  But I've never seen an implementation it does 
not work in.

You speak of automatic failover.   I don't know if you have done that before 
- if not, realize there's a lot of gotchas in that, and they change with the 
reason for failure and the scenario for recovery.

We have a few old programs that, instead of relying on this DNS change, have 
an extra library between themselves and the dblib.  It catches failure, and 
connects to the backup.   Let's say you're using mirroring to ensure the 
backup is good.  You need to keep track of the fact that you failed over, so 
that when the primary comes back up (out of date, now), you know not to just 
revert to the primary.

You need to handle the failback manually, most likely.  Given that,  how do 
you decide on an automatic failover?  What if the dataserver gets bounced 
overnight to install a security patch?   How does your failover know it's 
better to be down for 5 minutes than to start a process that will take you 
half the NEXT night to undo?

Depending on the complexity of your situation, you CAN do all this - but 
there's a bunch of stuff to do besides defining an alternate.

There are several products on the market that can handle a lot of this for 
you.  XOSoft is the one I am familiar with.  Golden Gate Software has another 
product.   I think there's a SonaSoft too, but I seem to recall that was 
assuming you were doing everything on Windows and used ADO.NET for everything 
(in which case, why bother with FreeTDS?)


-----Original Message-----
From: James K. Lowden [mailto:jklowden AT freetds.org] 
Sent: Monday, April 16, 2007 10:32 PM
To: FreeTDS Development Group
Subject: Re: [freetds] SQL Server 2005 Mirroring

Beth Woods wrote:
> 
> Our servers are mirrored so that if 
> the primary goes down the mirror will automatically handle any new
> connections.   How can I configure this with the freetds driver?  What I
> basically need is an  alternate server.  I did not see that as an option
> for the configuration file.  

Welcome to the project, Beth.  

FreeTDS doesn't do any kind of failover.  It follows DNS.  In my work
environment, we have a CNAME DNS record that points to the primary.  On
failure, that entry is changed to point to the secondary.  

I think this kind of arrangement is fairly common.  

HTH.  

--jkl