Dbvisit Standby SQL is a software product designed to provide fast & reliable Disaster Recovery (DR) setup for Microsoft SQL Server. In addition to being feature-rich, Standby SQL is first and foremost intended to be extremely simple to both configure and use, even for non-expert users and “accidental DBAs”.

The Standby SQL software is composed of two components: Standby SQL Agent and Standby SQL Control Center.

The Standby SQL Agent is an application that must be deployed on the database host, i.e. the location of the SQL Server Instance. This Agent is what connects to your Instance & Databases and liaises with the Control Center (more on this below) to perform all the actions that Standby SQL provides. You will therefore need to install the Standby SQL Agent at least twice - once on each of the SQL Server Instance locations that you are planning to use. If both of the Instances you intend to use are on the same host/location, a single Agent installation is sufficient, however such an arrangement would not allow for true DR, as the single host represents a single point of failure for both of your Instances.

The Standby SQL Control Center is the brains of the operation, the central command & communications hub that works with and directs/coordinates any number of Standby SQL Agents. It also includes a webserver and the browser-based GUI used to setup/control the product. It is therefore recommended that this be installed on an independent, third location, separate from any of the SQL Server Instance hosts you intend to use. If you must install the Control Center on the same location as one of the Instances, you should use the location for the Standby Instance.

Basic Concepts

Dbvisit Standby SQL works on the basis of a Disaster Recovery Configuration (DRC), which is a defined relationship between a set of Databases within two SQL Server Instances. This “Database Set” can then be assigned either a Primary or Standby role.

A Primary Database is one which is active (i.e. online) and in-use. Almost always, this will be your intended source of information, e.g. a production database. This is the Database you are interested in protecting via a DR configuration.

A Standby Database is one which is not active (i.e. in a recovering state), and is intended to be the target for the Primary Database to replicate to, e.g. your “backup” database. This is the Database that should be kept in-sync with the Primary, and the one which you expect to be able to switch to in case of an emergency.

Installation

Both of the Windows & Linux installers ask for the same basic user input, and install the same basic components. The only exceptions to this will be discussed briefly below.

You may select to install either the Standby SQL Agent or the Standby SQL Control Center, or both. For your SQL Server Instance locations, you need only install the Agent. As mentioned above, it is recommended you install the Control Center separately on a third location, otherwise you may install it on your Standby location at the same time as the Agent.

Brief additional descriptions for some of the installer input fields:

• Control Center Web Server Port: this is the port that the browser-based GUI will become available on after installation. For example, if you are accessing the GUI locally (i.e. from the same host where the Control Center is installed), you would access it by going to https://localhost:[THISPORT].

• Agent Communications Port: this port is used by both the Standby SQL Agents & Control Center for communication between each other. Ensure this port is open, and is set the same installation-wide.

• Linux-specific: If you answer No to the “do you want to automatically set up services?” question, the setup script will present you with a list of the required “actions to complete installation” once it is finished. Please run the commands displayed to start the required services. You can refer to the full Linux installation guide here: https://dbvisit.atlassian.net/wiki/spaces/DRI/pages/2048851973/Linux+Installation.

• Windows-specific: User Domain/User/Password - the user specified here needs to have access rights to the SQL Server Instance if you want to use Windows Authorization later on to connect to the Instance. Otherwise, you can ignore these and manually connect to the Instance later using SQL Server Mixed-Mode Authentication.

Linux Installation

This section will provide you with a quick overview of installing Dbvisit Standby for SQL on a Linux based system

 You can run the installer as Dbvisit user and run the services as root  or install the software and services as same user running mssql server service 

• Create UNIX users user which the Dbvisit Standby SQL services you will run, e.g.

  No Format
  # useradd --system dbvisit

  
  

• Create base software folder where the Dbvisit Standby for SQL will be installed into, example /usr/dbvisit

  No Format
  # mkdir /usr/dbvisit # chown -R dbvisit:dbvisit /usr/dbvisit

  
  

• Download the software to your system

  Example below the installation file is places in the temporary "install_files" folder under the user home directory

  No Format
  ~/install_files/linux-standbysql-v0.9.1.tar.gz

  
  

• Extract the files into a temporary location, e.g.

  No Format
  cd ~/install_files/ tar xzf linux-standbysql-v0.9.1.tar.gz

  
  

