Master-Slave Databases

When data grows in your application, the performance deteriorates and that's true. You can't stop data from developing and over some time, it becomes difficult to query the database efficiently. Think about what will happen when you have one huge database that starts failing. So replication is a common technique to address the problem of no support for failover and redundancy.

Replication uses copies of the database systems in the form of a master/slave relationship. Generally by design master database only supports write operations and the slave only supports read operations. Application complexity may require different numbers of database replicas but still works on the same principle. The number of slaves is usually larger than the master database as the read operation is generally higher than the write operation.

When the application is required to write (means insert/update/delete operations) any data to the database, it always targets to master database. Once data finishes writing, it shares the copy of changes to the slave databases and there are many ways to maintain the sync for maintaining high availability. Slave databases are carefully chosen to serve on a request and load balancers will be helpful to achieve that intention.

What will happen when Master fails? In this case, one of the Salve will be promoted to master till we get a replacement. Now let's assume other way now when the slave database fails, the request will route to another slave in case of multiple slaves, or in case of only one slave, the master acts as a slave till gets the replacement of the slave again. It may, be a little complicated architecture to perform these shuffling but I think it is good enough to give you at least an idea about master-slave and how it works.

Let's see how this can be achieved in real life by seeing an example. Setting up a master-slave (or primary-secondary) replication setup in SQL Server along with a C# application involves several steps.

Step 1: Configure SQL Server for Replication

1. Install SQL Server on Master and Slave Machines

Install SQL Server on both the master and the slave machines. You can use SQL Server Express, Developer, or any other edition that supports replication.

2. Configure the Master Server

A. Enable SQL Server Agent:

? Open SQL Server Configuration Manager.

? Ensure that the SQL Server Agent service is running.

B. Configure Distribution:

? Open SQL Server Management Studio (SSMS).

? Connect to the master server.

? Right-click on the Replication folder and select "Configure Distribution".

? Follow the wizard to configure the distribution database.

C. Create a Publication:

? Right-click on the "Local Publications" folder and select "New Publication".

? Follow the wizard to create a new publication, selecting the database and tables you want to replicate.

3. Configure the Slave Server

A. Enable SQL Server Agent: Ensure that the SQL Server Agent service is running on the slave server.

B. Create a Subscription:

? Connect to the slave server using SSMS.

? Right-click on the "Local Subscriptions" folder and select "New Subscription".

? Follow the wizard to subscribe to the publication created on the master server.

Step 2: Implement the C# Application

A. Install Required NuGet Packages

Make sure you have the necessary NuGet packages installed in your project:

? System.Data.SqlClient

dotnet add package System.Data.SqlClient        

B. Implement Database Connection and Failover Logic

Here's an example of how to set up a C# application to connect to the master database and failover to the slave database if the master is unavailable.

using System;
using System.Data.SqlClient;

namespace MasterSlaveReplication
{
    class Program
    {
        static string masterConnectionString = "Server=MASTER_SERVER_NAME;Database=DATABASE_NAME;User Id=USERNAME;Password=PASSWORD;";
        static string slaveConnectionString = "Server=SLAVE_SERVER_NAME;Database=DATABASE_NAME;User Id=USERNAME;Password=PASSWORD;";

        static void Main(string[] args)
        {
            try
            {
                // Attempt to connect to the master server
                using (var connection = new SqlConnection(masterConnectionString))
                {
                    connection.Open();
                    Console.WriteLine("Connected to master database.");
                    // Perform database operations
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Failed to connect to master database. Attempting to connect to slave database.");
                // Attempt to connect to the slave server
                try
                {
                    using (var connection = new SqlConnection(slaveConnectionString))
                    {
                        connection.Open();
                        Console.WriteLine("Connected to slave database.");
                        // Perform read-only database operations
                    }
                }
                catch (SqlException exSlave)
                {
                    Console.WriteLine("Failed to connect to slave database. Error: " + exSlave.Message);
                }
            }
        }
    }
}        

Step 3: Testing the Setup

A. Insert Test Data: Insert data into the master database and verify that it is replicated to the slave database.

B. Simulate Failover: Turn off the master server and ensure that the application connects to the slave server.

C. Check Data Consistency: Ensure that the data on the slave database is consistent with the master database.

Conclusion

Setting up master-slave replication in SQL Server and implementing failover logic in a C# application involves configuring replication on the SQL Server instances and writing code to handle failover scenarios. This setup helps ensure high availability and reliability of your database system.