Log shipping set up Q1: 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:

Component Primary Server Secondary Server Monitor Server

Edition of SQL Server required Enterprise or Developer Edition Enterprise or Developer Edition Any Edition

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.

Either start SQL Server and SQL Server Agent services under a domain account or configure the relevant primary, secondary and monitor servers for pass-through security (see question three in this heading for more information). You can set up log shipping from any computer that has SQL Server Enterprise Manager (SEM) installed. You must register all computers that are running SQL Server that function as servers, which are intended to be the secondary servers, through SEM, on the computer from which log shipping is going to be set up. Create a folder on the primary server for the transaction log back ups. You can create this folder anywhere on the primary computer. There must be enough free disk space on the drive on which you place the folder to hold at least one days worth of transaction log back ups. The exact space required is not easy to predict because it depends on the size and frequency of the transaction log back ups for the database. Microsoft recommends that you create a different folder for each database that you log ship. Share the folders you created in the previous step. Make sure that you grant READ and CHANGE permissions to the Microsoft Windows NT accounts under which SQL Server and SQL Server Agent services are started for the servers that participate in log shipping. If you use pass-

through security, grant these permissions to the local Windows NT account, under which the SQL Server related services are started. Remove or disable any transaction log back up jobs on the databases that will be log shipped. This includes any third party back up jobs. Q3: Do I have to start SQL Server related services under a domain account as opposed to a local Windows NT account? 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: Create a Windows NT account on the primary, secondary and monitor computers with the same name and passwords. Configure SQL Server related services to start under these Windows NT accounts on all computers. The SQL Server services must be started under a domain account if SQL Server is configured to run as a virtual server with Microsoft Cluster Service. Even if SQL Server is a virtual server, Microsoft recommends that you use a domain account to start the services when SQL Server computers are in a domain. You gain the following advantage by having SQL Server related services start under a domain account: Change of password for the SQL Server start up account will not result in a failure of log shipping jobs. To successfully continue log shipping in a pass-through security situation, all the servers must have the password changed for the Windows NT start up account, at the same time. Q4: Where can I set up log shipping from? 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: Multiple databases might be selected for the Maintenance Plan. The database that is selected is not in the Full or Bulk Logged Recovery model. SQL Server 2000 Enterprise Edition is not installed on the server. Q6: Why does the log shipping set up fail while performing the initial configuration? 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: 298743 BUG: All changes may not be rolled back when Log Shipping Maintenance Wizard fails Q7: Are the table schema and database file structure changes propagated to the secondary server? 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: 286280 Description of the effect to database recovery after you add or remove database files Q8: Can I script log shipping? 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: Use pass-through security. Configure Windows NT accounts with the same name and passwords on the primary, secondary and monitor servers. Configure SQL Server related services to start under these accounts on all servers and use SQL authentication while setting up log shipping to connect to the monitor server. -orUse conventional Windows NT security. You must configure the domains with two-way trusts. SQL Server related services can be started under domain accounts. Either SQL authentication or Windows authentication can be used by jobs on the primary and secondary servers to connect to the monitor server. All other requirements are the same as explained in question 2 of this section. Q10: Can I configure primary and secondary servers to use SQL authentication to connect to the monitor server?

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 considerations Q1: 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: 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 However, 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 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: 303722 How to grant access to SQL logins on a standby database when the guest user is disabled in SQL Server For more information, click the following article number to view the article in the Microsoft Knowledge Base: 321247 How to configure security for SQL Server log shipping Q2: What does sp_resolve_logins do? A2: At the time of the log shipping role change, the sp_resolve_logins 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 monitoring Q1: 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 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:

There are redundant transaction log backup jobs on the primary server that are causing the sequence to be broken. There are non-logged operations performed in the database. For more information, click the following article number to view the article in the Microsoft Knowledge Base: 272093 Description of the effects of nonlogged and minimally logged operations on transaction log backup and the restore process in SQL Server The recovery model of the database was probably toggled between transaction log backups. The Data Transformation Services (DTS) task on the primary server might be causing this problem. For more information, click the following article number to view the article in the Microsoft Knowledge Base: 308267 FIX: DTS Copy Objects Task (DMO) breaks transaction log backup chain by switching recovery mode to Simple during transfer Q3: Where can I find information about errors while performing back up, copy or restore operations?