• Run the setup program as the user that will run the Dbvisit Standby for SQL software.

  No Format

  [dbvisit@sqlrhl02 ~]$ tar xzf linux-standbysql-v0.9.1.tar.gz [dbvisit@sqlrhl02 ~]$ ls linux-standbysql-v0.9.1.tar.gz setup-standby-v0.9.1DEV-1-g4680e722 [dbvisit@sqlrhl02 ~]$ ./setup-standby-v0.9.1DEV-1-g4680e722 +---------------------------------------------------------------------------------------------------+ | ____ __ _ _ __ _____ __ ____ _____ ____ __ | | / __ \/ /_ _ __(_)____(_) /_ / ___// /_____ _____ ____/ / /_ __ __ / ___// __ \ / / | | / / / / __ \ | / / / ___/ / __/ \__ \/ __/ __ `/ __ \/ __ / __ \/ / / / \__ \/ / / / / / | | / /_/ / /_/ / |/ / (__ ) / /_ ___/ / /_/ /_/ / / / / /_/ / /_/ / /_/ / ___/ / /_/ / / /___ | | /_____/_.___/|___/_/____/_/\__/ /____/\__/\__,_/_/ /_/\__,_/_.___/\__, / /____/\___\_\/_____/ | | /____/ Copyright @2020 | +---------------------------------------------------------------------------------------------------+ Welcome to Dbvisit Standby SQL setup version v0.9.1DEV-1-g4680e722. ? Which directory should the software be installed into? /usr/dbvisit ? Which components do you wish to use on this host? Control Center, Agent - - - You are now configuring the Dbvisit Standby SQL Control Center - - - The Control Center will listen on the following discovered addresses: - 127.0.0.1 - 10.0.2.8 - 192.168.56.105 - 192.168.122.1 ? Control Center Web Server Port: 12345 ? Agent Communications Port: 4222 - - - Dbvisit Standby SQL Control Center configuration finished - - - - - - You are now configuring the Dbvisit Standby SQL Agent - - - ? Default location for SQL Server backup files /usr/dbvisit/standby/backup ? Resolvable Host Name: sqlrhl02.sqlkiwi.co.nz ? Control Center Address: 127.0.0.1 ? Agent Communications Port: 4222 - - - Dbvisit Standby SQL Agent configuration finished - - - - - - Ready to perform installation - - - ? Do you wish to proceed with the setup using the supplied configuration options? Proceed with setup - - - Performing installation - - - Copying software files to '/usr/dbvisit/standby'... Writing Control Center configuration file '/usr/dbvisit/standby/conf/standbycontrol.ini'... Writing Agent configuration file '/usr/dbvisit/standby/conf/standbyagent.ini'... - - - Installation successful! - - - To enable the installed Dbvisit Standby SQL components to run as system services, you will need to execute some commands as the "root" user in order to complete the setup. To install the system service(s), you can run, substituting the name of the user to run as if it is different from the current user: sudo /usr/dbvisit/standby/bin/standbycontrol service install --user dbvisit sudo /usr/dbvisit/standby/bin/standbyagent service install --user dbvisit To start the system service(s), you can run: sudo /usr/dbvisit/standby/bin/standbycontrol service start sudo /usr/dbvisit/standby/bin/standbyagent service start

  
  

Software installation is successful. Install and run the system services as root 

sudo /usr/dbvisit/standby/bin/standbycontrol service install --user root
sudo /usr/dbvisit/standby/bin/standbyagent service install --user root

sudo /usr/dbvisit/standby/bin/standbycontrol service start
	
sudo /usr/dbvisit/standby/bin/standbyagent service start

Services

Assuming you choose to allow the setup program to manage OS services, and that you’re using a Linux distribution that uses systemd for OS services, you can use systemctl to control the service(s).

To check whether the Agent service is running: 

sudo systemctl status standbyagent.service

Example, to check where the Control Center service is running: 

sudo systemctl status standbycontrol.service

Ex: To Install and run the service as mssql user . Installing Dbvisit standby control and standby agent on standby host sqlrhlo2

• Download the software 

[mssql@sqlrhl02 download]$ ls
linux-standbysql-v0.9.1.tar.gz

• Extract the files 

