design question

  • Greetings!

      First of all, I'm not a SQL expert.  I'm a SysAdmin assisting a design architect with a Datacenter redundancy design.  They want to have a 3 (or 4) node SQL cluster setup at two geographically separated locations (1 at each locaction) running multiple instances with Data Mirroring keeping Site 2 current with Site 1.  This seems to be cut and dry with SQL 2005.  However, they also wanted the abilty to make both sites active on the same database with replication between them keeping each site current with the changes made at the other site.

    Is this even possible?  I have done a lot of searching lately and found little on this type of setup.  I was hoping someone here can point me to a resource, a URL or a paper with information on this type of setup.  Would be better if someone actually HAD a setup like this that would be willing to share "lessons learned", but I'd be very happy for pointers to additional info.

    Thanks for any pointers, suggestions etc.

    Mike

  • I did a contract for Microsoft's poster-child for two way replication and the short answer to your question is, yes this can work.  The primary concerns you will have to deal with in a situation where you implement 2 way replication follow:

    1 - You need to make sure you have appropriate auditing columns in all tables so you can identify where the data originated and when.

    2 - Your biggest concern will be what's called loop back.  This is where Server A replicates a record to Server B then Server B replicates the same record back to Server A and so on in an infinite loop.

    3 - Identification of records.  Don't use an identity column as the primary key in your tables, use a uniqueidentifier (GUID).

    From what I know of database mirroring you cannot use database mirroring to do what you are trying to do.  The only way I know of achieving what you are trying to achieve is by implementing transational replication on both servers.

    As for pointing you in the right direction in terms of information on 2-way replication, there isn't any.  I've worked for some of the largest companies in the world and as a consultant and contractor worked for many different companies (probably on the order of 50).  Of all the compaines I've worked for one implemented 2-way replication.  My best advice to you is to suggest one of the following to your co-workers...

    1 - Find someone who has serious experience with replication or...

    2 - Forget using SQL Server replication and replicate the data in a middle tier instead (unfortunately this only shifts the complicated work off of a DBA and onto a developer).

  • Ed,

      Thanks for your reply.  I was afraid this was going to be a difficult implementation.  Data mirroring allows us to setup up a replica at the other site as long as the other site is not active.  I figured it would be of little use for replication in both directions since the second node needs to be off-line for mirroring to work (to my limited knowledge)

      After reading your message, one of the co-workers said "SAN is being replicated from site to site" but I told them that is NOT what we need to have happen.  The SAN replication would replicate the entire database from one to the other.

    Thanks again for your help.  It may not be what they wanted to hear, but maybe what they NEEDED to hear!

    Mike

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply