SQL Server 2000 Log Transfer Features - Description (2) Role change, role interchange, and location of the monitoring server
STEP 4: Notification Monitoring Server Roles Change SQL Server 2000 Log Transfer Install monitoring tools on the monitoring server; preferably in the third server. In order to notify the logging server log transfer has been changed, you must perform sp_change_monitor_role pre-depreciation on the monitor server, as shown in the program code list 3. Although the name contains Change word, it does not change the role of the monitoring server. Conversely, this pre-depression program will change the location of the Reference to the main / secondary server file sharing. It means: Monitoring server log_shipping_secondaries The data will be deleted in the previous reference. In the log_shipping_primaries data, the old primary server name is changed to the new main server name. This pre-depicting program does not add information to the log_shipping_secondaries document, because the new paired server is currently not established. Program Code List 3: Notify the Monitor of the Role Interchange Monitoring Server. USE masterGOEXEC msdb.dbo.sp_change_monitor_role @primary_server = 'oahu / sql2k_1', @secondary_server = 'oahu / sql2k_2', @database = 'Pubscopy', @new_source = 'oahu / sql2k_2' Step 5: Analytical sign on the secondary server Account You must first parse the old primary server on the new main server to log in to the account, and the user can access the new primary server; how the way is to use the login account file from step 1. This remapping file can be read by the sp_resolve_logins pre-preserved program and then parses the difference between the SID between each server. For example, program code list 4 demonstrates how to perform sp_resolve_logins pre-depreciation on new recovered Pubscopy databases to resolve the original login account. BOL Articles teach you to execute this pre-depression in the destination database. In fact, sp_resolve_logins uses non-complete reference (unqualified reference) pointing to the Syslogins viewing table, so you must execute this pre-depression in the Master database! Program Code List 4: Resolve the pre-depreciation of login accounts on the secondary server. Use mastergoexec sp_resolve_logins @Dest_db = 'pubscopy', @Dest_path = 'd: /', @FileName = 'syslogins.dat' Step 6: Connecting Data Inventory Take the Permissions BOL For role changes, the discussion is only at step 5, but It ignores an important step: Coordinating between "Data Inventory Random" and "Transfer Login Account". In order to connect the database in the new main server, the login account will be connected to the corresponding database user and its permissions, and you must perform a sp_change_users_login pre-save program for each login account. Use pubscopygoexec sp_change_users_login 'Update_one', 'username', 'loginname' Executes this pre-save ensures that the SQL Server login account can correctly connect the corresponding database user name.
To this end, you have successfully upgraded the secondary server to a new role, and the old primary server has already become a secondary server. However, you still have not yet built a new log transmission relationship. You finished just the role change, not the role interchange. Role Interchange To achieve a complete log transfer role interchange, you only need to reset a log transfer between the new primary server and the new secondary server. Because the new main server has a new database maintenance plan, you will tend to join the new secondary server directly in the maintenance plan, as a destination server. However, after many attempts, I found that the "Trading Log Backup Work" of the new main server will always fail, and the logs will not be transmitted from the new primary server to the new secondary server. So you need another way. You can directly reach a complete role interchange directly after performing the procedure of the log transmit role, and the steps I have previously described - building a new log transmission between the new main server and the new secondary server plan. To build the plan, you need to follow the steps: 1. Remove the log transfer function in the database maintenance plan of the new primary server. 2. Delete the database maintenance plan on the main server. 3. Delete the Database Maintenance Plan on the secondary server. 4. Maintain all transaction log files. 5. Create a new database maintenance plan on the new main server, specify the new secondary server, the destination database location, and the appropriate storage location of the transaction log file, like I introduced in Part 1. 6. Restart all activities of the new primary server. After you successfully set the role interchange and build a new log to send the matching server, Enterprise Manager's log transfer monitor may inform you that the new secondary server database is not synchronized with the new main server database (Out of Sync). If the time difference between the "most recently loaded trading log" and the "Lord's Trading Log" exceeds the OUT-OF-SYNC setpoint, you will receive this report. Until the last backup data is loaded, log delivery monitors will return to usually no error. Log transfer monitor location
Microsoft strongly recommends placing log transfer monitors on a standalone server. In this way, the monitor will send a warning (Alert) regardless of the main server or the secondary server execution work failure. If the monitor is one of the primary or secondary servers, the report results will depend on the server where the monitor is located. If the server where the monitor is located, it will not be able to continue to return the possible error. So, let the monitor independently return the log delivery system, the main or secondary server that may happen, and give a monitor a stand-alone server is a good implementation. In addition, this independent monitoring server can also be used to monitor other log transfer matters. If there is no other server to install the monitor, you need to put one of the primary or secondary servers. Which server should you put the log delivery monitor? Because the focus is to detect the log transmission issues that may occur on the primary server, it is appropriate to be in the secondary server. If you place the log transfer monitor on the primary server, when the primary server is parked, you cannot use the monitor, the monitor cannot send a warning when the log transmission problem occurs. So, if only two servers can be used, the secondary server is a better location for placing the log delivery monitor. At some point, in order to avoid the disaster, it is necessary to transmit the transaction log from a certain entity to another (maybe there is a distance). In this case, the log delivery monitor is best placed in other parts of the independent server, so that the disaster is not affected by the secondary server.
