asked
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 ?!
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.
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.....
17 People are following this question.
