Creating high availability architecture for 24/7 real world business operations introduces many challenges. Balancing availability and maintenance encompasses a number of important aspects:
- Data availability. Prevent interruption to business processes by ensuring access to data.
- Performance. Providing adequate response times for efficient business operations.
- Cost. Reducing costs as part of overall business strategy.
Replication allows data from a master database to be replicated to one or more child databases (also known as nodes) and vice versa.
Advantages of replication
There are many advantages to replication, including:
- Analytics. Analysis of information can take place on a child database, without affecting performance of the master database.
- Data distribution. Remote sites can work on local copies of data without permanent access to the master database.
- Increased availability. Should a node become unavailable due to hardware or other failure, clients can access an alternative node.
- Increased speed. Accessing data on a local network is generally faster than public network access.
Disadvantages of replication
There are several disadvantages to replication, including:
- Increased disk space. Storing copies of data on different sites consumes more disk space.
- Maintaining data integrity is more complex.
Providing a database for resilience, would allow business operations to continue should the primary database become unavailable through failure or maintenance. Replication is used to ensure the resilience and up-time within a site or location.
This image shows a configuration for departments on a site, each department would use their database instance, optimised for their needs, replication would synchronise their data and a further resilience database would be available should their primary database become unavailable. This model can be easily replicated across multiple sites and departments.
One Size Fits All
This is very rarely true; more often than not different business operations would require different database optimisations. Using replication, applications can connect to the most appropriate database for their needs. For instance, a website collecting SEO data would need to optimise the writing of data in order to optimise the user experience. This could be achieved by removing all indexes used in search operations. Using replication, a specific child database could be designated for SEO reports and include the required indexes to optimise querying the data. Primary and foreign keys should never be disabled in node databases.
At some point in the life cycle of a database it will require maintenance, whether it be backup and restore, optimizations or upgrading system versions. Using the resilience model above any database, even the master database, can be taken off line for a pre-determined period for maintenance purposes, without affecting business operations.
Connecting To Databases
The application code required to access multiple databases requires little overhead, a single method can be used to connect to a specific database, a further method would be required to specify the connection string.
The following code is in C# but can easily be ported to other languages.
An enum is used to specify which database to connect to. The Standard database is generic for most connections and the FailOver is used should the connection to the Standard database fail. The enum can be expanded to suit business specific requirements.
A string array is used to hold connection strings to specific database types, this would be one for each enum in DatabaseType.
The array can be initialised thus:
The GetConnectionString method will return the connection string required to connect to the database type specified.
The connect to database method makes a physical connection to the required database and starts a transaction, this method requires several private fields in order to automatically attempt to switch back from a FailOver database to the Standard database.
Please note it is the responsibility of the calling method to dispose of the FbConnection and FbTransaction objects.
The method will attempt to make a connection to the specified database type, if an error occurs more than 3 times whilst making the connection then the FailOver connection will be used up to MaxReconnectAttempts, after this the method will throw an exception.
This method will also work for pooled connections, if for any reason the pooled connection is lost then further connection attempts will be made.
The following code shows example usage for connecting to a database using the above methods.
Replication can be used to provide a 24/7 business operations. Modularisation of databases between sites and departments can provide improved performance and resiliance. The actual code required to support multiple databases requires little overhead.