New-SqlAvailabilityGroup

Creates an availability group on the server instance that will host the initial primary replica.
New-SqlAvailabilityGroup [-Name*] <string> [[-Path] <string>] -AvailabilityReplica* <AvailabilityReplica[]>[-AutomatedBackupPreference <AvailabilityGroupAutomatedBackupPreference>] [-Database <string[]>][-FailureConditionLevel <AvailabilityGroup>] [-HealthCheckTimeout <int>] [-Script] [-Confirm] [-WhatIf][<CommonParameters>]
New-SqlAvailabilityGroup [-Name*] <string> [-InputObject*] <Server> -AvailabilityReplica* <AvailabilityReplica[]>[-AutomatedBackupPreference <AvailabilityGroupAutomatedBackupPreference>] [-Database <string[]>][-FailureConditionLevel <AvailabilityGroup>] [-HealthCheckTimeout <int>] [-Script] [-Confirm] [-WhatIf][<CommonParameters>]

The New-SqlAvailabilityGroup cmdlet creates an availability group. The server instance specified by the InputObject or Path parameter will host the initial primary replica.

Parameters
-AutomatedBackupPreference <AvailabilityGroupAutomatedBackupPreference>

The automated backup preference for the availability group. Valid options are:Primary: Specifies that the backups always occur on the primary replica. This is often because the user needs features not available when backup is run on a secondary, such as differential backups.SecondaryOnly: Specifies that backups are never performed on primary replicas. If the primary replica is the only replica online, the backup will not occur.Secondary: Specifies that backups occur on secondary replicas unless the primary replica is the only replica online. Then the backup occurs on the primary replica.None: This option specifies that the user does not want primary/secondary status taken into account when choosing the replica to perform backups. The BackupPriority in combination with the Online/Connected status are the only factors considered.

-AvailabilityReplica <AvailabilityReplica[]>

  • This value is required

The AvailabilityReplica object for each availability replica you want to include in the new availability group. This parameter can be nested, but it cannot be empty or null.TIP: To create AvailabilityReplica objects, use the New-SqlAvailabilityReplica cmdlet with the AsTemplate parameter.

-Database <string[]>

A list of local, read-write user databases that use the full recovery model and do not use AUTO_CLOSE. These databases cannot belong to another availability group and cannot be configured for database mirroring. This parameter cannot be null or empty.

-FailureConditionLevel <AvailabilityGroup>

Determines the automatic failover behavior of the availability group. Valid options are:OnServerDown: Failover or restart when the SQL Server service stops.OnServerUnresponsive: Failover or restart when any condition of lower value is satisfied, plus when the SQL Server service is connected to the cluster and the “HealthCheckTimeout” threshold is exceeded, or when the Availability Replica currently in primary role is in a failed state. (See HealthCheckTimeout parameter.)OnCriticalServerError: Failover or restart when any condition of lower value is satisfied, plus when an internal critical Server error occurs (out of memory condition, serious write-access violation, or too much dumping).OnModerateServerError: Failover or restart when any condition of lower value is satisfied, plus when a moderate Server error occurs (persistent out of memory condition).OnAnyQualifiedFailureConditions: Failover or restart when any condition of lower value is satisfied, plus when a qualifying failure condition occurs (engine worker thread exhaustion, unsolvable deadlock detected).

-HealthCheckTimeout <int>

The length of time after which an unresponsive server is declared unhealthy.

-InputObject <Server>

  • This value is required
  • Accepts pipeline input ByValue

The Server object of the instance that hosts the primary replica.

-Name <string>

  • This value is required

The name of the availability group to be created.

-Path <string>

The path to the instance of SQL Server that will host the initial primary replica of the availability group. If the specified path does not exist an error is thrown. This is an optional parameter. If not specified, the value of this parameter defaults to the current working location.

-Script [<SwitchParameter>]

Outputs a Transact-SQL script that performs the task executed by this cmdlet.

-Confirm [<SwitchParameter>]

Prompts you for confirmation before executing the command.

-WhatIf [<SwitchParameter>]

Describes what would happen if you executed the command without actually executing the command.

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,ErrorAction, ErrorVariable, WarningAction, WarningVariable,OutBuffer, PipelineVariable, and OutVariable.

Inputs

Microsoft.SqlServer.Management.Smo.Server

Specifies the server instance that contains the the primary replica.

Outputs

Microsoft.SqlServer.Management.Smo.AvailabilityGroup

A new power shell object containing the availability group.

Examples
  1. This example creates a new availability group, ‘MyAG’, with two availability replicas and two databases (‘MyDatabase1’ and ‘MyDatabase2’):
    C:PS> $primaryServer = Get-Item "SQLSERVER:SQLPrimaryServerInstanceName"
    $secondaryServer = Get-Item "SQLSERVER:SQLSecondaryServerInstanceName"
    
       $primaryReplica = New-SqlAvailabilityReplica `
       -Name "PrimaryServerInstanceName" `
       -EndpointUrl "TCP://PrimaryServer.domain:5022" `
       -FailoverMode "Automatic" `
       -AvailabilityMode "SynchronousCommit" `
       -AsTemplate `
       -Version ($primaryServer.Version)
       # Create the initial secondary replica
       $secondaryReplica = New-SqlAvailabilityReplica `
       -Name "SecondaryServerInstanceName" `
       -EndpointUrl "TCP://SecondaryServer.domain:5022" `
       -FailoverMode "Automatic" `
       -AvailabilityMode "SynchronousCommit" `
       -AsTemplate `
       -Version ($secondaryServer.Version)
       # Create the Availability Group.
       New-SqlAvailabilityGroup -InputObject $primaryServer -Name MyAG -AvailabilityReplica ($primaryReplica, 
       $secondaryReplica) -Database @("MyDatabase1","MyDatabase2")

    First, the example uses the New-SqlAvailabilityReplica cmdlet to create in-memory representations of the two replicas (‘$primaryReplica’ and ‘$secondaryReplica’). These replicas are configured to use synchronous-commit availability mode with automatic failover. Their database mirroring endpoints use port 5022. The example then uses the New-SqlAvailabilityGroup cmdlet to create the availability group.

Additional Notes