Database mirroring with transactional replication

  • Hi

    How database mirroring is useful with transactional replication and in which situation it should be used?

    Regards

  • its for scalability and high availability that we use replication and mirroring together.

  • Can you tell me in which situations it can be used?

  • yes sure,

    before that i would like you to ask yourself few questions

    1. what are the options by which you can make your server available 99.9999% up in an year?

    2. what are the high availablity options available with sql server?

    ok lets take replication:

    there are different types of replications available.

    1.snapshot replication

    2. transactional replication

    3. merge replication

    replication has three servers

    1. publisher: the server from which data is published to the different servers in different or same location with the help of distribution server.

    2. distribution server distributes the data changes from the publisher to the different subscribers

    3. subscribers are the servers where data from publisher is replicated with the help of distributor.

    for a successful replication all the three servers should function correctly. if any of the servers dont do their function properly the replication is failed.

    coming to mirroring :

    this also has three servers

    1. principal server: this the server from which data is taken and mirrored on mirror server.

    2. witness server; it just watches whether the principal and mirror servers are able to communicate or not. if due to any reason if the principal server fails then the fail over is switched to mirror server and there will be no down time due the server failure.

    3. mirror server: this server will be in recovery mode and tranactions from principal server is mirrored to this server.

    note: only databases are mirrored or replicated it is not the instances which are done.

    and the important point to remember is in replication you can do the partial data to be replicated among the servers while in mirroring the whole database is mirrored and you cant select the subset of the data.

    when you combine the both high availability options you get double protection that is near 99.999 percent uptime.

    you can check the attachment to know how it is done.

  • Thanks a lot.It really helped to clear the concept.

Viewing 5 posts - 1 through 4 (of 4 total)

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