locked
SQL database replicating from multiple principles to a client RRS feed

  • Question

  • Hi there, I will like to get advices if the following scenario will work out and what is the possible solutions.

    I am looking at some SQL databases HA solution where multiple primary databases hosted on different servers are mirroring to a main single SQL server client. Where each of these primary SQL hosting server will have the ability to individually fail over to the main SQL server client when there is a need.

    For example, Site 1 can fail over to Client Server on cloud in case anything happens where the rest are still replicating from their own main DBs to the Client Server. Same goes for Site 2 database and so on. Each sites operates individually.

    Will the above scenario be possible? Sorry, I will like to accompany with a diagram for a more clearer explanation but was not allowed as my account is new.  

    Thanks.

    Wednesday, March 25, 2020 3:05 AM

All replies

  • Hello ToshYeo,

    First, according to your assumption, it is feasible. However database mirroring is not the best opinion. This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 

    For your scenario, you would better to use Always On availability groups.  An availability group supports a replicated environment for a discrete set of user databases, known as availability databases. An HA availability group is a group of databases that fail over together. An availability group supports one set of primary databases and one to eight sets of corresponding secondary databases. More detail, you can refer to Overview of Always On Availability Groups (SQL Server)

    And you can follow this article to configure AG:  Configuring a SQL Server AlwaysOn High Availability Group

    Hope it will help.

    Best Regards

    Dawn


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, March 26, 2020 8:03 AM
  • Hello friend,
    Have you solved the question?
    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members finding and reading the thread easily. 
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 27, 2020 9:40 AM
  • Hey Dawn,

    Appreciated your reply, I am gathering resources to test this out.
    Please give me some time to come back if I have concluded or there further questions.

    Thanks!

    Yeo 

    Monday, March 30, 2020 8:19 AM