This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What are common reasons which breaks or stops the Log Shipping?
- Recent modifications on the shared folders.
- Human error like, either someone used the option of truncate only or switched the recovery model.
- date/time for the windows servers unmatching due to any DST activities.
- Datafile added on Primary on different drives then you need to apply that on secondary with move until that your log shipping restore job will fail.
- Any I/O,Memory,N/w bottleneck.
- Your tuf file is missing.
- You may have set the incorrect value for the Out of Sync Alert threshold.
- Might be you have scheduled the jobs in same time.
- Might be your MSDB database is full.
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 “.
Was your sql server or agent restarted yesterday in either source or destination ? because the error states there is a mismatch in LSN. A particular transaction log was not applied in the destination server hence the subsequent transaction 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 database.
Incase if you are not able to find the next transaction log in secondary server shared folder, you need to reconfigure log shipping.
What are your basic steps to reconfigure the Log Shipping?
- 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.
Is it possible load balance in log shipping?
Yes: 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.
Can I take full backup of the log shipped database in secondary server?
No: You cannot take the full backup.
What are the benefits of Log Shipping?
- 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.
- 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.
Once the primary server comes back online, is it a difficult process to switch back to the primary database server?
- Traditionally, depending on the size and number of database that are invloved in logshipping, it may or may not be difficult.
- Database size and your network bandwidth matters a lot. Normally, you just have to resetup log-shipping. Meaning – point all your apps to primary and in background, do a full backup of primary, copy that to secondary server and restore it with no-recovery with subsequent log backups.
- To minimize the downtime, you can use “Reverse logshipping” will prove to be a huge help.
Will changing the Recovery model to Full (from simple) have any issues?
Yes. Changing recovery model breaks the log chain.
Will my existing backups (using Symantec Backup Exec) be affected by enabling log shipping and switching to a full recovery model?
Once you setup logshipping, there is no need to take any additional log backups. In fact, any adhoc log backups will break the log chain. Use COPY_ONLY backups.
There are two servers. one is primary and the other is secondary. primary is on production server.
- Logshipping configured and working fine. Suddenly, after some days, the sync between the servers is not there.
- What is the immediate action?
- First thing I’d do is check the backup log job and the copy job which copies the job from the primary to secondary and after that check the restore job which restores the log on the secondary. If there are no errors there then I’d start looking in msdb at the log shipping tables and see if I could determine the error from there.
Do we require any endpoints for Log Shipping?
We don’t need to create endpoints or assign port in log shipping, which is required for database mirroring.
What are the log shipping 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 transaction log sequence is broken.
You might find that the last backed up/copied/restored files do not reflect correctly in the log shipping reports when you use a remote monitor server. What is this issue?
The last copied and restored file will show up as null if the monitor instance is not on the same box as the secondary instance. The last backed up file will show up as null if the monitor instance is not on the same box as the primary instance if the select @@servername value is not used as the monitor server name while configuring the log shipping monitor.
What do you know about the below error?
Error: During startup of warm standby database ‘testdb’ (database ID 7), its standby file (‘<UNC path of the TUF file>’) was inaccessible to the RESTORE statement. The operating system error was ‘5(Access is denied.)’.
If you have configured Log Shipping with STANDBY mode on SQL Server 2008 and the destination folder for the TLOGS uses a remote server on which the sqlservice/sqlagent is not a Local Admin, then the restore job will fail every time.
The sp_resolve_logins stored procedure runs successfully; however, it does not perform the expected modifications to the security on the secondary server. Why?
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.
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?
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.
What are the main differences between Log Shipping and Database Mirroring?
Log Shipping:
- It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled.
- It has a manual Failover.
- You can use a secondary database for reporting purposes when the secondary database restore in STANDBY mode.
- The servers involved in log shipping should have the same logical design and collation setting.
- The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.
- The SQL server agent should be configured to start up automatically.
- You must have sysadmin privileges on each computer running SQL server to configure log shipping.
Mirroring:
- Database mirroring is a primarily software solution for increasing database availability.
- It has a Automatice Failover.
- Mirrored DB can only be accessed using snapshot DB.
- It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.
- Verify that there are no differences in system collation settings between the principal and mirror servers.
- Verify that the local windows groups and SQL Server logins definitions are the same on both servers.
- Verify that external software components are installed on both the principal and the mirror servers.
- Verify that the SQL Server software version is the same on both servers.
- Verify that global assemblies are deployed on both the principal and mirror server.
- Verify that for the certificates and keys used to access external resources, authentication and encryption match on the principal and mirror server.
