Microsoft SQL Server: the simplest high availability cluster between two redundant servers

Step 1. Real-time replication

Server 1 (PRIM) runs the Microsoft SQL Server application. Clients are connected to a virtual IP address. SafeKit replicates in real time modifications made inside files through the network.

The replication is synchronous with no data loss on failure contrary to asynchronous replication.

You just have to configure the names of directories to replicate in SafeKit. There are no pre-requisites on disk organization. Directories may be located in the system disk.

Step 2. Automatic failover

When Server 1 fails, Server 2 takes over. SafeKit switches the virtual IP address and restarts the Microsoft SQL Server application automatically on Server 2.

The application finds the files replicated by SafeKit uptodate on Server 2. The application continues to run on Server 2 by locally modifying its files that are no longer replicated to Server 1.

The failover time is equal to the fault-detection time (30 seconds by default) plus the application start-up time.

Redundancy at the application level

In this type of solution, only application data are replicated. And only the application is restared in case of failure.

With this solution, restart scripts must be written to restart the application.

We deliver application modules to implement redundancy at the application level (like the Microsoft SQL Server module provided in the free trial below). They are preconfigured for well known applications and databases. You can customize them with your own services, data to replicate, application checkers. And you can combine application modules to build advanced multi-level architectures.

This solution is platform agnostic and works with applications inside physical machines, virtual machines, in the Cloud. Any hypervisor is supported (VMware, Hyper-V...).

Solution for a new application (restart scripts to write): Windows, Linux

Redundancy at the virtual machine level

In this type of solution, the full Virtual Machine (VM) is replicated (Application + OS). And the full VM is restarted in case of failure.

The advantage is that there is no restart scripts to write per application and no virtual IP address to define. If you do not know how the application works, this is the best solution.

This solution works with Windows/Hyper-V and Linux/KVM but not with VMware. This is an active/active solution with several virtual machines replicated and restarted between two nodes.

Solution for a new application (no restart script to write): Windows/Hyper-V, Linux/KVM

SafeKit quick installation guide with Microsoft SQL Server

The SQL system databases (like master.mdf and mastlog.ldf) must be located in the same directories on both nodes. The directories will be configured as replicated at step 4 in the step by step configuration.
SQL must be also installed at the same location in the file system on both nodes because the read-only SQL resource database is located in the binary and is required for the failover. This database does not need to be replicated.
The SQL user databases (.mdf and .ldf) must be located in the same directories on both nodes. The directories will be configured as replicated at step 4 in the step by step configuration.

Package installation on Windows

Download the free version of SafeKit on 2 Windows nodes.

Note: the free version includes all SafeKit features. At the end of the trial, you can activate permanent license keys without uninstalling the package.
To open the Windows firewall, on both nodes start a powershell as administrator, and type
```
c:/safekit/private/bin/firewallcfg add
```
To initialize the password for the default admin user of the web console, on both nodes start a powershell as administrator, and type
```
c:/safekit/private/bin/webservercfg.ps1 -passwd pwd
```
1. Use aphanumeric characters for the password (no special characters).
2. pwd must be the same on both nodes.
Exclude from antivirus scans C:\safekit\ (the default installation directory) and all replicated folders that you are going to define.

Antiviruses may face detection challenges with SafeKit due to its close integration with the OS, virtual IP mechanisms, real-time replication and restart of critical services.

Step by step configuration

2. Configure node addresses

Enter the node IP addresses, press the Tab key to check connectivity and fill node names.
Then, click on Save and apply to save the configuration.

If either node1 or node2 has a red color, check connectivity of the browser to both nodes and check firewall on both nodes for troubleshooting.

If you want, you can add a new LAN for a second heartbeat and for a dedicated replication network.

This operation will place the IP addresses in the cluster.xml file on both nodes (more information in the training with the command line).

4. Configure the module

