New-SqlAvailabilityReplica

Creates an availability replica. This cmdlet must be executed on the server instance that hosts the primaryavailability replica.
New-SqlAvailabilityReplica [-Name*] <string> [[-Path] <string>] -AvailabilityMode*<AvailabilityReplicaAvailabilityMode> -EndpointUrl* <string> -FailoverMode* <AvailabilityReplicaFailoverMode?>[-BackupPriority <int>] [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>][-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>] [-ReadonlyRoutingConnectionUrl<string>] [-ReadonlyRoutingList <string[]>] [-Script] [-SessionTimeout <int>] [-Confirm] [-WhatIf][<CommonParameters>]
New-SqlAvailabilityReplica [-Name*] <string> [-InputObject*] <AvailabilityGroup> -AvailabilityMode*<AvailabilityReplicaAvailabilityMode> -EndpointUrl* <string> -FailoverMode* <AvailabilityReplicaFailoverMode?>[-BackupPriority <int>] [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>][-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>] [-ReadonlyRoutingConnectionUrl<string>] [-ReadonlyRoutingList <string[]>] [-Script] [-SessionTimeout <int>] [-Confirm] [-WhatIf][<CommonParameters>]
New-SqlAvailabilityReplica [-Name*] <string> -AsTemplate* -AvailabilityMode* <AvailabilityReplicaAvailabilityMode>-EndpointUrl* <string> -FailoverMode* <AvailabilityReplicaFailoverMode?> [-BackupPriority <int>][-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>] [-ConnectionModeInSecondaryRole<AvailabilityReplicaConnectionModeInSecondaryRole>] [-ReadonlyRoutingConnectionUrl <string>] [-ReadonlyRoutingList<string[]>] [-Script] [-SessionTimeout <int>] [-Version <ServerVersion>] [-Confirm] [-WhatIf] [<CommonParameters>]

The New-SqlAvailabilityReplica cmdlet creates an availability replica. This cmdlet must be executed on the instance of SQL Server that hosts the primary replica. The AsTemplate, InputObject, and Path parameters are mutually exclusive. To create AvailabilityReplica objects before creating a new availability group, use the AsTemplate parameter. When adding a replica to an existing availability group use either the InputObject or Path parameter to specify the path of the availability group. If neither is specified, the current path is used.

Parameters
-AsTemplate [<SwitchParameter>]

  • This value is required
  • Default value is 0

The path of a new availability replica for which you want to create a temporary AvailabilityReplica object in memory. Use AsTemplate to create each new availability replica that you plan to include in a new availability group. Then, create the availability group by running the New-SqlAvailabilityGroup cmdlet and specify the AvailabilityReplica objects as values of the AvailabilityReplica parameter.When AsTemplate is specified the InputObject and Path parameters are ignored.When AsTemplate is specified, you must pass a SQL Server version to the Version parameter or your current session must have an active connection to an instance.

-AvailabilityMode <AvailabilityReplicaAvailabilityMode>

  • This value is required

Indicates whether Replica is Synchronous Commit or Asynchronous Commit mode. It can be null.

-BackupPriority <int>

This option is an integer in the range of 0..100, and represents the desired priority of the replicas in performing backups. Of the set of replicas which are online and available, the replica with the highest priority will be chosen to perform the backup.

-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>

How the availability replica should handle connections when in the primary role. Valid settings are:AllowReadWriteConnections: Allow read/write connectionsAllowAllConnections: Allow all connections

-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>

How the availability replica should handle connections when in the secondary role. Valid settings are:AllowNoConnections: Disallows connectionsAllowReadIntentConnectionsOnly: Allows only read-intent connectionsAllowAllConnections: Allows all connections

-EndpointUrl <string>

  • This value is required

The URL of the database mirroring endpoint. This URL is a TCP address in the form TCP://system-address:port.

-FailoverMode <AvailabilityReplicaFailoverMode?>

  • This value is required

Can be Automatic or Manual. It can be null.

-InputObject <AvailabilityGroup>

  • This value is required
  • Accepts pipeline input ByValue

The AvailabilityGroup object of the availability group to which the new replica will belong.

-Name <string>

  • This value is required

