SymmetricDS

symmetricds: 3.0 is coming along. The new architecture allows the data loader to be pluggable. Tested it out by building an... http://t.co/CfNZ5LCL

symmetricds: Just added support for the SDO_GEOMETRY data type in Oracle. It is included in the (newly) released 2.5.8 version of SymmetricDS.

The Professional version of SymmetricDS 2.5 was released and includes some exciting new features. If you're not familiar with SymmetricDS, it's software for database replication that can capture data changes and keep many remote databases in sync. ... [More] We try to support every database we can (12 platforms supported now), but let us know if you have a need for your database to be included. Now, let's talk about what's new and improved with SymmetricDS Pro 2.5! Simple Install and Better Database Setup One of the first things we tackled is how the software is installed. In previous releases, the installer prompted you to configure the node and connect to the database you planned to replicate. It was possible to make mistakes and then the service wouldn't run. In this release, the installer just installs the software. The user experiences early success by immediately logging into the web console. From there, the user walks through a wizard that sets up a new node. The database connection is tested to make sure the information is correct. Single Instance, Multiple Servers Probably the most powerful new feature is multiple servers. Some people were hosting multiple instances of SymmetricDS on the same server, which required allocation of multiple ports, services, and Java VM processes. It worked great, but it consumed some extra memory to do it. In this release, you can run multiple SymmetricDS servers from the same instance. So you have one service that runs a Java VM for your SymmetricDS instance, but you can setup as many server (and client) nodes within it as you want. The web console has a cool switcher in the corner that lets you switch context between all your nodes. The main instance still has the usual conf/symmetric.properties file that configures it, but now each server you create gets its own engines/myserver.properties file. You can still edit the property files with an editor if you want, but the console is an easy way to change configuration and can be accessed remotely. Replicating Data to Greenplum Keeping with our plan of a multi-database, heterogenous replication solution, we've added support for the Greenplum database. This makes a total of 12 database platforms supported by SymmetricDS! While Greenplum is based on the open source PostgreSQL database, it is modified for massive parallel processing. Data capture is not enabled on this platform yet, but you can target data replication to it, and use any of those features such as horizontal/vertical filtering, transformation, and automatic recovery. Look for more integration with Greenplum as we improve support. For the next release, we're testing changes that will use Greenplum bulk loading APIs to speed up data loads. Finishing Touches The web console has a new look and some small UI tweaks to make editing configuration easier. We added some new troubleshooting tools that are convenient and could come in handy. Sometimes Support will ask to see the log file, so there is a screen to view it and even download it. Another screen shows running threads and their stack trace, which might be used to verify things are running normally or find where a thread is stuck. There is even a SQL screen to run adhoc queries that might be used to examine a data conflict or integrity violation. Wrapping Up The new release of SymmetricDS 2.5 brings improvements to installation, configuration, and support for the Professional version. Both the Community and Professional versions gained new platform support with Greenplum and a major new feature with multiple servers. Looking ahead, we're working on improving the data loader subsystem to support bulk load and batch updates. The changes will make it easier to implement conflict management and support Android in a future release. [Less]

