<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=4768124&amp;fmt=gif">
Blog

Tested: StandbyMP v11 for SQL Server

ASPICON already published their first impressions of  Dbvisit Standby Multiplatform v11 and were "excited", now they delve a little deeper, specifically into Standby for SQL Server.

Aspicon_Standbild
Disaster Recovery
Technical Piece
Review
StandbyMP V11
SQL Server
By Product Specialist |
May 3, 2022 |
Link Clicked!

ASPICON, are a group of IT specialists who work with their customers to design tailor-made IT infrastructure for everything to do with databases. Over 600 organizations from sectors such as healthcare, automotive and IT already trust ASPICON's expertise. They recently created a blog reviewing Dbvisit StandbyMP. The original version was published in German, the translated version is below. 


Dbvisit StandbyMP v11 – Tested for SQL Server!



At ASPICON, we have been using Dbvisit Standby products for many years to set up and operate highly available database infrastructures for our Oracle customers. With the introduction of "Standby MultiPlatform 11.x", Dbvisit now also offers a MultiPlatform approach that enables parallel use of both Oracle SE and Microsoft SQL Server.

We already published our first impressions in our last article "Dbvisit Standby Multiplatform v11 - we are excited", now we want to delve a little deeper, specifically into Standby for SQL Server. We put the software through its paces and for SQL Server we can already say:  It’s well worth looking at!


Introduction

To get started, I’ll give a brief overview of how "Dbvisit StandbyMP" works, our test environment, and the necessary preparation steps. Then I’ll take a closer look at its use with SQL Server databases under Windows and Linux.

We will not go into detail about the installation of the SQL Server database software in this article. Instead, we will focus entirely on the configuration of Dbvisit StandbyMP.


Functionality

For ​​SQL Server, the approach that Dbvisit has taken is based on the backup and restore of transaction log backups. In principle, this works in a similar way to Microsoft SQL Servers’ existing log shipping functionality.

However, in contrast to log shipping, StandbyMPs’ setup is fortunately much easier and faster, providing a decisive advantage.

Further, while switching back and forth with log shipping is a little challenging, with Dbvisit StandbyMP you can easily do failovers or planned switchovers - an automated switchover and switchback (if the database is to be operated on the former primary side again). And with Dbvisit the whole process can be easily set up with just a few clicks via a web interface.


Test environment

We have provided the following hosts for our test environment:

  • 3 x Windows Server 2019 Standard
    • 1 x Host for Control Centre
    • 2 x hosts with SQL Server 2019 installed
  • 2 x Oracle Enterprise Linux 8 (= similar to RHEL 8)
    • 2 x hosts with installed SQL Server 2019 on Linux

Preparation

As a prerequisite, the machines mentioned above were prepared and the appropriate database software installed. The firewall was also adjusted on the Windows hosts. Corresponding inbound rules for the following ports were configured:

  • DB Visit Control Centre
    - 4433/TCP
    - 5533/TCP
  • SQL Server host
    - 1433/TCP
    - 5533/TCP
    - 7890/TCP

We obtained the software as a test version from the Dbvisit website (target platform and version of standby specified):

Dbvisit website: test version


IMPORTANT: In order for everything to work on Windows systems, ensured that the corresponding service users are configured for the “Log on as a service” right in the “Local Security Policy” under “Local Policies” → “User Rights Assignment”.


Installation

Control Centre on Windows

After the software has been loaded, the installer for the Control Centre can simply be started on the relevant host. From our point of view, the installer is quite self-explanatory. You just have to make sure that you set an appropriate password and write it down in a safe place. This is also required for the agent installations in the next step.

Installing the Agents on Windows

Installing the agents under Windows is just as easy as installing the Control Centre. The installation wizard guides you through the process step by step. Please note the information for the Fully Qualified Domain Name (FQDN) of the Control Centre and for the Agent Host (an IP address can also be specified here). The password that was already set in the Control Centre is required again at this point.

dbvisit_sql_agent_configuration_screenshot

After the installation is complete, the Windows service will start. This is visible in the "services.msc".

dbvisit_sql_standbymp_agent

Agents on Linux

Now let's look at installing the agents on Linux. In contrast to the installation of agents on Windows, a folder must be prepared on Linux systems before installation.

dbvisit_ordner_vorbereiten

After that, the installer can be run from the shell:

dbvisit_installer

As with Windows, a few details about the host and the Control Centre must then be entered.


dbvisit_sql_host_und_control_center_angaben-1024x520

After completing the steps described, this host should also appear in the Control Centre.


SQL Server databases on Windows

At this point let's look at the configuration and behavior of SQL Server standby databases on Windows.

To test this, we created a few empty databases. In order to be able to check whether the changes are properly transferred to the second side, we have also added a database with changeable content for replication. We created this database with the open-source tool HammerDB.

It is important that all databases are operated in full recovery mode. If the Dbvisit Standby Agent has been installed and all firewall rules set correctly, it will be displayed immediately in the Control Centre.

dbvisit_sql_server_instances-600


