Article ID: 314515 - View products that this article applies to.
This article was previously published under Q314515
This article discusses several aspects of log shipping and answers the most commonly asked questions regarding setup, security, monitoring, role-change and the removal of log shipping in SQL Server 2000 Enterprise Edition.
Log shipping in SQL Server 2000 provides a means of establishing a warm back up solution by using the SQL Server Maintenance Plan Wizard. Transaction log backups from a database can be automatically shipped to a different server and applied to a standby database. You can use the standby database to perform read-only operations (depending on the load state).
Log shipping set upQ1: What edition of SQL Server do I have to have to set up log shipping?
A1: The following matrix shows the edition of SQL Server that is required for the three components that participate in log shipping:
Collapse this tableExpand this table
Q2: What do I have to do before I start the log shipping set up through SQL Server Enterprise Manager?
A2: Here is the list of what you must do before you start log shipping in SQL Server 2000.
A3: It is possible to configure SQL Server services to start under a local Windows NT account, unless SQL Server is configured to run as a virtual server in conjunction with Microsoft Cluster Service. You can use Windows NT pass-through security for this purpose. Follow these steps to configure pass-through security:
A4: In SQL Server Enterprise Manager, right-click the database for which log shipping has to be set up, and then click Maintenance Plan. In the Welcome dialog box, click Next. Click to select the Ship the transaction log to other SQL Servers (log shipping) check box. The check box indicates to the SQL Server Maintenance Plan Wizard that this database must have log shipping. You can perform this step from a client that has SQL Server Enterprise Manager installed.
Q5: Why is the log shipping check box sometimes dimmed in the Maintenance Plan dialog box?
A5: The check box can be dimmed for one of the following reasons:
A6: There are several reasons that may cause the log shipping set up to fail. At this time there is at least one known problem that causes this behavior. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
298743Q7: Are the table schema and database file structure changes propagated to the secondary server?
(http://support.microsoft.com/kb/298743/ )BUG: All changes may not be rolled back when Log Shipping Maintenance Wizard fails
A7: In SQL Server 2000, all table schema and database file structure changes are logged operations. However, if a new NDF or LDF file is added to the primary database, the transaction log restore job fails while loading the transaction log backup that was performed immediately after the database file was added to the primary database. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
286280Q8: Can I script log shipping?
(http://support.microsoft.com/kb/286280/ )Description of the effect to database recovery after you add or remove database files
A8: No. Currently, it is not possible to script log shipping. The only supported means of setting up log shipping is through the wizard as described in question 4 of this section.
Q9: Can I set up log shipping between servers in multiple domains?
A9: Yes. It is possible to set up log shipping between servers that are in separate domains. There are two ways to do this:
A10: Yes. It is possible to use either Windows or SQL authentication for primary and secondary servers to connect to the monitor server. Microsoft recommends that you use Windows authentication for this purpose. However, if it is not possible to use Windows authentication, you can use SQL authentication. SQL Server will create a "log_shipping_monitor_probe" account on the primary, secondary and monitor servers, if it does not already exist, with the password specified when you set up log shipping. If SQL authentication is used for log shipping, you must configure SQL Server on the primary, secondary and monitor servers to use Mixed Mode authentication.
Log shipping security considerationsQ1: If I make the "guest" account unavailable before setting up log shipping, and I want my secondary database to be in a standby state, how can I allow users to have access to the secondary database (enforcing the same security model as the primary server)?
A1: The "guest" account must not be removed from SQL Server for any reason. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
315523However, you can can make the "guest" account unavailable for databases where there might be security concerns. Because the secondary database is in a standby state, it is not possible to use the sp_change_users_login
(http://support.microsoft.com/kb/315523/ )Removal of the guest account may cause a 916 error in SQL Server 2000 SP4 or a handled exception access violation in earlier versions of SQL Server 2000
(http://msdn2.microsoft.com/en-us/library/aa259633(SQL.80).aspx)stored procedure to re-map the logins appropriately. To enforce the same security model on a standby database, create the logins on the secondary server by using the same security identifier (SID) value as the primary server. Read the following Microsoft Knowledge Base article for more information about creating logins with the same SID values:
303722For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/303722/ )How to grant access to SQL logins on a standby database when the guest user is disabled in SQL Server
321247Q2: What does sp_resolve_logins do?
(http://support.microsoft.com/kb/321247/ )How to configure security for SQL Server log shipping
A2: At the time of the log shipping role change, the sp_resolve_logins
(http://msdn2.microsoft.com/en-us/library/aa238877(SQL.80).aspx)stored procedure requires a BCP file of the syslogins system table from the primary server. This stored procedure loads the BCP file into the temporary table and loops through each login to verify if a login with the same name exists in the secondary server's syslogins system table. It then checks to see if the SID value for this login exists in the secondary database's sysusers system table. It finally checks to see if the SID value in secondary database's sysusers system table is not the same as the SID value in the secondary server's syslogins table. If these checks are satisfied, the sp_resolve_logins stored procedure runs the sp_Change_users_login stored procedure for that login, and fixes the SID in the secondary database's sysusers system table. Execution of this stored procedure is required only if there are new logins created on the primary server after log shipping has been initialized and those same logins are not created on the secondary servers with the same SID (as described in Microsoft Knowledge Base article Q303722).
Q3: The sp_resolve_logins stored procedure runs successfully; however, it does not perform the expected modifications to the security on the secondary server. Why?
A3: The sp_resolve_logins stored procedure requires an up-to-date BCP file of the primary server's syslogins system table. These logins must already by created on the secondary server. If these two conditions are met, the sp_resolve_logins stored procedure performs the modifications to the sysusers system table in the secondary database.
Q4: Do I have to run a Transfer Logins DTS task in conjunction with the sp_resolve_logins stored procedure before performing the role change?
A4: Yes. You must use the Transfer Logins task to make sure that the logins exist in the syslogins system table on the secondary server. This does not guarantee that the user can use the secondary database (if the secondary database is loaded in standby mode). If the user has to use the secondary database before performing the log shipping role change, see question 1 in this section.
Q5: Does the sp_resolve_logins stored procedure work for remote logins in SQL Server?
A5: No. The sp_resolve_logins stored procedure only works for typical logins. Any remote logins must be created manually on the secondary server.
Log shipping monitoringQ1: Log Shipping Backup and Out of Sync alerts are firing, even when the secondary server is updated with the transaction log backups. Is this possible?
A1: Yes. It is possible that the alerts might fire even when the secondary database is being updated. If the alert threshold is set to a value less than double the time between back up and copy or restore jobs, the alerts might be raised. If the alerts are being raised and the threshold is close to or less than two times the time between subsequent backup and copy or restore jobs, go ahead and increase the threshold.
Q2: Why do the transaction log backups fail to restore on the secondary server?
A2: Transaction Log backups can only be restored if they are in a sequence. This sequence is determined by the LastLSN and FirstLSN fields that are returned by the RESTORE HEADERONLY
(http://msdn2.microsoft.com/en-us/library/aa238455(SQL.80).aspx)command. If the LastLSN field and the FirstLSN field do not display the same number on consecutive transaction log backups, they are not restorable in that sequence. There may be several reasons for transaction log backups to be out of sequence. Some of the most common reasons are:
A3: To get more information about a particular log shipping pair, follow these steps:
A4: Each run of the copy and restore job is associated with at least one file. By default, if no files are copied or restored in a certain run of any of these two jobs, SQL Server places first_file_000000000000.trn in the file name field. This may or may not indicate a problem. For example, the very first time that copy or restore jobs are run on the secondary server, there might not be any files available to copy or restore. In this case, first_file_000000000000.trn does not necessarily represent an error. However, under certain circumstances, this might represent a problem. Read the following Microsoft Knowledge Base article for more information:
292586Q5: Is it possible to modify the frequency and destination of the transaction log backups, on the primary server, after log shipping has been operational for a while?
(http://support.microsoft.com/kb/292586/ )Backup, copy, and load job information is not updated on the log shipping monitor
A5: Yes. This information is in the Maintenance Plan on the primary server. To view the information, follow these steps:
Log shipping role changeQ1: How do I perform a log shipping role change?
A1: Click the following link to read the SQL Server 2000 Books Online topic about performing a log shipping role change:
How to set up and perform a log shipping role change (Transact-SQL)
Q2: Can I perform a role change while the primary server is offline or unavailable?
A2: Yes. Running the sp_change_primary_role
(http://msdn2.microsoft.com/en-us/library/aa259617(SQL.80).aspx)stored procedure on the primary server is optional.
Q3: Why does the sp_resolve_logins stored procedure fail with error message 208 when run from the secondary database at the time of a role change?
A3: The sp_resolve_logins stored procedure does not qualify the sysusers system table with the master database prefix. This is a known problem with the code for the sp_resolve_logins stored procedure. For more information about this problem, read the following article in the Microsoft Knowledge Base:
310882Q4: Is there a problem when promoting a secondary server to be a primary server, when there are multiple secondary servers involved in a role change?
(http://support.microsoft.com/kb/310882/ )BUG: sp_resolve_logins stored procedure fails if executed during log shipping role change
A4: Read the following Microsoft Knowledge Base article about a known problem that can cause errors while performing a role change that involves multiple secondary servers:
300497Q5: How can I re-establish log shipping after promoting the secondary server to be the primary server?
(http://support.microsoft.com/kb/300497/ )FIX: Log shipping: Cannot change role from secondary to primary when database names are different
A5: If the Allow database to assume Primary role check box is selected, while setting up log shipping, in the Add Destination Database dialog box, follow these steps to add a new secondary server after performing a role change. If the setting was not selected, use the Maintenance Plan Wizard to set up log shipping after a role change.
A6: It is possible to log ship between two servers repeatedly without having to restore the complete database backup. The requirement is that both the primary and secondary servers are available when you perform the role change procedure. As part of performing the role change, you must run the sp_change_primary_role
(http://msdn.microsoft.com/en-us/library/aa259617.aspx)stored procedure. You must run the sp_change_primary_role stored procedure with a @final_state parameter of either 2 or 3. This will leave the primary database in an unrecovered state after performing the transaction log back up. Because the database is left in an unrecovered state, this database can be selected when the log shipping destination is added (as explained in the previous question). This way you do not have to reload a database backup.
Log shipping removalQ1: How can I stop log shipping for a particular log shipping pair?
A1: Follow these steps to remove a log shipping pair:
A2: Read the following Microsoft Knowledge Base article, which discusses this problem in more detail:
(http://support.microsoft.com/kb/295936/ )FIX: Error removing log shipping on secondary database when database name has a quote
REFERENCESFor more information about log shipping, visit the following Microsoft Web sites
275146Didn't see an answer to your question? Visit the Microsoft SQL Server Newsgroups at:
(http://support.microsoft.com/kb/275146/ )Frequently asked questions - SQL Server 7.0 - Log shipping
Microsoft SQL Server Newsgroupshttp://www.microsoft.com/communities/newsgroups/en-us/Comments about this or other Microsoft Knowledge Base articles? Drop us a note at SQLKB@Microsoft.com
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/917544/ )BUG: You receive an error message when you run the "Log Shipping Alert Job - Restore" job in SQL Server 2000