locked
Combining Db mirroring and Replication RRS feed

  • Question

  • Hi Team,

    Can anybody help me understand why do one go combination of Database Mirroring + Replication.
    Why can't I just have Database mirroring for high availability of my database. What is the need of combining replication as well.
    In what scenario's we choose combining both these technologies.
    Can anyone give an example of combining these 2 technologies ?
    I am not sure if people a make things complex combining all high availability solutions. ( Clustering + db mirroring + replication)

    Thanks in advance.
    Thursday, February 27, 2014 8:44 AM

Answers

  • The key is to upgrade with minimal downtime. If your Publisher / Principal crashed or we can see some unexpected error  then its going to take very long time to rebuild your system. 

    Also, you will have stipulated time frame to perform this activity. So the best practice is always good to follow

    Before doing any upgrades make sure that you've backup in place for all the databases.

    Scenario 1:

    you can apply the service pack in any order for non-bidirectional replication. for bi-directional replication topology such as merge typology or transactional replication with updateable subscriptions, you must upgrade Distributor, Publisher, and Subscribers in the following order

    http://download.microsoft.com/download/5/7/1/5718A94A-3931-457B-8567-AA0995E34870/ReleaseNotesSQL2005SP3.htm

    Considerations for Upgrading Servers in a Replication Topology

    Distributor
    Publisher
    Subscribers

    Scenario 2:
      
    Remove the witness server from mirroring( the witness can be upgraded at any time)
    Apply the service pack to the mirror server
    Fail the mirrored databases over to the mirror server
    Apply the service pack to the former principle server (now a mirror)
    Fail the mirrored databases back to the former principle server (optional)
    Apply the service pack to the witness server
    Add the witness server back to the mirroring configuration

    Refer the below link for more information

    http://technet.microsoft.com/en-us/library/bb497962.aspx

    -Prashanth

    • Marked as answer by Sofiya Li Thursday, March 6, 2014 1:46 AM
    Tuesday, March 4, 2014 10:42 PM

All replies

  • DB Mirroring is mainly for High availability system. However the mirror database will not be able to use for other purposes as the db will be in restored state. But, with witness server and few accomodations at application side, we will be able to ensure the high availability for the system.

    Replication can be considered more of load balancing than high availability, though, to an extend we can use for HA also. For an example, we have reporting procedures running, then we can use Replication server for the same to avoid load on primary server(publisher database).

    Hope the above would be helpful.


    Thursday, February 27, 2014 8:49 AM
  • Most people will mirror their publication database so if the publisher goes down the mirror server can take over as the new publisher. The clients connecting to the original publication database on the publisher/principal must have the FailoverPartner defined in their connection string.  Then on failover they will be directed to the new principal.

    You must have a remote distributor in this case and it really should be clustered.

    You might want to cluster your publisher (on the principal and mirror) and remote distributor to provide maximum up time.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, February 27, 2014 1:15 PM
    Answerer
  • Hi Team,

    One follow up question out of curiosity. If I have transactional replication with remote distributor (say) and database mirroring for publisher database in High safety mode(i.e. automatic failover), then how would  I go with patching/apply service packs ? Where should I start applying the patch at the same time taking care of High Availability as well ?

     

    Thursday, February 27, 2014 3:44 PM
  • Patch the distributor, then the mirror. You will need to pause mirroring when you patch the mirror. Once you finish patching the mirror you will need to failover and then patch the failed over principle now the mirror.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, February 27, 2014 4:47 PM
    Answerer
  • Hi Hilary,

    Thanks for the response but I am sorry I couldn't get it.

    Lets me take a step back.

    scenario 1 :

    I have Transactional Replication with Remote Distributor.
    Lets say, I have 3 sql instances out of which 1 is Publisher, 1 is Distributor and 1 is Subscriber.
    If I had to apply service pack (sql 2005 sp4), from which server should I start and why should we pick that server as first one while applying a service pack ?

    Scenario 2:

    I have High Safety Database mirroring setup with witness.i.e. 3 instances 1 for Principal , 1 for Mirror and 1 for witness, then If I had to apply service pack, when where should  I start and why we should start from that server and why not can I chose other ones first.

    Appreciate if you can provide more insight on this.

    Thursday, February 27, 2014 5:02 PM
  • The key is to upgrade with minimal downtime. If your Publisher / Principal crashed or we can see some unexpected error  then its going to take very long time to rebuild your system. 

    Also, you will have stipulated time frame to perform this activity. So the best practice is always good to follow

    Before doing any upgrades make sure that you've backup in place for all the databases.

    Scenario 1:

    you can apply the service pack in any order for non-bidirectional replication. for bi-directional replication topology such as merge typology or transactional replication with updateable subscriptions, you must upgrade Distributor, Publisher, and Subscribers in the following order

    http://download.microsoft.com/download/5/7/1/5718A94A-3931-457B-8567-AA0995E34870/ReleaseNotesSQL2005SP3.htm

    Considerations for Upgrading Servers in a Replication Topology

    Distributor
    Publisher
    Subscribers

    Scenario 2:
      
    Remove the witness server from mirroring( the witness can be upgraded at any time)
    Apply the service pack to the mirror server
    Fail the mirrored databases over to the mirror server
    Apply the service pack to the former principle server (now a mirror)
    Fail the mirrored databases back to the former principle server (optional)
    Apply the service pack to the witness server
    Add the witness server back to the mirroring configuration

    Refer the below link for more information

    http://technet.microsoft.com/en-us/library/bb497962.aspx

    -Prashanth

    • Marked as answer by Sofiya Li Thursday, March 6, 2014 1:46 AM
    Tuesday, March 4, 2014 10:42 PM
  • Thank you very much Prashanth for valuable information. 
    Wednesday, March 5, 2014 5:56 AM