Database Synchronization

🔄 Common Approaches

Synchronizing or communicating data between two different databases can be approached in several ways, depending on your requirements for real-time sync, consistency, performance, and technology stack. Here are the main strategies:

1. Database Replication

• What it is: One database automatically copies changes to another.
• Types:
  • Master–Slave replication: One primary database pushes updates to secondary replicas.
  • Master–Master replication: Both databases can accept writes and sync with each other.
• Use case: High availability, disaster recovery, or scaling reads.

2. ETL (Extract, Transform, Load) Pipelines

• What it is: Data is periodically extracted from one DB, transformed, and loaded into another.
• Tools: SQL Server Integration Services (SSIS), Apache NiFi, Talend, Informatica.
• Use case: Data warehousing, analytics, scheduled synchronization.

3. Change Data Capture (CDC)

• What it is: Tracks changes (inserts, updates, deletes) in one database and streams them to another.
• Tools: Debezium, SQL Server CDC, Oracle GoldenGate.
• Use case: Near real-time synchronization across heterogeneous databases.

4. Message Queues / Event-Driven Sync

• What it is: Applications publish changes as events to a message broker (Kafka, RabbitMQ, Azure Service Bus), and consumers update the other database.
• Use case: Microservices, loosely coupled systems, scalable real-time sync.

5. API-Based Synchronization

• What it is: Applications expose APIs to push/pull data between databases.
• Use case: When databases are in different environments (e.g., on-prem SQL Server ↔ cloud MongoDB).

6. Linked Servers / Federated Queries

• What it is: Some databases allow querying another database directly (e.g., SQL Server Linked Server, MySQL Federated).
• Use case: When you need cross-database queries without full synchronization.

⚖️ Choosing the Right Method

Requirement	Best Approach
Real-time sync	CDC or Message Queue
Batch sync (nightly, hourly)	ETL
Cross-database queries only	Linked Servers
High availability / failover	Replication
Different DB technologies (SQL ↔ NoSQL)	API-based or CDC with connectors

💡 Tip: If you’re working in .NET C#, you can use Entity Framework Core with multiple database providers, or libraries like Dapper combined with a message queue (Kafka, RabbitMQ) to orchestrate synchronization logic.

🛠️ Step-by-Step Implementation using SQL Server and Kafka

1. Enable CDC on SQL Server

CDC tracks inserts, updates, and deletes.

sqlsvg

-- Enable CDC at database level
EXEC sys.sp_cdc_enable_db;

-- Enable CDC on a specific table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Customers',
    @role_name     = NULL;

This creates CDC system tables that log changes.

2. Set Up Kafka Connect with Debezium

Debezium is a connector that streams CDC changes into Kafka topics.

• Configure a Debezium SQL Server connector.
• Each table change (insert/update/delete) is published as an event in Kafka.

3. Consume Kafka Events in C#

Use a Kafka client library (e.g., Confluent.Kafka) in your .NET app.

using Confluent.Kafka;
using MySql.Data.MySqlClient;

class Program
{
    static void Main()
    {
        var config = new ConsumerConfig
        {
            BootstrapServers = "localhost:9092",
            GroupId = "sync-group",
            AutoOffsetReset = AutoOffsetReset.Earliest
        };

        using var consumer = new ConsumerBuilder<Ignore, string>(config).Build();
        consumer.Subscribe("dbo.Customers"); // Kafka topic created by Debezium

        while (true)
        {
            var cr = consumer.Consume();
            var changeEvent = cr.Message.Value;

            // Parse JSON event (Debezium format)
            dynamic evt = Newtonsoft.Json.JsonConvert.DeserializeObject(changeEvent);

            string op = evt.op; // c = create, u = update, d = delete
            dynamic data = evt.after;

            using var conn = new MySqlConnection("server=localhost;user=root;database=test;");
            conn.Open();

            if (op == "c") // Insert
            {
                var cmd = new MySqlCommand("INSERT INTO Customers (Id, Name) VALUES (@Id, @Name)", conn);
                cmd.Parameters.AddWithValue("@Id", data.Id);
                cmd.Parameters.AddWithValue("@Name", data.Name);
                cmd.ExecuteNonQuery();
            }
            else if (op == "u") // Update
            {
                var cmd = new MySqlCommand("UPDATE Customers SET Name=@Name WHERE Id=@Id", conn);
                cmd.Parameters.AddWithValue("@Id", data.Id);
                cmd.Parameters.AddWithValue("@Name", data.Name);
                cmd.ExecuteNonQuery();
            }
            else if (op == "d") // Delete
            {
                var cmd = new MySqlCommand("DELETE FROM Customers WHERE Id=@Id", conn);
                cmd.Parameters.AddWithValue("@Id", evt.before.Id);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

4. Result

• Any change in SQL Server’s Customers table is captured by CDC.
• Debezium streams it into Kafka.
• Your C# consumer listens and applies changes to MySQL.

⚖️ Why This Works Well

• Real-time sync without polling.
• Scalable — multiple consumers can process events.
• Cross-platform — works across SQL Server, MySQL, PostgreSQL, MongoDB, etc.
• Resilient — Kafka ensures durability and replay.

🛠️ Step-by-Step ETL Synchronization in C#

1. Extract Data from Source (SQL Server)

Use SqlConnection and SqlCommand to pull data.

using System.Data.SqlClient;

string sqlConnStr = "Server=localhost;Database=SourceDB;User Id=sa;Password=yourpassword;";
using var sqlConn = new SqlConnection(sqlConnStr);
sqlConn.Open();

string query = "SELECT Id, Name, Email FROM Customers WHERE LastUpdated > @LastSync";
using var cmd = new SqlCommand(query, sqlConn);
cmd.Parameters.AddWithValue("@LastSync", lastSyncTime);

using var reader = cmd.ExecuteReader();

2. Transform Data (Optional)

Apply business rules, mappings, or format conversions in C# before loading.

var customers = new List<Customer>();
while (reader.Read())
{
    customers.Add(new Customer
    {
        Id = reader.GetInt32(0),
        Name = reader.GetString(1).Trim(),
        Email = reader.GetString(2).ToLower()
    });
}

3. Load Data into Target (MySQL)

Insert or update into MySQL using MySqlConnection.

using MySql.Data.MySqlClient;

string mysqlConnStr = "Server=localhost;Database=TargetDB;User=root;Password=yourpassword;";
using var mysqlConn = new MySqlConnection(mysqlConnStr);
mysqlConn.Open();

foreach (var cust in customers)
{
    var cmd = new MySqlCommand(
        "INSERT INTO Customers (Id, Name, Email) VALUES (@Id, @Name, @Email) " +
        "ON DUPLICATE KEY UPDATE Name=@Name, Email=@Email", mysqlConn);

    cmd.Parameters.AddWithValue("@Id", cust.Id);
    cmd.Parameters.AddWithValue("@Name", cust.Name);
    cmd.Parameters.AddWithValue("@Email", cust.Email);
    cmd.ExecuteNonQuery();
}

4. Schedule the Job

• Use Windows Task Scheduler or Quartz.NET to run the sync job hourly/daily.
• Store the last sync timestamp in a config table or file to avoid reprocessing old records.

⚖️ When to Use ETL vs CDC

• ETL (batch jobs) → Best for nightly or hourly sync, simpler setup, fewer moving parts.
• CDC/Kafka (real-time) → Best when you need instant updates across systems.

✨ With this approach, you can keep two databases in sync without heavy infrastructure. It’s especially useful for reporting, backups, or hybrid systems where one DB is transactional and the other is analytical.