SQL Server 2000 Log Transfer Function - Settings (3)
Change the configuration settings of log transfer
You can use the Database Maintenance Program [Property] dialog box to change the log transfer related settings. Options provided in the [Transaction Record File Backup] setting page can change the configuration of the transaction log file backup during log transfer.
[Record Transfer] Settings page Show the log feedback match server you previously set within the maintenance plan; if you set the other group log transfer, it will be listed here. This setting page also contains the following options: New Purpose Database (to create a new log transmission match server), delete the properties of both log transfer matters, editing the current log transmission matching server, and removing the entire log transfer function .
When you click [Edit] in the [Record Transfer] setting page, you will open the [Edit Destination Database] dialog box. You can check the directory location of the transaction log file of the secondary server in the dialog box, and the path of the transaction log file for the secondary server, as well as the path to the directory. [Initialization] Settings The page will allow you to change the recovery mode, as well as the frequency of replication and recovery on the secondary server. [Critical value] page can set the critical cycle of log transmission.
In [Beyond Synchronous Critical Value] items can be set: When log transfer monitor generates a warning message, the maximum time interval allowed (in the latest source database transaction log file backup, and the latest transaction log file returns ). You can also set this parameter in the log delivery monitor. [In the time latency], [Archive Reservation Term] and [History Reserved Term] is set to the secondary server. Note: The monitoring server plays a considerable role in these configuration options. Because most of the information of [Record Transfer] The setting page depends on the monitoring server, you will not be able to change the configuration setting value of the log transfer when you monitor the server. When the SQL Server 2000 PROFILER is executed, the primary server is connected to the monitor server and then acquires existing log transfer plans from the log transfer data table. Therefore, you must make sure you can connect to the monitoring server in Enterprise Manager when you change the settings of the log transmission program.
Check and monitor log transmission action
The log transfer feature of SQL Server 2000 also provides a log transfer monitor that allows you to install on another independent monitoring server. In SQL Server Enterprise Edition and Developer Edition msdb database log shipping a total of seven on the table: log_shipping_plans log_shipping_plan_databases log_shipping_databases log_shipping_plan_history log_shipping_monitor log_shipping_primaries log_shipping_secondaries each of the above table are present in primary, secondary and monitor servers. Each server also uses some data sheets to store information, regarding why the server is in the role of the log delivery system. On the primary server, check the log transfer action from Enterprise Manager, you can log in to the primary server and observe and monitor the logging action. If a database is set to perform log transfer, you can know the role of the database (source database; or destination database) in the database [content] dialog box, you can also know that the log delivery monitor is located That server. You can check the status and history of the Log Transfer and Transaction Log file backup in Enterprise Manager. The main server only uses two logs of the MSDB database to transfer data sheets. In the log_shipping_databases table, each of SQL Server will join the database maintenance plan ID and log transfer source database. In the log_shipping_monitor data table, each of SQL Server adds a new name of the monitoring server, as well as login to the database. Viewing the log transmission action log transfer program on the secondary server exists at the secondary server. You can monitor SQL Agent work at the secondary server (copy the transaction log file to the secondary server, and return to the destination database). You can also view the property dialog box of the destination database to determine the role played by the database in the log transfer process. On the secondary server, SQL Server uses the four log data sheets of the MSDB database. When SQL Server creates a log transfer plan, it adds a message to the log_shipping_plan data sheet for record: Mainly with the name, profile location, copying and replication work ID of the secondary server (from the secondary server Sysjobs system data sheet). In the log_shipping_plan_database information table, SQL Server will connect the maintenance plan and the source / destination database name, and store the last file to copy and load actions. The log_shipping_plan_history table is a record of the replication of each log transfer to the recovery event, along with the information that the work is successful. SQL Server also adds a data in the log_shipping_monitor data table to refer to the monitoring server. If you have checked the [Allow Database to Assume Primary Role] check box, you will see an important additional item on the secondary server: another database maintenance plan (same as the name of the maintenance scheme you have previously established), But there is no activation log transmission. You will also see a non-action SQL Agent (back up the trading log for the database). Maybe you will be confused by these items. Although their names are the same, this additional maintenance plan is different from the one established.
SQL Server reserves the second reverse maintenance plan to prepare the main / secondary server role that may occur later. Viewing the log transmission action on the monitoring server When you correctly set the log transfer, SQL Server activates the log transfer monitoring tool program on the Monitor server on the Monitor server. In addition, SQL Server creates two SQL Agent alerts: A Alert Job: A to perform work and another processes OUT-OF-SYNC. The way using the monitoring tool is to open Enterprise Manager and connect to the monitor server, expand the [Management] node, then click [Record Transfer Monitor]]. When you click this tool program, it will list the list of log transfer matters. You can right click on the mouse button on the pairing server, check the execution history records such as backup, replication, and return. These historical records are very useful because the error message you get here will be more detailed than from the secondary server (SQL Agent replication and replication work). As shown in the figure: When you open the property dialog box, you enter the [Status] setting page, you can see the status of this pairing server to perform backup and repositor.
Its state (Status) can be Normal or OUT-OF-SYNC. If the SQL Server Agent has not copied or returned the transaction log file, the log file name will appear named first_file_000000000000.tn. This is not the actual file name, but it is only used to indicate that the SQL Server Agent has not processed any files. In the [Status] set page, the time consumed when the backup, replication, and load (replication), etc. are displayed. This setting page does not automatically update, so you must turn this dialog to turn it on to update your information. SQL Server uses only two data sheets within the MSDB database to store information related to log transfer servers. SQL Server gives an ID in these two data sheets as a link, and a foreign key (Foreign Key). The foreign button is set on the log_shipping_secondaries data sheet and refer to the primary_id field of the log_shipping_primaries document (both are the only information table with foreign key relationships in all log transfer data tables). Each material in the log_shipping_primaries data contains information about log transmission, such as the source database name, the status of the transaction log file backup work, and the planned lockout information (avoid unnecessary warning messages). And the log_shipping_secondaries table of information about the information of the destination database; each destination database is attached to a specific log transfer source database. The result of these two data sheets is the matching server information displayed in the log delivery monitor.
Remove and re-configure log transfer function
If you want to remove the log transfer function from the Database Maintenance Plan, refer to the following: Open the property dialog box, select the [Record Transfer] setting page, then click [remove record transfer]. This action will remove the backup and reposition of the SQL Server Agent from the secondary server, and clear all relevant information in the log transfer data. In addition, information about the log transfer monitor will also be cleared. However, this action will properly reserve the trading log backup of the SQL Server Agent on the primary server. This work will only be removed only when the database maintenance plan is deleted. If you want to remove the log transfer monitor from the monitoring server, use the data of the log_shipping_primaries with Log_Shipping_Primaries and Log_Shipping_Secondaries to delete the data of the two data sheets (MSDB databases in the monitoring server).
