Using SQL Server Database mirroring between Primary and DR cluster

Discussion in 'microsoft.public.sqlserver.clustering' started by xyberdobs, May 21, 2009.

  1. xyberdobs

    xyberdobs Guest

    Hi all.

    Any assistance regarding this would be greatly appreciated.

    We have configured two sql server 2005 clustered environments - one will be
    hosted at our local datacenter and the other at our DR site. These two
    clusters are geographically dispersed.

    The clusters will be configured as active/passive and I would like to
    incorporate sql server 2005 database mirroring between the two clusters for
    redundancy. Both clusters have the same domain names

    My questions are:
    can I configure both sql clustered instances to have the same sql virtual
    server and instance name, so that in the event that the datacenter goes down,
    we can initiate a manual failover between principal and mirror and the
    front-end applications would still be able to connect using the same server
    and instance name at the DR site?
    If we cant have the same server and instance names, how can I configure the
    cluster at DR site assume the identity of the primary virtual sql server and
    instance name so that no code changes are required to point to the databases?

    Many thanks.
     
    xyberdobs, May 21, 2009
    #1
    1. Advertisements

  2. xyberdobs

    Tom Moreau Guest

    You can't have identical server and instance names. You can set up the
    connect string to include the mirror. This way, when a client attempts to
    connect to the primary and it is offline, then they connect to the mirror.

    Other things to consider would include the speed of your WAN link and how
    much traffic you get when you defrag indexes, for example. In SQL 2005,
    mirror traffic is not compressed. You may want to consider log shipping but
    with a 3rd-party compression tool, such as Red Gate's SQL Backup.

    Also, in a DR situation, you would likely want to have redundancy of your
    middle-tier servers - not just your DB servers.

    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
    SQL Server MVP
    Toronto, ON Canada
    https://mvp.support.microsoft.com/profile/Tom.Moreau


    Hi all.

    Any assistance regarding this would be greatly appreciated.

    We have configured two sql server 2005 clustered environments - one will be
    hosted at our local datacenter and the other at our DR site. These two
    clusters are geographically dispersed.

    The clusters will be configured as active/passive and I would like to
    incorporate sql server 2005 database mirroring between the two clusters for
    redundancy. Both clusters have the same domain names

    My questions are:
    can I configure both sql clustered instances to have the same sql virtual
    server and instance name, so that in the event that the datacenter goes
    down,
    we can initiate a manual failover between principal and mirror and the
    front-end applications would still be able to connect using the same server
    and instance name at the DR site?
    If we cant have the same server and instance names, how can I configure the
    cluster at DR site assume the identity of the primary virtual sql server and
    instance name so that no code changes are required to point to the
    databases?

    Many thanks.
     
    Tom Moreau, May 21, 2009
    #2
    1. Advertisements

  3. I am implementing such a configuration now, except that DR is a stand-alone
    server.

    Use a DNS alias to abstract your Primary cluster. When you implement a
    mirror failover, change the DNS record to point to the alternate server.
     
    Geoff N. Hiten, May 21, 2009
    #3
  4. SQL server native client is SQL server mirror aware. If your application is
    using SQL server native Client, then you don’t need to have same SQL server
    name. The application will automatically detect primary database off line and
    jump over to mirrored instance (one time configuration at native client end
    is needed)


    Manpreet Singh
    http://crazysql.co.nr
    http://crazysql.wordpress.com
    MCITP – DB Admin, DB Dev SQL Server 2005
    MCTS – MOSS 2007, SQL server 2005
     
    Manpreet Singh, May 26, 2009
    #4
  5. xyberdobs

    briantn54203 Guest

    I was faced with the same problem that you are having here. I have been
    demoing a software called CA XOsoft Replication and High Availability.

    This works just like mirroring does without the witness server. It works
    through DNS redirection so your virtual server Name in DNS will change its IP
    Address so your SQL Instance will be up with the same name on either side of
    your DR Plan.

    This can be used for Exchange too, but that is for the Network guys. I have
    tested this thoroughly and it is a very solid product.

    I have this running on my production side with a 5 instance, 5 node,
    active/active cluster with virtual servers and physical servers on SQL Server
    2008 and Server 2008. I have it failing to my other DR site which is a
    standalone box (could be clustered though) and it fails within a minute. You
    can custom set your failover times to whatever you want.

    The only thing that I do not like about the product is the drive letters and
    locations have to match from one server to your DR server. This acts just
    like a microsoft cluster with the services stopping and starting on one side
    or the other.

    It is work checking out.
    http://www.arcserve.com/products/product.aspx?id=8282
     
    briantn54203, May 27, 2009
    #5
  6. xyberdobs

    Linchi Shea Guest

    Have you tried it under a real disaster scenario rather than a planned one?
    Does it ever hang from time to time?

    Linchi
     
    Linchi Shea, Jun 2, 2009
    #6
  7. I have used it as well. It doesn't replicate system databases such as msdb
    or distribution. You can see where that can cause problems.

    Much like mirroring, it is a database-level failover mechanism.

    --
    Geoff N. Hiten
    Principal SQL Infrastructure Consultant
    Microsoft SQL Server MVP
     
    Geoff N. Hiten, Jun 2, 2009
    #7
  8. xyberdobs

    Linchi Shea Guest

    If it's what I have had experience with, it is a file system level tool and
    can replicate any database files. In fact, I don't think it cares what
    database it's replicating. You specify what NTFS folders to replicate, and
    have some options to exclude folders/files.

    Linchi
     
    Linchi Shea, Jun 2, 2009
    #8
  9. The way it works with SQL is it has to make sure data and log blocks are
    sent in order. You install sql to the target system and it does some attach
    magic. It had problems with system databases, at least when I ran it.

    --
    Geoff N. Hiten
    Principal SQL Infrastructure Consultant
    Microsoft SQL Server MVP
     
    Geoff N. Hiten, Jun 2, 2009
    #9
  10. xyberdobs

    Linchi Shea Guest

    As far as I know it says it preserves write ordering. But there is no way of
    really validating that (not just for this product but all such products). You
    can test as much as you want, and there is still no guarantee.

    if we are indeed talking about the same product, on the DR server the SQL
    service must be kept offline for the database files to be replicated by the
    product.

    I'm curious how it works out for others as a SQL Server DR tool.

    Linchi
     
    Linchi Shea, Jun 2, 2009
    #10
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.