The Professional version of SymmetricDS 2.5 was released and includes some exciting new features. If you're not familiar with SymmetricDS, it's software for database replication that can capture data changes and keep many remote databases in sync. ... [More] We try to support every database we can (12 platforms supported now), but let us know if you have a need for your database to be included. Now, let's talk about what's new and improved with SymmetricDS Pro 2.5! Simple Install and Better Database Setup One of the first things we tackled is how the software is installed. In previous releases, the installer prompted you to configure the node and connect to the database you planned to replicate. It was possible to make mistakes and then the service wouldn't run. In this release, the installer just installs the software. The user experiences early success by immediately logging into the web console. From there, the user walks through a wizard that sets up a new node. The database connection is tested to make sure the information is correct. Single Instance, Multiple Servers Probably the most powerful new feature is multiple servers. Some people were hosting multiple instances of SymmetricDS on the same server, which required allocation of multiple ports, services, and Java VM processes. It worked great, but it consumed some extra memory to do it. In this release, you can run multiple SymmetricDS servers from the same instance. So you have one service that runs a Java VM for your SymmetricDS instance, but you can setup as many server (and client) nodes within it as you want. The web console has a cool switcher in the corner that lets you switch context between all your nodes. The main instance still has the usual conf/symmetric.properties file that configures it, but now each server you create gets its own engines/myserver.properties file. You can still edit the property files with an editor if you want, but the console is an easy way to change configuration and can be accessed remotely. Replicating Data to Greenplum Keeping with our plan of a multi-database, heterogenous replication solution, we've added support for the Greenplum database. This makes a total of 12 database platforms supported by SymmetricDS! While Greenplum is based on the open source PostgreSQL database, it is modified for massive parallel processing. Data capture is not enabled on this platform yet, but you can target data replication to it, and use any of those features such as horizontal/vertical filtering, transformation, and automatic recovery. Look for more integration with Greenplum as we improve support. For the next release, we're testing changes that will use Greenplum bulk loading APIs to speed up data loads. Finishing Touches The web console has a new look and some small UI tweaks to make editing configuration easier. We added some new troubleshooting tools that are convenient and could come in handy. Sometimes Support will ask to see the log file, so there is a screen to view it and even download it. Another screen shows running threads and their stack trace, which might be used to verify things are running normally or find where a thread is stuck. There is even a SQL screen to run adhoc queries that might be used to examine a data conflict or integrity violation. Wrapping Up The new release of SymmetricDS 2.5 brings improvements to installation, configuration, and support for the Professional version. Both the Community and Professional versions gained new platform support with Greenplum and a major new feature with multiple servers. Looking ahead, we're working on improving the data loader subsystem to support bulk load and batch updates. The changes will make it easier to implement conflict management and support Android in a future release. [Less]

The Professional version of SymmetricDS 2.5 was released and includes some exciting new features. If you're not familiar with SymmetricDS, it's software for database replication that can capture data changes and keep many remote databases in sync. ... [More] We try to support every database we can (12 platforms supported now), but let us know if you have a need for your database to be included. Now, let's talk about what's new and improved with SymmetricDS Pro 2.5! [Less]

SymmetricDS 2.5.0 was released with new features that include a multi-server option and support for the Greenplum database. Multiple engines with different configurations can now run within the same server, conserving memory and consolidating ... [More] management. The Greenplum database, popular for its data warehousing and analytics, is now supported as a target for replication. See the release notes for the full list of bug fixes and improvements: [Less]

symmetricds: 2.5 has been released. Release notes can be found here: http://t.co/svMHprzR. Enjoy! http://t.co/ANiXNOVM

SymmetricDS has for years been a robust, reliable solution for synchronizing data between multiple databases and multiple database platforms. With SymmetricDS 2.4, SymmetricDS has expanded to also prove data transformation capabilities while ... [More] synchronizing. The data transformation is performed via configuration settings in new SymmetricDS configuration tables, in a manner consistent with and familiar to existing SymmetricDS users. [Less]