A3: To get more information about a particular log shipping pair, follow these steps: 1. Open SQL Server Enterprise Manager, and then connect to the monitor server. 2. Under Management, click Log Shipping Monitor. In the right window pane, all the log shipping pairs are displayed (that have been configured with this server as the monitor server). If the log shipping pair is not visible, right-click the Log Shipping Monitor (under Management), and then click Refresh. 3. Right-click the log shipping pair that you want information about, and then click View Backup History to view the back up job history. 4. Right-click the log shipping pair, and then click View Copy/Restore History to view the history for copy and restore jobs. 5. Right-click the log shipping pair, and then click Properties to view the current log shipping status, Source, and Destination alert status. Q4: Does the file name first_file_000000000000.trn indicate that the copy or restore job was unsuccessful? 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:

292586 Backup, copy, and load job information is not updated on the log shipping monitor Q5: 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? A5: Yes. This information is in the Maintenance Plan on the primary server. To view the information, follow these steps: 1. Double-click the Maintenance Plan on the primary server for the database for which this information must be modified. 2. Click the Transaction Log Backup tab. Modify the destination and frequency in the dialog box. 3. Because the copy job on the secondary server is expecting to copy transaction log backups from the share specified at the time log shipping was set up, this job might fail after modifying the target folder for the transaction log back ups. For more information about how to work around this problem, read the following article in the Microsoft Knowledge Base: 314570 Cannot modify backup network share after you change the transaction log backup folder Log shipping role change Q1: 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 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:

310882 BUG: sp_resolve_logins stored procedure fails if executed during log shipping role change Q4: 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? 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: 300497 FIX: Log shipping: Cannot change role from secondary to primary when database names are different Q5: How can I re-establish log shipping after promoting the secondary server to be the primary server? 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. 1. Open SQL Server Enterprise Manager, and then connect to the promoted primary server. Register the server that you intend to add as the secondary server. 2. Expand Management (in SQL Server Enterprise Manager), and then click Maintenance Plans. Right-click the appropriate Maintenance Plan from the list, and then click Properties. 3. Click the Log Shipping tab, and then click Add. 4. Provide the appropriate information regarding the secondary server about this dialog box, and then click OK. This will add the new secondary server to log shipping. Q6: How can I continue to log ship to the former primary server without restoring a database backup? 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 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 removal

Q1: How can I stop log shipping for a particular log shipping pair? A1: Follow these steps to remove a log shipping pair: 1. Open the SQL Server Enterprise Manager on the primary server. Expand Management, and then click Maintenance Plan. Right-click the Maintenance Plan, and then click Properties. 2. Click the Log Shipping tab, and then click to select the log shipping pair that you want to remove. 3. Click the Delete command button to remove this pair from log shipping. If this is the last pair in log shipping, clicking Delete removes log shipping. If you want to continue log shipping to a different server or to a database, click Add. Then, click to select the appropriate server or database to act as the secondary server before you remove the existing log shipping secondary. Q2: Is there a problem with removing log shipping for a database that has special characters in it's name? A2: Read the following Microsoft Knowledge Base article, which discusses this problem in more detail: 295936 FIX: Error removing log shipping on secondary database when database name has a quote **Sql 7.0
Q. Where can I find the log shipping installation files or program? A. In SQL Server 7.0 log shipping is in the Back Office Resource Kit. The files supplied as part of the Log Shipping utility include scripts to create the required tables and stored procedures to set up log shipping. The tool also includes a help file, which covers setup and troubleshooting techniques.

Q. Will my SQL Server logins be transferred as part of log shipping? A. No. SQL Server logins are not transferred during log shipping. SQL Server logins are stored in the master database of the server. To make sure that you have all the logins from the production server on the warm standby server you can: Backup and restore the master database from the production server to the warm standby server before you set up log shipping.

-orCreate the logins manually on the destination server. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: 246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server Q. What needs to be done in an environment where log shipping is set up to bring a warm standby server online?

