SupportAbility Data Replicas

Database replication is a process that creates a secondary, read-only copy of your SupportAbility data. All changes made to your SupportAbility master database would be automatically applied to the slave replica database. The replication generally occurs within a few seconds. This means that the data you access on the replica is incredibly close to your live data.

Audience: Authorised Representatives, Executive Management, IT

Summary

The following list summarises the content within this article. Click on the links below to take you to the relevant sections:

Why would our organisation require access to a Data Replica?

Many larger organisations require direct access to SupportAbility data for the following reasons:

data warehousing purposes
data mining and data analytics purposes
the ability to create custom reports internally is required without requiring work to be performed by the SupportAbility Engineering team

To help organisations meet these requirements, we offer annual subscriptions to a MySQL slave replica of your SupportAbility installation data by way of the Data Replica.

Return to Summary

How can the Data Replica be used to connect to a third-party application?

Any third-party application that allows the configuration of an SSH tunnel can be used to connect to the SupportAbility Data Replica.

Once a connection has been established, the third-party application will have read access to the full database, and data can be fetched using MySQL queries.

To determine if this is possible, it is important to clarify if the third-party application supports SSH tunnelling to a database server.

If it does, it is important to determine what options the third-party application provides to fetch the data from the database once connected.

If this connection is possible, we can provide an organisation with a Data Replica subscription with technical support to assist you in reviewing SQL queries to fetch the correct data for your integration requirements.

Return to Summary

SupportAbility Data Replica Details and Pricing

Important facts about SupportAbility MySQL slave replica databases:

The access provided is read-only (only SELECT queries are allowed. INSERT, UPDATE, DELETE, CREATE, DROP and other statements will be denied)
Replication to the slave database server is usually only seconds behind the live master but may be up to 60 seconds behind the master when under extreme load
A single MySQL access username and password will be provided which will give you access to all tables and fields
You must provide us with an IP address. Your provisioned MySQL user account will be locked to only allow requests from the nominated IP address.
All traffic to and from the MySQL slave replica server is encrypted using SSL
The SupportAbility database only stores the raw, normalised SQL data that underpins the SupportAbility installation. Many of the complex calculations such as Client NDIS Funding utilisation figures are not stored in the database directly but are calculated dynamically in the SupportAbility codebase using a series of separate SQL queries and code-based logic. These types of complex calculations require a programming language to determine the appropriate outcome and cannot be calculated with SQL queries alone.

If your organisation would like more information, including pricing or request the provision of a data replica, please email this request through support@supportability.com.au.

Return to Summary

Why is accessing a Data Replica an additional subscription fee?

In previous decades, where software systems utilised centralised system architectures, clients would host their own database, on their own premises, and access it as needed. In contrast to this, Cloud Systems like SupportAbility utilise a shared infrastructure across the organisations that use the software. In a true cloud platform, the only access to the system is provided via the application itself. Raw database access, especially direct access to the Master Cluster is never provided to individual clients because it has the potential to affect security, availability and performance of SupportAbility for all providers.

As a general rule, most cloud-based Software SaaS applications like SupportAbility, host the master data for their applications in what is called a 'Master Cluster'. A Master cluster is just a number of database servers, hosted in separate geo-locations, that are identical replicas of each other. The purpose being, that in the event of an extreme natural disaster, chances are that only servers in a single geolocation are affected, meaning the other servers and as a result, the software remains available. In addition to natural disasters, there are other factors that can affect the availability and performance of the Master Cluster, and it is our responsibility to ensure that SupportAbility is protected from those threats also. Some of these are listed below:

Bad Queries that affect performance and availability

The database queries developed by SupportAbility's Engineers and are used within the SupportAbility product are very carefully built by SQL experts. When we construct a new database query, we often add the appropriate database indexes to ensure that query runs as fast as possible and does not slow down the system for all other users.

Generally speaking, our subscribers do not have qualified DBAs (Database Administrators) on staff and nor do they have the comprehensive understanding of the SupportAbility database schema and data. Furthermore, in giving raw access to SQL layer, we have no control of the queries providers are sending to the server. The entire database server (and hence all client databases hosted within it) can experience significant performance issues and in some cases even be taken offline if it is sent bad queries that:

Return more rows than required (performing a search that returns 1,000,000 journal records rather than 100 at a time )
Return more fields than required (performing a search that all information for 10,000 client records rather than just the required fields such as first name and last name)
Searching across non-indexed data (this can bring a server to a grinding halt)
Use too much memory (this can bring a server to a grinding halt)

Security

The Master Cluster is comprised of several database nodes with the capacity to both read and write data. Even though read only access to the master can be configured, we choose not to do this for security purposes. In an extreme example, something as simple as a misconfigured database privilege could accidentally provide one client with write access to all client databases.

