Converting a logshipping configuration to Availability Group

[SQL-Server-Team]

First published on MSDN on Jan 09, 2012

Converting Log Shipping Configurations to a New Availability Group

Let's take a scenario where you already have a log shipping configuration. You have three databases (db1, db2, and db3) that are set up for log shipping among three different data centers. The primary server for all three databases is AlwaysOn-srv1. The secondary servers are on AlwaysOn-srv2 and AlwaysOn-srv3. The nodes that host these server instances belong to a single Windows Server Failover Clustering (WSFC) cluster.

Each log-shipping primary database will become an AlwaysOn primary database, and each log-shipping secondary database will become an AlwaysOn secondary database.

Note: An AlwaysOn database is also known as an availability database.

Prerequisites

• In each log shipping configuration, the primary and secondary databases must have the same name.


• Each of the host server instances (in this example, AlwaysOn-srv1, AlwaysOn-srv2, and AlwaysOn-srv3) must support AlwaysOn Availability Groups. Also these server instances must reside on nodes of a single Windows Server Failover Clustering (WSFC) cluster. For more information, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)... in SQL Server 2012 Books Online.


• The server instances must already be enabled for AlwaysOn. For more information, see Prepare SQL Server for using AlwaysOn feature blog, and Enable and Disable AlwaysOn Availability Groups (SQL Server) in SQL Server 2012 Books Online.

To migrate Databases from Log Shipping to a New AlwaysOn Availability Group

To migrate the log shipping databases on AlwaysOn-srv1, AlwaysOn-srv2, and AlwaysOn-srv3 into a new availability group, perform the following steps:

1. Prepare log-shipping backup jobs on the primary server instance.

Connect to the log-shipping primary server (AlwaysOn-srv1), and perform the following steps:

• Identify the log-shipping jobs for backing up the three primary databases by expanding the SQL Server Agent\Jobs folder in SQL Server Management Studio. The job name normally looks like:

LSbackup_<dbname>

• Execute the backup job for each database.


• When each backup job finishes, disable it.

Tip: Alternatively, if you are including a lot of databases in an availability group and no other SQL Server aAgent jobs do not need to run while you are converting the databases from log shipping to AlwaysOn Availability Groups,  you could stop SQL Server Agent until the conversion is finished.

2. On the first secondary server (AlwaysOn-srv2), prepare the secondary databases for an availability group .

Connect to AlwaysOn-srv2, and perform the following steps:

• Identify the log shipping jobs for copying and restoring the three secondary databases by expanding SQL Server Agent\Jobs folder in SQL Server Management Studio. The job name normally looks like:

LSCopy_<primaryServername>_<dbname> and LSCopy_<primaryServername>_<dbname>

• Execute the job.


• When the job finishes done, disable it.

Important: Before you create the availability group, make sure that all the log files are restored. You can go to the job history dialog for the log shipping restore job to check this.

3. On the other secondary server (AlwaysOn-srv3), prepare the secondary databases for an availability group .

Connect to AlwaysOn-srv3. Then perform the same steps that are described in Step 2.

Important: Before you create the availability group, make sure that all the log files are restored. You can go to the job history dialog for the log shipping restore job to check this.

4. Create the availability group and join the secondary databases to it.

As soon as possible, connect to AlwaysOn-srv1 and launch the New Availability Group Wizard. For information about how to use this wizard, see Use the New Availability Group Wizard (SQL Server Management Studio) in SQL Server 2012 Books Online.

The wizard will prompt you for information. For the example presented here, the following information is particularly relevant:

1. On the Specify Availability Group Name page, enter a name for the new availability group.


2. On the Select Databases page, add the three log-shipping databases (db1, db2, and db3) to the new availability group.


3. On the Specify Replicas Page, AlwaysOn-srv1 will be listed as the primary replica. Specify the log-shipping secondary servers (AlwaysOn-srv2 and AlwaysOn-srv3) to host the AlwaysOn secondary replicas. Then choose the replica options that you want.


4. On the Select Initial Data Synchronization page, choose the Join only option.


5. Click Finish .

The availability group will be created using the specified name. This availability group will contain three availability replicas and the three databases. On each of the secondary replicas, the wizard will attempt to join each of the secondary databases to the availability group.

5.Use the Results page of the wizard to verify whether the secondary databases have successfully joined the availability group on each of the secondary replicas.

Note: When a secondary database joins the availability group, data movement begins between the corresponding primary database and the joined secondary database.

6.Follow up after running the wizard: Depending on the success or failure of the join-database operations, perform one of the following actions:

• If one or more join-database operations failed on each of the secondary replicas

Start over, as follows:

1. Delete the new availability group. For more information, see Remove an Availability Group (SQL Server) in SQL Server 2012 Books Online.


2. Repeat Steps 1–6.

• If the join-database operations succeeded on some, but not all, secondary replicas

For a given availability replica, the appropriate action depends on whether some of the join-database operations failed, as follows:

• For any secondary server whose secondary databases all successfully joined the availability group

Remove log shipping from the local secondary databases, as follows:

1. Connect to the log shipping primary server (AlwaysOn-srv1).


2. Right-click each primary database, in turn, and select Tasks \ Ship Transaction Logs .


3. On the Transaction Log Shipping page, remove the secondary server (AlwaysOn-srv2 or AlwaysOn-srv3) from the secondary list.


4. Click OK .

Repeat Steps b–d for each of the log-shipping databases.

• For any secondary server instance on which some/all secondary databases failed to join the availability group

Retain log shipping on the secondary server(s) whose secondary databases failed to join the availability group, and complete the following steps:

1. Remove the secondary replica hosted by the server instance from the availability group. For more information, see Remove a Secondary Replica from an Availability Group (SQL Server) .


2. Repeat Step 2(prepare the secondary databases for an availability group).


3. Use the Add Replica to Availability Group Wizard to add an availability replica to an AlwaysOn availability group (this replaces Step 4, above). For information about how to use this wizard, see Add a Secondary Replica to an Availability Group (SQL Server) in SQL Server 2012 Books Online.


4. Repeat Steps 5(verify whether the secondary databases have successfully joined the availability group) and 6 (follow up after running the wizard).

• If all secondary databases on every log-shipping secondary server were successfully joined to the availability group

Disable log shipping on each of the primary databases, 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 .

Repeat these Steps b–d for each of the databases.