Creating a new standby configuration

To configure a new database for availability, one or more databases from an instance can be selected in the dashboard under “New Configuration”.

dbvisit_sql_database_configuration 600

 

Once the target instance has been selected and all prerequisites are met the configuration can be initiated.  StandbyMP performs these pre-checks in the background.



dbvisit_sql_verfuegbarkeit_initiieren 550

 

After the configuration has been created, it is visible in the dashboard. Disaster recovery can now be initiated here.

 

dbvisit_desaster_recovery 600

In the dialogue that then opens, all parameters for the synchronization can be confirmed.  The paths for the backup files on both hosts and the storage locations of the database files on the target instance can be adjusted if necessary. If everything is set correctly, the process can be initiated by clicking the "Start" button.



dbvisit_synchronisation 600

A complete backup of the database on the source instance is then created by the Dbvisit Agents and then restored on the target instance. The recovery leaves the database in RECOVERY mode so that transaction log backups are then imported.

Transaction log backups are regularly created on the source database and imported into the target database at a user specified interval. The current default value is 300 seconds, which is 5 minutes. But it can be customized as required.

dbvisit_zieldatenbank


Performing a (Graceful) Switchover

A switchover can be initiated easily via the dashboard.

dbvisit_switchover_actions

Various actions can be selected via the “Actions” button of the respective database.  Here is a view of the action drawer. 

dbvisit_action drawer

One of these options is "Graceful Switchover"

dbvisit_graceful_switchover 600

The switchover process is now initialized by pressing the “Start” button. After completing the process, the database on the former standby instance is active as primary and the database on the former primary instance is now running as standby in recovery mode. The regular transaction log backups are now imported in the opposite direction, i.e. on the new standby instance.

This is basically where we see the biggest advantage of Dbvisit Standby:  In contrast to native log shipping from Microsoft SQL Server, Dbvisit Standby directly reverses the direction of synchronization so that availability is still guaranteed. With the native log shipping solution, some manual work would still be required to restore this state. With Standby you are also able to rollback with the click of a button and return to the initial state.


Performing Database Failover

When it comes to automated failover, opinions are always divided. While some do not want to lose any time in the event of an error, a large number of administrators would prefer to carry out such a process in a controlled manner or decide individually. Therefore, Dbvisit tends to follow the majority approach here, that the failover is not carried out automatically in the default setting. However, automated failover can be set accordingly if desired. The "Observer" component of Dbvisit is responsible for the failover. Observer monitors the accessibility of the databases and, if necessary, initiates the failover. If no automatic failover is set, the "Observer" can send appropriate notifications via Slack and email, or execution of a custom script.

The parameters of the “Observer” can be set individually. For example, you can set the check interval or the maximum number of attempts before an action is initiated.

dbvisit_failover

From our point of view, it is generally a sensible setting not to let the failover happen automatically if no corresponding load balancer configuration is implemented for the application.


SQL Server databases on Linux

The configuration and behavior of SQL Server databases under Linux is actually largely the same as SQL Server databases on Windows.

Once the agents have connected to the Control Center the databases can be configured. The only differences are the paths in the file system and that a login with a SQL Server login is recommended (if no Kerberos authentication is configured on the SQL Server on Linux).

IMPORTANT:
Dbvisit Standby currently supports the Linux distributions Oracle Enterprise Linux 6-8, Red Hat Enterprise Linux 6-8 and SUSE Linux Enterprise Server 11 and 12. Microsoft supports the distributions Red Hat Enterprise Linux 7.7 - 7.9 and 8.0 - 8.5, SUSE Linux Enterprise Server 12 and 15 and Ubuntu Linux 16.04, 18.04 and 20.04.  This limits the choice to RHEL and SUSE Linux.


Concluding thoughts

Our conclusion for "Dbvisit StandbyMP" is clearly positive!

In addition to the simple operation and clear graphical user interface, the solution for SQL Server is particularly advantageous if the database hosts are operated in geographically different locations.  Here the log shipping architecture combined with Dbvisits' compression makes it a good choice. Further, it is a convenient and secure Disaster Recovery solution that can be configured even on the SQL Server standard license.

But even if the database hosts are closer together the use of Dbvisit StandbyMP can still be beneficial. For example, if the effort to set up and maintain Basic Availability Groups (on SQL Server Standard license) is problematic, or if there is not a high requirement for a completely synchronized standby.

Written by Björn Ohlrich
Aspicon Gmbh

Product Specialist


Subscribe to our monthly blog updates

By subscribing, you are agreeing to have your personal information managed in accordance with the terms of DBVisit's Privacy Policy


Try StandbyMP for free

See for yourself how our continuous database protection can help keep your world in motion.

Find a local partner

We work with partners around the world to give you the best advice and world-class local support.

Mask Group 59
Mask Group 184-1
get a price2
get a price2
Get Pricing

With Dbvisit's StandbyMP software, Gold Standard Disaster Recovery doesn't have to be difficult or expensive. Get an instant quote now.