A. To bring a warm standby server online, use these steps: 1. Restore the last transaction log backup from the production server database to the warm standby server by using the WITH RECOVERY option to recover the database. 2. Rename the standby server to the name of the primary server or redirect your application to the standby server. 3. The SQL Server administrator must ensure that all SQL Server logins exist on the standby server. To do this, Microsoft recommends that you restore the master database on the standby server from the primary server before you set up log shipping.NOTE: Restoring the primary server's master database to the standby server removes references to any user databases on the standby server. Existing databases on the standby server can then be reattached. You must re-create any logins that are specific to the standby server. Q. Can you use log shipping in an environment where there are non-logged operations like SELECT INTO or BulkCopy? A. No. The log shipping sequence is broken when you perform any non-logged operations on the source server. Transaction log backups are not allowed after you perform non-logged operations. Perform either a full or a differential backup of the database after any non-logged operation. Restore that backup on the destination server before you resume log shipping.

Q. What process does Microsoft recommend for setting up log shipping in Microsoft SQL Server 7.0? A. Use these steps: 1. If you have any logins that need to be transferred, transfer the logins to the warm standby server by restoring the master database. For more information, see the "How to restore the master database" topic in SQL Server Books Online. 2. Make a backup of the production database that is being set up for log shipping, and then restore the backup on the standby server and use either the NORECOVERY or STANDBY option. 3. Run the log shipping stored procedures included with the tool to create the log shipping control tables and jobs on the secondary server. Q. Where can I check for log shipping error messages? A. The SQL Server error logs on the servers have information about backup and restore operations. In addition, the backup_movement_plan_history table is a history table that is populated by the copy and load jobs on the standby server. The backup_movement_plan_history table provides useful information about the cause of log shipping failures.

Q. When I try to add a backup movement plan by using this code exec msdb..sp_create_backup_movement_plan I get this error message: "SQLServerAgent is not currently running so cannot be notified of this action".

Why do I get this error message and what are the implications of this error message? A. Running the preceding stored procedure populates the table msdb..backup_movement_plan, and then creates the SQL Server Agent copy and load jobs. If the SQL Server Agent is not running on the destination server, the error message occurs. You can safely ignore the error message after you start the SQL Server Agent on the destination server.

Q. The copy job on my destination server fails. What can I check? A. You can check: To see if the SQL Server Agent is running on the destination server. The SQL Server Agent startup account on the standby server. Make sure that it is a domain account and that is has read permissions on the backup share where the backups reside. If the SQL Server Agent is using the system account, the SQL Agent is not able to access the network resources. The job history on the destination server to see which specific job is failing.

Q. Can I have the destination database online while transaction logs are being loaded on the database? A. No. You must restore the full source database backup on the destination database by using either the NORECOVERY or the STANDBY option for log shipping to work. If not, the following error message appears in the backup_movement_plan_history table: "Error on loading file c:\mssql7\backup\Log_Source_tlog_200010090015.TRN : [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4306: [Microsoft][ODBC SQL Server Driver][SQL Server]The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. [Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally." Q. What can I check if I see this error message in the backup_movement_plan_history table? Source directory \\Servername\logshipping share\ is not accessible A. This error message indicates either a connectivity or permissions problem. Try mapping the share from the standby server in Microsoft Windows Explorer. If you are not successful in mapping the share then: Check to make sure that the share has the right permissions and the correct share name. Check to see if the primary server is online. Check the network connectivity between the two servers.

Q. When I see the following error message in the backup_movement_plan_history table, what are the available options? This backup set cannot be restored because the database has not been rolled forward far enough A. Typically, this error message occurs if the transaction log backup sequence is broken. A transaction log backup may have been made that was not part of the log shipping process. The best way to recover from a situation like this is to disable the load job on the secondary server, and then check for the last

successfully loaded transaction log on the database. If the log backups are all available, apply each transaction log backup since the last successful restore. If one or more transaction log backups are unavailable, you must resynchronize log shipping. If you have several files to load to get the databases resynchronized, you can always create a complete backup of the production database, and then perform a restore that uses the NORECOVERY or STANDBY option on the secondary server.

Q. Can database files be added or deleted from the database that is the source in log shipping? A. No. The addition or deletion of any files by altering the database results in the need to resynchronize log shipping. Transaction log restores on the destination server fail with this error message: Server: Msg 3155, Level 16, State 1, Line 1 The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.

******

what are the logshipping frequent issues you have faced? It depends. I have seen some issues like transaction log cannot restored on the secondary database which is due to that the trnsaction log sequence is broken.

