SQL Server 2000 Log Transfer Features - Description (1) Role change, role interchange, and location of the monitoring server
When the online database is stopped (it is probably maintained within the plan, or the situation expected), if there is also a database on the standby server, you may be more peaceful. A well-designed log transfer system (transfer database transaction log files from primary server to the standby server) to give you self-confidence. Built in the Enterprise Manager tool program for the SQL Serve 2000 Enterprise Board and the Development Edition to support log transfer.
Role change
Once the log is transferred from the primary server to the secondary server, you can replace the primary server at the secondary server when necessary. If the primary server has problems, or a planned stop (such as upgrade hardware or installation correction procedures), the online database must stop the service period. At this point you can change the role of the database on the secondary server, so that it replaces the primary server and then makes an online database. The SQL Server 2000 online manual (BOOKS online, bol) is called the log shipping role change (LOG Ship short ". In the log transmission process, the secondary server needs to be set in the nonRecovered state, so the transaction log can return from the primary server to the secondary server (one but you will reply, you can no longer return the transaction record). When changing the role, you need to restore the database of the secondary server and labeled it as a new primary server database. You can also set the old primary server database to a new secondary server database. If the old primary server database is not damaged, you can re-establish the log transmission function between the new primary server and the old primary server (become a new secondary server). This way we call Role Reversal.
These operations guidelines can be revised as six basic steps, respectively: 1. Transfer and export login account, 2, DDRE) primary server, 3, upgrade secondary server, 4, notification monitoring server role has changed , 5. Pay the login account, 6, and the connection data in the secondary server.
Step 1: Transfer and Exchange Login Account First, BOL recommends that you create a SQL Server 2000 DTS package (Package) to transfer the login account of the main server to the secondary server, and execute the resolution of the login account between each server. . The DTS Transfer logins task for transferring login account can only be used within SQL Server 2000 DTS Designer. You can establish an execution of the DTS package on the primary server, then call DTSRun.exe to set up the encapsulation - through the work of the primary server SQL Server Agent (JOB). This package is executed to transfer the login account from a server to another server, but it does not parse the SID of its login account (whose login account will be parsed in the later step). However, in order to resolve the login account later, you must first create a file, which includes the remap of the main server Syslogins data sheet.
When the login account arrives at the secondary server, BOL recommends that you create a two-stage SQL Server Agent work: use the BCP to exchange, and copy the login account. In the first step, you will use the original mode BCP to the login account to a file. In the second step, you must copy the login account to a certain file of the secondary server so that you can use the role change to resolve login accounts. At step 5 You will use the sp_resolve_logins pre-depreciation to resolve the SID of the login on the account number on the server. Once this work is established, it can be performed regularly (for example, once per night). This will keep the latest login account remittance on the server to make log transfer roles.
Step 2: Diming the main server in order to make the main server no longer a source of log delivery systems, you must "downgrade". You can downgrade the source of the primary server to make it a potential secondary server. Then execute the sp_change_primary_role pre-depreciation on the primary server, and the purpose is to remove the original log transfer function. Program Code List 1 Displaying how the prescore changes the PubsCopy log to the read / write mode into read-only backup data, ready to receive backup materials of the transaction log. The pre-depoxing program deletes the primary server database after several steps. The incoming parameters will do the following work: Back up the last transaction log, end all users in the database online, set the database to the backup status and multi-user access level. The recovery code of the pre-depreciation will be launched in a backup log narrative sentence. Program Code List 1: Transfers log transfer databases from read / write mode into read-only modes. Use mastergoexec msdb.dbo.sp_change_primary_role @db_name = 'pubscopy', @Backup_log = 1, @TERMINATE = 1, @ACCESS_LEVEL = 1
Step 3: Upgrading the next step of the next step is to upgrade the current secondary server into a recovery state, so that it can replace the original online database and become a potential log to transfer the primary server database. On the secondary server, if you have confirmed that no user continues to access the database, you can execute sp_change_secondary_role pre-depreciation, such as program code list 2:
Program Code List 2: Upgrade the secondary server database into pre-depreciations of the primary server database. USE masterGOEXEC msdb.dbo.sp_change_secondary_role @db_name = 'Pubscopy', @do_load = 1, @force_load = 1, @final_state = 1, @access_level = 1, @terminate = 1, @keep_replication = 0, @stopat = null
These parameters will cause the pre-depreciation to try all the remaining transaction log files from the original primary server to the secondary server and load these log files Secondary Server Database. Parameters @ do_load = 1 will perform the last backup, and load all transaction log files; parameter @ force_load = 1 Specifies a FORCELOAD option that has not been filed when executing SQLMAINT.exe; parameter @ final_state = 1 Set new main server database setting To restore mode; parameter @access_level sets the access mode back the previous multi-user status. Parameters @ Terminate = 1 Promoting the pre-depicting program interrupts all users' data inventory - mode is to perform ALTER DATABASE to match the immediate option. However, if you do this, your own Enterprise Manager and the database inter-database are in turn on, and the ALTER DATABASE action will fail. So you must manually confirm whether all databases have been interrupted. Finally, if the database is set to the Replication Publisher Database (Publisher), then @ Keep_Replication = 0 parameters will still maintain all replication settings on the server. If you have selected the secondary server to become a future potential server, the Database Maintenance Plan will build a trading log backup on the secondary server (the Transaction-Log Backup Job) of SQL Server Agent. After this work is activated, the trading log backup file will begin to appear in the new main server. You need these files to reset to transfer log back to the new secondary server.
