New-SqlAvailabilityReplica

Creates an availability replica.

Syntax

New-SqlAvailabilityReplica
   -AvailabilityMode <AvailabilityReplicaAvailabilityMode>
   -FailoverMode <AvailabilityReplicaFailoverMode>
   -EndpointUrl <String>
   [-SessionTimeout <Int32>]
   [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>]
   [-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>]
   [-BackupPriority <Int32>]
   [-ReadOnlyRoutingList <String[]>]
   [-ReadonlyRoutingConnectionUrl <String>]
   [-SeedingMode <AvailabilityReplicaSeedingMode>]
   [-LoadBalancedReadOnlyRoutingList <String[][]>]
   [-Name] <String>
   [[-Path] <String>]
   [-Script]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
New-SqlAvailabilityReplica
   -AvailabilityMode <AvailabilityReplicaAvailabilityMode>
   -FailoverMode <AvailabilityReplicaFailoverMode>
   -EndpointUrl <String>
   [-SessionTimeout <Int32>]
   [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>]
   [-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>]
   [-BackupPriority <Int32>]
   [-ReadOnlyRoutingList <String[]>]
   [-ReadonlyRoutingConnectionUrl <String>]
   [-SeedingMode <AvailabilityReplicaSeedingMode>]
   [-LoadBalancedReadOnlyRoutingList <String[][]>]
   [-AsTemplate]
   [-Version <ServerVersion>]
   [-Name] <String>
   [-Script]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
New-SqlAvailabilityReplica
   -AvailabilityMode <AvailabilityReplicaAvailabilityMode>
   -FailoverMode <AvailabilityReplicaFailoverMode>
   -EndpointUrl <String>
   [-SessionTimeout <Int32>]
   [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>]
   [-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>]
   [-BackupPriority <Int32>]
   [-ReadOnlyRoutingList <String[]>]
   [-ReadonlyRoutingConnectionUrl <String>]
   [-SeedingMode <AvailabilityReplicaSeedingMode>]
   [-LoadBalancedReadOnlyRoutingList <String[][]>]
   [-Name] <String>
   [-InputObject] <AvailabilityGroup>
   [-Script]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]

Description

The New-SqlAvailabilityReplica cmdlet creates an availability replica. Run this cmdlet on the instance of SQL Server that hosts the primary replica.

To create an availability replica before you create an availability group, specify the AsTemplate parameter. To add a replica to an existing availability group, either the InputObject or Path parameter specifies the availability group.

Examples

Example 1: Creates a representation of an availability replica

PS C:\> $ServerObject = Get-Item "SQLSERVER:\Sql\PrimaryServer\InstanceName"
PS C:\> New-SqlAvailabilityReplica -Name "PrimaryServer\Instance" -EndpointUrl "TCP://PrimaryServerName.domain.com:5022" -FailoverMode Automatic -AvailabilityMode SynchronousCommit -AsTemplate -Version $ServerObject.Version

This example creates an in-memory representation of an availability replica. No changes are committed to the server. You can use this replica as a value for the AvailabilityReplica parameter of New-SqlAvailabilityGroup

The first command gets an instance of the primary server.

The second command creates the availability replica. This replica uses 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 Version parameter specifies the version of the server instance that will host this new replica.

Example 2: Creates an availability replica that supports manual failover and the asynchronouscommit

PS C:\> $ServerObject = Get-Item "SQLSERVER:\Sql\PrimaryServer\InstanceName"
PS C:\> New-SqlAvailabilityReplica -Name "SecondaryServer\Instance" -EndpointUrl "TCP://PrimaryServerName.domain.com:5022" -FailoverMode Manual -AvailabilityMode AsynchronousCommit -AsTemplate -Version $ServerObject.Version

This example creates an in-memory representation of an availability replica. No changes are committed to the server.

The first command gets an instance of the primary server.

The second command creates the availability replica. This replica uses the database mirroring 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 Version parameter specifies the version of the server instance that will host this new replica.

Example 3: Add an availability replica to an availability group

PS C:\> New-SqlAvailabilityReplica -Name "SecondaryServer\Instance" -EndpointUrl "TCP://PrimaryServerName.domain.com:5022" -FailoverMode Manual -AvailabilityMode AsynchronousCommit -ConnectionModeInSecondaryRole AllowAllConnections -Path "SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\MainAG"

This command adds an availability replica to an existing availability group named 'MainAG'. This replica supports manual failover and asynchronous-commit availability mode. In the secondary role, this replica supports read access connections. This configuration lets you offload read-only processing to this replica.

Parameters

-AccessToken

The access token used to authenticate to SQL Server, as an alternative to user/password or Windows Authentication.

This can be used, for example, to connect to SQL Azure DB and SQL Azure Managed Instance using a Service Principal or a Managed Identity.

The parameter to use can be either a string representing the token or a PSAccessToken object as returned by running Get-AzAccessToken -ResourceUrl https://database.windows.net.

This parameter is new in v22 of the module.

Type:PSObject
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-AsTemplate

Indicates that this cmdlet creates a temporary AvailabilityReplica object in memory. Specify this parameter to create an availability group before you create an availability replica. Create an availability group by using the New-SqlAvailabilityGroup cmdlet. Specify the temporary availability replica as the value of the AvailabilityReplica parameter.

If you specify AsTemplate, this cmdlet ignores values for the InputObject and Path parameters.

If you specify this parameter, you must also specify a SQL Server version for the Version parameter, or your current session must have an active connection to an instance.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-AvailabilityMode

Specifies the replica availability mode.

You can specify a value of $Null.

Type:AvailabilityReplicaAvailabilityMode
Accepted values:AsynchronousCommit, SynchronousCommit, Unknown, ConfigurationOnly
Position:Named
Default value:None
Required:True
Accept pipeline input:False
Accept wildcard characters:False

