Post reply

Create multiple Basic Availability Group SQL 2016 standard edition

  • December 9, 2016 at 9:29 am

    #401188

    Hello,

    I have a quick question regarding to BAG in SQL server 2016 standard edition. Can i create multiple BAG with multiple databases and fail-over using multiple listener? Or is there any other way to fail-over multiple BAG at the same time?

  • December 9, 2016 at 1:56 pm

    #1917166

    pretty sure standard edition featureing multiple Basic Availability Group is supported, and if you have multiple databases, that kind of forces you into having to design one db per group.

    this link has someone asking the similar question:

    http://dba.stackexchange.com/questions/148388/multiple-basic-availability-groups-and-performance-sql-server/148390

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 12, 2016 at 11:53 am

    #1917456

    Thanks Lowell for your response. But, this article is quit different than our requirement.

    Here is an example:

    3 databases in 3 AG.

    TestAG1, TestAG2 and TestAG3.

    I need a script which will monitor all of the AG's and if one of the AG get failed i need to fail over all AG's to other node.

  • December 13, 2016 at 10:25 am

    #1917685

    You may check the status using sys.dm_hadr_database_replica_cluster_states.

    Fail over can be automated in multiple ways like from T-SQL and PowerShell.

    From T-SQL

    ALTER AVAILABILITY GROUP <AG> FAILOVER

    OR

    ALTER AVAILABILITY GROUP <AG>FORCE_FAILOVER_ALLOW_DATA_LOSS

    From PowerShell:

    Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\SecondaryServer\InstanceName\AvailabilityGroups\<AG>

    or

    Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\SecondaryServer\InstanceName\AvailabilityGroups\<AG> -AllowDataLoss

    This page has more information on this: https://msdn.microsoft.com/en-us/library/hh231018.aspx

    Swarndeep

    http://talksql.blogspot.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply