SQL Server - Logshipping Information

Asked By prathap chowdary on 23-Feb-12 02:15 AM
Hi,

I have configured Logshipping............Everything is fine..........

But i have the doubt........if the schema changes in primary server...........automatically all schema changes reflected into secondary server ?

or else again we need to restore of full back up all?

Please help me.

Regards,
Prathap.D
kalpana aparnathi replied to prathap chowdary on 23-Feb-12 02:22 AM
hi,

If your database is in simple recovery model, then you cannot perform a point-in-time recovery. If the database is not in simple-recovery model and you have log backups right upto the point of failure without a break in the log chain, then you can take a tail-log backup (if LDF is intact and the command succeeds) as pointed out by yrushka and perform a point-in-time recovery of the database without losing any data. If there are no log backups till now, then nothing can be done unfortunately.


Another option is that if you have the database in full/bulk logged-recovery model and no log backups have been taken, then the transaction log can be read using tools (eg. from Idera, LiteSpeed) which will allow you to generate the commands from the transaction log file which can be replayed on the database once it is restored.


Additionally, if you have a full backup and all the log backups till now, then you can restore them till the latest available log backup. Then using log reader tools above, you can generate the rest of the commands from the LDF file and replay them against the database.


Regards,

Suchit shah replied to prathap chowdary on 23-Feb-12 02:25 AM
Log Shipping is used to synchronize the Distributed Database Server. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server used SQL Server Job Agents for making those processes automatic. Log Shipping does not involve automatic transfer of server if there is any failure. This means it has just synchronized the databases but if the primary server fails, it will not redirect your application to a secondary server. This has to be done manually.

The main functions of Log Shipping are as follows:
 •Backing up the transaction log of the primary database
•Copying the transaction log backup to each secondary server
•Restoring the transaction log backup on the secondary database


So if there is no failure then it will do it by transfering transcation log
Somesh Yadav replied to prathap chowdary on 23-Feb-12 03:35 AM

We found our own solution as follow,

  1. Mirroring and Log Shipping both require VPN and High Security So we dumped them.
  2. Mirroring and log shipping and almost all synchronization methods of SQL Server really does not care about network bandwidth usage and they dont compress anything.

MSDN Says Differential File Backups are faster, we chosen differential file backups. Yes for 15 min, it looks little overkill but they are fastest and more reliable. And for 24 hours, the accumalated changes are only few MBs.

The backups are taken by custom windows service and they are also compressed to save network transfer. Plus we get proper email notifications of everything.

Plus slave database can be anywhere over internet. Backups are secure and compressed with password. And a small HTTP in built web server transfers data from one machine to another so less configuration overhead is required.

When we have many servers, configuring them is huge pain. Plus every network admin may make a mistake and create disaster.