none
How many mirror servers we can create in sql server mirroring RRS feed

  • Question

  • Hi Frnds,

    Can any one tell me how many Mirror server we can create in Sql server database mirroring ?

    And why?

    Thanks 

    Tuesday, March 7, 2017 5:31 AM

All replies

  • you can have only one mirrored database.

    Hope it Helps!!

    • Proposed as answer by Shanky_621MVP Tuesday, March 7, 2017 6:04 AM
    Tuesday, March 7, 2017 5:35 AM
  • Thanks Stan,

    But Why it can be only one ?

    Can you explain me pls. 

    Tuesday, March 7, 2017 5:43 AM
  • Hi,

      I don't think anyone can give you a reason for it, that is just how it was designed. However, if you wanted/needed to have more then 1, you can. either have more mirror DB (as you can have more then 1 mirror DB setup between servers), or using AG if you need them to failover at the same time.

      Or you can let us know what issue you are currently facing and we might can try to help you with that.

    Hope that helps.

    Tuesday, March 7, 2017 5:58 AM
  • Thanks Stan,

    But Why it can be only one ?

    Can you explain me pls. 

    Simply because MS designed it like so. The technology is provided by MS and it decided to keep one mirror database. Going further MS realized that lot of customers are asking for multiple copies which can run read only queries and this from SQL server 2012 we have AlwaysON Availbility Groups where you can have multiple replicas which can handle read only workloads.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Tuesday, March 7, 2017 6:04 AM
  • Adding to what is referred above, the database mirroring architecture components comprised of two mandatory server (principal and mirror) roles (and optional third role - witness). This is implemented on per-database basis and works with databases with FULL recovery model.

    Why only 1 mirror database? 

    It is by design, which required a dedicated DB mirroring endpoint to communicate and distinguish that database from other instances in the network, a similar concept with failover clustering (but with limited functionality).


    Based on the popularity and ask from the community, since SQL Server 2012 DB Mirroring has been deprecated and Availability Groups has been introduced.

    The Availability Groups feature provides an enterprise-level alternative to database mirroring. The availability group supports the failover environment for a set of read-write primary databases, and one to four sets of corresponding secondary databases. Additionally, you can make secondary databases available for read-only access or backup operations. With AlwaysOn Availability Groups automatic failover mode enabled, if the primary replica fails, a secondary replica will automatically take over servicing client requests and will become the new primary replica.


    Satya SKJ, Moderator - SQL Server MVP [Knowledge Sharing Network - www.sqlserver-qa.net]
    Author of SQL Server 2008 R2 Administration cookbook.
    Follow me @sqlmaster.

    Tuesday, March 7, 2017 7:59 AM
  • Database mirroring is designed for just one mirrored db.

    Always On functionality gives you oportunity to have up to 8 replicated redable databases.

    Regards

    Monday, November 5, 2018 3:35 PM