Mirroring VS Replication

Mirroring vs Replication

It turns out that Database mirroing prevents data to be accessed directly, mirrored data are only accessable through a database snapshot, so reports from snapshot data will not be up to date so, I will use Database Transactional Replication to provide high availabilit and load balancing.

Apache Kafka: Mirroring vs. Replication

They are used for different use cases. Let's try to clarify.

As described in the documentation,

The purpose of adding replication in Kafka is for stronger durability and higher availability. We want to guarantee that any successfully published message will not be lost and can be consumed, even when there are server failures. Such failures can be caused by machine error, program error, or more commonly, software upgrades. We have the following high-level goals:

Inside a cluster there might be network partitions (a single server fails, and so forth), therefore we want to provide replication between the nodes. Given a setup of three nodes and one cluster, if server1 fails, there are two replicas Kafka can choose from. Same cluster implies same response times (ok, it also depends on how these servers are configured, sure, but in a normal scenario they should not differ so much).

Mirroring, on the other hand, seems to be very valuable, for example, when you are migrating a data center, or when you have multiple data centers (e.g., AWS in the US and AWS in Ireland). Of course, these are just a couple of use cases. So what you do here is to give applications belonging to the same data center a faster and better way to access data - data locality in some contexts is everything.

If you have one node in each cluster, in case of failure, you might have way higher response times to go, let's say, from AWS located in Ireland to AWS in the US.

You might claim that in order to achieve data locality (services in cluster one read from kafka in cluster one) one still needs to copy the data from one cluster to the other. That's definitely true, but the advantages you might get with mirroring could be higher than those you would get by reading directly (via an SSH tunnel?) from Kafka located in another data center, for example single connections down, clients connection/session times longer (depending on the location of the data center), legislation (some data can be collected in a country while some other data shouldn't).

Replication is the basis of higher availability. You shouldn't use Mirroring to handle high availability in a context where data locality matters. At the same time, you should not use just Replication where you need to duplicate data across data centers (I don't even know if you can without Mirroring/an ssh tunnel).

MySQL Replication VS Mirroring and how to do both?

MySQL Replication: https://www.youtube.com/watch?v=APAmsHAYUiI

So replication it is one way relationship: Master to Slave.

Mysql:

1. Stop Slave: stop slave;
2. Tell the Slave that it is a Slave and where to locate the Master:

Mysql:

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.111', 
MASTER_USER = 'master_replication_user', 
MASTER_PASSWORD =  'master_password', 
MASTER_LOG_FILE = 'DESKTOP-MASTER-PC-bin.000978', 
MASTER_LOG_POS = 678; 

3. Start Slave: start slave;
4. Check Slave Status: show slave status;

---

---

MySQL Mirroring: https://www.ryadel.com/en/mysql-master-master-replication-setup-in-5-easy-steps/

The main point here is: There are two Masters (so they like a slave to each other, but they will never admit it). You just tell Master A that it is slave of the Master B. And then you tell Master B that he is the slave of Master A. It is reciprocal relationship.

1. Stop Master A: stop slave;
2. Stop Master B: stop slave;

3.Tell the Master A where to locate the Master B

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.111', 
MASTER_USER = 'master_b_replication_user', 
MASTER_PASSWORD =  'master_b_password', 
MASTER_LOG_FILE = 'DESKTOP-MASTER-B-PC-bin.000756', 
MASTER_LOG_POS = 888; 

4.Tell the Second Master where to locate the First Master

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.112', 
MASTER_USER = 'master_a_replication_user', 
MASTER_PASSWORD =  'master_a_password', 
MASTER_LOG_FILE = 'DESKTOP-MASTER-A-PC-bin.000001', 
MASTER_LOG_POS = 777; 

Mysql:

5.Master A: start slave;

6.Master B: start slave;

7.Check Master A Status: show slave status;

8.Check Master B Status: show slave status;

Database Replication or Mirroring?

In short, mirroring allows you to have a second server be a "hot" stand-by copy of the main server, ready to take over any moment the main server fails. So mirroring offers fail-over and reliability.

Replication, on the other hand, allows two or more servers to stay "in sync" - that means the secondary servers can answer queries and (depending on setup) actually change data (it will be merged in the sync). You can also use it for local caching, load balancing, etc.

What are the scenarios for using mirroring, log shipping, replication and clustering in SQL Server

Failover clustering is an availability technology that provides redundancy at the hardware level and is built on top of Windows Clustering technology, i.e. it is not specific to SQL Server.

For example, the processor blows up on Server A. Fortunately Server A is part of a SQL Server Cluster and so Server B takes over the job of providing the SQL Server Service, within a matter of seconds. All of this occurs automatically and is transparent to the database users and or application being served by the cluster.

The main difference between Database Mirroring and clustering is that SQL Clustering provides redundancy at the instance level whereas database mirroring provides redundancy at the database level.

The following link provides a comparison between these two technologies that you may find of use.

http://msdn.microsoft.com/en-us/library/ms191309(SQL.90).aspx

Log shipping is considered more of a redundancy technology.

For example, it can be used to provide a full copy of your primary environment, typically used as a warm standby that can be manually brought online. This can be used to provide additional redundancy to your backup strategy.
Log shipping can also be used to offload reporting from a primary server by creating a read only copy of the production database at an alternative location/server.

Replication is quite a diverse technology and can be used to cater for a number of different scenarios, the choice of which will determine the specific type of replication that is implemented.

For example, merge replication can be used to support distributed processing by spreading the workload of an application across several servers, i.e. distributed processing architectures.

Merge replication often requires an application that is relatively aware of its environment. Techniques such as conflict resolution also have to be taken into consideration in order to ensure data consistency across the entire integrated environment.

Transactional Replication can be used in a similar fashion to log shipping however you can limit the specific objects that are replicated to the subscriber. This can be useful if only a subset of tables is required for reporting purposes.

I hope this clears things up for you a little. You can find a wealth of documentation regarding each of these technologies within SQL Server books online, or by searching for each technology in Google. That said if you have any specific queries I would be happy to help so feel free to drop me line.

Cheers, John

How to do mirroring or replication in table level in SQL Server with SQL Query

You can use a straightforward trigger for this

CREATE TRIGGER tr_Patients_Tasks1Copy ON dbo.Patients AFTER INSERT
AS

SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM inserted)
    INSERT Task1.dbo.Patients
      (FirstName, Lastname, Address, ContactNo, Gender, DateOfBirth)
    SELECT FirstName, Lastname, Address, ContactNo, Gender, DateOfBirth
    FROM inserted i;

GO

Note that the inserted table may have multiple or even zero rows

What is the difference between Database Mirroring and Database Replication such as Multi A-Z deployment in Amazon RDS?

I think you need both the Multi-AZ and the Read Replica features of AWS RDS.

Multi-AZ just creates a non-accessible secondary DB in another availability zone and in case the primary fails, AWS would switch over to the secondary DB. So you have failover.

In the case you want to increase the performance, and your application can work in read-only mode in Singapore (for example), the Read Replica would be perfect. If writes are also required, you would need to route them to the primary read-write database.

AWS supports a combination of the two approaches.

---

Related Topics