db replication vs mirroring

Question

Can anyone explain the differences from a replication db vs a mirroring db server?

I have huge reports to run. I want to use a secondary database server to run my report so I can off load resources from the primary server.

Should I setup a replication server or a mirrored server and why?

Answer 1

For your requirements the replication is the way to go. (asumming you're talking about transactional replication) As stated before mirroring will "mirror" the whole database but you won't be able to query unless you create snapshots from it.

The good point of the replication is that you can select which objects will you use and you can also filter it, and since the DB will be open you can delete info if it's not required( just be careful as this can lead to problems maintaining the replication itself), or create specific indexes for the report which are not needed in "production". I used to maintain this kind of solutions for a long time with no issues.

Answer 2

(Assuming you are referring to Transactional Replication)

The biggest differences are: 1) Replication operates on an object-by-object basis whereas mirroring operates on an entire database. 2) You can't query a mirrored database directly - you have to create snapshots based on the mirrored copy.

In my opinion, mirroring is easier to maintain, but the constant creation of snapshots may prove to be a hassle.