Shulou(Shulou.com)05/31 Report--

This article shows you how to send sqlserver2000 logs, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The log shipping function automatically copies the transaction log files from the database and saves them back to another database on the backup server (standby server). Therefore, the availability of SQL Server database can be greatly improved. Because the backup database fully receives the changes in the source database, it is a copy of the source database-the only difference is the time difference between the data replication and loading process. However, when the primary server shuts down, you can change the backup server to the new primary server. If the original primary server can be brought back online, you can set it up as the new backup server-in fact, switching the roles of the two servers.

In SQL Server 2000 Enterprise Edition or Development Edition, Microsoft provides a log transfer (Log Shipping) feature within Enterprise Manager-part of the database maintenance planning wizard. When using the previous SQL Server, you need to set up your own log shipping system.

Set up log shipping

The primary server (primary server) is the official server that actually processes the data; this server has the source database. The secondary server (secondary server) stores the destination database, which is used to copy and store the transaction log files of the source database. The monitoring server (monitor server) is used to monitor primary and secondary servers. Unlike SQL Server 7.0, which monitors log shipping on a secondary server, SQL Server 2000 uses Enterprise Manager's log shipping monitoring tool to monitor log data in each set of transfers. Microsoft recommends that you install this utility on another monitoring server.

You can use Enterprise Manager's database maintenance plan wizard to set up log shipping for SQL Server 2000. But before you can activate the wizard, you must do some preparatory work. Please follow the following steps at the beginning:

1. Determine a set of servers to set up log shipping (that is, what are the primary and secondary servers during log shipping).

two。 Select a monitoring server. It is best to be different from the primary or secondary server.

3. Set the security of all servers. The Windows account you use to set up log shipping must have the privileges of SQL Server system Manager (sa) on all servers.

4. Create a sharing folder on the primary / secondary server. First, set the directory where the source database transaction log files are located to the sharing directory. Then, on the secondary server, share the directory where you intend to save the transaction log file. In order to clearly identify each sharing directory, please indicate the name of the server and database in the sharing name. If the share directory name already exists, you may need to delete or move other files from the share directory, especially the old log backup files. Then open the permissions of these shared directories to the Windows account used by SQL Agent on each server. Are you tired? let's play.

5. Decide how to establish and initialize the destination database. You can first establish and synchronize the destination database with the initial synchronization during the log shipping setup process, otherwise you must manually save back the initial database.

6. Register these three servers (that is, primary, secondary, and monitoring servers) with Enterprise Manager.

When you complete these preparatory actions, you are ready to activate the database maintenance plan wizard to set up log shipping. You can first examine the five consecutive steps of the log shipping process, as shown in figure 1:

Figure 1:SQL Server 2000 log shipping setup steps.

The first two are optional steps. If you have not synchronized the source and destination databases, step 1 will back up the source database for you, and then perform the synchronization action. At step 2, the wizard copies the backup file to the secondary server and saves it back to the destination database.

The wizard must perform the remaining three steps. At step 3, the wizard will establish a SQL Agent work (job) on the primary server. This work will periodically back up the contents of the transaction log file to the disk file. The wizard also sets up a database maintenance plan for shipping logs on the secondary server; this project involves two SQL Agent tasks: one is to copy the transaction log files to the secondary server (step 4), and the other is to save the transaction log files back to the destination database (step 5). These steps will establish a set of log shipping servers (two databases that have log shipping relationships with each other). If you want to provide additional fault tolerance or set up a report server, you can combine the primary server with another secondary server and set up a set of log shipping matching servers.

Preparatory work

1. Prepare Primary Server (below for Ztao-1) and Secondary Server (below for IntronTest)

two。 The restore model (Recovery Model) of the database to be Log Shipping (IntronERP below) is set to complete (FULL). Dl. Network management software download

3. Add the SQL Server service accounts of two computers to the Administrator group

4. Establish a folder for Primary Server backup Log

a. Set up C:\ Logfile to store the backup of the Primary Server database Transaction Log

b. C:\ Logfile is used for data sharing, and the permissions of the sharing directory are open to the Windows account used by SQL Agent.

5. Create a folder for Secondary Server restore (in Secondary Server)

a. Create a C:\ Shippedlog folder to hold backups of Transaction log transferred from Primary Server

b. Create a C:\ Logfile folder, which can store the database Transaction Log of the new Primary Server when the roles are exchanged.

c. The C:\ Logfile folder is used as a resource sharing, and the permissions of the sharing directory are open to the Windows account used by SQL Agent.

6. In Primary Server, add the registration information of Secondary Server

Step by step setup

In Primary Server, set Standby Server and Log shipping

1. Open Enterprise Manager, and click the database maintenance plan from the tool.

2. Welcome screen:

3. Select the database: check Log shipping

4. Update data optimization information: maintain the preset, do not have to choose!

5, database integrity check: maintain the preset, do not have to choose!

