Log
shipping is a data base level high availability feature, Basically it works
using log backup and restore.
- It is simple method of keeping
entire database in different server.
- It works with backup, copy and
restore jobs.
- We need 3 servers
* Primary server
* Secondary ,,
* Monitor server (optional) - In primary server database is
in online state where the applications or users are connected.
- In secondary server database is
in standby( Read Only ) mode where we can read data.
- In secondary server T.Log
backups are applied either with
* Norecovery or
* Standby - It will take 45 min to apply
the changes from primary to secondary data base.
- We can have multiple secondary
servers.
Advantages :
- To reduce downtime for the
applications or users in case of primary server failed.
- To implement load balancing.
i.e we can allow reading from secondary server and
manipulations(insert, update, delete) in primary server.
- To maintain multiple secondary
databases.
Disadvantages :
- No automatic fail over, It
supports only manual fail over.
- It will take at least 45 min to
apply the changes from primary to secondary data base.
- In case primary data base
crashed there might be a data loss.
It is another high availability feature available
from SQL Server 2005. Previous versions support the simple high availability
feature called Transaction Log Shipping. Log shipping has its own limitation as
it doesn’t support automatic fail over as well as there might be data
loss. All these limitation we can overcome with database mirroring.
Database mirroring supports automatic fail over and the
transactions are applied to standby (Mirror) server immediately once they are
committed at principle server. Like Log shipping no need of backup, copy and
restore operations and jobs.
· In Principal server database is in ONLINE
state.
· In mirror server database is in a restoring
state, which means it is not available for incoming requests.
Advantages and benefits:
· Protection against database failures
· Automatic failure detection and fail over
· Support of easy manual fail over
· Automatic client redirection
· Multiple operating modes
· No special hardware requirements
· Minimized chance of data loss
· Relatively ease of set up and configuration
Note: If we want to use secondary data base for
reading purpose it is not possible in mirroring because secondary database will
be in restoring state.
Replication
allows to maintain same database multiple copies at different locations. Log
shipping and mirroring allows to maintain complete database redundancy whereas
replication allows to maintain some part of the database (a set of required
objects) at users location. Changes made at different user locations are
synchronized to the main server. It is object level high availability feature
Advantages :
·
Improved performance
·
To reduce locking
conflicts when multiple users are working
·
Improved availability
·
Easy maintenance
·
To allow sites work
independently. So that each location can set up its own rules and procedures
for working with its copy of the data.
·
To move data closer to
the user
1. Snapshot Replication
· The snapshot process is commonly used to
provide the initial set of data and database objects for transnational and
merge publications.
· It copies and distributes data and database
objects exactly as they appear at the current moment of time.
·
Snapshot replication
is used to provide the initial data set for transnational and merge
replication.
·
It can also be used
when complete refreshes of data are appropriate (BOL).
·
Scenarios
·
When the data is not
changing frequently.
·
If we want to
replicate small amount of data.
·
To replicate Look-up
tables which are not changing frequently.
Note: We can go with Snapshot Replication if data
is not changing frequently also there is delay in replicating the data from
publisher to subscriber (secondary database).
No comments:
Post a Comment