SQL Server Replication

Shabna_Nazar
Shabna_Nazar Member Posts: 10
edited 2007-09-10 in SQL General
We have two databases on two SQL servers
DB1 on SQL Server A
DB2 on SQL Server B

User enters all transactions in DB1. This has to be replicated in DB2. If SQL Server A goes down, users should be able to use DB2 on SQL Server B. When DB1 on SQL Server A is up, we want all the transactions done in DB2 on SQL Server B to be replicated in DB1 on SQL Server A. Is this scenario possible in Navision 4.0 SP3?

Comments

  • ovicash
    ovicash Member Posts: 141
    yes is possible. I don't know if replication is the best option. You could try log shipping.
    ovidiu

    Best Regards
  • stryk
    stryk Member Posts: 645
    I don't think that "replication" is the solution you're looking for, as it is basically considered to merge transactions from different systems.

    As far as I understood, you're looking for a standby/failover soution. Here plenty of solutions are possible, from "Log Shipping" over "Mirroring" to "Failover Clustering". Which option is the best one, depends on your "safety"requirements: the acceptable server-down-time and amount of data-loss. Further it depends on your facilities and the budget.
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Shabna_Nazar
    Shabna_Nazar Member Posts: 10
    How log shipping can be achieved through two databases on two different servers? Please brief me about the steps for the same.
  • stryk
    stryk Member Posts: 645
    What version and edition of SQL Server are you using?
    You need "SQL Server 2000 Enterprise Edition" or "SQL Server 2005 Standard Edition" (or higher).

    If you're browsing the "Books Online" for "Transaction Log Shipping" you'll find a detailed description about how to setup and use the feature.

    Basically, it works like this:
    You create periodically a backup of the TLog of DB A (Server 1) and transfer it to Server 2. Server 2 now re-processes the TLog on DB B, thuhs DB B is an asynchronously updated version of DB A.
    If Server 1/DB A fails, you can switch Server 2/DB B online and continue working.
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • colin_leversuch-roberts
    colin_leversuch-roberts Member Posts: 113
    You need peer to peer replication, log shipping will not do what you require.
    However as to how well Navision will work with replication is another matter, and that's not a criticism of Navision, database/applications that are required to be replicated usually should be designed for this. I've successsfully replicated ( one way ) databases you can't replicate with standard transactional replication, but it's not easy. Not done much with peer to peer ( only available in 2005 ) but it should do what you want.
  • DenSter
    DenSter Member Posts: 8,307
    Won't replication interfere with regular NAV running, with the sheer number of queries flying around?

    As long as the time interval used for log shipping is an acceptable loss (the default is 15 minutes, but you can go down to less than that), I think log shipping is an excellent solution for this, it is even intended for that scenario.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • colin_leversuch-roberts
    colin_leversuch-roberts Member Posts: 113
    I can't say with certainity but I doubt replication would hurt performance, I've used it in some busy databases. I like log shipping but it provides a different level of availablity of your standby server, all depends what you want. Standby servers take longer to bring on line and switch back - changing the direction of log shipping etc. isn't that quick - that said if replication fails then putting it back can sometimes be a real pain.
    My personal preference for standby is log shipping.
  • bbrown
    bbrown Member Posts: 3,268
    The new SQL 2005 Database Mirroring is also an option.
    There are no bugs - only undocumented features.
  • DenSter
    DenSter Member Posts: 8,307
    That's basically log shipping on a per-transaction basis, it has very similar features.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • bbrown
    bbrown Member Posts: 3,268
    DenSter wrote:
    That's basically log shipping on a per-transaction basis, it has very similar features.

    True, to a point. However Database Mirroring (full safety mode) eliminates the manual steps of bringing the standby server online. Users simply restart their Navision clients and connect to the backup server.
    There are no bugs - only undocumented features.
  • DenSter
    DenSter Member Posts: 8,307
    Of course the details are different, I just wanted to illustrate that mirroring follows the same mechanism as log shipping.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • bbrown
    bbrown Member Posts: 3,268
    Database Mirroring and log Shipping are as similar as they are different. With Database Mirroring (full safety) both databases are written to together. This eliminates the out-of-sync or loss data issues possible with log shipping. However this make Database Mirroring (full safety) a poor choice for servers connected over low speed WAN links.

    There are several options when it comes to fault-tolerance and disaster-recovery. The idea is to select the choice that best provides the needed features at the right budget, and not simply select the method that is gettign the most buzz.
    There are no bugs - only undocumented features.
  • DenSter
    DenSter Member Posts: 8,307
    What I always understood mirroring to be is:
    - transaction is committed to source database
    - transaction log for that transaction is shipped to the mirrored database
    - instead of backup files on user defined time intervals it's at every transaction

    The difference would then be that there's not an actual transaction log backup file being transferred, so that part is done internally.

    But I always understood that it has a sequence: source database, commit, mirror the transaction to the mirrored database, commit.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • bbrown
    bbrown Member Posts: 3,268
    It depends on the setting for transaction safety. With transaction safety off mirroring behaves much like log shipping (as you describe). With transaction safety on, mirroring commits changes to either both databases or neither. In this mode, since the primary must wait for the secondary, a secondary that is considerably slower than the primary or a slow network link between the systems can have a performance impact.
    There are no bugs - only undocumented features.
  • DenSter
    DenSter Member Posts: 8,307
    ah thanks :) very useful information.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • Peter_Wijntjes
    Peter_Wijntjes Member Posts: 28
    Instead of looking for the SQLServer alternatives you can look into "replicated" storage.

    I know of some sites where the storage system has been configured to do exactly what you want by mirroring SQL files to a separate set of disks.

    This type of "software" works indepent of your application requirements.
    Peter Wijntjes
    MBS NAV Consultant
  • bbrown
    bbrown Member Posts: 3,268
    Peter Wijntjes wrote:
    Instead of looking for the SQLServer alternatives you can look into "replicated" storage.

    I know of some sites where the storage system has been configured to do exactly what you want by mirroring SQL files to a separate set of disks.

    This type of "software" works indepent of your application requirements.

    That's what SQL Mirroring is doing. Many of these third-party solutions are leveraging underlying Windows Server and SQL technologies
    There are no bugs - only undocumented features.

Categories