SQL Server 2000 Log shipping Function-settings

Server SQL Server 2000 Log shipping Function-settings (1)

The log shipping feature automatically copies the transaction log file for the database and is saved to another database in the standby server (standby server). Therefore, the availability of SQL Server databases can be greatly improved. Because the standby database completely receives the variance of the source database, it is a copy of the source database-the difference is only the time lag generated by the data copying and loading process. However, when the primary server is down, you can change the backup server to the new primary server. If the original primary server can be used again, you can set it up as a new standby server-in fact, the role of swapping two servers.

In SQL Server 2000 Enterprise or Development editions, Microsoft provides a log shipping function within Enterprise Manager-one of the parts of the Database Maintenance Plan Wizard. When you use the previous SQL Server, you need to establish a log shipping system yourself.

Set Log shipping

The primary server (primary server) is the official server that actually processes the data, and the source database is in this server. The secondary server (secondary server) holds the destination database, which is used to replicate and back up the transaction log files for the source database. The Monitoring server (monitor server) is used to monitor primary and secondary servers. Unlike SQL Server 7.0, where SQL Server 7.0 monitors log shipping actions on secondary servers, SQL Server 2000 uses the Log Shipping monitoring tool of Enterprise Manager to monitor log data in each group of transmissions. Microsoft recommends that you install this utility on a different monitor server.

You can use Enterprise Manager's Database Maintenance Plan Wizard to set up log shipping for SQL Server 2000. However, before you activate the wizard, you must do some preparatory work first. First, follow these steps:
1. Decide on a set of servers to set log shipping (i.e., the primary and secondary servers during log shipping).
2. Select a monitor server. It is best to be different from the primary or secondary server.
3. Set the security of all servers. The Windows account that you use to set up log shipping must have the permissions of the SQL Server System Manager (SA) on all servers.
4. Create a sharing folder on a primary/secondary server. First, set the directory where the transaction log files for the source database are located as shared directories. Then, on the secondary server, share the directory where you intend to return the transaction log files. In order to clearly identify the shared directory, please indicate the name of the server and database in the share name. If the shared directory name already exists, you may need to remove or move other files from the shared directory, especially the old log backup files. These shared directory permissions are then opened to the Windows account used by the SQL Agent on each server.
5. Decide how to establish and initialize the database of destinations. You can establish a database with the initial synchronization destination in the log shipping setup process, otherwise you must manually perform the initial database back up action.
6. Register this three servers (that is, primary, secondary, and monitoring servers) at Enterprise Manager.
As you complete these preparation actions, you are ready to activate the Database Maintenance Plan Wizard to set log shipping. You can first view the five sequential 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 (optional) steps. If you have not synchronized the source and destination databases, step 1 backs up the source database for you and then performs the synchronization action. At step 2 o'clock, the wizard copies the backup files to the secondary server and back to the destination database.
The wizard will certainly perform the remaining three steps. At step 3 o'clock, the wizard will establish a SQL Agent job (job) on the primary server. This work will periodically back up the transaction log file contents to the disk file. The wizard also establishes a database maintenance plan for the transfer log on the secondary server; This project contains two SQL Agent work: One is to copy the transaction log file to a secondary server (step 4) and the other is to save the transaction log file to the destination database (step 5). These steps establish a set of log shipping servers (two databases that have log shipping relationships to 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 pairing servers.

preparatory work
1. Prepare Primary Server (hereinafter ZTAO-1) and secondary server (hereinafter Introntest)
2. The restore model for the database (INTRONERP) to be used for Log Shipping (the following is Recovery) is set to complete (full).

3. Add the SQL Server service account of two computers to the group of administrators
4. Create a primary Server backup log data clip
A. Establishment of C:\Logfile to store backup of primary server database transaction log
B. C:\Logfile data sharing, sharing directory permissions open to the SQL Agent for the Windows account used.
5. Create a secondary server-restored folder (in secondary server)
A. Creating a C:\Shippedlog folder to hold backups of the transaction log transferred from primary server
B. Create a C:\Logfile Data folder, when the role is exchanged, can store the new primary server database transaction Log
C. The C:\Logfile Data folder is shared as a resource, and the permissions to share the directory are open to the Windows account used by the SQL Agent.
6. In primary server, add registration information for secondary server

If exists (
 select * from sysobjects
  where name= ' pr_backup_db ' and xtype= ' P '
 )
Begin
 drop proc pr_backup_db
