Replication made easy

With ScimoreDB there are multiple options for distributing data. There is replication and distributed clusters. In this howto we will introduce replication. First an introduction to the technices.

Replication

Replication is between a master and a client - one database instance can subscribe to data on another database instance. Synchronisation occurs when you issue the synchronise command. Replication can be used for many things. One of the primary usages is "in the field applications", where e.g. person updates the database while being offline, and synchronize changes back into the mail database when online. Its also usefull for scaling database reads, e.g. distributing the data over large amount of read only database servers.

Distributed clusters

A group of machines that act as if they are one SQL server. The machines should be on the same low latency network. Within the cluster, depending on the parition key on each table, the data of the table can be either distributed to all, or split over all. You can read more about distribution cluster, and how to make one - here.

Replication is a powerfull feature that should be in all programmers toolbox. Yet, it is not often used, because of the complexity of setting up replication. With ScimoreDB we have attempted to make replication easy to use.

Now a quick introduction to replication and the types of replication that ScimoreDB enables, and later will be examples sql statements and c# code.

In ScimoreDB you can replicate databases and tables. There are three types of replications.

Read only replication

Master/slave replication. It is the simplest type of replication. All insert/updates are done on the master database instance, and subscribing instances synchronises periodically and get all updates since last synchronisation with the master.

It be used for:

• Offline read applications - applications
• High load/uptime for web scenario that mostly does reads. Reads are on replicated database instances, while to central database instrance

Bi-directional replication

A master database contains the primary set of data. Client databases subscribe to the master. Data can be changed both on master and the client.
When the clients synchronises with the master, changes done on master since last replication will be applied on the client, and changes on the client will be applied to the master. After synchronisation they will be identical.

Merge conflicts can occur, when the same data is updated on both the server and the client. The client issuing the synchronisation, will be notified of the merge conflict, and can handle conflicts in code. An example follows.

Where can it be used:

• In the field applications - where the client goes offline, but still updates data
• High load/uptime for web scenario that mostly does frequent writes, aggregates data and synchronizes aggregated data with master
• And many more...

Filtered replication

With filtered replication it is possible to subscribe to only parts of the masters data. The filter is done using standard boolean where clause.

Hands on

Its time to get our hands dirty with some sample code. I have installed a scimore server instance on 'scimoredb42.scimore.com:999', containing a copy of data from CIA factbook.

On my laptop machine I have installed the Scimore manager and embedded ScimoreDB dll. I will on my embedded instance do different types of subscription of the data on the server. Note that is is also possible to do replication between embedded/embedded, and between server/server.

Hands on: Read only replication

First we create the replication-subscription on the client. I load Scimore manager and creates a local embedded database. Using the manager i start the embedded database and get a query window, in which I execute the following command:

        -- Create a database
        create database if not exists CIA1; go;
        
        -- Subscribe to CIA.Country table from scimoredb42.scimore.com:999 to my local CIA1.Country table
        subscribe from 'scimoredb42.scimore.com:999'
        (
	        read CIA.Country to CIA1.Country
        )
	

The result is:

        282 row(s) affected
    

I have now created a subscription of the CIA.Country table, and the table schema and table data has been synchronized to my embedded database. I can execute statements on my local embedded instances e.g.

        select top 3 Name, Population, GDP, GDP / Population as GDPPerCapita 
        from CIA1.Country order by GDPPerCapita desc
    

Shows me the vatican is doing pretty well.

On the server I update the population of the Vatican with:

        update CIA.Country set Population = Population+1 where Name like 'Holy %'
    

To update the embedded database with the change, just synchronize the updated table by executing the following command on the subscriber instance. In our case the local embedded database instance:

        synchronize table CIA1.Country
    

Or I can synchronize all tables in CIA1 with the command:

        synchronize database CIA1
    

Hands on: Bi directional replication

The bi-directional replication is equally easy to set up, and to synchronize.

        -- Create a database
        create database if not exists CIA2; go;
        
        -- subscribe for bi-directional, merge
        subscribe from 'scimoredb42.scimore.com:999'
        (
            merge CIA.Country to CIA2.Country
        )
    

When doing bi-directional synchronisation conflicts can occur. When both server and client have modified the same data, which data is most correct?

The database doesn't have domain specific knowledge on your database, and leaves it up to the client to resolve possible conflicts.

Here is a short sample of conflict resolution in c#.

Hands on: Filtered replication

Filtered replications works straight forward. You provide a standard sql where clause when you subscribe.

        -- Create a database
        create database if not exists CIA3; go;
        
        -- Filtered subscribe to CIA.Country table only synchronising elements larger starting with A
        subscribe from 'scimoredb42.scimore.com:999'
        (
	        merge CIA.Country to CIA3.Country where region = 'Asia'
        )
    

This concludes our introduction to replication in ScimoreDB. We hope you find it usefull. We have done our utmost to make it as simple and powerfull as possible.