Monday, August 5, 2013

What's the difference between SQL Replication and SQL Database Mirror?

A.)

Mirroring:-
The Mirror database is not accessible for read or write access.

Replication:-
The Subscriber Database (backup site) is open to reads and writes.

B.)
Mirroring:-
Information flow will be only one way (from Principal to Mirror Server)

Replication:-
Changes can be merged, bi-directional changes can be made, so the information can flow from Publisher to Subscriber and the other way around.

C.)
Mirroring:-
In case of failure of the Principal Database, the Mirror Database will take over the control and will act as Principal and applications can be redirected automatically to connect to this new Principal Server. Very little downtime. No code change required in the application.

Replication:-
In case of failure on Publisher, applications need to be re-directed to the Subscriber manually (in case you really want to do that), requires code change in the app or the connection string.


D.)
Mirroring:-
Almost everything inside the DB is replicated to the DR site, Schema changes can be replicated easily.

Replication:-
You have the option to replicate selected set of tables/SP/functions inside the DB, Schema changes can give some hiccups.




In Short, Mirroring is a good tool for DR (Disaster Recovery) with very little downtime, but the drawback is that the DR site will *not* be accessible to users, whereas Replication can be used to Merge Data between two Servers, can act as a good tool for Reporting purposes as the backup site is accessible to the users, can also act a DR solution.

It all depends on what you need, what are the business requirements , which will help you to choose the right topology in your environment. You can go through SQL Books Online for more details about Mirroring and Replication.

No comments:

Post a Comment