**
Question : IS it possible to log ship database between SQL 2000 & SQL 2005? Answer: No, thats impossible, In SQL 2005 transaction log architecture is changed compared to SQL 2000 and hence you won't be able to restore tlog backups from SQL 2000 to SQL 2005 or vice versa. Question:I'm getting the below error message in restoration job on secondary server, WHY?

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 7000000026200001, which is too late to apply to the database. An earlier log backup that includes LSN 6000000015100001 can be restored. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally. Answer:Was your sql server or agent restarted Y'day in either source or destination ? because the error states there is a mismatch in LSN. A particular tran log was not applied in the destination server hence the subsequent tran logs cannot be applied as a result ! You can check log shipping monitor \ log shipping tables to check the which transaction log is last applied to secondary db, if the next consecutive transaction logs are available in the

secondary server share folder you manually RESTORE the logs with NORECOVERY option, Once you restored all the logs automatically from the next cycle the job will work fine. Incase if you are not able to find the next transaction log in secondary server shared folder, you need to reconfigure log shipping. Try the below tasks to re-establish log shipping again.
o Disable all the log shipping jobs in source and destination servers o Take a full backup in source and restore it in secondary server using the With Standby option o Enable all the jobs you disabled previously in step1 Question: Is it possible load balance in log shipping? Answer:Yes ofcourse its possible in log shipping, while configuring log shipping you have the option to choose standby or no recovery mode, there you select STANDBY option to make the secondary database readonly. Question: Can I take full backup of the log shipped database in primary server?? Answer: In SQL Server 2000 you won't be able to take full backup of log shipped database, because this will break the LSN chain and it directly affects the log shipping. In SQL Server 2005, yes its possible. You can take full backup of log shipped database and this won't affect the log shipping. Question : Can I shrink log shipped database log file?? Answer: Yes ofcourse you can shrink the log file, but you shouldn't use WITH TRUNCATE option. If you use this option obviously log shipping will be disturbed. Question : Can I take full backup of the log shipped database in secondary server?? Answer: No chance , you won't be able to execute BACKUP command against a log shipped database in secondary server. Question: I've configured Log shipping successfully on standby mode, but in the restoration job I'm getting the below error. What I do to avoid this in future??

Message 2006-07-31 09:40:54.33 *** Error: Could not apply log backup file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogShip\TEST_20060731131501.trn' to secondary database 'TEST'.(Microsoft.SqlServer.Management.LogShipping) *** 2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use. RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) *** Answer: To restore transaction logs to the secondary db, SQL Server needs exclussive access on the database. When you configure it in standby mode, users will be able to access the database and runs query against the secondary db. Hence If the scheduled restore jobs runs at that time, the db will have a lock and it won't allow SQL Server to restore the tlogs. To avoid this you need to check "Disconnect users in the database when restoring backups" options in log shipping configuration wizard. Question : Suddenly I'm getting the error below, How can I rectify this??? [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4323: [Microsoft][ODBC SQL

Server Driver][SQL Server]The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally Answer : We had the same issue some time ago, this was related to a new file being created in a filegroup on the source. Don't know if this applies to your case, but restoring a backup of this new file on the secondary server solved the problem. Question : Is it possible to log ship database from SQL server 2005 to SQL server 2008 and vice versa? Answer : Yes you can log ship database from SQL server 2005 to SQL Server 2008 this will work. However log
shipping from SQL Server 2008 to SQL Server 2005 is not possible because you wont be able to restore SQL server 2008 backup to SQL Server 2005 (downgrading version) . Question:Benefits of Log Shipping While I have already talked about some of the benefits of log shipping, let's take a more comprehensive look: Log shipping doesn't require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement. In addition, you can use the standby server for other tasks, helping to justify the cost of the standby server. Just keep in mind that if you do need to fail over, that this server will have to handle not one, but two loads. I like to make my standby server a development server. This way, I keep my developers off the production server, but don't put too much work load on the standby server. Once log shipping has been implemented, it is relatively easy to maintain. Assuming you have implemented log shipping correctly, it is very reliable. The manual failover process is generally very short, typically 15 minutes or less. Depending on how you have designed your log shipping process, very little, if any, data is lost should you have to failover. The amount of data loss, if any, is also dependent on why your production server failed. Implementing log shipping is not technically difficult. Almost any DBA with several months or more of SQL Server 7 experience can successfully implement it. Question: Problems with Log Shipping Let's face it, log shipping is a compromise. It is not the ideal solution, but it is often a practical solution given real-world budget constraints. Some of the problems with log shipping include: o o Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs. The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over. Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.

