Share via


Changing Roles Between Primary and Secondary Servers

After you have failed over to a secondary server, you can configure your secondary database to act as a 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 Failing Over to a Log Shipping Secondary.
  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, see How to: Enable Log Shipping (SQL Server Management Studio). 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.

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 Managing Metadata When Making a Database Available on Another Server Instance.

See Also

Concepts

Log Shipping Tables and Stored Procedures

Other Resources

Log Shipping

Help and Information

Getting SQL Server 2005 Assistance