SQL Mirroring VS SQL Replication

if i have Database located in two instance one of them hosted on web server and the other is local instance . i need to make a scenario to synchronize data between them on time . any transaction run on the web instance must run in local too . i searched in this and i am not sure if i can use the replication (merge replication ) or use the mirroring .. can i get from your experiences friends ?!

more ▼

asked Mar 15, 2016 at 02:45 PM in Default

avatar image

21 2

I have read that mirroring will be soon be a deprecated feature, hence SQL mirroring is not advised if you are starting new, considering future scalability. If your SQL Server version is 2012 and above, you can also think of using AlwaysOn Availability Groups which is a HA-DR solution providing an enterprise-level alternative to database mirroring.

Mar 16, 2016 at 09:55 AM nidheesh.r.pillai
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Both features will do what you want to a varying degree. But there are some differences.

Merge Replication allows data updates to happen at both instances, whereas Mirroring is one-way. There is more control in replication as to what to copy over - you can choose individual columns, you can exclude indexes, etc. Mirroring is an exact copy - all or nothing! Mirrored databases are also not accessible, so you can't connect and read from it (workaround in Enterprise is to run snapshots). You can access the databases in Replication.

Typically Mirroring is seen as a feature for Disaster Recovery, Replication is more for sharing out data - you mention Merge Replication, but there are other types too - what scenario exactly are you trying to cover?

As another poster has said, Mirroring is a deprecated feature, but it's still there in 2012 and 2014. 2016 has introduced Basic Availability Groups as the intended replacement, especially for those people sticking to Standard Edition.

And then there's Log Shipping.....

more ▼

answered Mar 16, 2016 at 02:22 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 15, 2016 at 02:45 PM

Seen: 196 times

Last Updated: Mar 16, 2016 at 02:22 PM

Copyright 2018 Redgate Software. Privacy Policy