Export (0) Print
Expand All

Managing Logins and Jobs After Role Switching

Only content within the principal database is mirrored. Associated information in the master or msdb system databases cannot be mirrored. Such associated information includes jobs set up against the principal database and logins that are added to the principal server.

If such information is important to supporting role switching, the information should be duplicated at the mirrored site. If it is possible, after roles are switched it is best to programmatically reproduce the information on the new principal database. The most common issues are logins and jobs.

For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

If a user is orphaned on the new principal, create the login on the new principal and run sp_change_users_login (Transact-SQL). For more information, see Troubleshooting Orphaned Users.

Jobs, such as backup jobs, require special consideration. Typically, after a role switch, the database owner or system administrator must re-create the jobs for the new principal database.

When the former principal server is available, you should also delete the original jobs from the new mirror database. Jobs on the mirror database fail because it is in the RESTORING state and so it is unavailable.

ms191458.note(en-US,SQL.90).gifNote:
The partners might be configured differently, with different tape drive letters or such. The jobs for each partner must allow for any such differences.

Community Additions

ADD
Show:
© 2014 Microsoft