To significantly reduce the potential risks of raw SQL access affecting the system security, availability and performance for all clients, we create a read-only slave replica of the master database which:

is identical to the live master cluster data set (< 200ms lag behind master)
contains only that client's data
is only capable of read-only queries
has its own resources (RAM and CPU) for handling significant query volumes and is better at handling bad queries
does not affect the performance or availability of the Master Cluster
does not affect the security of the Master Cluster

Creating and hosting a read-only slave replica incurs additional costs and is not a standard part of any SupportAbility subscription. A Data Replica is only ever created, hosted and managed by SupportAbility upon request and when a provider has accepted the additional subscription fee associated with this.

Return to Summary

Do you provide a data schema for SupportAbility?

Providers with a Data Replica subscription will have access to provisioning an additional privilege in the User Details tab of Staff records called 'View Database Schema':

Staff with the 'View Database Schema' privilege access will have access to the 'Current Schema' and 'Schema Changelog' for SupportAbility, accessible via the Reports menu:

With every SupportAbility version released, both the Current Schema and the Schema Changelog will be updated to reflect any changes made to the schema.

It is important to note that advance notification cannot be provided for schema changes.

Return to Summary

Do you provide a data dictionary for SupportAbility?

We do not currently have a data dictionary for SupportAbility, however, we can provide you with support to assist you to create SQL queries that achieve your specific needs as long as that support is channelled via your two nominated SupportAbility Authorised Representatives.

How do we access our SupportAbility Data Replica?

Once your MySQL data replica has been provisioned, we will provide you with credentials and :

Access details: These include the domain and TCP port of your slave replica, as well as your MySQL username and password. Access details are provided by SupportAbility upon purchase of a slave replica.
A MySQL Client: This is the tool that you use to connect to the MySQL server. You may choose to use a graphical interface tool such as MySQL Work Bench (http://dev.mysql.com/downloads/workbench/), a shell based tool (http://dev.mysql.com/downloads/shell/) or you may choose to build programs using languages that have a built-in MySQL client such as PHP (http://php.net/manual/en/book.mysqli.php).
SSL Certificate Public Key: SupportAbility uses SSL certificates signed by a certificate authority to encrypt all traffic between the MySQL client and MySQL slave replica server. The SSL certificate includes the slave replica domain name as the Common Name (CN) for the SSL certificate to guard against spoofing attacks. The public key is stored at http://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

Using the access details for your slave replica provided by SupportAbility once purchased, you will be able to configure your MySQL client of choice to connect to the Supportability MySQL slave replica.

For example, let's assume that after purchasing a SupportAbility slave replica subscription, we provided you with the following access details:

replica domain: my.replica.com
replica TCP port: 3306
username: my-company
password: my-password
database name: my-database

Using these credentials, you could connect you the MySQL slave replica using the following shell command from a Linux, OS X, or Unix server assuming your SSL Certificate Public Key file was located in the /tmp/ directory:

mysql \
--user=my-company \
--password=my-password \
--host=my.replica.com \
--port=3306 \
--ssl-ca=/tmp/rds-combined-ca-bundle.pem \
--ssl-verify-server-cert \
my-database

It is important to note that MySQL client connections to the SupportAbility MySQL slave replica will only be accepted from the IP address provided to us for your organisation.

Once your MySQL data replica has been provisioned, we will provide you with access details and documentation on how to connect with your data through different methods.

For security reasons, the connection relies on establishing a secure SSH Tunnel between your computer or application and the data replica server.

SupportAbility supports any connection method where an SSH Tunnel can be established.

The most common methods are:

Mysql GUI clients such as MySQL Work Bench
SSH client such as PuTTY in combination with a mysql shell based client
Native SSH clients on Linux, OS X, or Unix server in combination with a mysql shell based client

SupportAbility will provide you with the following information:

SSH Username
Private SSH Key
MySQL Username
MySQL Password

For example, let's assume that after purchasing a SupportAbility slave replica subscription, we provided you with the following access details:

SSH Username: my-ssh-username
MySQL Username: my-db-username
Private SSH Key file name: my-key_rsa

Using those credentials, you could connect to the MySQL slave replica by establishing a secure SSH Tunnel between your computer and the database using the shell command from a Linux, OS X, or Unix server. Assuming your Private SSH Key file was located in the /tmp/ directory, the following command can establish the tunnel:

ssh -i /tmp/my-key_rsa -M -S /tmp/tunnelsocket -f my-ssh-username@bastion.hacloud.es -L3306:clientdatabase.network.envision:3306 -N

Once the tunnel is successfully established a mysql shell based client can be used to access the database:

mysql -h 127.0.0.1 -u my-db-username -p

It is important to note that MySQL client connections to the SupportAbility MySQL slave replica will only be accepted from the IP address provided to us for your organisation.

Return to Summary