End
Go
/* Back Up Database */
CREATE proc pr_backup_db
@flag varchar (a) out,
@backup_db_name varchar (128),
@filename varchar (1000)  --path + file name
as
declare @sql nvarchar (4000), @par nvarchar (1000)
Select @par = ' @filename varchar (1000) '
Select @sql = ' BACKUP DATABASE to disk= @filename with init '
Execute sp_ ExecuteSQL @sql, @par, @filename
Select @flag = ' OK '
go

if exists (
SELECT * from sysobjects
where Name= ' Fn_getfilepath ' and xtype= ' FN '
)
Begin
Drop function Fn_getfilepath
End
Go
/* Create a function to get the file path * *
Create function Fn_getfilepath (@filename nvarchar (260))
Returns nvarchar (260)
As
Begin
declare @file_path nvarchar (260)
declare @filename_reverse nvarchar (260)
Select @filename_reverse =reverse (@filename)
Select @file_path =substring (@filename, 1,len (@filename) +1-charindex (' \ ', @filename_reverse))
Return @file_path
End

Go

if exists (
SELECT * from sysobjects
where Name= ' pr_restore_db ' and xtype= ' P '
)
Begin
drop proc pr_restore_db
End
Go
CREATE proc pr_restore_db/* RESTORE DATABASE * *
@flag varchar () out,/* The status of the process running flag, is the input parameters * *
@restore_db_name nvarchar (128),/* The name of the data to be restored * *
@filename nvarchar (260)/* backup file stored path + backup file name * *
As
DECLARE @proc_result tinyint/* Return system stored procedure xp_cmdshell run result * *
DECLARE @loop_time smallint/* Cycle times * *
Declare @max_ids the maximum number of IDs columns in the smallint/* @tem table * *
declare @file_bak_path nvarchar (260)/* Original Database storage Path * *
DECLARE @flag_file bit/* File Store flag * *
declare @master_path nvarchar (260)/* Database Master file path * *
declare @sql nvarchar (4000), @par nvarchar (1000)
declare @sql_sub nvarchar (4000)
declare @sql_cmd nvarchar (4000)
/*
Determine the legality of the parameter @filename file format to prevent users from entering illegal file names such as D: or c:\a\
Parameter @filename must have ' \ ' and not end with ' \ '
*/
If Right (@filename, 1) <> ' and charindex (' \ ', @filename) <>0
Begin
Select @sql_cmd = ' dir' + @filename
EXEC @proc_result = Master. xp_cmdshell @sql_cmd, No_output
IF (@proc_result <>0)/* System stored procedure xp_cmdshell return code value: 0 (successful) or 1 (failure) */
Begin
Select @flag = ' not exist ' * backup file does not exist * *
Return//* Exit Process * *
End
/* Create a temporary table that holds a result set consisting of a list of databases and log files contained in the backup set.
CREATE TABLE #tem (
LogicalName nvarchar (128),/* The logical name of the file * *
PhysicalName nvarchar (260),/* File physical name or operating system name * *
Type char (1),/* data file (D) or log file (L) * *
FileGroupName nvarchar (128),/* file group name containing file * *
[Size] Numeric (20,0),//current size (in bytes) * *
[MaxSize] Numeric (20,0)/* Maximum size allowed (in bytes) * *
)
/*
CREATE table variables, table structure is the same as temporary tables
Just two more columns,
Column IDs (from numbered columns),
Column File_path, storing the path to the file
*/
DECLARE @tem table (
IDS smallint identity,/* self-added numbering column * *
LogicalName nvarchar (128),
PhysicalName nvarchar (260),
File_path nvarchar (260),
Type char (1),
FileGroupName nvarchar (128)
)
INSERT INTO #tem
Execute (' Restore FILELISTONLY fromdisk= ' + @filename + ')
/* The temporary table is imported into the table variable and the corresponding path is calculated.
Insert into @tem (logicalname,physicalname,file_path,type,filegroupname)
Select Logicalname,physicalname,dbo.fn_getfilepath (physicalname), Type,filegroupname
From #tem
If @ @rowcount >0
Begin
drop table #tem
End
Select @loop_time =1
The maximum number of IDs columns in the Select @max_ids =max (IDS)/* @tem Table * *
From @tem
While @loop_time <= @max_ids
Begin
Select @file_bak_path =file_path
From @tem whereids= @loop_time
Select @sql_cmd = ' dir' + @file_bak_path
EXEC @proc_result = Master. xp_cmdshell @sql_cmd, No_output
/* System stored procedure xp_cmdshell return code value: 0 (Success) or 1 (failure)/
IF (@proc_result <>0)
Select @loop_time = @loop_time +1
Else
Break/* Did not find the original storage path of the data file before the backup, exit cycle * *
End
Select @master_path = '
If @loop_time > @max_ids
Select @flag_file = 1/* Pre-backup data file original store path exists * *
Else
Begin
Select @flag_file = 0/* Pre-backup data file original repository path does not exist * *
Select @master_path =dbo.fn_getfilepath (filename)
From Master.. sysdatabases where name= ' master '
End
Select @sql_sub = '
/*type= ' d ' is a data file, type= ' l ' is a log file * *
/* @flag_file = 1 o'clock the new database file is still stored in the original path, otherwise the path is the same as the master database path.
Select @sql_sub = @sql_sub + ' move ' ' +logicalname+ ' "' to '"
+case type
When the ' d ' then case @flag_file
When 1 then File_path
else @master_path
End
When the ' l ' then case @flag_file
When 1 then File_path
else @master_path
End
End
+case type
When ' d ' then @restore_db_name + ' _ ' +logicalname+ ' _data.mdf ', '
When ' l ' then @restore_db_name + ' _ ' +logicalname+ ' _log.ldf ', '
End
From @tem
Select @sql = ' RESTORE DATABASE @db_name fromdisk= @filenameWith '
Select @sql = @sql + @sql_sub + ' replace '
Select @par = ' @db_name nvarchar (128), @filename nvarchar (260) '
Print @sql
Execute sp_executesql @sql, @par, @db_name = @restore_db_name, @filename = @filename
Select @flag = ' OK '/operation successful *
End
Else
Begin
SELECT @flag = ' File type error '/* parameter @filename Input format error * *
End

--Backup Database Test_database
DECLARE @fl varchar (10)
Execute pr_backup_db @fl out, ' test_database ', ' C:\test_database.bak '
Select @fl

--Restore database, input parameter error
DECLARE @fl varchar (20)
EXEC pr_restore_db @fl out, ' sa ', ' c:\ '
Select @fl

--Restore the database, that is, create a copy of the database Test_database test_db
DECLARE @fl varchar (20)
EXEC pr_restore_db @fl out, ' test_db ', ' C:\test_database.bak '
Select @fl

Change the configuration settings for log shipping

You can use the Database Maintenance Plan Properties dialog box to change the log shipping related settings. The options available on the Transaction File backup Settings page change the configuration of the transaction log file backup during the journal shipping.

The Record Transfer Settings page displays the log shipping pairing server that you previously set up in the maintenance plan, and is listed here if you set up other group log shipping pairing servers. This page also includes the following options: New destination database (used to create a new log shipping pairing server), removal of existing log shipping pairing servers, editing of current log shipping pairing server properties, and removal of 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 secondary server's transaction log file in the General Settings page in the dialog box, as well as the path to share the directory as a primary server in the future. The Initialize Settings page allows you to change the recovery mode and the frequency of replication and recall on secondary servers. The critical value page can set the critical period for log shipping.

In the "Out of sync Critical" project, you can set the maximum time interval (between the most recent source database transaction log file backup and the most recent transaction log file fallback action) that the log transmission monitor can allow before it generates a warning message. You can also set this parameter in the log shipping monitor. "In-time delay", "archive retention", and "history retention period" are settings related to secondary servers.

Note: The monitoring server plays a fairly important role in these configuration options. Because most of the information on the record Transfer Settings page depends on the monitoring server, you will not be able to change the configuration settings for log shipping when the monitor server is down. When the monitoring server executes SQL Server Profiler, the primary server connects to the monitoring server and then obtains the existing log shipping plan from the Log shipping table. Therefore, to change the log shipping schedule, you must determine that you can connect to the monitoring server within Enterprise Manager.

Check and monitor log shipping actions

The log shipping feature of SQL Server 2000 also provides a log shipping monitor that allows you to install on another stand-alone monitoring server.
There are a total of seven log shipping tables in the SQL Server Enterprise and development version of the msdb database:
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 these tables is available on the primary, secondary, and monitoring servers. The servers also use certain tables to store data, depending on the role of the server in the log shipping system.

View Log shipping actions on the primary serverFrom Enterprise Manager, you can log in to the primary server and observe and monitor logs shipping actions. If a database has been set up for log shipping, the database's role (source database, or destination database) is available on the "General" page of the Database Content dialog box, as well as the log shipping monitor is located on that server. In Enterprise Manager, you can view the status and history of log shipping and backup work for the ITL file in the Jobs node of the SQL Server agent. The primary server uses only two log shipping tables in the msdb database. In the Log_shipping_databases table, each additional amount of data added to SQL Server will link the database maintenance plan ID and the log shipping source database. In the Log_shipping_monitor table, each new SQL Server data contains the name of the monitor server and how it is logged into the database.

to view log shipping actions on a secondary serverLog shipping plans exist on secondary servers. You can monitor the SQL Agent work on the secondary server (copy the transaction log file to a secondary server and 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 the log shipping process.

On secondary servers, SQL Server uses four log shipping tables in the msdb database. When SQL Server establishes a log shipping plan, it adds a new piece of data to the Log_shipping_plan table to record the name of the primary and secondary server, the file location, the copy and the back work ID (from the Sysjobs system table of the secondary server). In the log_shipping_plan_databases table, SQL Server links the maintenance plan with the name of the source/destination database and stores information about the last file copy and load action. The Log_shipping_plan_history table records the replication and recall events for each log shipping, along with information about whether the work was successful. SQL Server also adds a new piece of data to the Log_shipping_monitor table to refer to the monitoring server.

If you select the Allow database to assume primary check box, you will see an important additional project on the secondary server: Another database maintenance plan (the same as the maintenance plan name you established earlier), but no log shipping is activated. You will also see an inactive (disabled) SQL Agent work (the transaction log that backs up the database). Perhaps you will be confused by these projects. Although their names are the same, this extra maintenance plan is different from the one that was created. SQL Server reserves the second reverse maintenance plan for major/secondary server roles that can occur later to prepare for the transfer.

         View Log shipping actions on the monitoring server after you correctly set log shipping, SQL Server The Log Shipping Monitor utility for enterprise Manager on the monitoring server is activated. In addition, SQL Server establishes two SQL Agent alerts (alert job): One to perform the work, and the other to handle out-of-sync situations.
 
         Use the Monitoring utility program by opening the Enterprise Manager and connecting to the monitoring server to expand Management node, and then click Log Shipping Monitor (log shipping monitors). When you click the utility program, a list of log shipping pairing servers is listed. You can click the right mouse button on the pairing server to view the execution history of its backup, copy, and back up work. These historical records are useful because the error messages you get from here are more detailed than those from the secondary server (SQL Agent replication and back-and-save work).
         as shown in the figure: when you open the Properties dialog box for the pairing server and enter the Status settings page, You can view the status of this pairing server performing a backup and a callback program.

The state (status) can be normal or out-of-sync. If the SQL Server agent has not replicated or returned to 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, but is used to indicate that the SQL Server agent has not processed any files. The Status Settings page also displays the time taken to perform actions such as backup, replication, and load (save). The information for this setting page is not automatically updated, so you must close and then open the dialog box before you can update its data.

SQL Server uses only two tables in the msdb database to store information about the log shipping server. SQL Server gives an ID as a link in both tables, as well as a foreign key (foreign key). The foreign key is set on the Log_shipping_secondaries table and references the primary_id field of the Log_shipping_primaries table (these are the only tables in all log shipping tables that have foreign key relationships). Each of the data in the Log_shipping_primaries table contains information about log shipping, such as the source database name, status of the transaction log file backup execution, and planned lockout information (to avoid unwanted warning messages). and log_shipping_secondaries information about the destination database; Each destination database is attached to a specific log shipping source database. The result of the links between the two tables is the pairing server information displayed in the Log shipping monitor.

Removing and re-configuration log shipping features

If you want to remove log shipping from the database maintenance plan, you can refer to the following ways: Open the Schedule's Properties dialog box, select the Record Transfer Settings page, and then click Remove Record Transfer. This action removes the backup and recall of the SQL Server Agent from the secondary server and clears all relevant information from the Log Shipping table. In addition, information about the log shipping monitor is also cleared. However, this action will appropriately retain the SQL Server Agent's transaction log backup work on the primary server. The work is removed only when the database maintenance plan is deleted. If you want to remove the log shipping monitor from the monitor server, manually remove the data from the log_shipping_primaries and log_shipping_secondaries tables (the msdb database at the monitor server).

If you set up log shipping within a database maintenance plan, you have allowed the destination database to be a new log shipping source database. When you delete a maintenance plan for a primary server, the secondary server retains its database maintenance plan and the transaction log file backup work. The way to delete these items is to delete the database maintenance plan associated with log shipping on the secondary server directly.