The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally. When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user's applications to the new standby server. In some cases, neither of these options is practical.

Question:Log Shipping Overview Before we get into the details of how to implement log shipping, let's take a look at the big picture. Essentially, here's what you need to do in order to implement log shipping: o o o o o Ensure you have the necessary hardware and software properly prepared to implement log shipping. Synchronize the SQL Server login IDs between the production and standby servers. Create two backup devices. One will be used for your database backups and the other will be used for your transaction log backups. On the production server, create a linked server to your standby server. On the standby servers, create two stored procedures. One stored procedure will be used to restore the database. The other stored procedure will be used to restore transaction logs. On the production server, create two SQL Server jobs that will be used to perform the database and transaction log backups. Each job will include multiple steps with scripts that will perform the backups, copy the files from the production server to the standby server, and fire the remote stored procedures used to restore the database and log files. Start and test the log shipping process. Devise and test the failover process. Monitor the log shipping process.

o o o

Q: What is Log Shipping? A: Log Shipping is one of the solutions that are available with SQL Server to cater to the needs of highly available business applications. It was first introduced with the release of SQL Server 7.0.

As its name suggests, it ships the transaction logs (T-Log) of the one server (primary) to another server (secondary) over the network, to keep the Secondary Server synchronized with the Primary Server. In case of the failure of the Primary Server, we can quickly move over to Secondary Server with the help of just a few steps.

Q: Why we should have Log Shipping as a High Availability option or as a Disaster Recovery option in our environment? A: Following are the few points we can provide for justification:

Easier to set up: It's really an easy job to create Log Shipping. The only thing we need to identify is that we must have two or three servers. The witness server( first is primary/source server and second is secondary/target server) is optional to configure, and it is acceptable if this witness server is not identical to other machines.

Manageability: Managing Log Shipping is the simplest among the available HA options that we have with SQL Server. There are only a few errors that can show up with the Log Shipping and they are quite simple to solve (we will see this in the appendix on troubleshooting in detail).

Can act as a Reporting server: The Secondary or standby server can act as a Reporting server. We may use this server to read only queries, or to generate reports from. Although, it is really easy to get it working as a reporting server, we need to consider the latency. This means if the T-Log backup copy and restoration would take 10 minutes, and we have scheduled T-Log backup every hour, we will observe 10 minutes delay in data / per hour.

Multiple database(s)/servers: We may use multiple servers as a standby/Secondary Server, where one server can be used for reporting purposes, whereas the other can be utilized for HA only.

Cheaper than cluster: If we go for clustering, we have to have a special hardware that is compatible with clustering and should be identical in all the participating nodes. On the contrary, Log Shipping doesn't require the same set of hardware.

Logshipping Interview Questions

Question:What is Log Shipping Essentially, log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all. The key feature of log shipping is that is will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in "synch". Should the production server fail, all you have to do is point the users to the new server, and you are all set. Well, its not really that easy, but it comes close if you put enough effort into your log shipping setup.

Question : IS it possible to log ship database between SQL 2000 & SQL 2005?

