Microsoft SQL Server High Availability with Synchronous Replication and Failover

Evidian SafeKit brings high availability to Microsoft SQL Server. This article explains how to implement quickly a Microsoft SQL Server cluster without shared disk and without specific skills. A free trial is offered.

How the Evidian SafeKit software simply implements Microsoft SQL Server high availability with real-time synchronous replication and failover without shared disk

How the Evidian SafeKit mirror cluster implements Microsoft SQL Server high availability with synchronous replication and failover?

On the previous figure, the server 1/PRIM (Windows) runs Microsoft SQL Server (any edition, even the free Microsoft SQL Server Express edition). Users are connected to the virtual IP address of the mirror cluster. SafeKit replicates files opened by Microsoft SQL Server in real time. Only changes in the files are replicated across the network, thus limiting traffic (byte-level file replication). Names of file directories containing Microsoft SQL Server database are simply configured in SafeKit. There are no pre-requisites on disk organization for the two servers. Directories to replicate may be located in the system disk. SafeKit implements synchronous replication with no data loss on failure contrary to asynchronous replication.

In case of server 1 failure, there is an automatic failover on server 2 with restart of Microsoft SQL Server. Then, when server 1 is restarted, SafeKit implements failback with reintegration of Microsoft SQL Server database without stopping Microsoft SQL Server on server 2. Finally, the system returns to synchronous replication between server 2 and server 1. The administrator can decide to swap the role of primary and secondary and return to a server 1 running Microsoft SQL Server. The swap can also be done automatically by configuration.

Configuration overview of Microsoft SQL Server high availability with synchronous replication and failover

With SafeKit, you can configure either a farm application module or a mirror application module according the high availability architecture suited for an application. For Microsoft SQL Server high availability with synchronous replication and failover, the right module is the mirror module.

Configuration overview of Microsoft SQL Server high availability with synchronous replication and failover

The configuration files for Microsoft SQL Server high availability are given  for Windows here.

They include:

1. the Microsoft SQL Server stop and start scripts,

2. the configuration file userconfig.xml which contains:

Deployment of Microsoft SQL Server high availability with synchronous replication and failover requires no specific IT skills:

    • install Microsoft SQL Server on two standard Windows servers
    • install the SafeKit software on both servers
    • install the sqlserver.safe module

SafeKit configuration files on Windows and for Microsoft SQL Server high availability with synchronous replication and failover

Installation instructions

userconfig.xml

<!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">
	<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 end

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

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

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

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

:end

Demonstration

This demonstration is made with Microsoft SQL Server Express.

More on SafeKit

Other examples of high availability modules:

Mirror modules

Windows

Linux

Microsoft SQL ServerWindows module-
OracleWindows moduleLinux module
MySQLWindows moduleLinux module
PostgreSQLWindows moduleLinux module
FirebirdWindows moduleLinux module
Hyper-VWindows module-
Hanwha SSMWindows module-
Milestone XProtectWindows module-
Generic moduleWindows moduleLinux module

Farm modules

Windows

Linux

IIS moduleWindows module-
Apache moduleWindows moduleLinux module
Generic moduleWindows moduleLinux module
contact
CONTACT
Demonstration

Evidian SafeKit Pricing





White Papers

contact
NEWS

To receive Evidian news, please fill the following form.