SQL Server Mirroring vs Replication – Quick Overview

As a DBA your job is often to advise why one technology should be used versus another. One question I have been asked various times over the last few years is which is better to use when it comes to replication vs mirroring?

Often the reason the question is being asked is because alot of people don’t really understand exactly how these technologies work, so are unsure of which will best suit their requirements.

One important difference to bear in mind is that replication can be just a single table ( or multiple tables and objects), whereas mirroring
is an entire database.

mirroring vs replication

The first thing to do is to figure out what exactly the requirements are, what version of SQL you are using, and then look at both methods to see which would be best suited.

Things get more complex from SQL 2012 onward with availability groups, to keep it simple this article primarily applies to SQL2008 and SQL2008R2

If you are using SQL 2012 or greater check out this article on Microsoft about availability groups for extra info.

Why use Replication?

Pros

  • Can be just one table or many (And you can replicate other objects as well)
  • You can use the data at both ends, to what degree depends on the replication method used

Cons

  • Can require considerable admin time – the more tables and data the more issues that can be encountered
  • No automatic out of the box fail over if used for DR

Why use Mirroring?

Pros

  • Depending on configuration and SQL version you can do read only operations from the secondary
  • Maintenance overhead is minimal and failover is relatively simple and can even be automatic (with a witness)

Cons

Often unfortunately the answer to this question depends on a lot of factors. You need to weigh up the technology you have available, along with the options and requirements and choose the best solution based on those findings.

If you have any questions drop us a comment with your scenario, we would be happy to share our advice.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

2 Comments

  1. hi, i need your advise for our database administering. We developed our app with symfony one for Administration and another for Front end user. we have demographic contents like for local data, Lankan, Thailand, UK, USA. its a Video content site but in back end we have other systems like HR management, Account payroll etc but in single database, now for regional content we need one database to have all regional content data, and other with specific regional data as we have millions of data so we want to reduce query by region. so what will be the best way engineer our database, replication or mirroring or another technology

    1. Hey there, it would be hard to advise without knowing all the details, however if you are only requiring a subset of the data replication could be one option. Thanks for dropping by.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.