Choose an Automatic start of the module at boot without delay.
Normally, you have a single Heartbeat network on which the replication is made. But, you can define a private network if necessary (by adding a LAN at step 2).
Enter a Virtual IP address. A virtual IP address is a standard IP address in the same IP network (same subnet) as the IP addresses of both nodes.
Application clients must be configured with the virtual IP address (or the DNS name associated with the virtual IP address).
The virtual IP address is automatically switched in the event of a failure.
Check that the Replicated directories are installed on both nodes and contain the application data.
Data and log replication are essential for a database..
You can create additional replicated directories as required.
Note that if a process name is displayed in Monitored processes/services, it will be monitored with a restart action in case of failure. Configuring a wrong process name will cause the module to stop right after its start.

If you click on Advanced configuration, the userconfig.xml file is displayed (example with Microsoft SQL Server).

5. Edit scripts (optional)

start_prim and stop_prim must contain starting and stopping of the Microsoft SQL Server application (example provided for Microsoft SQL Server on the right).
You can add new services in these scripts.
Check that the names of the services started in these scripts are those installed on both nodes, otherwise modify them in the scripts.
On Windows and on both nodes, with the Windows services manager, set Boot Startup Type = Manual for all the services registered in start_prim (SafeKit controls the start of services in start_prim).

9. Start the node with up-to-date data

If node 1 has the up-to-date replicated directories, select it and start it As primary.

When node 2 will be started, all data will be copied from node 1 to node 2.

If you make the wrong choice, you run the risk of synchronizing outdated data on both nodes.

It is also assumed that the Microsoft SQL Server application is stopped on node 1 so that SafeKit installs the replication mechanisms and then starts the application in the start_prim script.

Use Start for subsequent starts: SafeKit retains the most up-to-date server. Starting As primary is a special start-up the first time or during exceptional operations.

10. Wait for the transition to ALONE (green)

Node 1 should reach the ALONE (green) state, which means that the virtual IP is set and that the start_prim script has been executed on node 1.

If ALONE (green) is not reached or if the application is not started, analyze why with the module log of node 1.

click the "log" icon of node1 to open the module log and look for error messages such as a checker detecting an error and stopping the module.
click on start_prim in the log: output messages of the script are displayed on the right and errors can be detected such as a service incorrectly started.

If the cluster is in WAIT (red) not uptodate, STOP (red) not uptodate state, stop the WAIT node and force its start as primary.

13. Testing

Stop the PRIM node by scrolling down its contextual menu and clicking Stop.
Verify that there is a failover on the SECOND node which should become ALONE (green).
And with Microsoft Management Console (MMC) on Windows or with command lines on Linux, check the failover of Microsoft SQL Server services (stopped on node 1 in the stop_prim script and started on node 2 in the start_prim script).

If ALONE (green) is not reached on node2 or if the application is not started, analyze why with the module log of node 2.

click the "log" icon of node2 to open the module log and look for error messages such as a checker detecting an error and stopping the module.
click on start_prim in the log: output messages of the script are displayed on the right and errors can be detected such as a service incorrectly started.

If everything is okay, initiate a start on node1, which will resynchronize the replicated directories from node2.

If things go wrong, stop node2 and force the start as primary of node1, which will restart with its locally healthy data at the time of the stop.

More information on tests in the User's Guide.

15. If necessary, configure a splitbrain checker

See below "What are the different scenarios in case of network isolation in a cluster?" to know if you need to configure a splitbrain checker.
Go to the module configuration and click on Checkers / Splitbrain (see image) to edit the splitbrain parameters.
Save and apply the new configuration to redeploy it on both nodes (module must be stopped on both nodes to save and apply).

Parameters:

Resource name identifies the witness with a resource name: splitbrain.witness. You can change this value to identify the witness.
Witness address is the argument for a ping when a node goes from PRIM to ALONE or from SECOND to ALONE. Change this value with the IP of the witness (a robust element, typically a router).
Note: you can set several IPs separated by white spaces. Pay attention that the IP addresses must be accessible from one node but not from the other in the event of network isolation.

When there is a network isolation, the behavior with a split-brain checker is:

a split-brain checker has been configured with the IP address of a witness (typically a router),
the split-brain checker operates when a server goes from PRIM to ALONE or from SECOND to ALONE,
in case of network isolation, before going to ALONE, both nodes test the IP address,
the node which can access the IP address goes to ALONE, the other one goes to WAIT,
when the isolation is repaired, the WAIT node resynchronizes its data and becomes SECOND.

