SupportAbility Data Replicas
What is a Data Replica?
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 live data.
Click on the links below to take you to the relevant sections within this article:
Why would our organisation require access to a Data Replica?
Many larger organisations require direct access to their raw SupportAbility data for the following reasons:
- data warehousing purposes
- data mining and data analytics purposes
- they require the ability to create custom reports internally without requiring work to be performed the SupportAbility engineering team
To help organisations meet these requirements, we offer annual subscriptions to a MySQL slave replica of your SupportAbility installation data.
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.
Data replicas are available at $9,000 per replica per annum.
If your organisation would like more information or to request on the provision of a data replica, please email this request through email@example.com.
Why is accessing a Data Replica at 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 is a single geolocation are affected, meaning the other servers and as a result the software remain 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 scheme 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)
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.
Do you provide a data schema for SupportAbility?
Providers with a Data Replica subscription will have access to 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 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.
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 create SQL queries that achieve your specific needs as long as that support is channeled 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.