-BackupPriority

Specifies the desired priority of the replicas in performing backups. The acceptable values for this parameter are integers from 0 through 100. Of the set of replicas which are online and available, the replica that has the highest priority performs the backup.

Type:Int32
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Confirm

Prompts you for confirmation before running the cmdlet.

Type:SwitchParameter
Aliases:cf
Position:Named
Default value:False
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ConnectionModeInPrimaryRole

Specifies how the availability replica handles connections when in the primary role.

The acceptable values for this parameter are:

  • AllowReadWriteConnections. Allows read/write connections
  • AllowAllConnections. Allows all connections
Type:AvailabilityReplicaConnectionModeInPrimaryRole
Accepted values:AllowAllConnections, AllowReadWriteConnections, Unknown
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ConnectionModeInSecondaryRole

Specifies how the availability replica handles connections when in the secondary role. The acceptable values for this parameter are:

  • AllowNoConnections. Disallows connections
  • AllowReadIntentConnectionsOnly. Allows only read-intent connections
  • AllowAllConnections. Allows all connections
Type:AvailabilityReplicaConnectionModeInSecondaryRole
Accepted values:AllowNoConnections, AllowReadIntentConnectionsOnly, AllowAllConnections, Unknown
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Encrypt

The encryption type to use when connecting to SQL Server.

This value maps to the Encrypt property SqlConnectionEncryptOption on the SqlConnection object of the Microsoft.Data.SqlClient driver.

In v22 of the module, the default is Optional (for compatibility with v21). In v23+ of the module, the default value will be 'Mandatory', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:String
Accepted values:Mandatory, Optional, Strict
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-EndpointUrl

Specifies the URL of the database mirroring endpoint. This URL is a TCP address in the following form:

TCP://system-address:port

Type:String
Position:Named
Default value:None
Required:True
Accept pipeline input:False
Accept wildcard characters:False

-FailoverMode

Specifies the failover mode.

You can specify a value of $Null

Type:AvailabilityReplicaFailoverMode
Accepted values:Automatic, Manual, External, Unknown
Position:Named
Default value:None
Required:True
Accept pipeline input:False
Accept wildcard characters:False

-HostNameInCertificate

The host name to be used in validating the SQL Server TLS/SSL certificate. You must pass this parameter if your SQL Server instance is enabled for Force Encryption and you want to connect to an instance using hostname/shortname. If this parameter is omitted then passing the Fully Qualified Domain Name (FQDN) to -ServerInstance is necessary to connect to a SQL Server instance enabled for Force Encryption.

This parameter is new in v22 of the module.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-InputObject

Specifies the availability group, as an AvailabilityGroup object, to which the replica belongs.

Type:AvailabilityGroup
Position:2
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-LoadBalancedReadOnlyRoutingList

Specifies the load-balanced read-only routing list.

The routing list is a list of load-balanced sets, which in turn are lists of replicas.

For example, passing a value like

@('Server1','Server2'),@('Server3'),@('Server4')

means what we are passing 3 load-balanced sets: 1 with 2 replicas (Server1 and Server2) and 2 with just one (Server3 and Server4, respectively).

At runtime, SQL Server will look sequentially at all the load-balanced sets until finds one such that at least on replica in it is available and use it for load-balancing.

So, in the example above, if both Server1 and Server2 are not available, but Server3 is, SQL Server will pick Server3.

This cmdlet only sets the read-only routing list and does not check on the availablility of the specified replicas.

Type:String[][]
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Name

Specifies a name for the availability replica in the following format: Computer\Instance

Type:String
Position:1
Default value:None
Required:True
Accept pipeline input:False
Accept wildcard characters:False

-Path

Specifies the path of the availability group to which the replica belongs. If you do not specify this parameter, this cmdlet uses current working location.

Type:String
Position:2
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ReadonlyRoutingConnectionUrl

Specifies the fully-qualified domain name (FQDN) and port to use when routing to the replica for read only connections, as in the following example: TCP://DBSERVER8.manufacturing.Contoso.com:7024

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ReadOnlyRoutingList

Specifies 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 parameter applies if the availability replica is the current primary replica of the availability group.

Type:String[]
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Script

Indicates that this cmdlet returns a Transact-SQL script that performs the task that this cmdlet performs.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-SeedingMode

Specifies how the secondary replica will be initially seeded.

Allowed values:

  • Automatic. Enables direct seeding. This method will seed the secondary replica over the network. This method does not require you to backup and restore a copy of the primary database on the replica.
  • Manual. Specifies manual seeding. This method requires you to create a backup of the database on the primary replica and manually restore that backup on the secondary replica.
Type:AvailabilityReplicaSeedingMode
Accepted values:Automatic, Manual
Position:Named
Default value:Manual
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-SessionTimeout

Specifies the amount of time, in seconds, to wait for a response between the primary replica and this replica before the connection fails.

Type:Int32
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-TrustServerCertificate

Indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

In v22 of the module, the default is $true (for compatibility with v21). In v23+ of the module, the default value will be '$false', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Version

Specifies a SQL Server version. If you specify the AsTemplate parameter, you must specify a version. The template object is created in design mode on a server that includes this version. You can specify an integer or a string, as in the following examples (SQL Server 2017):

  • 14
  • '14.0.0'
Type:ServerVersion
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type:SwitchParameter
Aliases:wi
Position:Named
Default value:False
Required:False
Accept pipeline input:False
Accept wildcard characters:False

Inputs

Microsoft.SqlServer.Management.Smo.AvailabilityGroup

You can pass an availability group to this cmdlet.

Outputs

Microsoft.SqlServer.Management.Smo.AvailabilityReplica

This cmdlet returns an availability replica.