SQL Server 2000 Log Shipping Features-description

Server

Role changes, role swaps, and monitoring server location

When an online database is shut down (either planned maintenance or unplanned), you might be more comfortable if the database on the standby server is available for access. A well-designed log shipping system that delivers the database transaction log files from the primary server to the standby server gives you this confidence. Log shipping is supported within the SQL Serve 2000 Enterprise Board and the development version of the Enterprise Manager utility.

Role changes

After the log is transferred from the primary server to a secondary server, you can replace the primary server with a secondary server if necessary. If there is a problem with the primary server, or if you are planning to shut down (such as upgrading your hardware or installing an amendment), the online database must stop serving for a period of time. At this point you can change the role of the database on the secondary server to replace the primary server and then become an online database. The SQL Server 2000 online manual (books online,bol) calls this operation the log shipping role changes (log shipping change). In the log shipping process, the secondary server is set in an nonrecovered state, so the transaction log can be saved from the primary server to the secondary server (once you have restored the database, you cannot back up the transaction). When you change a role, you need to restore the secondary server's database and mark it as the new primary server database. You can also set the old primary server database as the new secondary server database. If the old primary server database is not corrupted, log shipping can be reset between the new primary server and the old primary server (which has become a new secondary server). This switching mode is called role reversal.

These operational guidelines can be revised to six basic steps, respectively: 1, transfer and import and export login account, 2, downgrade (demote) primary server, 3, upgrade (promote) secondary server, 4, notify Monitoring server role has changed, 5, on the secondary server to resolve login account, 6, and link database access and permissions.

Step 1: Transfer and Export login account first, BOL recommends that you create a SQL Server Master DTS Package (package) to transfer the primary server's login account to a secondary server and perform the parse action of the login account SID between the servers. The DTS Transfer logins task used to transfer login accounts can only be used within SQL Server DTS Designer. You can set up and store DTS packages on the primary server, and then call DTSRun.exe to set how the package is performed-through the work (job) of the primary server SQL server Agent. The encapsulation executes to transfer the login account from a server to another server, but it does not resolve the SID of its login account (in a later step, it explains why the login account is being parsed). However, in order to successfully parse the login account later, you must first create a file containing the data from the primary server syslogins table.

When you export your login account to a secondary server, Bol recommends that you establish a two-phase SQL Server Agent work: Export using bcp, and copy the login account. In the first step, you will use the original mode of BCP to remit the login account to a file. In the second step, you must copy the login account to a file on the secondary server, so that you can resolve the login account when you change the role later. In step 5 you will use the Sp_resolve_logins stored procedure to resolve the SID of the login account on the secondary server. Once the work is established, it can be performed on a regular basis (for example, once every night). As a result, the secondary server will keep the most current login account to export files for log shipping role changes.

Step 2: Demote the primary server in order for the primary server to be no longer a data source for the log shipping system, you must "demote" it. You can demote the primary server's source database to become a potential secondary server. The sp_change_primary_role stored procedure is then executed on the primary server to remove the original log shipping functionality. Program code Listing 1 shows how the stored procedure changes the Pubscopy log shipping database from read/write mode to read-only standby mode, ready to accept the transaction log's backup data. The stored procedure deletes the primary server database in a log shipping plan after several steps. Incoming parameters will tell the stored procedure to do the following: Back up the most recent transaction log, end all users online in the database, set the database in the standby state and the multiple user access level. The return code of the stored procedure will indicate whether the BACKUP LOG statement was executed successfully.

Program code Listing 1: A stored procedure that degrades the log shipping database from read/write mode to read-only mode.
Use master
Go
EXEC Msdb.dbo.sp_change_primary_role
@db_name = ' pubscopy ',
@backup_log = 1,
@terminate = 1,
@final_state = 3,
@access_level = 1

Step 3: Upgrade the secondary server the next step is to upgrade the current secondary server to a recovered state, so that it replaces the original online database and becomes the primary server database for the potential log shipping. On a secondary server, if you have confirmed that no users continue to access the database, you can perform sp_change_secondary_role stored procedures, as shown in Listing 2 of program code:

    Program Code Listing 2: a stored procedure that upgrades a secondary server database to a primary server database.
Use master
Go
EXEC 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 cause the stored procedure to attempt to replicate all remaining transaction log files from the original primary server to the secondary server, and to load the secondary server database with the log files. Parameter @do_load =1 will make the most recent backup and load all transaction log files, with parameters @force_load =1 specifying forceload options that have not been documented when the sqlmaint.exe is executed; parameter @final_state =1 Set the new primary server database as a recovery mode, and the parameter @access_level to set the access mode back to the previous multiple-user state. Parameter @terminate =1 prompts the stored procedure to interrupt all user's database access actions by executing the ALTER database matching IMMEDIATE option. However, if you execute this stored procedure, the ALTER database action will fail if your own Enterprise Manager is online open with the database. Therefore, you must manually confirm that all databases have been interrupted online. Finally, if the database is set as the publisher of the database replication (replication), the @keep_replication = 0 parameter will still maintain all replication settings on the server.

