locked
Connecting to Secondary SQL server , If primary SQL Server is down RRS feed

  • Question

  • Thanks in advance,

    I have different windows  + web applications which are connected to a SQL Server which is hosted in the web, I wanted to establish a secondary SQL server to applications will connect if primary SQL server goes to down , i need solution / help 

    for two things 

    1). Replicate the data on Secondary SQL server should use  mirroring ? or any other  solution.

    2). Should create a heartbeat utility which will inform Primary server is down ? 

    Using (SQL Server 2016 Std Edition), Thinking to go for Azure.

    Thanks



    • Edited by Mirza Rahman Sunday, November 5, 2017 3:51 PM Added Version
    Sunday, November 5, 2017 11:47 AM

All replies

  • Database mirroring is one option, based on my understanding of your requirement. Although, that feature has been deprecated and will be removed from a future version of SQL Server, so something you should take into consideration for your decision.

    It sounds like what you're really after is a High Availability solution, which is exactly what AlwaysOn Availability Groups is designed for. You can read more about it here:
    Overview of Always On Availability Groups (SQL Server)

    Replication and Log shipping features are more 'distributed availability' solutions than 'high availability', so I don't think they would fit the need you describe.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Sunday, November 5, 2017 2:28 PM
  • since you are using  SQL 2016 Standard edition, basic availability group is a good option, but please check whether your can be part of a domain and cluster .  else check for the below options as well.

    a)log shipping : easy to setup but manualy fail over is required when primary is down

    b)Mirroring : automatic failover can be configured if a witness instance cab be setup 


    Thanks, Nithin

    Sunday, November 5, 2017 6:22 PM
  • Hey,

    As mentioned with SQL Server 2016 Std, you can go with Basic Availability Groups. Another option is to go with Failover Cluster Instances. However, it will require shared storage. StarWind Virtual SAN Free can help you here. 

    Good comparison: 

    https://www.starwindsoftware.com/blog/cost-and-license-considerations-between-always-on-availability-groups-and-always-on-basic-availability-groups


    Cheers,

    Alex Bykvoskyi

    StarWind Software

    Blog:   Twitter:   LinkedIn:  

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Monday, November 6, 2017 4:42 PM