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 http://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 http://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 http://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 http://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.