2

We currently use SQL Server 2012 Standard Edition and we mirror from one SQL Server instance to another for automatic failover purposes.

I want to replicate the database which is mirrored, to another SQL Server instance, for reporting purposes. The replication will need to be as close to real time as possible.

Questions

  • Can you have SQL Server replication working alongside mirroring?
  • If so, how does it function and does it have constraints / impact on the mirroring?
Improve this question

2 Answers 2

Reset to default
2

You can absolutely setup transactional replication on mirrored publishing databases. It is fully supported, and Microsoft has some good content on getting it up and running.

The only caveats are the ones that already exist for transactional replication, and the recommendation to have the distributor database on another server outside of the mirrored pair.

Improve this answer
2
  • Thanks for the link. What is the impact if any on turning replication on for the primary production SERVER?
    – Hamesh Luthra
    Commented Nov 10, 2016 at 21:17
  • From an impact perspective, you'll see heavy IO during the snapshot phase (unless you initialize from a backup). Other than that, increased memory usage in windows (for the agents), but not a significant one. You will also want to be more aware of transaction log usage, as a dead subscriber will prevent log clearing.
    – Nic
    Commented Nov 10, 2016 at 22:01
0

Yes, all different types of replication work with mirroring.

You may have a performance impact, which cannot be assessed with the information you provide. However, it would be minimal with respect to the mirroring, it just adds resource usage on your master; but similar concerns to what you should evaluate for replication in a non-mirrored database.

However, you may prefer to consider snapshots in concert with mirroring, as a solution for reporting purposes.

References:

Database Mirroring and Database Snapshots

https://msdn.microsoft.com/en-us/library/ms175511.aspx

Database Mirroring and Replication (SQL Server)

https://msdn.microsoft.com/en-us/library/ms151799.aspx

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.