6. Specify a database backup plan: generally, there is no need to choose!

7. Specify the backup disk directory for transaction files: the directory where the backup files are stored points to the location where the data log files are stored on Primary Server!

8. Specify the transaction share: in this window you must specify the name of the sharing directory on the primary server. You can press [... Browse for the directory name after the] button.

9. Specify the record delivery destination: click the [Add] button to open the "New destination Database" dialog box.

10. Add destination database: enter relevant information about all Secondary Server

[server name] the drop-down menu lists the Secondary Server names that you registered with Enterprise Manager in your previous preparation. In the "Directory" text field, enter the directory name of Secondary Server to receive a copy of the transaction log file from the source database; this name is the local path name, not the sharing directory name.

You have two options for the load state of the database: no recovery mode (No recovery mode) and standby mode (Standby mode). The so-called "no recovery mode" means that the user will not be able to query the data, and the only action that can be performed is to save the transaction log file. Standby mode, on the other hand, sets the database to read-only; you can query data as long as you are not saving back to the database. There is also an option to terminate the user in the database (recommended option) in the window, which occurs when you save back the database or the transaction log file. When saving back a database or transaction log file, the Save back Program will be the only user in the database. Therefore, Microsoft recommends that you check this option, otherwise other users may affect the save back operation. 11. Specify the record delivery destination: this setting is complete.

12. Initialize the destination server: you can select the most recent backup data, or create a new backup data. For large databases, it is more efficient to use existing backup data. However, all transaction log files after that backup must exist in the shared directory of the transaction log files on the primary server for the wizard to copy and save back to the secondary server. If the database is not very large, it will be easier for the wizard to make a new backup.

13, record delivery scheduling: you can set the backup frequency of transaction log files on the source database; you can also set the frequency of SQL Agent work on the secondary server (established by the wizard to copy and load transaction log files). The frequency of log delivery can be roughly set to once a minute, but for large databases, once every five minutes is a common choice.

14. The critical value of record transmission: set a reasonable delay time for the backup action of transaction log files, as well as the copying and saving work. When the critical time is exceeded, the log shipping monitor dialog box will respond to a warning message.

15. Specify the record to transmit the monitoring server information: the default value may be used here, but the default monitoring server is set as the primary server. In general, a primary or secondary server is not regarded as a monitoring server, because if one of the servers shuts down, you will not know the current state of log shipping.

16. Generate a report: it is recommended to save the report to your Log directory, or to a special Log directory about Log Shipping, so as to find out the cause if something goes wrong!

17. Maintain the history record of the plan: also keep a record on Secondary Server. Bbs. The earliest network management forum in China

18. Set the Log Shipping name

19. Press finish! At this point, the wizard automatically sets the log shipping action between the primary server and the secondary server, and sets the log shipping monitor on the monitoring server.

Change the configuration settings for log shipping

You can use the Properties dialog box of the database maintenance plan to change log shipping settings. The options provided on the transaction File backup settings page change the configuration of transaction log file backups during log delivery.

The record Shipping] settings page shows the log shipping pairing server that you previously set up in the maintenance plan; if you set up another group of log shipping pairing servers, it will also be listed here. This setup page also contains the following options: add a new destination database (to create a new log shipping pairing server), delete the existing log shipping pairing server, edit the properties of the current log shipping pairing server, and remove the entire log shipping function.

When you click Edit in the record transfer settings page, the Edit destination Database dialog box opens. You can view and modify the directory location of the transaction log files on the secondary server and the path to the shared directory when you are the primary server in the general settings page of the dialog box. The initialization settings page allows you to change the recovery mode and how often it is copied and saved back on the secondary server. The critical value page sets the critical period for log delivery.

The [synchronization threshold] item can set the maximum time interval that can be allowed before the log transmission monitor generates a warning message (between the most recent backup of the source database transaction log file and the latest transaction log file save action). You can also set this parameter in the log shipping monitor. [delay in entry time], [file retention period], and [course record retention period] are settings related to the secondary server.

Note: the monitoring server plays an important role in these configuration options. Because most of the information on the Log Shipping settings page depends on the monitoring server, you will not be able to change the configuration settings for log shipping once the monitoring server shuts down. When the monitoring server executes SQL Server 2000 Profiler, the primary server connects to the monitoring server and obtains the existing log shipping plan from the log shipping table. Therefore, when you want to change the settings of the log shipping schedule, you must make sure that you can connect to the monitoring server within the Enterprise Manager. Check and monitor log delivery actions

The log shipping feature of SQL Server 2000 also provides a log shipping monitoring program that allows you to install on another independent monitoring server.

There are seven tables about log shipping in the msdb database of SQL Server Enterprise Edition and Development Edition:

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 tables exists on the primary, secondary, and monitoring servers. Servers also use certain tables to store data, depending on the server's role in the log shipping system.

