Log Shipping
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.
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.
What are
Disadvantage of Log Shipping?
1. No Automatic
Fail-over: DBA needs to manually fail-over the server.
2. Downtime is required
when there is fail-over.
3. Possible Data Loss:
Some data can lost in case of server crash, it depends
on schedule of Log Shipping jobs.
4. Log shipping set-up
cannot be scripted.
5. Each database which
needs to be log shipped should be set up through separate Log shipping Wizard.
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.
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.
Is it possible to
configure Log Shipping from lower version to upper version and Vice versa?
Yes it is possible to configure Log Shipping from lower to upper version.
But it is not possible vice versa.
What all jobs are
created after configuring the Log Shipping?
Internally when
Log Shipping is configured, there are 4 jobs created between Primary Server and
Secondary Server, they are Backup Job, Copy Job, Restore Job and Alert Job
·
Backup job: This job is created on Primary Server; this job
takes the transaction log backup of the Database on a scheduled time
·
Copy Job: This job is created on Secondary Server,
this job Copies the transaction log Backup from Primary Server to the
Standby/Secondary Server.
·
Restore Job: This job is created on Secondary Server; this job
restored the copied transaction log backup on the Secondary Server.
What permissions are
required for shared folders on Primary and secondary for the service accounts?
·
For the backup job, read/write permissions to the backup
directory are required to the following:
SQL
Server service account on the primary server instance.
Proxy
account of the backup job. By default, this is the SQL Server Agent account on
the primary server instance.
·
For the copy job, read
permissions to the backup directory and write permissions to the copy directory are required by the proxy
account of the copy job. By default, this is the SQL Server Agent account on
the secondary server instance.
·
For the restore job, read/write
permission to the copy directory are required by the following: The SQL
Server service account on the secondary server instance. The proxy account of
the restore job. By default, this is the SQL Server
Agent account on the secondary server instance.
TUF
stands for Transaction Undo file.
The
log shipping will not work. We have to setup the Log Shipping again.
No,
it will not be created on the secondary server.
Yes,
it will be created automatically on the secondary database.
Yes it’s possible. We can take full backup of log
shipped database and this won’t affect the log shipping.
How to failover secondary server, when the
Primary Server fails?
If the Primary Server will
become un-available, do the following steps.
a) Take the Tail of Log from Primary
server if possible.
b) Restore Tail of log into all Secondary
Database
c) Remove Log-shipping
configuration from Primary Server
d) Select any one of Secondary server and
bring into online with Alter Database DBName set
Online
e) Right click on Primary Database and
Generate script for Users and Logins.
f) Then move the script to
Secondary server to create Users and Logins
g) Re-configure log shipping from New
Server (Secondary server)
What are errors occurred in Log
shipping?
There are two errors are
occurred during Log shipping
1) 14420:-
This error occurs when the Backup job fails
2) 14421:-
This error occurs when the Restoring job fails
What is WRK file in log shipping?
he .wrk files are produced
when the transaction log backups are
copied from the backup location to the secondary server (by the agent job on
the secondary). The files are named .wrk during the copy operation; when they have been
completely copied they are renamed to have the .trn
extension
·
TUF File: It’s Transaction Undo File. It Generated only when you Have
Configured Log Shipping With Stand by Option. Since In Stand by Log Shipping Secondary Database is Available to
User. So TUF Keep Pending Transaction Which are in Log File Came from Primary
So That when Next Log Backup Will Come From Primary
They Can Be Synchronized At Secondary.
·
.WRK : This Extension
Is Given To A File Which is Being Copied From Primary Backup Location to
Secondary and Once Copy Process has been completed these file are renamed with
.trn file.
Types of mode in SQL Log
shipping?
Two modes are used in
log shipping:
1. No-Recovery- No user
access is given to the secondary database and the database will be in Restoring
state so the database does not have to care about
uncommitted transactions.
2. STANDBY - secondary
database can be used for Read-only purpose and .TUF file is used to contain the
uncommitted transactions.