tar xzf linux-standbysql-v0.9.1.tar.gz

• Run the setup

./setup-standby-v0.9.1DEV-1-g4680e722
[mssql@sqlrhl02 download]$ ./setup-standby-v0.9.1DEV-1-g4680e722
+---------------------------------------------------------------------------------------------------+
| ____ __ _ _ __ _____ __ ____ _____ ____ __ |
| / __ \/ /_ _ __(_)____(_) /_ / ___// /_____ _____ ____/ / /_ __ __ / ___// __ \ / / |
| / / / / __ \ | / / / ___/ / __/ \__ \/ __/ __ `/ __ \/ __ / __ \/ / / / \__ \/ / / / / / |
| / /_/ / /_/ / |/ / (__ ) / /_ ___/ / /_/ /_/ / / / / /_/ / /_/ / /_/ / ___/ / /_/ / / /___ |
| /_____/_.___/|___/_/____/_/\__/ /____/\__/\__,_/_/ /_/\__,_/_.___/\__, / /____/\___\_\/_____/ |
| /____/ Copyright @2020 |
+---------------------------------------------------------------------------------------------------+

Welcome to Dbvisit Standby SQL setup version v0.9.1DEV-1-g4680e722.

? Which directory should the software be installed into? /usr/dbvisit
? Which components do you wish to use on this host? Control Center, Agent

- - - You are now configuring the Dbvisit Standby SQL Control Center - - -

The Control Center will listen on the following discovered addresses:
- 127.0.0.1
- 10.0.2.8
- 192.168.56.105
- 192.168.122.1
? Control Center Web Server Port: 12345
? Agent Communications Port: 4222

- - - Dbvisit Standby SQL Control Center configuration finished - - -


- - - You are now configuring the Dbvisit Standby SQL Agent - - -

? Default location for SQL Server backup files /usr/dbvisit/standby/backup
? Resolvable Host Name: sqlrhl02.sqlkiwi.co.nz
? Control Center Address: 127.0.0.1
? Agent Communications Port: 4222

- - - Dbvisit Standby SQL Agent configuration finished - - -


- - - Ready to perform installation - - -

? Do you wish to proceed with the setup using the supplied configuration options? Proceed with setup

- - - Performing installation - - -

Copying software files to '/usr/dbvisit/standby'...
Writing Control Center configuration file '/usr/dbvisit/standby/conf/standbycontrol.ini'...
Writing Agent configuration file '/usr/dbvisit/standby/conf/standbyagent.ini'...

- - - Installation successful! - - -


To enable the installed Dbvisit Standby SQL components to run as system services, you will need to execute some commands as the "root" user in order to complete the setup.

To install the system service(s), you can run, substituting the name of the user to run as if it is different from the current user:
sudo /usr/dbvisit/standby/bin/standbycontrol service install --user mssql
sudo /usr/dbvisit/standby/bin/standbyagent service install --user mssql
To start the system service(s), you can run:
sudo /usr/dbvisit/standby/bin/standbycontrol service start
sudo /usr/dbvisit/standby/bin/standbyagent service start

• Run the install standbycontrol and standby agent services

[mssql@sqlrhl02 download]$ sudo /usr/dbvisit/standby/bin/standbycontrol service install --user mssql
I: 10:18:14 Loading config from '/usr/dbvisit/standby/conf/standbycontrol.ini'
I: 10:18:14 Changing to directory '/usr/dbvisit/standby'

[mssql@sqlrhl02 download]$ sudo /usr/dbvisit/standby/bin/standbyagent service install --user mssql
I: 10:18:19 Loading config from '/usr/dbvisit/standby/conf/standbyagent.ini'
I: 10:18:19 Changing to directory '/usr/dbvisit/standby'

• Start the services

[mssql@sqlrhl02 download]$ sudo /usr/dbvisit/standby/bin/standbyagent service install --user mssql
I: 10:18:19 Loading config from '/usr/dbvisit/standby/conf/standbyagent.ini'
I: 10:18:19 Changing to directory '/usr/dbvisit/standby'

[mssql@sqlrhl02 download]$ sudo /usr/dbvisit/standby/bin/standbyagent service start
I: 10:18:40 Loading config from '/usr/dbvisit/standby/conf/standbyagent.ini'
I: 10:18:40 Changing to directory '/usr/dbvisit/standby'

Check the services

[mssql@sqlrhl02 download]$ systemctl status standbyagent
● standbyagent.service - The Dbvisit Standby SQL Agent provides connectivity to databases inside SQL Server Instances on this computer. This Agent is used & managed by the Dbvisit Standby SQ>
   Loaded: loaded (/etc/systemd/system/standbyagent.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2020-08-15 10:18:40 NZST; 16s ago
 Main PID: 3578 (standbyagent)
    Tasks: 8 (limit: 23818)
   Memory: 25.1M
   CGroup: /system.slice/standbyagent.service
           └─3578 /usr/dbvisit/standby/bin/standbyagent

[mssql@sqlrhl02 download]$ systemctl status standbycontrol
● standbycontrol.service - The Dbvisit Standby SQL Control Center provides centralized coordination for Dbvisit Standby SQL Agent operations, as well as the web-based management console.
   Loaded: loaded (/etc/systemd/system/standbycontrol.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2020-08-15 10:18:34 NZST; 1h 42min ago
 Main PID: 3563 (standbycontrol)
    Tasks: 11 (limit: 23818)
   Memory: 67.4M
   CGroup: /system.slice/standbycontrol.service
           └─3563 /usr/dbvisit/standby/bin/standbycontrol

Installing only standbyagent on Primary host sqlrhl01

[dbvisit@sqlrhl01 ~]$ tar xzf linux-standbysql-v0.9.1.tar.gz
[dbvisit@sqlrhl01 ~]$ ls
linux-standbysql-v0.9.1.tar.gz  setup-standby-v0.9.1DEV-1-g4680e722
[dbvisit@sqlrhl01 ~]$ ./setup-standby-v0.9.1DEV-1-g4680e722
+---------------------------------------------------------------------------------------------------+
|     ____  __         _      _ __     _____ __                  ____             _____ ____    __  |
|    / __ \/ /_ _   __(_)____(_) /_   / ___// /_____ _____  ____/ / /_  __  __   / ___// __ \  / /  |
|   / / / / __ \ | / / / ___/ / __/   \__ \/ __/ __ `/ __ \/ __  / __ \/ / / /   \__ \/ / / / / /   |
|  / /_/ / /_/ / |/ / (__  ) / /_    ___/ / /_/ /_/ / / / / /_/ / /_/ / /_/ /   ___/ / /_/ / / /___ |
| /_____/_.___/|___/_/____/_/\__/   /____/\__/\__,_/_/ /_/\__,_/_.___/\__, /   /____/\___\_\/_____/ |
|                                                                    /____/        Copyright @2020  |
+---------------------------------------------------------------------------------------------------+