If you have chosen to make a secondary server a potential primary server for the future, the database maintenance plan builds a transaction log backup work (the SQL Server Agent's transaction-log backup job) on the secondary server. After the work is activated, the transaction log backup files begin to appear on the new primary server. You need these files to reset the log shipping back to the new secondary server.

Step 4: Notifies the monitoring server role that the log shipping of SQL Server 2000 has been changed to install the Monitor utility on the monitoring server, preferably on a third server. To inform that the role of the Monitoring server log shipping has changed, you must perform a sp_change_monitor_role stored procedure on the monitoring server, as shown in Listing 3 of the program code. Although the name contains a change word, it does not alter the role of the monitor server. Instead, this stored procedure changes the location of the file sharing Reference (reference) within the primary/secondary server. This means that the data in the Monitor Server Log_shipping_secondaries table that originally referenced the old secondary server will be deleted. In the Log_shipping_primaries table, the old primary server name is changed to the new primary server name. This stored procedure does not add data to the Log_shipping_secondaries table because the new pairing server is not currently built.

Program code Listing 3: Notifies the monitor server of the stored procedure of the role swap result.
Use master
Go
EXEC 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: Resolve the login account on the secondary server you must first resolve the old primary server login account on the new primary server, and the user can access the new primary server by using the login account file that is remitted from step 1. This outbound file can be read by the sp_resolve_logins stored procedure and then resolve the differences in SIDS between servers. For example, program code Listing 4 demonstrates how to perform a sp_resolve_logins stored procedure on a newly restored pubscopy database to parse the original login account. Bol article once taught you that you must be in the destination database to execute the stored procedure. In fact, Sp_resolve_logins uses an incomplete reference (unqualified reference) to point to the syslogins view, so you must be able to execute this stored procedure in the master database!

Program code listing 4: Resolves a stored procedure on a secondary server for login account.
Use master
Go
EXEC sp_resolve_logins
@dest_db = ' pubscopy ',
@dest_path = ' D:\ ',
@filename = ' Syslogins.dat '

Step 6: Link database access and permissions BOL the discussion of role changes is limited to step 5, but it ignores an important step: coordinating actions between database access rights and log on after transfer. In order to access the database on the new main server, link the parsed login account to the corresponding database user and its permissions, you must perform a sp_change_users_login stored procedure for each login account.

Use Pubscopy
Go
EXEC sp_change_users_login ' Update_One ', ' UserName ', ' LoginName '

Executing this stored procedure ensures that SQL Server login accounts correctly link to the corresponding database user name.

So far, 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 built a new log shipping relationship. All you have to do is change the role, not the role swap.

Role swaps

To achieve a complete log shipping role swap, you only need to reset log shipping between the new primary server and the new secondary server. Because the new primary server already contains a new database maintenance plan, you will prefer to join the new secondary server as the destination server within the maintenance plan. However, after many attempts, I have found that the transaction log backup of the new primary server will always fail, and the logs will not be transferred to the new secondary server from the primary server.

So, you need another approach. After you have performed the stored procedures for log shipping role changes, as well as the steps that I have described previously, you can directly achieve a complete role swap-a new log shipping plan between the new primary server and the new secondary server. To build the plan, you follow these steps:
1. Remove log shipping from the database maintenance plan for the new primary server.
2. Delete the database maintenance plan on the primary server.
3. Delete the database maintenance plan on the secondary server.
4. Maintenance of all transaction log documents.
5. Establish a new database maintenance plan on the new primary server, specify the location of the new secondary server, the destination database, and the appropriate storage location for the transaction log file, as I have described in Part 1.
6. Restart all activities for the new primary server.

After you successfully set up a role swap and build a new log shipping pairing server, Enterprise Manager's log shipping monitor may tell you that the new secondary server database has not been synchronized with the new primary server database (out of sync). You receive this report if the time lag between the last loaded transaction log and the last backup transaction log exceeds the Out-of-sync set value. The log shipping monitor returns to the normal error-Free state until the most recent backup data has been loaded.

Log Shipping Monitor Location

Microsoft strongly recommends placing the log shipping monitor on a stand-alone server. As a result, the monitor sends alerts (alert) whenever a primary server or secondary server fails to perform work. If the monitor is on one of the primary or secondary servers, the report results depend on the server on which the monitor resides. If the monitor's server is shut down, it will not be able to continue to return a possible error condition. Therefore, it is a better practice to give the monitor a standalone server to allow for problems that may occur on the primary or secondary servers in the monitor independent return log shipping system. In addition, you can use this stand-alone monitoring server to monitor other log shipping pairing servers.

If no other server can install the monitor, it needs to be one of the primary or secondary servers. Which server should I put the log shipping monitor on? Because it is important to detect log shipping problems that may occur on the primary server, it is appropriate to put them on a secondary server. If you put the log shipping monitor on the primary server, you will not be able to use the monitor when the primary server is down, and the monitor will not be able to send alerts when there is a problem with log shipping. So, if only two servers are available, the secondary server is a better place to drop the log shipping monitor. At some point, to avoid impacting secondary servers in the event of a disaster, the transaction log must be routed from one entity location to another (perhaps for a distance). In this case, the log shipping monitor is best placed on a stand-alone server elsewhere, allowing disaster to occur without affecting primary and secondary servers.

To pick "Digjim's column"--really fine canon, hope that more people learn, resource sharing