SQL Mirroring And Replication

Database Mirroring

Database Mirroring between two SQL server instances is designed
to have a mirror of your main database. The mirror server database is capable
of handling all the database activities as the main server database in case of
the main server’s failure.

In a mirroring scenario, the main database server is called
the principle server while the mirror server database is called the mirror
server. Thus, the general diagram for database mirroring looks as follows

 

Optionally, you can add a witness server that monitors the
mirroring. In case of failure in the principal server, the witness server
presence supports automatic failover to the mirror server if both the witness
and the mirror server are connected. (Note: In the absence of a witness server,
only manual failover is possible). The general diagram for database mirroring
is as follows:

 

 

For an overview of mirroring in SQL 2008 R2 as well as
information on synchronous/asynchronous mirroring, please check https://msdn.microsoft.com/en-us/library/ms189852.aspx

Thus, at any point only the principal server database will
be serving users’ requests. The mirror server database will keep up-to-date
(synchronously or asynchronously) so that it can be used in case of principal
server’s failure.

Database Replication

Database Replication on the other hand is designed to keep
replicas of the same database on different servers. It can be implemented for security
reasons (to use data differently at different locations so each database has a
replica of a part of a master database) or to increase distributed application performance
(to have a replica of the database, or parts of it, at different locations so that
each site would have its own local database).

In a replication environment, there is a publisher, and one
or many subscribers. A publisher is the central source of data, while the
subscribers receive copies of all the data or the changes based on the publication
types. An additional role in the replication scenario is the distributor who
handles the distribution of data between the publisher and the subscriber, but
you can set the publisher server to be the distributor.

Database Replication has the following types:

· Snapshot replication  

Distribute data as it appears on a specific
time. For an overview, check https://msdn.microsoft.com/en-us/library/ms151832(v=SQL.100).aspx

 

· Transactional replication

Takes an initial snapshot of data as
snapshot replication, and then distributes subsequent data changes to the subscribers.
For an overview, check https://msdn.microsoft.com/en-us/library/ms151176(v=SQL.100).aspx

 

· Merge replication

Takes an initial snapshot of data as
snapshot replication, and then tracks changes on the publisher and subscribers
through triggers. Replication merges the changes between the different servers
so that the publisher and the subscribers will have all the updated data on any
of the servers. For an overview, check https://msdn.microsoft.com/en-us/library/ms152746(v=SQL.100).aspx

 

In all of the above replication types, the different
database servers can be accessed and updated independently of the others, even
if there is a dis-connectivity between them.