SQL server replication and database mirroring is used copy data into different server or location. Anybody can confused with them. Both have some difference. This article describes some difference between SQL server replication and SQL Server database mirroring .
Replication VS Mirroring
The difference between SQL server replication and database mirroring:
- Subscriber/ Replicated Database are accessible for read or write access.
- Information flow can be bi-directional (Publisher to Subscriber).
- 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.
- There is a option to select all or customize table/SP/View.
- Mirror database is not accessible for read or write access. Secondary server will only be available when the primary server goes down.
- Information flow will be unidirectional (from Principal to Mirror Server).
- 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.
- There is no such option.
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 we need, what are the business requirements, which will help us to choose the right topology in our environment. We can go through SQL Books Online for more details about Mirroring and Replication.