Welcome to Dbvisit Standby SQL setup version v0.9.1DEV-1-g4680e722.

? Which directory should the software be installed into? /usr/dbvisit
? Which components do you wish to use on this host? Agent

- - - You are now configuring the Dbvisit Standby SQL Agent - - -

? Default location for SQL Server backup files /usr/dbvisit/standby/backup
? Resolvable Host Name: sqlrhl01.sqlkiwi.co.nz
? Control Center Address: 192.168.56.105
? Agent Communications Port: 4222

- - - Dbvisit Standby SQL Agent configuration finished - - -


- - - Ready to perform installation - - -

? Do you wish to proceed with the setup using the supplied configuration options? Proceed with setup

- - - Performing installation - - -

Copying software files to '/usr/dbvisit/standby'...
Writing Agent configuration file '/usr/dbvisit/standby/conf/standbyagent.ini'...

- - - Installation successful! - - -


To enable the installed Dbvisit Standby SQL components to run as system services, you will need to execute some commands as the "root" user in order to complete the setup.

To install the system service(s), you can run, substituting the name of the user to run as if it is different from the current user:
	sudo /usr/dbvisit/standby/bin/standbyagent service install --user dbvisit
To start the system service(s), you can run:
	sudo /usr/dbvisit/standby/bin/standbyagent service start

• Run the install standby agent services

[mssql@sqlrhl02 download]$ sudo /usr/dbvisit/standby/bin/standbycontrol service install --user mssql
I: 10:18:14 Loading config from '/usr/dbvisit/standby/conf/standbycontrol.ini'
I: 10:18:14 Changing to directory '/usr/dbvisit/standby'