Note: If the witness is down or disconnected, both nodes go to WAIT and the application is no more running. That's why you must choose a robust witness like a router.

Internals

Internals of a SafeKit / Microsoft SQL Server high availability cluster with synchronous replication and failover

Go to the Advanced Configuration tab in the console, for editing these files

Internal files of the Windows sqlserver.safe module

userconfig.xml (description in the User's Guide)

<!DOCTYPE safe>
<safe>
<service mode="mirror" defaultprim="alone" maxloop="3" loop_interval="24" failover="on">
  <!-- Heartbeat Configuration -->
  <!-- Names or IP addresses on the default network are set during initialization in the console -->
  <heart pulse="700" timeout="30000">
    <heartbeat name="default" ident="flow">
    </heartbeat>
  </heart>
  <!-- Virtual IP Configuration -->
  <!-- Replace
     * VIRTUAL_TO_BE_DEFINED by the name of your virtual server 
  -->
  <vip>
    <interface_list>
        <interface check="on" arpreroute="on"> 
	  <real_interface>
               <virtual_addr addr="VIRTUAL_TO_BE_DEFINED" where="one_side_alias" />
          </real_interface>
        </interface>
    </interface_list>
  </vip>
  <!-- Software Error Detection Configuration -->
  <errd polltimer="10">
    <!-- Microsoft SQL Server process -->
    <proc name="sqlservr.exe" atleast="1" action="restart" class="prim" />
    <proc name="sqlagent.exe" atleast="1" action="restart" class="prim" />
    <proc name="sqlwriter.exe" atleast="1" action="restart" class="prim" />
  </errd>
  <!-- File Replication Configuration -->
  <!-- Replace
     * YOUR_INSTANCE to set the directory of your Microsoft SQL Server database and logs
  -->
  <rfs async="second" acl="off" nbrei="3" roflags="0x10">
	<replicated dir="C:\Program Files\Microsoft SQL Server\YOUR_INSTANCE\MSSQL\DATA" mode="read_only" />
	<replicated dir="C:\Program Files\Microsoft SQL Server\YOUR_INSTANCE\MSSQL\Log" mode="read_only" />
  </rfs>
  <!-- User scripts activation -->
  <user nicestoptimeout="300" forcestoptimeout="300" logging="userlog" />
</service>
</safe>

start_prim.cmd

@echo off
rem Script called on the primary server for starting application services 

rem For logging into SafeKit log use:
rem "%SAFE%\safekit" printi | printe "message"

rem stdout goes into Application log
echo "Running start_prim %*" 

set res=0

net start "MSSQLServer" > nul
if not %errorlevel% == 0 (
  %SAFE%\safekit printi "MSSQLServer start failed"
) else (
  %SAFE%\safekit printi "MSSQLServer started"
)

net start "SQLServerAgent" > nul
if not %errorlevel% == 0 (
  %SAFE%\safekit printi "SQLServerAgent start failed"
) else (
  %SAFE%\safekit printi "SQLServerAgent started"
)

net start "SQLWriter" > nul
if not %errorlevel% == 0 (
  %SAFE%\safekit printi "SQLWriter start failed"
) else (
  %SAFE%\safekit printi "SQLWriter started"
)

if %res% == 0 goto end

:stop
set res=%errorlevel%
"%SAFE%\safekit" printe "start_prim failed"

rem uncomment to stop SafeKit when critical
rem "%SAFE%\safekit" stop -i "start_prim"

:end

stop_prim.cmd

@echo off
rem Script called on the primary server for stopping application services 

rem ----------------------------------------------------------
rem
rem 2 stop modes:
rem
rem - graceful stop
rem   call standard application stop with net stop
rem
rem - force stop (%1=force)
rem   kill application's processes
rem
rem ----------------------------------------------------------

rem For logging into SafeKit log use:
rem "%SAFE%\safekit" printi | printe "message"

rem stdout goes into Application log
echo "Running stop_prim %*" 

set res=0

rem default: no action on forcestop
if "%1" == "force" goto check

rem %SAFE%\safekit printi "Stopping SQLWriter..."
rem net stop "SQLWriter" /Y > nul

rem %SAFE%\safekit printi "Stopping SQLServerAgent..."
rem net stop "SQLServerAgent" /Y > nul

%SAFE%\safekit printi "Stopping MSSQLServer..."
net stop "MSSQLServer" /Y > nul

rem wait a little for a real stop of services
%SAFEBIN%sleep 10
goto end

:check

rem call :checksrv "SQLWRITER"
rem call :checksrv "SQLServerAgent"
call :checksrv "MSSQLServer"

goto end

:checksrv
 set SERVICE=%1
 net stop %SERVICE% /Y  
 if NOT ERRORLEVEL 1 goto end
 sc query state= inactive | findstr %SERVICE%
 if NOT ERRORLEVEL 1 goto end
 "%SAFE%\safekit" stop -i "stop_prim : service not stopped" 
 exit


:end

Advanced clustering architectures

Several modules can be deployed on the same cluster. Thus, advanced clustering architectures can be implemented:

the farm+mirror cluster built by deploying a farm module and a mirror module on the same cluster,
the active/active cluster with replication built by deploying several mirror modules on 2 servers,
the Hyper-V cluster or KVM cluster with real-time replication and failover of full virtual machines between 2 active hypervisors,
the N-1 cluster built by deploying N mirror modules on N+1 servers.

Key differentiators of a mirror cluster with replication and failover

Evidian SafeKit mirror cluster with real-time file replication and failover
3 products in 1 More info >	The SafeKit high availability software saves on Windows and Linux the cost of : external shared or replicated storage, load balancing boxes, enterprise editions of OS and databases SafeKit includes all clustering features: synchronous real-time file replication, monitoring of server / network / software failures, automatic application restart, virtual IP address switched in case of failure to reroute clients
Very simple configuration More info >	The cluster configuration is very simple and made by means of application modules. New services and new replicated directories can be added to an existing application module to complete a high availability solution All the configuration of clusters is made using a simple centralized web administration console There is no domain controller or active directory to configure as with Microsoft cluster
Synchronous replication More info >	The real-time replication is synchronous with no data loss on failure This is not the case with asynchronous replication
Fully automated failback More info >	After a failure when a server reboots, the replication failback procedure is fully automatic and the failed server reintegrates the cluster without stopping the application on the only remaining server This is not the case with most replication solutions particularly with replication at the database level. Manual operations are required for resynchronizing a failed server. The application may even be stopped on the only remaining server during the resynchonization of the failed server
Replication of any type of data More info >	The replication is working for databases but also for any files which shall be replicated This not the case for replication at the database level
File replication vs disk replication More info >	The replication is based on file directories that can be located anywhere (even in the system disk) This is not the case with disk replication where special application configuration must be made to put the application data in a special disk
File replication vs shared disk More info >	The servers can be put in two remote sites This is not the case with shared disk solutions
Remote sites and virtual IP address More info >	All SafeKit clustering features are working for 2 servers in remote sites. Replication requires an extended LAN type network (latency = performance of synchronous replication, bandwidth = performance of resynchronization after failure). If both servers are connected to the same IP network through an extended LAN between two remote sites, the virtual IP address of SafeKit is working with rerouting at level 2 If both servers are connected to two different IP networks between two remote sites, the virtual IP address can be configured at the level of a load balancer with the "healh check" of SafeKit.
Quorum and split brain More info >	The solution works with only 2 servers and for the quorum (network isolation between both sites), a simple split brain checker to a router is offered to support a single execution of the critical application This is not the case for most clustering solutions where a 3^rd server is required for the quorum
Active/active cluster More info >	The secondary server is not dedicated to the restart of the primary server. The cluster can be active-active by running 2 different mirror modules This is not the case with a fault-tolerant system where the secondary is dedicated to the execution of the same application synchronized at the instruction level
Uniform high availability solution More info >	SafeKit implements a mirror cluster with replication and failover. But it imlements also a farm cluster with load balancing and failover. Thus a N-tiers architecture can be made highly available and load balanced with the same solution on Windows and Linux (same installation, configuration, administration with the SafeKit console or with the command line interface). This is unique on the market This is not the case with an architecture mixing different technologies for load balancing, replication and failover
RTO / RPO More info >	SafeKit implements quick application restart in case of failure: around 1 mn or less Quick application restart is not ensured with full virtual machines replication. In case of hypervisor failure, a full VM must be rebooted on a new hypervisor with a recovery time depending on the OS reboot as with VMware HA or Hyper-V cluster

Key differentiators of a farm cluster with load balancing and failover

Evidian SafeKit farm cluster with load balancing and failover
No load balancer or dedicated proxy servers or special multicast Ethernet address More info >	The solution does not require load balancers or dedicated proxy servers above the farm for imlementing load balancing. SafeKit is installed directly on the application servers in the farm. The load balancing is based on a standard virtual IP address/Ethernet MAC address and is working with physical servers or virtual machines on Windows and Linux without special network configuration This is not the case with network load balancers This is not the case with dedicated proxies on Linux This is not the case with a specific multicast Ethernet address on Windows
All clustering features More info >	The solution includes all clustering features: virtual IP address, load balancing on client IP address or on sessions, monitoring of server / network / software failures, automatic application restart with a quick revovery time and a replication option with a mirror module This is not the case with other load balancing solutions. They are able to make load balancing but they do not include a full clustering solution with restart scripts and automatic application restart in case of failure. They do not offer a replication option The cluster configuration is very simple and made by means of application modules. There is no domain controller or active directory to configure on Windows. The solution works on Windows and Linux
Remote sites and virtual IP address More info >	If servers are connected to the same IP network through an extended LAN between remote sites, the virtual IP address of SafeKit is working with load balancing at level 2 If servers are connected to different IP networks between remote sites, the virtual IP address can be configured at the level of a load balancer with the help of the SafeKit health check. Thus you can implement load balancing but also all the clustering features of SafeKit, in particular monitoring and automatic recovery of the critical application on application servers
Uniform high availability solution More info >	SafeKit imlements a farm cluster with load balancing and failover. But it implements also a mirror cluster with replication and failover. Thus a N-tiers architecture can be made highly available and load balanced with the same solution on Windows and Linux (same installation, configuration, administration with the SafeKit console or with the command line interface). This is unique on the market This is not the case with an architecture mixing different technologies for load balancing, replication and failover

Key differentiators of the SafeKit high availability technology

Software clustering vs hardware clustering More info >
A simple software cluster with the SafeKit package just installed on two servers	Complex hardware clustering with external storage or network load balancers
Shared nothing vs a shared disk cluster More info >
SafeKit is a shared-nothing cluster: easy to deploy even in remote sites	A shared disk cluster is complex to deploy
Application High Availability vs Full Virtual Machine High Availability More info >
Application HA supports hardware failure and software failure with a quick recovery time (RTO around 1 mn or less). Application HA requires to define restart scripts per application and folders to replicate (SafeKit application modules).	Full virtual machines HA supports only hardware failure with a VM reboot and a recovery time depending on the OS reboot. No restart scripts to define with full virtual machines HA (SafeKit hyperv.safe or kvm.safe modules). Hypervisors are active/active with just multiple virtual machines.
High availability vs fault tolerance More info >
No dedicated server with SafeKit. Each server can be the failover server of the other one. Software failure with restart in another OS environment. Smooth upgrade of application and OS possible server by server (version N and N+1 can coexist)	Secondary server dedicated to the execution of the same application synchronized at the instruction level. Software exception on both servers at the same time. Smooth upgrade not possible
Synchronous replication vs asynchronous replication More info >
SafeKit implements real-time synchronous replication with no data loss in case of failure	With asynchronous replication, there is data loss on failure
Byte-level file replication vs block-level disk replication More info >
SafeKit implements real-time byte-level file replication and is simply configured with application directories to replicate even in the system disk	Block-level disk replication is complex to configure and requires to put application data in a special disk
Heartbeat, failover and quorum to avoid 2 master nodes More info >
To avoid 2 masters, SafeKit proposes a simple split brain checker configured on a router	To avoid 2 masters, other clusters require a complex configuration with a third machine, a special quorum disk, a special interconnect
Virtual IP address primary/secondary, network load balancing, failover More info >
No dedicated proxy servers and no special network configuration are required in a SafeKit cluster for virtual IP addresses	Special network configuration is required in other clusters for virtual IP addresses. Note that SafeKit offers a health check adapted to load balancers

Network load balancing and failover
Windows farm	Linux farm
Generic Windows farm >	Generic Linux farm >
Microsoft IIS >	-
NGINX >
Apache >
Amazon AWS farm >
Microsoft Azure farm >
Google GCP farm >
Other cloud >

Real-time file replication and failover
Windows mirror	Linux mirror
Generic Windows mirror >	Generic Linux mirror >
Microsoft SQL Server >	-
Oracle >
MariaDB >
MySQL >
PostgreSQL >
Firebird >
Windows Hyper-V >	Linux KVM >
-	Docker > Podman > Kubernetes K3S >
-	Elasticsearch >
Milestone XProtect >	-
Genetec SQL Server >	-
Hanwha Vision > Hanwha Wisenet >	-
Nedap AEOS >	-
Siemens SIMATIC WinCC > Siemens SIMATIC PCS 7 > Siemens Siveillance suite > Siemens Siveillance VMS > Siemens Desigo CC > Siemens SiPass > Siemens SIPORT >	-
Bosch AMS > Bosch BIS > Bosch BVMS >	-
Amazon AWS mirror >
Microsoft Azure mirror >
Google GCP mirror >
Other cloud >

Microsoft SQL Server: the simplest high availability cluster between two redundant servers

With the synchronous replication and automatic failover provided by Evidian SafeKit

How the Evidian SafeKit software simply implements a Microsoft SQL Server high availability cluster?

The solution for Microsoft SQL Server

A generic product

A complete solution

Partners, the success with SafeKit

How the SafeKit mirror cluster works with Microsoft SQL Server?

Step 1. Real-time replication

Step 2. Automatic failover

Step 3. Automatic failback

Step 4. Back to normal

Choose between redundancy at the application level or at the virtual machine level

Redundancy at the application level

Redundancy at the virtual machine level

Typical usage with SafeKit

Why a replication of a few Tera-bytes?

Why a replication < 1,000,000 files?

Why a failover ≤ 32 replicated VMs?

Why a LAN/VLAN network between remote sites?

SafeKit free trial + mirror module for Microsoft SQL Server + quick installation guide

Prerequisites

Package installation on Windows

Module installation on Windows

1. Launch the SafeKit console

2. Configure node addresses

3. Select a module

4. Configure the module

5. Edit scripts (optional)

6. Communication encryption (optional)

7. Save and apply

8. Verify successful configuration

9. Start the node with up-to-date data

10. Wait for the transition to ALONE (green)

11. Start node 2

12. Verify that the cluster is operational

13. Testing

14. Support

15. If necessary, configure a splitbrain checker

What are the different scenarios in case of network isolation in a cluster?

A single network

Two networks with a dedicated replication network

A single network and a splitbrain checker

Internals of a SafeKit / Microsoft SQL Server high availability cluster with synchronous replication and failover

Internal files of the Windows sqlserver.safe module

SafeKit Modules for Plug&Play Redundancy and High Availability Solutions

Network load balancing and failover

Advanced clustering architectures

Real-time file replication and failover

Evidian SafeKit Webinar

Webinar (pptx)

5 minutes demonstration

SafeKit Customers in all Business Activities

Best use cases [+]

Video management, access control, building management [+]

TV broadcasting [+]

Finance [+]

Industry [+]

Air traffic control [+]

Bank [+]

Transport [+]

Healthcare [+]

Government [+]

SafeKit High Availability Differentiators against Competition

Evidian SafeKit mirror cluster with real-time file replication and failover

Evidian SafeKit farm cluster with load balancing and failover

Evidian SafeKit 8.2

All new features compared to SafeKit 7.5 described in the release notes

Packages

One-month license key

Technical documentation

Training

Product information

Preconfigured solutions with quick installation guides

New application (empty restart scripts)

Web (network load balancing and failover)

Database (real-time replication and failover)

Full VM or container real-time replication and failover

Physical security (real-time replication and failover)

Siemens (real-time replication and failover)