SymmetricDS has for years been a robust, reliable solution for synchronizing data between multiple databases and multiple database platforms. With SymmetricDS 2.4, SymmetricDS has expanded to also prove data transformation capabilities while ... [More] synchronizing. The data transformation is performed via configuration settings in new SymmetricDS configuration tables, in a manner consistent with and familiar to existing SymmetricDS users. This article will provide an overview of the configuration and settings for data transformation and will demo a few simple examples of data transformation in action. There is also additional detail available in the latest SymmetricDS User's guide. Overview The data transformation feature was first added to open-source SymmetricDS in release 2.4 as a result of requests from the user's community for this functionality. The SymmetricDS Pro version has also been updated to provide simple GUI configuration of transformations. Data transformation still relies on SymmetricDS configuration to capture a change, of course. If you haven't configured a SymmetricDS trigger / router to capture the change on the source, the transformation will obviously not occur. The source trigger creates the synchronization data, while the transformation configuration decides what to do with the synchronization data as it is being extracted from the source or loaded into the target. Some examples of transformations that can now be accomplished through configuration include: Copy a column from a source table to two (or more) target table columns, Merge columns from two or more source tables into a single row in a target table, Insert constants in columns in target tables based on source data synchronizations, Insert multiple rows of data into a single target table based on one change in a source table, Apply a Bean Shell script to achieve a custom transform when loading into the target database. Many more complex transformation scenarios can also be performed as a result of the flexible configuration settings. Flexible Configuration The data transformation engine offers a great deal of flexibility in terms of defining when and how a transformation occurs. Configuration starts by defining a source table and target table for the transformation. At this level, you can then set a number of options on how the transformation is to occur. For example, you can: define whether the data transformation logic will run at data extract time or data load time, define the order in which this particular transformation occurs relative to the other defined transformations that might run for a given data change, define the behavior that occurs in the case of a Delete DML type on the source, define whether Insert DML types should attempt to perform an update first As eluded to in the above list, transformation behavior is based on the original DML type that resulted in the data capture originally. In other words, if a data change was captured that was caused by an Update, SymmetricDS will attempt to perform the transformation by creating one (or more) update statements at the target. So, generally speaking, updates on the source resulted in updates on the target; inserts result in inserts, and deletes result in, well, it depends. In the case of Delete, you have the flexibility to (a) do nothing, (b) delete a corresponding target row, or (c) set one or more columns in the target to a predefined value. In the last case, consider the following example: you might want a delete of an employee row in a source database to cause an "active/inactive" flag to be set to inactive in the target instead of deleting the row in the target. For each source table / target table pair, you can then define transformation behavior at the column level, specifying both the source column name and target column name. You can even vary the column-level transform behavior based on the DML type itself, allowing you to specify different sets of columns in the case of an insert versus an update, for example. You can also specify the relative order that column-level transforms occur, along with a "transform type" specifying how the data is actually altered, if needed (a copy of the column data is the default operation). One additional note before we do some examples. Keep in mind that all transformations occur "in line"; that is, as SymmetricDS is processing the change data that was captured. Each of your transformations are performed as independent operations in sequence and must be "complete" from a SQL perspective. In other words, you must define columns for the transformation that are sufficient to fill in any primary key or other required data in the target table if the source operation was an Insert, for example. Transformation Examples With that, let's code a simple real-world example. Assume for the moment you run a succesful dentistry practice that has expanded to have more than one location. As such, you use SymmetricDS to synchronize a "corporate" central office database with a database at each of your dental clinics. In this way, you can keep patient data, schedules, etc, synchronized between the central office and each dental location. Unfortunately, the software you use in the central office doesn't have quite the same database schema as that present in your dentistry office locations. In particular, let's say that, in the central office, all patient information is kept in a single, denormalized table called "customer". Each patient has a row, with a unique id, first and last names, email address, etc. However, in the dental office locations, patient data is kept in two tables: A master table (called 'patient') contains the id, first name, and last name, but email addresses are kept in a separate 'patient_contact' table, containing an id, contact type (a fixed string of 'email', or 'phone', etc.) and a value to hold the information. Create statements for these sample tables are as follows (my source and target databases for this example are H2): create table customer ( id integer not null, first_name varchar2(100), last_name varchar2(100), email varchar2(100), primary key (id)); create table patient ( id integer not null, first_name varchar2(100), last_name varchar2(100), primary key (id)); create table patient_contact ( id integer not null, contact_type varchar2(10) not null, contact_value varchar2(100), primary key (id, contact_type)); Inserts into the central office should create two insert statements in the dental office database, and updates would update the appropriate columns and rows. For deletion of patients in the central office, the delete of the patient row in the central office should result in a delete of the corresponding rows in both tables at the dental office. As far as SymmetricDS basic setup is concerned, this is a pretty typical two-tier node network. We'll use a node group of "server" for the corporate central office server, and "client" for the dental office location databases, with the clients pushing data to the server and the clients pulling data from the server. We'll also configure two routers, one for each direction (although our example will focus on server to client (central office to dental office locations) synchronizations only). Before we configuration the data transformation, we need a trigger on the customer data that sends the data to the client. I've used SymmetricDS Pro's ability to auto-create triggers based on database metadata and configured a trigger for our customer data for sending data to the client. If you are using the open source version, this corresponds to creating a row in sym_trigger, sym_router, and sym_trigger_router. So, how would we configure the transformations to achieve these goals? For the data transformation, we need three columns from customer to map to patient and want a delete action to delete the corresponding row. Set-up of this transformation using SymmetricDS Pro is quite easy. We defined a single transform id, customer2patient, mapping us from customer to patient tables, and fill in three source/target columns, being careful to define id as the primary key. We use the default transformation type of "copy", since we wish to copy the values to the target unchanged. If you are using the open-source version of SymmetricDS, this is equivalent to creating one entry in sym_transform_table and three entries in sym_transform_column tables. Note that if your column data applies to all DML types (as in this case), you can use an "*" in the include_on column to represent all types. For the customer to patient_contact mapping, things are almost as straightforward. We define a transform mapping between the two tables, called customer2patientcontact. We map two columns (id to id and email to contact_value) but we also need to map a third target column, contact_type, which we need to be a constant value "email". So, we use a transform type of "const" (constant) and supply the constant value in the transform expression. We mark both id and email as part of the primary key so that the deletes and updates will operate only on the appropriate rows. Adding new dynamic data Let's extend our example slightly. Let's say, for example, that the 'patient' data table has a last_updated column that we wish to fill in whenever a transformation occurs. This column doesn't exist in the central office customer table, so we need to add the data dynamically as part of the transformation. We'll add the new column using the following SQL: alter table patient add column LAST_UPDATED datetime; Next, we simply add an additional column to the customer2patient transform to supply new data for the target, similar to our use of the "constant" transform type for email. This time, we'll take advantage of the "variable" transform type, which provides a few dynamically computed variables that can be placed into a target column. In this case, we'll specify "system_timestamp" to fill in the current data and time at the moment the transformation occurs. Summary The few quick examples above are really just a basic sample of the kinds of transformations you can configure. More complex examples, where the columns vary based on DML type, for example, are possible and easy to configure. For more details and a complete list of configuration options, please consult the SymmetricDS User's Guide, available at www.symmetricds.org [Less]

