Microsoft SQL Server 2012 cluster with replication and failover

How Evidian SafeKit implements simply a Microsoft SQL Server 2012 cluster with replication and failover?

The goal of this article is to explain how to implement a Microsoft SQL Server 2012 cluster in two VMs. After the implementation, you will be able to run the following video with real-time database replication and automatic failover.
For more information on the architecture, see File replication at byte level with failover: the mirror cluster.

Minimum hardware

The minimum hardware is a laptop  with VMware Player (http://www.vmware.com/products/player) and with a least the following configuration:

2 Windows 2012 R2 VMs must be created by running the following procedure. Note that you can create the second VM either by re-executing the procedure with Windows installation and SQL Server installation, or by cloning the first VM and changing hostname and IP address.

Windows 2012 R2 180 days evaluation

Note: You can also install Windows 8.1 x64 90 days.

Do this procedure with Internet connected for installing updates.

In VMware Player, create a VM

Set autologin for demonstration purpose

Set hostname to server1

Start File manager from the task bar and create a new folder named c:\packages

Install VMware tools through VMware player / Manage menu

Restart the system

Install Sysinternals Suite for setting hostname and IP address in the background of the Windows 2012 R2 desktop

Microsoft® SQL Server® 2012 Service Pack 1 (SP1) Express

This is the free version Microsoft SQL Server 2012.

Go to http://www.microsoft.com/en-us/download/details.aspx?id=35579

Download SQLEXPRWT_x64_ENU.exe (1 GB): it includes SQL Server 2012 Management Studio Express required for the test.

Copy SQLEXPRWT_x64_ENU.exe from host to guest in C:\packages

Install the package in the guest

Click on the Windows start logo to get Windows tiles, click on the arrow to get all installed Apps and search in Apps "SQL Server Management Studio", right click and Pin to taskbar for ease in the demonstration. Return to desktop.

Download AdventureWorks2012 Data File at http://msftdbprodsamples.codeplex.com/releases/view/55330

Remove automatic start of SQL service at boot if you have set it during SQL installation (the start will be managed by SafeKit)

Install and configure SafeKit

At this step, 2 VMs SERVER1 and SERVER2 are supposed to be created according the previous procedures.

Download and install SafeKit (at least 7.1.3.7) in both VMs: http://www.evidian.com/products/high-availability-software-for-application-clustering/cluster-test-free-download/

Add firewall rules in the Microsoft firewall ruleset to allow the SafeKit processes to communicate with their peers in the cluster. Start a command line as Administrator :

At this step, you are ready to run the previous video and you are able to demonstrate a Microsoft SQL Server 2012 cluster with replication and failover.

Note: To enhance the demonstration, behind net stop "SQL Server (SQLEXPRESS)" in the video you can add %SAFE%\safekit kill -name="ssms.exe" -level="terminate". The 2nd line kills automatically SQL Server Management Studio if the service is no more running on the server.

All tutorials on the SafeKit high availability and load balancing software are available here.

Annex - video scenario [+]

Start SafeKit Web Console and in Configuration tab,

  • enter IP addresses of the cluster (Tab key for auto completion)
  • install and configure a new module
  • double click on mirror.safe
  • name of the module : sqlexpress instead of mirror
  • for real-time replication set
    C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA
  • no virtual IP address because nobody connects remotely in the demonstration
  • click on start_prim and put behind "Rem Fill with your services start call"
    net start "SQL Server (SQLEXPRESS)"
    Save and exit the window
  • click on stop_prim and set behind "set res=0"
    net stop "SQL Server (SQLEXPRESS)"
    %SAFE%\safekit kill -name="ssms.exe" -level="terminate"
    Note: The 2nd special line just kills automatically SQL Server Management Studio if the service is no more running on the server.
    Save and exit the window
  • click on Validate
  • click on Configure
  • check success and click on Next
  • choose the server with the up-to-date database and click on Start
  • close and check that the server goes to ALONE state
  • start the SECOND server and check that the state of sqlexpress becomes PRIM-SECOND

Start SQL Server Management Studio on the PRIM server from the taskbar

  • authentication : SQL Server Authentication
    login : sa
    password : safekit1234AB
  • +Databases +AdventureWorks2012 +Tables, Right click on Person.Person / Edit Top 200 Rows
  • change First Name/Last Name of row 1 and 2 with Albert Einstein and William Shakespeare
    Note: You must exit the field FirstName or Lastname to validate what you have typed else the data are not committed in the database
  • in the SafeKit web console, click on PRIM/Swap
  • SQL Server Management Studio should be killed on PRIM if you have added the special line in stop_prim
  • the PRIM/SECOND role should be inversed
  • goto the new PRIM server
  • start SQL Server Management Studio from the taskbar
  • authentication : SQL Server Authentication
    login : sa
    password : safekit1234AB
  • +Databases +AdventureWorks2012 +Tables, Right click on Person.Person / Edit Top 200 Rows
  • check that row 1 an 2 contain Albert Einstein and William Shakespeare

In the SafeKit web console (previously started), goto Advanced Configuration tab

  • on the left, open sqlexpress, conf/userconfig.xml
  • after </rfs>, add the following section for monitoring the sqlservr.exe process (database engine process)
    <errd polltimer="5">
    <proc name="sqlservr.exe" atleast="1" action="stopstart" class="prim"/>
    </errd>
    The action stopstart means that there is a failover on the other server if sqlservr.exe disappears
    Other actions are possible: see the User's Guide http://www.evidian.com/safekit/docs/cluster-guide.htm#_Software_Error_Detection
  • save userconfig.xml and exit the window
  • right click on sqlexpress module / Apply the configuration / Configure ; check success on both servers
  • close and restart globally the module by clicking on sqlexpress module / Start

For demonstration purpose, in userconfig.xml you can also

On PRIM server,

  • right click on Windows logo / Task Manager
  • kill sqlservr process
  • check the failover on the other server in the web console

Go to the other server to check the automatic restart

  • start SQL Server Management Studio from the taskbar
  • authentication : SQL Server Authentication
    login : sa
    password : safekit1234AB
  • +Databases +AdventureWorks2012 +Tables, Right click on Person.Person / Edit Top 200 Rows
  • check that row 1 an 2 contain Albert Einstein and William Shakespeare

Reboot PRIM server:

  • in SQL Server Management Studio
  • change FirstName and LastName of row 1 and 2 with Albert1 Einstein1 and William1 Shakespeare1
  • in the SafeKit web console, go to Advanced configuration
  • click on sqlexpress / PRIM and in the menu choose Admin / Configure boot start ; check enabled to have an automatic restart of sqlexpress after the reboot
  • Windows start logo / Right click / Shutdown or sign out / Restart
  • After the reboot and login, start the SafeKit web console and check that the sqlexpress returns automatically to a SECOND state on the rebooted server

Go to the other server

  • start SQL Server Management Studio from the taskbar
  • authentication : SQL Server Authentication
    login : sa
    password : safekit1234AB
  • +Databases +AdventureWorks2012 +Tables, Right click on Person.Person / Edit Top 200 Rows
  • check that row 1 an 2 contain Albert1 Einstein1 and William1 Shakespeare1

 


Demonstration




White Papers


contact
CONTACT


contact
NEWS

To receive Evidian news, please fill the following form.