Share via


Overview of PowerShell Cmdlets for AlwaysOn Availability Groups (SQL Server)

Microsoft PowerShell is a task-based command-line shell and scripting language designed especially for system administration. AlwaysOn Availability Groups provides a set of PowerShell cmdlets in SQL Server 2012 that enable you to deploy, manage, and monitor availability groups, availability replicas, and availability databases.

Note

A PowerShell cmdlet can complete by successfully initiating an action. This does not indicate that the intended work, such as the fail over of an availability group, has completed. When scripting a sequence of actions, you might have to check the status of actions, and wait for them to complete.

This topic introduces the cmdlets for the following sets of tasks:

  • Configuring a server instance for AlwaysOn Availability Groups

  • Backing up and restoring databases and transaction logs

  • Creating and managing an availability group

  • Creating and managing an availability group listener

  • Creating and managing an availability replica

  • Adding and managing an availability database

  • Monitoring availability group health

Note

For a list of topics in SQL Server 2012 Books Online that describe how to use cmdlets to perform AlwaysOn Availability Groups tasks, see the "Related Tasks" section of Overview of AlwaysOn Availability Groups (SQL Server).

Configuring a Server Instance for AlwaysOn Availability Groups

Cmdlets

Description

Supported on

Disable-SqlAlwaysOn

Disables the AlwaysOn Availability Groups feature on a server instance.

The server instance that is specified by the Path, InputObject, or Name parameter. (Must be an edition of SQL Server 2012 that supports AlwaysOn Availability Groups.)

Enable-SqlAlwaysOn

Enables AlwaysOn Availability Groups on an instance of SQL Server 2012 that supports the AlwaysOn Availability Groups feature. For information about support for AlwaysOn Availability Groups, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

Any edition of SQL Server 2012 that supports AlwaysOn Availability Groups.

New-SqlHadrEndPoint

Creates a new database mirroring endpoint on a server instance. This endpoint is required for data movement between primary and secondary databases.

Any instance of SQL Server

Set-SqlHadrEndpoint

Changes the properties of an existing database mirroring endpoint, such as the name, state, or authentication properties.

A server instance that supports AlwaysOn Availability Groups and lacks a database mirroring endpoint

Arrow icon used with Back to Top link [Top]

Backing Up and Restoring Databases and Transaction Logs

Cmdlets

Description

Supported on

Backup-SqlDatabase

Creates a data or log backup.

Any online database (for AlwaysOn Availability Groups, a database on the server instance that hosts the primary replica)

Restore-SqlDatabase

Restores a backup.

Any instance of SQL Server (for AlwaysOn Availability Groups, a server instance that hosts a secondary replica)

Important

When preparing a secondary database, you must use the -NoRecovery parameter in every Restore-SqlDatabase command.

For information about using these cmdlets to prepare a secondary database, see Manually Prepare a Secondary Database for an Availability Group (SQL Server).

Arrow icon used with Back to Top link [Top]

Creating and Managing an Availability Group

Cmdlets

Description

Supported on

New-SqlAvailabilityGroup

Creates a new availability group.

Server instance to host primary replica

Remove-SqlAvailabilityGroup

Deletes availability group.

HADR-enabled server instance

Set-SqlAvailabilityGroup

Sets the properties of an availability group; take an availability group online/offline

Server instance that hosts primary replica

Switch-SqlAvailabilityGroup

Initiates one of the following forms of failover:

  • A forced failover of an availability group (with possible data loss).

  • A manual failover of an availability group.

Server instance that hosts target secondary replica

Arrow icon used with Back to Top link [Top]

Creating and Managing an Availability Group Listener

Cmdlet

Description

Supported on

New-SqlAvailabilityGroupListener

Creates a new availability group listener and attaches it to an existing availability group.

Server instance that hosts primary replica

Set-SqlAvailabilityGroupListener

Modifies the port setting on an existing availability group listener.

Server instance that hosts primary replica

Add-SqlAvailabilityGroupListenerStaticIp

Adds a static IP address to an existing availability group listener configuration. The IP address can be an IPv4 address with subnet, or an IPv6 address.

Server instance that hosts primary replica

Arrow icon used with Back to Top link [Top]

Creating and Managing an Availability Replica

Cmdlets

Description

Supported on

New-SqlAvailabilityReplica

Creates a new availability replica. You can Use the -AsTemplate parameter to create an in-memory availability-replica object for each new availability replica.

Server instance that hosts primary replica

Join-SqlAvailabilityGroup

Joins a secondary replica to the availability group.

Server instance that hosts secondary replica

Remove-SqlAvailabilityReplica

Deletes an availability replica.

Server instance that hosts primary replica

Set-SqlAvailabilityReplica

Sets the properties of an availability replica.

Server instance that hosts primary replica

Arrow icon used with Back to Top link [Top]

Adding and Managing an Availability Database

Cmdlets

Description

Supported on

Add-SqlAvailabilityDatabase

  • On the primary replica, adds a database to an availability group.

  • On a secondary replica, joins a secondary database to an availability group.

Any server instance that hosts an availability replica (behavior differs for primary and secondary replicas)

Remove-SqlAvailabilityDatabase

  • On the primary replica, removes the database from the availability group.

  • On a secondary replica, removes the local secondary database from the local secondary replica.

Any server instance that hosts an availability replica (behavior differs for primary and secondary replicas)

Resume-SqlAvailabilityDatabase

Resumes the data movement for a suspended availability database.

The server instance on which the database was suspended.

Suspend-SqlAvailabilityDatabase

Suspends the data movement for an availability database.

Any server instance that hosts an availability replica.

Arrow icon used with Back to Top link [Top]

Monitoring Availability Group Health

The following SQL Server cmdlets enable you to monitor the health of an availability group and its replicas and databases.

Security note Security Note

You must have CONNECT, VIEW SERVER STATE, and VIEW ANY DEFINITION permissions to execute these cmdlets.

Cmdlet

Description

Supported on

Test-SqlAvailabilityGroup

Assesses the health of an availability group by evaluating SQL Server policy based management (PBM) policies.

Any server instance that hosts an availability replica.*

Test-SqlAvailabilityReplica

Assesses the health of availability replicas by evaluating SQL Server policy based management (PBM) policies.

Any server instance that hosts an availability replica.*

Test-SqlDatabaseReplicaState

Assesses the health of an availability database on all joined availability replicas by evaluating SQL Server policy based management (PBM) policies.

Any server instance that hosts an availability replica.*

* To view information about all of the availability replicas in an availability group, use to the server instance that hosts the primary replica.

For more information, see Use AlwaysOn Policies to View the Health of an Availability Group (SQL Server).

Arrow icon used with Back to Top link [Top]

See Also

Concepts

Overview of AlwaysOn Availability Groups (SQL Server)

Get Help SQL Server PowerShell