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.
is an entire database.
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
- Must be the entire database
- Can affect performance ( Synchronous Database Mirroring (High-Safety Mode) )
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.
No comments:
Post a Comment