Step 5: Resolving the logins on the secondary server. Resolving the old primary server logins on the new primary server lets users access the new primary server. You can resolve the logins on the new production server by using the logins file that you exported in Step 1. The sp_resolve_logins stored procedure reads the exported logins file, then resolves the differing SIDs between the servers. For example, Listing 4 shows how you can execute the sp_resolve_logins stored procedure to resolve the logins on the newly recovered Pubscopy database. The BOL article says you must run this stored procedure in the target database. But in fact, sp_resolve_logins makes an unqualified reference to the syslogins view, so you must run the stored procedure from the master database.
Step 6: Linking database access to permissions. The BOL article ends its discussion of role changes at Step 5, but it omits an important step: coordinating database access with permissions for the transferred logins. To link the transferred and resolved logins with their corresponding database users and permissions, in the new primary server's production database, you need to execute the sp_change_users_login stored procedure once for each login:
USE pubscopy
GO
EXEC sp_change_users_login 'Update_One', 'UserName', 'LoginName'
Executing this stored procedure ensures that the SQL Server logins link correctly to their corresponding usernames in the database.
At this point, you've successfully promoted the secondary server to its new role, and the old primary server is ready to become a secondary server. However, you still haven't established a log shipping relationship. You've made a role change but not a role reversal.
Reversing Roles
To accomplish a full log shipping role reversal, you only need to set up log shipping from the new primary server to the new secondary server. Because the new primary server contains a new database maintenance plan, your natural inclination is to add the new secondary server as a destination server in that plan. However, after repeatedly trying to add the new secondary server as a destination server, I found that the transaction-log backup job on the new primary server fails, and log shipping won't start from the new primary server to the new secondary server.
You need an alternative strategy. After you apply the log shipping role-change stored procedures and tasks that I detailed earlier, you can complete a full role reversal by setting up a new log shipping plan from the new primary server to the old secondary server. To set up the plan, you need to take the following steps:
- Remove log shipping from the database maintenance plan on the new primary server.
- Delete the database maintenance plan on the primary server.
- Delete the database maintenance plan on the secondary server.
- Retain all transaction log files.
- Create a new database maintenance plan on the new primary server, specifying the new secondary server and database and appropriate transaction-log file locations, as I described in Part 1 of this series.
- Resume application activity on the new primary server.
After you set up the role reversal and the new log shipping pair, Enterprise Manager's Log Shipping Monitor might report that the new secondary server database is out of sync with the new primary server database. You'll receive this report if the time difference between the last transaction log loaded and the transaction-log backup from the new primary server exceeds the out-of-sync threshold. If you wait until the load job loads the latest backup file, the Log Shipping Monitor status will return to its usual error-free state.
Where to Place the Log Shipping Monitor
As I explained in Part 1, Microsoft strongly recommends that you put the log shipping monitor on its own server. On an independent server, the monitor can raise alerts if either the primary server or secondary server fails to do its job. If the monitor were on either the primary or secondary server, its reporting would depend on that server. And if the server that the monitor is running on failed, the monitor couldn't report an error. So, to let the monitor report errors from either the primary or secondary log shipping server, giving the monitor its own server is the better course of action. In addition, you can use the independent monitor server to monitor other log shipping pairs.
If you don't have a separate server for the monitor utility and need to put it on either the primary server or the secondary server, which server would best house the Log Shipping Monitor? Because your main concern will probably be detecting a log shipping failure caused by a failure of the primary server, the best location is the secondary server. If you put the monitor on the primary server and the primary server goes down, you won't be able to access the monitor, and the monitor can't alert you that log shipping has failed. So, if you must work with only two servers, the secondary server is a better location for the Log Shipping Monitor.
Sometimes you must ship transaction logs from one physical location to another, over some distance, to prevent disasters from affecting the secondary server. In that case, the best location for the Log Shipping Monitor is on its own server in a separate location, protected from disasters that might occur to either the primary or secondary server.
A Significant Improvement
Setting up and monitoring log shipping in SQL Server 2000 is supported by Enterprise Manager's built-in utilities. However, making role changes and role reversals requires extra work. Because Enterprise Manager's log shipping utility can't make a log shipping role change, you have to manually apply stored procedures. Furthermore, making a full role reversal requires workaround steps. And Enterprise Manager's log shipping utility doesn't provide a method for scripting the log shipping setup or removal, as it does for replication. Nevertheless, the SQL Server 2000 log shipping utility represents a significant improvement over earlier utilities by making setup and monitoring easy and intuitive.
End of Article
Prev. page
1
[2]
next page -->
