Log Shipping Fail over and Role Change

Fail Over to a Log Shipping Secondary (SQL Server)

Failing over to a log shipping secondary is useful if the primary server instance fails or requires maintenance.

Preparing for a Controlled Failover

---

Typically, the primary and secondary databases are unsynchronized, because the primary database continues to be updated after its latest backup job. Also, in some cases, recent transaction log backups have not been copied to the secondary server instances, or some copied log backups might still not have been applied to the secondary database. We recommend that you begin by synchronizing all of the secondary databases with the primary database, if possible.

For information about log shipping jobs, see About Log Shipping (SQL Server).

Failing Over

---

To fail over to a secondary database:

1. Copy any uncopied backup files from the backup share to the copy destination folder of each secondary server.
2. Apply any unapplied transaction log backups in sequence to each secondary database. For more information, see Apply Transaction Log Backups (SQL Server).
3. If the primary database is accessible, back up the active transaction log and apply the log backup to the secondary databases.

If the original primary server instance is not damaged, back up the tail of the transaction log of the primary database using WITH NORECOVERY. This leaves the database in the restoring state and therefore unavailable to users. Eventually you will be able to roll this database forward by applying transaction log backups from the replacement primary database.

For more information, see Transaction Log Backups (SQL Server).

4. After the secondary servers are synchronized, you can fail over to whichever one you prefer by recovering its secondary database and redirecting clients to that server instance. Recovering puts the database into a consistent state and brings it online. 

          Note:

When you make a secondary database available, you should ensure that its metadata is consistent with the metadata of the original primary database. For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)

        5.  After you have recovered a secondary database, you can reconfigure it to act as a primary database  for other secondary databases.

Login Management

The logins from the Primary database must be defined on the Secondary. The best time to accomplish the

login synchronization is not during an emergency; therefore, I recommend a regular Agent job to

synchronize the logins between servers.

Failback

The Secondary database has become the Primary after Failover. In order to Failback to the original

Primary database the process is similar to the original Log Shipping configuration process.

1. Backup the database

2. Copy the backup to the instance

3. Restore the backup

4. Reconfigure Log Shipping

Change Roles Between Primary and Secondary Log Shipping Servers (SQL Server)

After you have failed over a SQL Server log shipping configuration to a secondary server, you can configure your secondary database to act as the primary database. Then, you will be able to swap primary and secondary databases as needed.

Performing the Initial Role Change

---

The first time you want to fail over to the secondary database and make it your new primary database, there is a series of steps you must take. After you have followed these initial steps, you will be able to swap roles between the primary database and the secondary database easily.

1. Manually fail over from the primary database to a secondary database. Be sure to back up the active transaction log on your primary server with NORECOVERY. For more information, see Fail Over to a Log Shipping Secondary (SQL Server).
2. Disable the log shipping backup job on the original primary server, and the copy and restore jobs on the original secondary server.
3. On your secondary database (the database you want to be the new primary), configure log shipping using SQL Server Management Studio. For more information, seeConfigure Log Shipping (SQL Server). Include the following steps:
1. Use the same share for creating backups that you created for the original primary server.
2. When adding the secondary database, in the Secondary Database Settings dialog box, enter the name of the original primary database in the Secondary database box.
3. In the Secondary Database Settings dialog box, select No, the secondary database is initialized.
4. If log shipping monitoring was enabled on your former log shipping configuration, reconfigure log shipping monitoring to monitor the new log shipping configuration. Execute the following commands, replacing database_name with the name of your database:
1. On the new primary server

Execute the following Transact-SQL statements:

-- Statement to execute on the new primary server

USE msdb

GO

EXEC master.dbo.sp_change_log_shipping_secondary_database @secondary_database = N'database_name', @threshold_alert_enabled = 0;

GO

2. On the new secondary server

Execute the following Transact-SQL statements:

-- Statement to execute on the new secondary server

USE msdb

GO

EXEC master.dbo.sp_change_log_shipping_primary_database @database=N'database_name', @threshold_alert_enabled = 0;

GO

Swapping Roles

---

After you have completed the steps above for the initial role change, you can change roles between the primary database and the secondary database by following the steps in this section. To perform a role change, follow these general steps:

1. Bring the secondary database online, backing up the transaction log on the primary server with NORECOVERY.
2. Disable the log shipping backup job on the original primary server, and the copy and restore jobs on the original secondary server.
3. Enable the log shipping backup job on the secondary server (the new primary server), and the copy and restore jobs on the primary server (the new secondary server).

Important:

When you change a secondary database to the primary database, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata for the database, such as logins and jobs, on the new primary server instance. For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)