Setting up SQL Server with master-slave replication using Portainer

ylwong

Setting up SQL Server with master-slave replication using Portainer involves multiple steps, as SQL Server doesn’t natively support master-slave replication like MySQL does. Instead, you can achieve a similar setup using SQL Server Always On Availability Groups or Database Mirroring. However, for the sake of simplicity and feasibility in a containerized environment, I'll guide you through setting up a basic SQL Server instance using Docker, managed by Portainer, and then outline how to configure replication-like behavior using SQL Server's backup and restore features.

Prerequisites

Portainer installed and running to manage Docker containers.
Basic knowledge of Docker and SQL Server.
Two separate nodes (containers) to simulate master and slave.

Step 1: Deploy SQL Server Containers using Portainer

Log in to Portainer and go to the Containers section.
Deploy the Master SQL Server Container:
- Click on the Add Container button.
- Set the Container Name to something like mssql-master.
- In the Image field, use mcr.microsoft.com/mssql/server:2019-latest for the SQL Server image.
- Set the command to initialize SQL Server:
```
     /opt/mssql/bin/sqlservr
```
- Configure environment variables:
  - ACCEPT_EULA=Y
  - SA_PASSWORD=YourStrong@Passw0rd
- Set Ports to map 1433 on the container to 1433 on the host (or a different host port if needed).
Deploy the Slave SQL Server Container:
- Repeat the above steps with a different container name, e.g., mssql-slave.
Start both containers and ensure they are running correctly.

Step 2: Configure SQL Server for Replication-like Setup

Connect to Master SQL Server:

Use any SQL Server client or tools like sqlcmd or Azure Data Studio to connect to the master instance.

Run the following commands to create a test database:

     CREATE DATABASE TestDB;
     USE TestDB;
     CREATE TABLE TestTable (ID INT PRIMARY KEY, Value NVARCHAR(50));
     INSERT INTO TestTable VALUES (1, 'Master record');

Backup the Database on Master:

   BACKUP DATABASE TestDB TO DISK = '/var/opt/mssql/backup/TestDB.bak' WITH FORMAT;

Copy Backup to Slave:
- Use Docker commands to copy the backup file from the master container to your local machine, and then from the local machine to the slave container, or use a shared volume between the containers.

Restore Database on Slave:

Connect to the slave instance and restore the database:

     RESTORE DATABASE TestDB FROM DISK = '/var/opt/mssql/backup/TestDB.bak' WITH MOVE 'TestDB' TO '/var/opt/mssql/data/TestDB.mdf', MOVE 'TestDB_log' TO '/var/opt/mssql/data/TestDB_log.ldf';

Step 3: Automating the Replication (Optional)

For more automated "replication," you could set up a scheduled job in SQL Server Agent on the master to regularly back up the transaction log and a corresponding job on the slave to restore it. This is not true replication but can achieve similar outcomes depending on your requirements.

Step 4: Monitoring and Management with Portainer

Use Portainer to monitor the health of your containers and logs.
You can scale the system by adding more SQL Server containers and potentially setting up Always On Availability Groups for high availability if required.

This setup provides a basic master-slave replication-like configuration using backup and restore methods with SQL Server containers in Portainer.

For true replication or high availability, consider using SQL Server's built-in features like Always On Availability Groups, but this requires a more complex setup beyond simple Docker containers.

Follow GPT creator profile on LinkedIn for the latest updates and insights https://linkedin.com/in/khanukov/, and AskYourDatabase.com enables you to directly chat with your databases.

yldnsroot

Testing replied

sqlserver

@ylwong