Skip to Content.
Sympa Menu

freetds - Re: [freetds] SQL Server 2005 Mirroring

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Reid, Roger L." <roger.reid AT dpw.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] SQL Server 2005 Mirroring
  • Date: Tue, 17 Apr 2007 09:51:10 -0400

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





Archive powered by MHonArc 2.6.24.

Top of Page