Answer: No, thats impossible, In SQL 2005 transaction log architecture is changed compared to SQL 2000 and hence you won't be able to restore tlog backups from SQL 2000 to SQL 2005 or vice versa. Question:I'm getting the below error message in restoration job on secondary server, WHY? [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 7000000026200001, which is too late to apply to the database. An earlier log backup that includes LSN 6000000015100001 can be restored. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally. Answer:Was your sql server or agent restarted Y'day in either source or destination ? because the error states there is a mismatch in LSN. A particular tran log was not applied in the destination server hence the subsequent tran logs cannot be applied as a result ! You can check log shipping monitor \ log shipping tables to check the which transaction log is last applied to secondary db, if the next consecutive transaction logs are available in the secondary server share folder you manually RESTORE the logs with NORECOVERY option, Once you restored all the logs automatically from the next cycle the job will work fine. Incase if you are not able to find the next transaction log in secondary server shared folder, you need to reconfigure log shipping. Try the below tasks to reestablish log shipping again.
o o

Disable all the log shipping jobs in source and destination servers Take a full backup in source and restore it in secondary server using the With Standby option Enable all the jobs you disabled previously in step1

Question: Is it possible load balance in log shipping? Answer:Yes ofcourse its possible in log shipping, while configuring log shipping you have the option to choose standby or no recovery mode, there you select STANDBY option to make the secondary database readonly. Question: Can I take full backup of the log shipped database in primary server?? Answer: In SQL Server 2000 you won't be able to take full backup of log shipped database, because this will break the LSN chain and it directly affects the log

shipping. In SQL Server 2005, yes its possible. You can take full backup of log shipped database and this won't affect the log shipping. Question : Can I shrink log shipped database log file?? Answer: Yes ofcourse you can shrink the log file, but you shouldn't use WITH TRUNCATE option. If you use this option obviously log shipping will be disturbed. Question : Can I take full backup of the log shipped database in secondary server?? Answer: No chance , you won't be able to execute BACKUP command against a log shipped database in secondary server. Question: I've configured Log shipping successfully on standby mode, but in the restoration job I'm getting the below error. What I do to avoid this in future?? Message 2006-07-31 09:40:54.33 *** Error: Could not apply log backup file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogShip\TEST_20060731131501.trn' to secondary database 'TEST'.(Microsoft.SqlServer.Management.LogShipping) *** 2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use. RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) *** Answer: To restore transaction logs to the secondary db, SQL Server needs exclussive access on the database. When you configure it in standby mode, users will be able to access the database and runs query against the secondary db. Hence If the scheduled restore jobs runs at that time, the db will have a lock and it won't allow SQL Server to restore the tlogs. To avoid this you need to check "Disconnect users in the database when restoring backups" options in log shipping configuration wizard. Question : Suddenly I'm getting the error below, How can I rectify this??? [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4323: [Microsoft][ODBC SQL Server Driver][SQL Server]The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally Answer : We had the same issue some time ago, this was related to a new file being created in a filegroup on the source. Don't know if this applies to your case, but restoring a backup of this new file on the secondary server solved the problem.

Question : Is it possible to log ship database from SQL server 2005 to SQL server 2008 and vice versa? Answer : Yes you can log ship database from SQL server 2005 to SQL Server 2008 this will work. However log shipping from SQL Server 2008 to SQL Server 2005 is not possible because you wont be able to restore SQL server 2008 backup to SQL Server 2005 (downgrading version). Question:Benefits of Log Shipping While I have already talked about some of the benefits of log shipping, let's take a more comprehensive look:

Log shipping doesn't require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement. In addition, you can use the standby server for other tasks, helping to justify the cost of the standby server. Just keep in mind that if you do need to fail over, that this server will have to handle not one, but two loads. I like to make my standby server a development server. This way, I keep my developers off the production server, but don't put too much work load on the standby server. Once log shipping has been implemented, it is relatively easy to maintain. Assuming you have implemented log shipping correctly, it is very reliable. The manual failover process is generally very short, typically 15 minutes or less. Depending on how you have designed your log shipping process, very little, if any, data is lost should you have to failover. The amount of data loss, if any, is also dependent on why your production server failed. Implementing log shipping is not technically difficult. Almost any DBA with several months or more of SQL Server 7 experience can successfully implement it.

Question: Problems with Log Shipping Let's face it, log shipping is a compromise. It is not the ideal solution, but it is often a practical solution given real-world budget constraints. Some of the problems with log shipping include:
o

Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs. The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server

failure, your network, the standby server, and the application or applications to be failed over.
o

Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable. The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally. When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user's applications to the new standby server. In some cases, neither of these options is practical.

Question:Log Shipping Overview Before we get into the details of how to implement log shipping, let's take a look at the big picture. Essentially, here's what you need to do in order to implement log shipping:
o

Ensure you have the necessary hardware and software properly prepared to implement log shipping. Synchronize the SQL Server login IDs between the production and standby servers. Create two backup devices. One will be used for your database backups and the other will be used for your transaction log backups. On the production server, create a linked server to your standby server. On the standby servers, create two stored procedures. One stored procedure will be used to restore the database. The other stored procedure will be used to restore transaction logs. On the production server, create two SQL Server jobs that will be used to perform the database and transaction log backups. Each job will include multiple steps with scripts that will perform the backups, copy the files from the production server to the standby server, and fire the remote stored procedures used to restore the database and log files. Start and test the log shipping process. Devise and test the failover process. Monitor the log shipping process

o o o

how can the secondary server access the backup file how will the secondary server know which files to restore how will the secondary server know which files not to restore, as they may be in the process of being copied