Replication between istances with failover RRS feed

  • Question

  • Good morning.

    This question is about replication and mirroring between databases.
    I did not know where to post it, so i placed here, i think it's the better place.

    I'm not looking for deep detailed solution. I know only basic mirroring and replication features, so i'm anyway going to buy right books about this 2 powerful features (if you can suggest me some book title).
    What i want to know is if Sql Server can help me and provide me tools to reach my aim (explained here).

    We got 3 location, X,Y,Z. Just to have a basic idea:

    X to Z = 800 kilometers
    X to Y = 40 kilometers
    Y to Z = 780 kilometers

    We got a quite large Database (about 14 gigabytes) in X location and software that uses it in same LAN, so all operations are resolved locally. X is the main data source.
    I'm not database creator, and, basically, i can't change it. Keep this in mind.

    The Y location does not make ANY operation and never will do it. We got Sql Server instance with a better ADSL line and aim is to use it as a mirror.

    In the same time, Z use a my C# 4.0 software that fetches, stores and updates data from X source.
    To do it, i use remote connection IP in connection string.
    Everything works, but for the Z location it's a little bit too slow (especially for like researches).
    I moved CPU intensive computation in main server using a CLR Dll table valued function and performance improved (from 40 secs to 12),  but anyway all system it's slow.

    So i got 2 problems to solve

    A) Mirroring in case of line miss in X location
    B) Replication in Z location

    Now what am i going to say may be wrong, i repeat, i know only basic of Replication and Mirroring, so please forgive me and advice me good books about.

    Mirroring uses a witness server to understand WHERE to send a client connection request. If server A (which is main) is aviable, connect to A; else, promove B as main and redirect connection on B.
    Automatic syncronization will be done when A will be aviable again.

    My idea was to place main server in X location (in other words, do not move it) and place a Witness and secondary server in Y location (where ADSL never miss).
    In this way, i should change all X and Z connection strings from local to witness server IP.

    First problem: What if X ADSL line falls? Z can still work (thanks to witness) but X can't connect to witness server. So they are off.
    I may someway redirect connections on local, but when A line will be up again, if i understand, B will overwrite A data, losing all work.
    Is there a way to prevent this? How can i manage this issue?
    Main problem resides in primary keys.
    This database use sometime integer identity as Primary Key.
    If local make a row with ID 1010 becouse last one was 1009, also Z will have 1010 ID.
    And what will happen on syncronizathion?

    The same problem would happen if i set up a replication on Z to speed up query operation (and avoid it's mirroring).

    The best solution would be that X and Z may work in local with their replication, and submit changes to main database, preventing and saving primary key identity.

    I hope i was clear in explaining issue and please
    1) suggest me books (i found Pro Sql Server 2008 Replication and Mirroring, they looks like good)
    2) Say me if SQL Server can help me.

    Obiously i don't have to do it today or tomorrow.
    I have all time i need.
    Thank you a lot!

    Il saggio è quello che sa di non sapere
    Thursday, June 3, 2010 11:37 AM


All replies