View log transfer actions on the primary server from Enterprise Manager, you can log in to the primary server and observe and monitor log transfer actions. If a database is set up for log delivery, you can know the role of the database (source database or destination database) on the General page of the database [content] dialog box. you can also know which server the log shipping monitor is located on. You can view the status and history of log transfer and transaction log file backup work in the SQL Server Agent jobs node in Enterprise Manager. The primary server uses only two logs from the msdb database to send tables. In the log_shipping_databases table, each addition to SQL Server will link the database maintenance plan ID to the log shipping source database. In the log_shipping_monitor table, each addition to SQL Server contains the name of the monitoring server and how to log in to the database.

View the log shipping action log shipping plan on the secondary server. You can monitor SQL Agent work on the secondary server (copy the transaction log file to the secondary server and save it back to the destination database). You can also view the properties dialog box of the destination database to determine the role of the database in log shipping.

On the secondary server, SQL Server uses four logs from the msdb database to send tables. When SQL Server establishes a log transfer plan, it adds a piece of data to the log_shipping_plan table to record: the name of the primary and secondary server, file location, copy and save work ID (sysjobs system table from the secondary server). In the log_shipping_plan_databases table, SQL Server links the maintenance plan and the name of the source / destination database, and stores information about the last file copy and load operation. The log_shipping_plan_history table records the copy and recall events of each log transmission, together with information about whether the work was successful or not. SQL Server will also add a piece of data to the log_shipping_monitor table to refer to the monitoring server.

If you check the [Allow database to assume primary role] check box, you will see an important additional item on the secondary server: another database maintenance plan (with the same name as the one you established earlier), but log shipping is not activated. You will also see a non-active (disabled) SQL Agent job (the transaction log that backs up the database). Perhaps you will be confused by these projects. Although they have the same name, the additional maintenance plan generated is different from the one originally established. SQL Server retains a second reverse maintenance plan in preparation for possible future transfers of primary / secondary server roles.

View log shipping actions on the monitoring server when you set up log shipping correctly, SQL Server activates the log shipping monitoring tool program for Enterprise Manager on the monitoring server. In addition, SQL Server sets up two SQL Agent alert jobs (alert job): one to perform the work and the other to handle the out-of-sync situation.

The way to use the monitoring tool program is to open Enterprise Manager and connect to the monitoring server, expand the Management node, and then click record transfer Monitor (Log Shipping Monitor). When you click on this utility, a list of log shipping matching servers is listed. You can press the right mouse button on the paired server to view the execution history of its backup, replication, and rollback work. These histories are useful because the error messages you get from here are more detailed than those from secondary servers (SQL Agent copy and save back work).

As shown in the figure: when you open the properties dialog box of the paired server and enter the [Status] settings page, you can view the status of the backup and save back program performed by the paired server.

Its Status can be Normal or Out-of-Sync. If SQL Server Agent has not copied or saved the transaction log file, the log file name first_file_000000000000.trn will be displayed in the dialog box. This is not the actual file name, just to indicate that SQL Server Agent has not processed any files. The [Status] settings page also shows the time it takes to perform actions such as backup, copy, and load (save back). The information on this settings page is not updated automatically, so you must close this dialog box and then open it before you can update its data.

SQL Server uses only two tables in the msdb database to store data related to the log transfer server. SQL Server gives an ID as a link and a foreign key (foreign key) in both tables. The foreign key is set on the log_shipping_secondaries table and refers to the primary_id field of the log_shipping_primaries table (these are the only tables of all log shipping tables that have a foreign key relationship). Each piece of data in the log_shipping_primaries table contains information about log transfers, such as the name of the source database, the status of the backup of the transaction log files, and planned shutdown information (to avoid unnecessary warning messages). On the other hand, each piece of data in the log_shipping_secondaries table has information about the destination database; each destination database is attached to a specific log transfer source database. The result of the connection between the two tables is the paired server information displayed in the log shipping monitor.

Remove and reconfigure log shipping function

If you want to remove log shipping from the database maintenance plan, you can refer to the following ways: open the properties dialog box for the plan, select the record transfer settings page, and then click remove record transfer. This action removes SQL Server Agent backup and rollback work from the secondary server and clears all related data from the log transfer table. In addition, the relevant information of the log shipping monitoring program will also be cleared. However, this action will properly retain the backup of the transaction log of SQL Server Agent on the primary server. This work is removed only when the database maintenance plan is deleted. If you want to remove the log shipping monitor from the monitoring server, delete the data in the log_shipping_primaries and log_shipping_secondaries tables (located in the msdb database of the monitoring server) manually.

If you set up log shipping in the database maintenance plan, you have allowed the destination database to be the new log shipping source database. When you delete the maintenance plan for the primary server, its database maintenance plan and backup of transaction log files are still retained on the secondary server. These items are deleted by directly deleting the database maintenance plan related to log shipping on the secondary server.

The above is how to send sqlserver2000 logs. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.