In distributed, networked environments, a system can consist of multiple applications. Each application can have its own database. The advanced replication architecture supports a model where a replica database contains data from multiple master databases. For instance, a local database can contain a replica from a billing host system and network configuration host system. Both two-tier and many-tier architectures are scalable to accommodate multiple master databases. The two-tier architecture shown in Figure 4 makes use of multi-master synchronization.
The example shown below illustrates the concept of multi-master synchronization on a table level.
▪A database server can contain replica databases from multiple masters.
▪Systems A and B are separate and independent of each other.
▪For each replica database, a database catalog is created in the database server.
▪Replica A synchronizes with master A and replica B synchronizes with master B.
▪A database server can also have one or multiple master databases or local-only databases in one or multiple catalogs.
Key features in multi-master synchronization models
▪Replicas can register to synchronize data with multiple masters.
▪Replica data from different masters is kept separate using catalogs.
▪Local data is kept separate from shared data.
Managing replica data in a multi-master environment
A solidDB® database can be divided into multiple, independent partitions or catalogs. Each catalog can be divided into multiple independent schemas. The ability to divide a database into catalogs is useful if your database contains multiple topics or is used by more than one application. Typically, each application would have its data stored in a separate database catalog.
In a multi-master environment, the ability to have multiple catalogs enables you to specify multiple databases (master or replica) for synchronization within one database server. For example, a solidDB® server of an access router can have two catalogs, one for a replica of a configuration management database and the other catalog for a replica of a subscriber provisioning system.
solidDB® database, catalogs, and schemas: The solidDB® server stores data in a file (or a set of files). These files are known as the physical database. The locations of these files are specified in the solid.ini configuration file. These files can be stored in one user-specified directory or in multiple user-specified directories.
Since each solid.ini configuration file specifies the location of one physical database, a single solidDB® program can theoretically operate on different physical databases at different times, simply by starting the server with different solid.ini files. Although you can have more than one physical database from creating multiple physical files (and solid.ini files), one solidDB® instance "sees" and "works with" only one physical database at a time. Using a single solidDB® program with multiple databases at different times is uncommon.
A physical database file can contain more than one logical database. Each logical database is a complete, independent group of database objects, such as tables, indexes, procedures, triggers, and so on. Each logical database is called a catalog. However, a catalog contains a wide variety of data objects, not just indexes as in the traditional sense of a library card catalog, which serves to locate an item without containing the full contents of the item.
As a general rule, the term catalog refers to a logical database and database refers to a physical database.
Catalogs and the objects within them are arranged in a hierarchy. Catalogs can contain database objects known as schema. You can have multiple schemas within each catalog. Each schema, in turn, can have multiple database objects, such as tables, views, sequences, and so on. Catalogs are at the top of the hierarchy and objects such as tables are at the bottom of the hierarchy.
Within a schema, the name of each object must be unique. For example, you cannot have two tables named table1 inside the same schema. Similarly, within a catalog, the name of each schema must be unique. For example, you cannot have two schemas named smith_schema inside the same catalog.
Although object names within a schema must be unique, different objects in different schemas can have the same name. For example, it is legal to have both of the following:
smith_schema.table1 jones_schema.table1
Similarly, although schema names within a catalog must be unique, different catalogs may contains schemas with the same name. For example, the following is valid:
When an object name is preceded by the schema name and the catalog name, the object name is fully qualified, that is, unambiguous. Database object names are qualified in DML statements in the following way:
catalog_name.schema_name.database_object
or
catalog_name.user_id.database_object
Typically, all users in a catalog can have their own schema(s). For example, a user can own smith_schema in employee_catalog.
A single user can have schemas in more than one catalog. For example, as shown above, user smith might have schemas named smith_schema in multiple catalogs. As long as database objects within each catalog are specified by fully qualified names (that is, names that include the object name, the schema name, and the catalog name), there is no confusion as to which database object is the one required. By organizing your data appropriately inside catalogs and schemas, you can restrict the context so that users or applications see only those database objects that are relevant to their task. You apply these concepts when creating catalogs and schemas for synchronization.
Catalogs and synchronization: Catalogs allow you to implement multiple logical databases for synchronization. If your local physical database has replica data from multiple masters, then the replica needs one catalog for each master database of which the replica has a copy (or a partial copy). Note that a catalog on a replica may contain not only synchronized data, but also local data, which belongs to the local database only and never gets replicated from and to other databases.
In this way, tables are defined in both replica and master databases to distinguish local from shared data. Shared data is synchronized with the master database, but data belonging to only the replica or only the master is never changed during synchronization.
In a multi-master environment, catalogs keep data from different masters separated. Object name conflicts between catalogs do not occur. Even when the same table names and other object names are used in different masters, the distinct catalog names for each master qualify table and object names, as well as provide a way to specify which objects belong to which master. In addition, solidDB® advanced replication enforces that a single catalog contain no objects from different masters. However, using catalogs does not require that all objects in a schema be a synchronized object, so catalogs can contain local tables.
Schema names of a master and replica must be identical. This is consistent with the basic two-tiered architecture, which is no different from a single replica that is registered to a single master. When a database is created, a default schema name is created which is the user id of the database owner. Separate schemas within databases are created with the CREATE SCHEMA statement. For details on managing database objects with schemas, read solidDB® Administration Guide.
solidDB® advanced replication's use of catalogs and schemas for synchronization offers a flexible and scalable architecture. One replica is registered to only one master; that is, one replica catalog is mapped to one master catalog. However, a single physical database can have multiple catalogs. Additional master databases are included by creating more master catalogs in the same local database which map to new replica catalogs in the same or different database servers. Furthermore, both master catalogs and replica catalogs can exist in the same physical database.
As shown in Advanced multitier architecture, a master/replica hierarchy may have more than two layers, and some catalogs within that hierarchy may serve as both replica and master catalogs. For details on defining catalogs with the dual role of master and replica, read Defining master and replica databases.
Transactions in a multi-master environment: In a multi-master environment, a transaction cannot span two different masters. For example, the following is invalid:
SAVE UPDATE table A in CATALOG A SAVE UPDATE table B in CATALOG B COMMIT WORK
A transaction is propagated to a specific master database. This master cannot be changed in the middle of a transaction, which means that all saved statements in the transaction are propagated to one master only. solidDB® can detect cases where one transaction is updating data from two different masters. In such cases, the operation fails with an error message.
solidDB® advanced replication allows local data modifications in the same transaction within a catalog. The SET CATALOG command explicitly defines the master used for all advanced replication-related operations. The SET CATALOG command is executed before any synchronization command and is required when more than one catalog is defined in the database.
