Add a Log Shipping Primary Database and Secondary Database(s) to an Existing Availability Group

  • Artigo

Add a Log Shipping Primary Database and Secondary Database(s) to an Existing Availability Group
by Margi Showman

This blog is relevant if: (1) you have a log shipping primary database on the server instance that hosts the primary replica of an availability group, (2) at least one of the corresponding log shipping secondary database resides on a server instance that hosts one of the secondary replicas, and (3) the secondary log shipping primary and secondary databases have the same database name.

Important: If a given server instance that hosts a secondary replica for the availability group does not also host a secondary database for the log shipping configuration, you will need to manually prepare the AlwaysOn secondary database on that server instance. For more information, see Manually Prepare a Secondary Database for an Availability Group (SQL Server) in SQL Server Books Online.

To prepare AlwaysOn secondary databases

  1.  Connect to the server instance that hosts both primary replica and the log-shipping primary database, and perform the following steps:

    1. Identify the log-shipping backup job for the log shipping primary database by expanding the SQL Server Agent\Jobs folder in SQL Server Management Studio. The job name normally looks like: LSbackup_<dbname>
    2. Execute the backup job.
    3. When the backup job finishes, disable it. The primary database will remain available.

  2. For the log shipping secondary database on the instance of SQL Server that hosts a secondary replica:

    1. Connect to the server instance.
    2. Identify the log shipping copy job and restore job of the log shipping secondary database by expanding SQL Server Agent\Jobs folder in SQL Server Management Studio. The job names normally looks like: LSCopy_<primaryServername>_<dbname> and LSCopy_<primaryServername>_<dbname>

c.    Execute both jobs.

d.    When they finish, disable them.

If the log shipping configuration includes any other secondary database on another secondary replica, complete Steps a–d for each such secondary database.

Important: Before you add the log-shipping primary database to the availability group, make sure that all its log files are restored on every secondary database that you plan to join to the availability group. You can go to the job history dialog for the log shipping restore job to check this.

3.    Once all of the secondary databases are ready, add the primary database to the availability group.Connect to the server instance that hosts the current primary replica. Select the availability group and run the Add Database to Availability Group Wizard. For information about how to use this wizard, see Use the Add Database to Availability Group Wizard (SQL Server Management Studio) in SQL Server Books Online.

The wizard will prompt you for information. The following is particularly relevant:

    1. On the Select Databases page, add the log-shipping database to the availability group.
    2. On the Select Initial Data Synchronization page, choose the Join only option.
    3. Click Finish.

On each of the secondary replicas, the wizard will attempt to join the new secondary database to the availability group.

4.    Use the Results page of the wizard to verify whether the secondary database has successfully joined the availability group on each of the secondary replicas.

5.    Follow up after running the wizard

Depending on the success or failure of the join-database operation, perform one of the following actions:

  • ·         If the join-database operation failed on any secondary replica

Start over, as follows:

    1. Remove the log-shipping database from the availability group. For more information, see Remove a Secondary Database from an Availability Group (SQL Server) in SQL Server Books Online.
    2. Return to Step 5 of this procedure ("Prepare the secondary databases for the availability group"), and repeat Steps 5–8.
  • ·         If the join-database operation succeeded on every secondary replica

Disable log shipping on the primary database, as follows:

    1. Connect to the log shipping server (AlwaysOn-srv1).
    2. Right-click each primary database, in turn, and select Tasks\Ship Transaction Logs.
    3. In the Transaction Log Shipping page, disable log shipping by de-selecting the Enable this as a primary database in a log shipping configuration checkbox.
    4. Click OK.

Related Content

Overview of AlwaysOn Availability Groups (SQL Server)

Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)

Upgrade Log Shipping

Configure Log Shipping (SQL Server)

Add a Database to an Availability Group (SQL Server)

Join a Secondary Database to an Availability Group (SQL Server)

Remove Log Shipping (SQL Server)

Remove a Secondary Database from a Log Shipping Configuration (SQL Server)