February 27, 2014 at 1:44 am
Hi Team,
Can anybody help me understand why do one go combination of Database Mirroring + Replication.
Why can't I just have Database mirroring for high availability of my database. What is the need of combining replication as well.
In what scenario's we choose combining both these technologies.
Can anyone give an example of combining these 2 technologies ?
I am not sure if people a make things complex combining all high availability solutions. ( Clustering + db mirroring + replication)
Thanks in advance.
February 27, 2014 at 2:49 am
Oracle_91 (2/27/2014)
Hi Team,Can anybody help me understand why do one go combination of Database Mirroring + Replication.
Why can't I just have Database mirroring for high availability of my database. What is the need of combining replication as well.
In what scenario's we choose combining both these technologies.
Can anyone give an example of combining these 2 technologies ?
I am not sure if people a make things complex combining all high availability solutions. ( Clustering + db mirroring + replication)
Thanks in advance.
Database Mirroring operates (unsurprisingly, given the name) at the database level and provides a non-queryable copy of an entire database on another instance which is typically on a separate server to the Principal database and indeed in a separate location, e.g. a Disaster Recovery (DR) site. It can be run in various configurations, some of which allow for automatic failover and client redirection if the Principal fails. This makes it possible to recover very quickly from a production outage.
Replication works at a lower level of granularity - it is possible to replicate a subset of tables, views, stored procedures etc and even filter rows within the tables. The data is replicated to one or more Subscriber databases which are queryable.
So, an example of combining these technologies would be to use mirroring as a DR strategy and replication to populate an Operational Data Store (ODS) or reporting database to protect the Production OLTP database from excessive load caused by reporting queries.
You also mentioned clustering: that operates at the Windows server level so all instances on a cluster node would failover to another node in the event of a server-level problem.
It is very common to use all 3 technologies (Mirroring, Replication and Clustering). Indeed that has been standard practice in every shop I've worked in.
You can find out much more information in Books Online (BOL) or at the links below.
http://technet.microsoft.com/en-us/library/ms151198(v=sql.105).aspx
http://technet.microsoft.com/en-us/library/ms189852(v=sql.105).aspx
Regards
Lempster
February 27, 2014 at 8:18 am
Thank you very much Lempster and Perry.
February 27, 2014 at 8:44 am
Hi,
One follow up question out of curiosity. If I have transactional replication with remote distributor (say) and database mirroring for publisher database in High safety mode(i.e. automatic failover), then how would I go with patching/apply service packs ? Where should I start applying the patch at the same time taking care of High Availability as well ?
February 27, 2014 at 8:45 am
Oracle_91 (2/27/2014)
Thank you very much Lempster and Perry.
You're welcome. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply