locked
AlwaysOn Solution RRS feed

  • Question

  • How can plan to have a good solution to set up several "AlwaysOn" environments with SQL 2012 or SQL 2014?
    Thursday, August 21, 2014 5:34 AM

Answers

  • Provide a deeper discussion of the feature capabilities, rationale, and dependencies of the layers of protection offered by a SQL Server AlwaysOn solution. Describes infrastructure availability, SQL Server instance-level protection, database-level protection, and data tier application capabilities.

    Planning for Disaster Recovery

    While high availability efforts entail what you do to prevent an outage, disaster recovery efforts address what is done to re-establish high availability after the outage.

    As much as possible, disaster recovery procedures and responsibilities should be formulated before an actual outage occurs. Based upon active monitoring and alerts, the decision to initiate an automated or manual failover and recovery plan should be tied to pre-established RTO and RPO thresholds. The scope of a sound disaster recovery plan should include:

    • Granularity of failure and recovery. Depending upon the location and type of failure, you can take corrective action at different levels; that is, data center, infrastructure, platform, application, or workload.
    • Investigative source material. Baseline and recent monitoring history, system alerts, event logs, and diagnostic queries should all be readily accessible by appropriate parties.
    • Coordination of dependencies. Within the application stack, and across stakeholders, what are the system and business dependencies?
    • Decision tree. A predetermined, repeatable, validated decision tree that includes role responsibilities, fault triage, failover criteria in terms of RPO and RTO goals, and prescribed recovery steps.
    • Validation. After taking steps to recover from the outage, what must be done to verify that the system has returned to normal operations?
    • Documentation.  Capture all of the above items in a set of documentation, with sufficient detail and clarity so that a third party team can execute the recovery plan with minimal assistance.  This type of documentation is commonly referred as a ‘run book’ or a ‘cook book’.
    • Recovery rehearsals. Regularly exercise the disaster recovery plan to establish baseline expectations for RTO goals, and consider regular rotation of hosting the primary production site on the primary and each of the disaster recovery sites.

    Please go to http://msdn.microsoft.com/en-us/library/hh781257.aspx
    Download the Guide line


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by David Gutter Thursday, August 21, 2014 6:01 AM
    Thursday, August 21, 2014 5:55 AM
  • It is called an active/active SQL Server Failover Cluster. Generally only active/passive is recommended, but we've used active/active plenty.
    Active/passive is preferred because if you lose a server, then you don't have 2 SQL instances running on the same host. If your hardware can
    support the load or if you have a plan B where you cutover one of the SQL instances to a DR site.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by David Gutter Thursday, August 21, 2014 6:09 AM
    Thursday, August 21, 2014 6:00 AM
  • Please go through the steps on below link :

    http://www.brentozar.com/archive/2011/07/how-set-up-sql-server-denali-availability-groups/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by David Gutter Thursday, August 21, 2014 6:11 AM
    Thursday, August 21, 2014 6:11 AM
  • Every source recommends to run only one instance on the sql server because of hardware issues that can occur.But I can only imagine that there will be a problem with the memory if you don't set a maximum memory for each instance,since SQL is using as much memory as possible.he cpu and disk usage should be no problem at all.Besides,The failover is just a security mesure if something goes horribly wrong. So the system will still be running, even if it would be a little bit slower.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by David Gutter Thursday, August 21, 2014 6:57 AM
    Thursday, August 21, 2014 6:45 AM

All replies

  • Provide a deeper discussion of the feature capabilities, rationale, and dependencies of the layers of protection offered by a SQL Server AlwaysOn solution. Describes infrastructure availability, SQL Server instance-level protection, database-level protection, and data tier application capabilities.

    Planning for Disaster Recovery

    While high availability efforts entail what you do to prevent an outage, disaster recovery efforts address what is done to re-establish high availability after the outage.

    As much as possible, disaster recovery procedures and responsibilities should be formulated before an actual outage occurs. Based upon active monitoring and alerts, the decision to initiate an automated or manual failover and recovery plan should be tied to pre-established RTO and RPO thresholds. The scope of a sound disaster recovery plan should include:

    • Granularity of failure and recovery. Depending upon the location and type of failure, you can take corrective action at different levels; that is, data center, infrastructure, platform, application, or workload.
    • Investigative source material. Baseline and recent monitoring history, system alerts, event logs, and diagnostic queries should all be readily accessible by appropriate parties.
    • Coordination of dependencies. Within the application stack, and across stakeholders, what are the system and business dependencies?
    • Decision tree. A predetermined, repeatable, validated decision tree that includes role responsibilities, fault triage, failover criteria in terms of RPO and RTO goals, and prescribed recovery steps.
    • Validation. After taking steps to recover from the outage, what must be done to verify that the system has returned to normal operations?
    • Documentation.  Capture all of the above items in a set of documentation, with sufficient detail and clarity so that a third party team can execute the recovery plan with minimal assistance.  This type of documentation is commonly referred as a ‘run book’ or a ‘cook book’.
    • Recovery rehearsals. Regularly exercise the disaster recovery plan to establish baseline expectations for RTO goals, and consider regular rotation of hosting the primary production site on the primary and each of the disaster recovery sites.

    Please go to http://msdn.microsoft.com/en-us/library/hh781257.aspx
    Download the Guide line


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by David Gutter Thursday, August 21, 2014 6:01 AM
    Thursday, August 21, 2014 5:55 AM
  • If it would be possible to create such a structure with microsoft sql. So the hardware of both servers will be used but you still have a failover in case that a server is failing.
    Thursday, August 21, 2014 5:56 AM
  • It is called an active/active SQL Server Failover Cluster. Generally only active/passive is recommended, but we've used active/active plenty.
    Active/passive is preferred because if you lose a server, then you don't have 2 SQL instances running on the same host. If your hardware can
    support the load or if you have a plan B where you cutover one of the SQL instances to a DR site.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by David Gutter Thursday, August 21, 2014 6:09 AM
    Thursday, August 21, 2014 6:00 AM
  • Please go through the steps on below link :

    http://www.brentozar.com/archive/2011/07/how-set-up-sql-server-denali-availability-groups/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by David Gutter Thursday, August 21, 2014 6:11 AM
    Thursday, August 21, 2014 6:11 AM
  • Does SQL still have a problem with multiple instances in the newer versions?
    Thursday, August 21, 2014 6:11 AM
  • Every source recommends to run only one instance on the sql server because of hardware issues that can occur.But I can only imagine that there will be a problem with the memory if you don't set a maximum memory for each instance,since SQL is using as much memory as possible.he cpu and disk usage should be no problem at all.Besides,The failover is just a security mesure if something goes horribly wrong. So the system will still be running, even if it would be a little bit slower.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by David Gutter Thursday, August 21, 2014 6:57 AM
    Thursday, August 21, 2014 6:45 AM