Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Resume an Availability Database (SQL Server)

SQL Server 2012

You can resume a suspended availability database in AlwaysOn Availability Groups by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2012. Resuming a suspended database puts the database into the SYNCHRONIZING state. Resuming the primary database also resumes any of its secondary databases that were suspended as the result of suspending the primary database. If any secondary database was suspended locally, from the server instance that hosts the secondary replica, that secondary database must be resumed locally. Once a given secondary database and the corresponding primary database are in the SYNCHRONIZING state, data synchronization resumes on the secondary database.

Note Note

Suspending and resuming an AlwaysOn secondary database does not directly affect the availability of the primary database. However, suspending a secondary database can impact redundancy and failover capabilities for the primary database, until the suspended secondary database is resumed. This is in contrast to database mirroring, where the mirroring state is suspended on both the mirror database and the principal database until mirroring is resumed. Suspending an AlwaysOn primary database suspends data movement on all the corresponding secondary databases, and redundancy and failover capabilities cease for that database until the primary database is resumed.

Limitations and Restrictions

A RESUME command returns as soon as it has been accepted by the replica that hosts the target database, but actually resuming the database occurs asynchronously.

Prerequisites

  • You must be connected to the server instance that hosts the database to be resumed.

  • The availability group must be online.

  • The primary database must be online and available.

Security

Permissions

Requires ALTER permission on the database.

Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Arrow icon used with Back to Top link [Top]

To resume a secondary database

  1. In Object Explorer, connect to the server instance that hosts the availability replica on which you want to resume a database, and expand the server tree.

  2. Expand the AlwaysOn High Availability node and the Availability Groups node.

  3. Expand the availability group.

  4. Expand the Availability Databases node, right-click the database, and click Resume Data Movement.

  5. In the Resume Data Movement dialog box, click OK.

Note Note

To resume additional databases on this replica location, repeat steps 4 and 5 for each database.

Arrow icon used with Back to Top link [Top]

To resume a secondary database that was suspended locally

  1. Connect to the server instance that hosts the secondary replica whose database you want to resume.

  2. Resume the secondary database by using the following ALTER DATABASE statement:

    ALTER DATABASE database_name SET HADR RESUME

Arrow icon used with Back to Top link [Top]

To resume a secondary database

  1. Change directory (cd) to the server instance that hosts the replica whose database you want to resume. For more information, see Prerequisites, earlier in this topic.

  2. Use the Resume-SqlAvailabilityDatabase cmdlet to resume the availability group.

    For example, the following command resumes data synchronization for the availability database MyDb3 in the availability group MyAg.

    Resume-SqlAvailabilityDatabase ` 
    -Path SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MyAg\Databases\MyDb3
    
    NoteNote

    To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.

To set up and use the SQL Server PowerShell provider

Arrow icon used with Back to Top link [Top]

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.