commands

^

Add-DbaAgReplica

Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

Want to see the source code for this command? Check out Add-DbaAgReplica on GitHub.
Want to see the Bill Of Health for this command? Check out Add-DbaAgReplica.

Synopsis

Adds a replica to an availability group on one or more SQL Server instances.

Description

Adds a replica to an availability group on one or more SQL Server instances.

Automatically creates database mirroring endpoints if required.

Syntax

Add-DbaAgReplica
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Name] <String>]
    [[-ClusterType] <String>]
    [[-AvailabilityMode] <String>]
    [[-FailoverMode] <String>]
    [[-BackupPriority] <Int32>]
    [[-ConnectionModeInPrimaryRole] <String>]
    [[-ConnectionModeInSecondaryRole] <String>]
    [[-SeedingMode] <String>]
    [[-Endpoint] <String>]
    [[-EndpointUrl] <String[]>]
    [-Passthru]
    [[-ReadOnlyRoutingList] <String[]>]
    [[-ReadonlyRoutingConnectionUrl] <String>]
    [[-Certificate] <String>]
    [-ConfigureXESession]
    [[-SessionTimeout] <Int32>]
    [-InputObject] <AvailabilityGroup>
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2017a -AvailabilityGroup SharePoint | Add-DbaAgReplica -SqlInstance sql2017b

Adds sql2017b to the SharePoint availability group on sql2017a

Example: 2
PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2017a -AvailabilityGroup SharePoint | Add-DbaAgReplica -SqlInstance sql2017b -FailoverMode Manual

Adds sql2017b to the SharePoint availability group on sql2017a with a manual failover mode.

Example: 3
PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2017a -AvailabilityGroup SharePoint | Add-DbaAgReplica -SqlInstance sql2017b -EndpointUrl 'TCP://sql2017b.specialnet.local:5022'

Adds sql2017b to the SharePoint availability group on sql2017a with a custom endpoint URL.

Required Parameters

-SqlInstance

The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher.

Alias
Required True
Pipeline false
Default Value
-InputObject

Enables piping from Get-DbaAvailabilityGroup.

Alias
Required True
Pipeline true (ByValue)
Default Value

Optional Parameters

-SqlCredential

Login to the target instances using alternative credentials. Accepts PowerShell credentials (Get-Credential). Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported. For MFA support, please use Connect-DbaInstance.

Alias
Required False
Pipeline false
Default Value
-Name

The name of the replica. Defaults to the SQL Server instance name. This parameter is only supported if the replica is added to just one instance.

Alias
Required False
Pipeline false
Default Value
-ClusterType

Cluster type of the Availability Group. Only supported in SQL Server 2017 and above. Options include: Wsfc, External or None. Defaults to Wsfc (Windows Server Failover Cluster). The default can be changed with: Set-DbatoolsConfig -FullName 'AvailabilityGroups.Default.ClusterType' -Value '...' -Passthru | Register-DbatoolsConfig

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'AvailabilityGroups.Default.ClusterType' -Fallback 'Wsfc')
Accepted Values Wsfc,External,None
-AvailabilityMode

Sets the availability mode of the availability group replica. Options are: AsynchronousCommit and SynchronousCommit. SynchronousCommit is default.

Alias
Required False
Pipeline false
Default Value SynchronousCommit
Accepted Values AsynchronousCommit,SynchronousCommit
-FailoverMode

Sets the failover mode of the availability group replica. Options are Automatic and Manual. Automatic is default.

Alias
Required False
Pipeline false
Default Value Automatic
Accepted Values Automatic,Manual,External
-BackupPriority

Sets the backup priority availability group replica. Default is 50.

Alias
Required False
Pipeline false
Default Value 50
-ConnectionModeInPrimaryRole

Specifies the connection intent mode of an Availability Replica in primary role. AllowAllConnections by default.

Alias
Required False
Pipeline false
Default Value AllowAllConnections
Accepted Values AllowAllConnections,AllowReadWriteConnections
-ConnectionModeInSecondaryRole

Specifies the connection modes of an Availability Replica in secondary role. Options include: AllowNoConnections, AllowReadIntentConnectionsOnly, AllowAllConnections Defaults to AllowNoConnections. The default can be changed with: Set-DbatoolsConfig -FullName 'AvailabilityGroups.Default.ConnectionModeInSecondaryRole' -Value '...' -Passthru | Register-DbatoolsConfig

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'AvailabilityGroups.Default.ConnectionModeInSecondaryRole' -Fallback 'AllowNoConnections')
Accepted Values AllowNoConnections,AllowReadIntentConnectionsOnly,AllowAllConnections,No,Read-intent only,Yes
-SeedingMode

Specifies how the secondary replica will be initially seeded. 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 requires you to create a backup of the database on the primary replica and manually restore that backup on the secondary replica.

Alias
Required False
Pipeline false
Default Value
Accepted Values Automatic,Manual
-Endpoint

By default, this command will attempt to find a DatabaseMirror endpoint. If one does not exist, it will create it. If an endpoint must be created, the name "hadr_endpoint" will be used. If an alternative is preferred, use Endpoint.

Alias
Required False
Pipeline false
Default Value
-EndpointUrl

By default, the property Fqdn of Get-DbaEndpoint is used as EndpointUrl. Use EndpointUrl if a different URL is required due to special network configurations. EndpointUrl has to be an array of strings in format 'TCP://system-address:port', one entry for every instance. See details at: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/specify-endpoint-url-adding-or-modifying-availability-replica If an endpoint must be created, EndpointUrl will be used for configuration, if system-address is an ipv4 address.

Alias
Required False
Pipeline false
Default Value
-Passthru

Don't create the replica, just pass thru an object that can be further customized before creation.

Alias
Required False
Pipeline false
Default Value False
-ReadOnlyRoutingList

Sets the read only routing ordered list of replica server names to use when redirecting read-only connections through this availability replica. This parameter is only supported if the replica is added to just one instance.

Alias
Required False
Pipeline false
Default Value
-ReadonlyRoutingConnectionUrl

Sets the read only routing connection url for the availability replica. This parameter is only supported if the replica is added to just one instance.

Alias
Required False
Pipeline false
Default Value
-Certificate

Specifies that the endpoint is to authenticate the connection using the certificate specified by certificate_name to establish identity for authorization. The far endpoint must have a certificate with the public key matching the private key of the specified certificate.

Alias
Required False
Pipeline false
Default Value
-ConfigureXESession

Configure the AlwaysOn_health extended events session to start automatically as the SSMS wizard would do. https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-extended-events#BKMK_alwayson_health

Alias
Required False
Pipeline false
Default Value False
-SessionTimeout

How many seconds an availability replica waits for a ping response from a connected replica before considering the connection to have failed.

Alias
Required False
Pipeline false
Default Value 0
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Alias cf
Required False
Pipeline false
Default Value