[mssql@sqlrhl01 ~]$ sudo /usr/dbvisit/standby/bin/standbyagent service start
I: 10:09:26 Loading config from '/usr/dbvisit/standby/conf/standbyagent.ini'
I: 10:09:26 Changing to directory '/usr/dbvisit/standby'

Log files

Once the service has started, there should be a log file and some repository files present in the DBVISIT_BASE/standby/log folder

Upgrade

1. Uninstall (we don’t currently support upgrading repositories so they must be removed when upgrading)

2. Remove the files extracted from the installation package

   NOTE - you choose the location of these files when you extract them - there is no default

   No Format
   rm -r Dbvisit/

   
   

3. Run the setup program again

Uninstall

There is no automated uninstall procedure currently.

1. Stop services

   No Format
   sudo /usr/dbvisit/standby/bin/standbyagent service stop sudo /usr/dbvisit/standby/bin/standbycontrol service stop

   
   

2. Uninstall services

   No Format
   sudo /usr/dbvisit/standby/bin/standbyagent service uninstall sudo /usr/dbvisit/standby/bin/standbycontrol service uninstall

   
   

3. Remove logs and repositories

   NOTE - if you changed the default path during installation, you will have to make sure you use that when you run the command below

   No Format
   sudo rm -rf /usr/dbvisit/standby

   
   

Windows

Installation

This

section

will

provide

you

with

a

quick

overview

of

installing

Dbvisit

Standby

for

SQL

on

a

Windows-based

system.

The

windows

installation

is

similar

to

Dbvisit

standby

installation

and

it

is

easy

and

straightforward.

Step

1

Double

click

the

Standby.msi [

1 ] file

which

will

lead

to

the

setup

wizard

and

click

Next [

2 ]

Image Modified

Step

2

Accept

the

license

agreement

and

move

to

the

next

step

Image Modified

Step

3

This

will

prompt

you

by

default

to

install

both

Dbvisit

Standby

SQL

Control

Center [

1 ]

and

the

Dbvisit

Standby

SQL

Agent [

2 ].

The

location

is

by

default

C:\Program

Files\Dbvisit\standby [

3 ].

You

can

browse

to

a

different

location

and

point

the

installation

to

a

different

drive/folder.

Image Modified

Step

4

Image Modified

Step

5

This is a very important part of the installation. 

1. The

   resolvable

   Host

   Name

   is

   automatically

   populated 

2. The

   Control

   Center

   Address

   will

   be

   based

   on

   the

   server

   in

   which

   the

   web

   interface

   is

   installed.

   In

   the

   below

   case

   the

   Control

   center

   is

   installed

   in

   the

   same

   host.

3. Username

   and

   Password

   credentials

   for

   the

   host

   in

   which

   the

   standby

   component

   is

   installed.

Image Modified 

Step

6

If

the

credentials

and

details

are

provided

correctly

in

step

5

the

installation

should

proceed

without

issues

and

the

services

would

also

be

started 

Image Modified

Image Modified

Step

7

When

installing

components

in

the

primary

server,

you

don't

have

to

install

the

SQL

Control

Center.

You

just

have

to

install

the

Dbvisit

Standby

SQL

Agent.

Image Modified

Configuration

Once everything is installed, you can begin configuring the product for your needs by accessing the browser-based GUI, available at the Control Center’s installation address + the Web Server port you specified during installation (as mentioned above). For example: https://localhost:12345.

For now, you can log in using any username as admin and the password “admin” - user management is coming soon.

Dbvisit Standby SQL has no command-line client - the browser GUI is the only way to use & configure the product. This GUI is always available as long as the Control Center process is running.

Registering Hosts & Instances

Begin by registering a Host and any Instances on it - the GUI will prompt you to do so.

A Host is a location with a Standby SQL Agent installed and running - this should have been done during installation above. When you click on the Manage Instances or Let's do this now, you will see 

Step1

The below Figure will show the discovered hosts in the central console.

The servers in which the Dbvisit SQL is installed and the agent is running will be displayed below.

Step2

You will have to register the discovered host, when you click on Register Host you will see the below options

1. Choose the Server to add
2. You can add the server with both SQL Server Authentication or Windows Authentication

Step3