SymmetricDS has for years been a robust, reliable solution for synchronizing data between multiple databases and multiple database platforms. With SymmetricDS 2.4, SymmetricDS has expanded to also prove data transformation capabilities while ... [More] synchronizing. The data transformation is performed via configuration settings in new SymmetricDS configuration tables, in a manner consistent with and familiar to existing SymmetricDS users. This article will provide an overview of the configuration and settings for data transformation and will demo a few simple examples of data transformation in action. There is also additional detail available in the latest SymmetricDS User's guide. Overview The data transformation feature was first added to open-source SymmetricDS in release 2.4 as a result of requests from the user's community for this functionality. The SymmetricDS Pro version has also been updated to provide simple GUI configuration of transformations. Data transformation still relies on SymmetricDS configuration to capture a change, of course. If you haven't configured a SymmetricDS trigger / router to capture the change on the source, the transformation will obviously not occur. The source trigger creates the synchronization data, while the transformation configuration decides what to do with the synchronization data as it is being extracted from the source or loaded into the target. Some examples of transformations that can now be accomplished through configuration include: Copy a column from a source table to two (or more) target table columns, Merge columns from two or more source tables into a single row in a target table, Insert constants in columns in target tables based on source data synchronizations, Insert multiple rows of data into a single target table based on one change in a source table, Apply a Bean Shell script to achieve a custom transform when loading into the target database. Many more complex transformation scenarios can also be performed as a result of the flexible configuration settings. Flexible Configuration The data transformation engine offers a great deal of flexibility in terms of defining when and how a transformation occurs. Configuration starts by defining a source table and target table for the transformation. At this level, you can then set a number of options on how the transformation is to occur. For example, you can: define whether the data transformation logic will run at data extract time or data load time, define the order in which this particular transformation occurs relative to the other defined transformations that might run for a given data change, define the behavior that occurs in the case of a Delete DML type on the source, define whether Insert DML types should attempt to perform an update first As eluded to in the above list, transformation behavior is based on the original DML type that resulted in the data capture originally. In other words, if a data change was captured that was caused by an Update, SymmetricDS will attempt to perform the transformation by creating one (or more) update statements at the target. So, generally speaking, updates on the source resulted in updates on the target; inserts result in inserts, and deletes result in, well, it depends. In the case of Delete, you have the flexibility to (a) do nothing, (b) delete a corresponding target row, or (c) set one or more columns in the target to a predefined value. In the last case, consider the following example: you might want a delete of an employee row in a source database to cause an "active/inactive" flag to be set to inactive in the target instead of deleting the row in the target. For each source table / target table pair, you can then define transformation behavior at the column level, specifying both the source column name and target column name. You can even vary the column-level transform behavior based on the DML type itself, allowing you to specify different sets of columns in the case of an insert versus an update, for example. You can also specify the relative order that column-level transforms occur, along with a "transform type" specifying how the data is actually altered, if needed (a copy of the column data is the default operation). One additional note before we do some examples. Keep in mind that all transformations occur "in line"; that is, as SymmetricDS is processing the change data that was captured. Each of your transformations are performed as independent operations in sequence and must be "complete" from a SQL perspective. In other words, you must define columns for the transformation that are sufficient to fill in any primary key or other required data in the target table if the source operation was an Insert, for example. Transformation Examples With that, let's code a simple real-world example. Assume for the moment you run a succesful dentistry practice that has expanded to have more than one location. As such, you use SymmetricDS to synchronize a "corporate" central office database with a database at each of your dental clinics. In this way, you can keep patient data, schedules, etc, synchronized between the central office and each dental location. Unfortunately, the software you use in the central office doesn't have quite the same database schema as that present in your dentistry office locations. In particular, let's say that, in the central office, all patient information is kept in a single, denormalized table called "customer". Each patient has a row, with a unique id, first and last names, email address, etc. However, in the dental office locations, patient data is kept in two tables: A master table (called 'patient') contains the id, first name, and last name, but email addresses are kept in a separate 'patient_contact' table, containing an id, contact type (a fixed string of 'email', or 'phone', etc.) and a value to hold the information. Create statements for these sample tables are as follows (my source and target databases for this example are H2): create table customer ( id integer not null, first_name varchar2(100), last_name varchar2(100), email varchar2(100), primary key (id)); create table patient ( id integer not null, first_name varchar2(100), last_name varchar2(100), primary key (id)); create table patient_contact ( id integer not null, contact_type varchar2(10) not null, contact_value varchar2(100), primary key (id, contact_type)); Inserts into the central office should create two insert statements in the dental office database, and updates would update the appropriate columns and rows. For deletion of patients in the central office, the delete of the patient row in the central office should result in a delete of the corresponding rows in both tables at the dental office. As far as SymmetricDS basic setup is concerned, this is a pretty typical two-tier node network. We'll use a node group of "server" for the corporate central office server, and "client" for the dental office location databases, with the clients pushing data to the server and the clients pulling data from the server. We'll also configure two routers, one for each direction (although our example will focus on server to client (central office to dental office locations) synchronizations only). Before we configuration the data transformation, we need a trigger on the customer data that sends the data to the client. I've used SymmetricDS Pro's ability to auto-create triggers based on database metadata and configured a trigger for our customer data for sending data to the client. If you are using the open source version, this corresponds to creating a row in sym_trigger, sym_router, and sym_trigger_router. So, how would we configure the transformations to achieve these goals? For the data transformation, we need three columns from customer to map to patient and want a delete action to delete the corresponding row. Set-up of this transformation using SymmetricDS Pro is quite easy. We defined a single transform id, customer2patient, mapping us from customer to patient tables, and fill in three source/target columns, being careful to define id as the primary key. We use the default transformation type of "copy", since we wish to copy the values to the target unchanged. If you are using the open-source version of SymmetricDS, this is equivalent to creating one entry in sym_transform_table and three entries in sym_transform_column tables. Note that if your column data applies to all DML types (as in this case), you can use an "*" in the include_on column to represent all types. For the customer to patient_contact mapping, things are almost as straightforward. We define a transform mapping between the two tables, called customer2patientcontact. We map two columns (id to id and email to contact_value) but we also need to map a third target column, contact_type, which we need to be a constant value "email". So, we use a transform type of "const" (constant) and supply the constant value in the transform expression. We mark both id and email as part of the primary key so that the deletes and updates will operate only on the appropriate rows. Adding new dynamic data Let's extend our example slightly. Let's say, for example, that the 'patient' data table has a last_updated column that we wish to fill in whenever a transformation occurs. This column doesn't exist in the central office customer table, so we need to add the data dynamically as part of the transformation. We'll add the new column using the following SQL: alter table patient add column LAST_UPDATED datetime; Next, we simply add an additional column to the customer2patient transform to supply new data for the target, similar to our use of the "constant" transform type for email. This time, we'll take advantage of the "variable" transform type, which provides a few dynamically computed variables that can be placed into a target column. In this case, we'll specify "system_timestamp" to fill in the current data and time at the moment the transformation occurs. Summary The few quick examples above are really just a basic sample of the kinds of transformations you can configure. More complex examples, where the columns vary based on DML type, for example, are possible and easy to configure. For more details and a complete list of configuration options, please consult the SymmetricDS User's Guide, available at www.symmetricds.org [Less]