CODEWITHSUNDEEP
sql-serverdatabase
Andre Rubnikowich
Andre Rubnikowich

Reputation: 475

db replication vs mirroring

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?

Upvotes: 13

Views: 12441

Answers (3)

dbamex
dbamex

Reputation: 211

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.

Upvotes: 5

ColdSolstice
ColdSolstice

Reputation: 465

(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.

Upvotes: 3

dstrants
dstrants

Reputation: 7705

As mentioned here

Database mirroring and database replication are two high data availability techniques for database servers. In replication, data and database objects are copied and distributed from one database to another. It reduces the load from the original database server, and all the servers on which the database was copied are as active as the master server. On the other hand, database mirroring creates copies of a database in two different server instances (principal and mirror). These mirror copies work as standby copies and are not always active like in the case of data replication.

This question can also be helpful or have a look at MS Documentation

Upvotes: 2

Related Questions