I have chosen the Windows Authentication and you get the below results

1. The SQL Server instance name.
2. Confirm if the test connection was successful and the Dbvisit components are able to connect to the SQL server instance.
3. Available user databases.
4. Click Confirm&Add Instance(s) to register it to the central console.

1. Instance Name and the green signal signifies that its online
2. Edit Instance button to add authentication details if you want to change to SQL Server Authentication
3. Unlink Instance to remove it from the control center.

Step4

Both the primary and standby host has been registered with the central console. We recommend using the same authentication type to register both primary and standby hosts.

Database Configurations

After registering the hosts you will see a new option on the left pane called "Dashboard" when you click on it you can use the New Database configurations

This will pop up with options of selecting the databases from the primary host and the instances from the standby host in which you want to restore the databases.

Choose the SQLServer  Instance from the host in which you want to restore the database to.

The location for the backups can be customised in both primary and standby or this will take the default location provided during the registration of the hosts.

Create Standby Database

In the below figure, it can be seen that the Database Configuration has been created for two databases (AdventureWorksDW2019 and ZEE) and these databases are ONLINE in the primary server DBWINSQL01. There is no standby database created. To create the standby database click on "Create one now?" This will lead to the next window for creating the standby database.

You can change the datafile location in the standby by providing a different location [ 1 ]and then click start [ 2 ]

You can create the standby database individually or select all the configurations together and create the standby database. The options will appear from the bottom of the screen and display available actions that can be performed for the configurations.

Once the process is started you can see the status of the task under Tasks & Events Timeline[ 2 ]. The status of the standby database will be shown as restoring [ 1 ] 

Clicking on the tasks you can see the progress along with steps performed for the particular task. This will show the progress of the backups and the tasks performed.

Usage

Once the standby is created you can now perform other actions to the database configuration. Below are the actions that can be performed for the database configurations.

1. Backup & Send Logs
2. Restore Logs
3. Schedule Backups
4. Synchronize
5. Switchover
6. Failover
7. Delete Standby Database
8. Unlink Database

Let's go through these actions individually.

1. Backup & Send Logs

This option would backup the transaction logs from the primary and send it to the standby server.

The action will appear in the Tasks & Events Timeline section and you can see that the task is successfully completed.

Restore Logs

After step 1 is completed, now the transaction logs must be applied to the standby database.

Schedule Backups

This option is used to schedule transaction log backups or the differential backups at specific intervals and can be transferred to the standby server and applied there.

1. The toggle button is used to enable or disable this option
2. The backup type can be Log Backup or Database Differential backups, this can be decided by the user.
3. Interval in minutes these backups are scheduled. We have used 1 minute for the schedule( the default is 5mins)

After every scheduled interval, the transaction log backups are taken and then applied in the standby. The time gap between primary and standby is updated every scheduled interval.

Synchronize

This action is performed when you want to take the backup of the primary transaction logs and then restore all the available logs to the standby there my syncing the database with primary.

From the below screenshot , we can see that the there were two transaction log backup files restored in the standby which was available to be restored.

Switchover

The switchover is basically reversing the roles between the primary and standby database. The standby database is restored with the latest backup and then it is open and online and the primary database status is changed to restoring status.

Once the action is successfully completed, the database roles are switched, the primary goes to the restoring status [ 2 ] and the standby database status is now online [ 1 ].

Delete Standby Database

This option is to remove the standby database from the SQL Server instance. This option is available only for Beta testers and will be removed from the final product

Failover

This action is performed when your primary server is down or your SQL Server instance is down with an unrecoverable state. Failover will bring the database in standby online.

Once the failover is completed. The old standby server assumes the primary role and the database is online [ 1 ]. The old primary database and server go to the failover state [ 2 ] and becomes the standby.

The logs indicated below.

You can clean up the new standby server and recreate the standby database. Please remember this would clean up the former Primary database along with the backups. This will enable you to recreate the standby database on the old primary server. Once this action is completed you can just switch over the database to return to the pre-failover state.

After the cleanup is done, the next step is to unlink the database from the configuration. The database must be added again to the configuration before you can re-run the CSD operations.

After the above operation, the database is no longer under the Database configurations page and you have to add it again from the new primary which is DBVWINSQL02.

Once the configuration is created, the standby database can be created again.

Related pages