The name of the new availability replica. Specify this name using the computerinstance format.

-Path <string>

The path to the availability group. The new replica will belong to the availability group at this path. This is an optional parameter. If not specified, the value of this parameter defaults to the current working location.

-ReadonlyRoutingConnectionUrl <string>

The connectivity fully-qualified domain name (FQDN) and port to use when routing to the replica for read only connections. For example: TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024

-ReadonlyRoutingList <string[]>

An ordered list of replica server names that represent the probe sequence for connection director to use when redirecting read-only connections through this availability replica. This is applicable when the availability replica is the current primary of the availability group.

-Script [<SwitchParameter>]

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

-SessionTimeout <int>

The number of seconds to wait for a response between the primary replica and this replica before considering the connection failed.

-Version <ServerVersion>

Specifies a SQL Server version. This parameter is only relevant if AsTemplate is specified. The template object is created in design mode on a server with this version.Version can be specified as an integer or string, if desired. For example: -Version 11, or -Version “11.0.0”

-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.AvailabilityGroup

Specifies the availability group for the replica.

Outputs

Microsoft.SqlServer.Management.Smo.AvailabilityReplica

A power shell object that contains the newly created availability replica.

Examples
  1. This example creates an in-memory representation of an availability replica:
    C:PS> $serverObject = (Get-Item SQLSERVER:SqlPrimaryServerInstanceName)
    $serverVersion = $serverObject.Version
    $endpointURL = "TCP://PrimaryServerName.domain.com:5022"
    $failoverMode = "Automatic"
    $availabilityMode = "SynchronousCommit"
    New-SqlAvailabilityReplica -Name PrimaryServerInstance -EndpointUrl $endpointURL -FailoverMode $failoverMode -AvailabilityMode $availabilityMode -AsTemplate -Version $serverVersion
    

    No changes are committed to the server. This replica should be passed to the AvailabilityReplica parameter of the New-SqlAvailabilityGroup cmdlet. This replica will use the database mirroring endpoint located at the specified URL to communicate with other replicas in the availability group. This replica supports automatic failover and the synchronous-commit availability mode. The -AsTemplate and -Version parameters are required to construct the in-memory representation. The -Version parameter specifies the version of the server instance that will host this replica. The version number must be 11.

  2. This command creates an in-memory representation of an availability replica:
    C:PS> $serverObject = (Get-Item SQLSERVER:SqlPrimaryServerInstanceName)
    $serverVersion = $serverObject.Version
    $endpointURL = "TCP://PrimaryServerName.domain.com:5022"
    $failoverMode = "Manual"
    $availabilityMode = "AsynchronousCommit"
    New-SqlAvailabilityReplica -Name SecondaryServerInstance -EndpointUrl $endpointURL -FailoverMode $failoverMode -AvailabilityMode $availabilityMode -AsTemplate -Version $serverVersion
    

    No changes are committed to the server. This replica should be passed to the AvailabilityReplica parameter of the New-SqlAvailabilityGroup cmdlet. This replica will use the endpoint located at the specified URL to communicate with other replicas in the availability group. This replica supports manual failover and the asynchronous-commit availability mode. The -AsTemplate and -Version parameters are required to construct the in-memory representation. The -Version parameter specifies the version of the server instance that will host this replica. The version number must be 11.

  3. This example adds an availability replica to an existing availability group named ‘MyAg’:
    C:PS> $agPath = "SQLSERVER:SqlPrimaryServerInstanceNameAvailabilityGroupsMyAg"
    $endpointURL = "TCP://PrimaryServerName.domain.com:5022"
    $failoverMode = "Manual"
    $availabilityMode = "AsynchronousCommit"
    $secondaryReadMode = "AllowAllConnections"
    New-SqlAvailabilityReplica -Name SecondaryServerInstance -EndpointUrl $endpointURL -FailoverMode $failoverMode -AvailabilityMode $availabilityMode -ConnectionModeInSecondaryRole $secondaryReadMode -Path $agPath
    

    This replica supports manual failover and asynchronous-commit availability mode. In the secondary role, this replica will support read access connections, allowing you to offload read-only processing to this replica.

Additional Notes