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.
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).
To fail over to a
secondary database:
- Copy any uncopied backup files from the backup share to
the copy destination folder of each secondary server.
- Apply any unapplied transaction log backups in sequence
to each secondary database. For more information, see Apply
Transaction Log Backups (SQL Server).
- 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).
- 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)
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.
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.
- 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).
- Disable the log shipping backup job on the original
primary server, and the copy and restore jobs on the original secondary
server.
- 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:
- Use the same share for creating backups that you
created for the original primary server.
- 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.
- In the Secondary Database Settings dialog
box, select No, the secondary database is initialized.
- 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:
- 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
- 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
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:
- Bring the secondary database online, backing up the
transaction log on the primary server with NORECOVERY.
- Disable the log shipping backup job on the original
primary server, and the copy and restore jobs on the original secondary
server.
- 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)
