Try this solution for heterogeneous transactional database replication
Maintaining multiple database platforms is a way of life for most organizations. Each platform supports its own application type, but businesses almost invariably need to exchange data between different database platforms. SQL Server's built-in replication has limited support for other database platforms. HiT Software's DBMoto 6.6 solves the problem of cross-platform communication through real-time database replication between almost all popular database platforms, including SQL Server, IBM DB2, Oracle, Sybase ASE, SQL Anywhere, Cloudscape, MySQL, Informix, Ingres, PostgreSQL, Microsoft Access, Gupta SQLBase, Firebird, and Solid.
Crossing the Great Database Divide
Setting up DBMoto was quick and easy. The only hassle was the need to manually import the license key by navigating to a text file provided by HiT Software. I installed DBMoto on my SQL Server system; the installation process prompted me to select the SQL Server service as a dependency for the DBMoto replication service. DBMoto supports three types of replication:
- Refresh mode is essentially snapshot replication: DBMoto sends a point-in-time copy from the source to the target system.
- Mirroring mode is like transactional replication: Real-time changes are sent from the source to the target.
- Synchronization mode enables bidirectional replication between the source and the target.
In addition to replication, DBMoto can automatically convert data types as well as perform data transformations using Visual Basic .NET scripts. A grouping feature lets DBMoto intelligently handle common connections to remote databases.
Ready? Set. Replicate!
I tested DBMoto's replication between a SQL Server 2008 system and an IBM i running V5R4. No additional middleware was required. DBMoto installed all the necessary drivers to connect to the IBM DB2 for i database. Typically, for this sort of replication you need to install the System i Access software to connect to databases on the IBM i.
DBMoto was easy to use after getting over the product's unusual nomenclature. For instance, DBMoto describes replication configurations as metadata. You define replication by creating metadata. The metadata definitions can be stored in the built-in SQL Server CE database, or you can store DBMoto's configuration database on any of the supported database types. I stored my metadata definition on SQL Server 2008, but I had to manually create the database. The installation also asked if I wanted to run DBMoto as a service or an application. I selected to run it as a service.
DBMoto's replication is easy to set up. I created my replication configurations by running DBMoto's three configuration wizards. The Source Connection Wizard defines your source databases, and the Target Connection Wizard defines your target databases; you can define multiple source and target connections. When defining the connection to the IBM i, DBMoto prompted me to install the DBMotoLIB library on the IBM i. There was an option to create the IBM i library automatically; unfortunately, it didn't work. The manual upload and creation functions found in the DBMoto Help file let me complete the setup. When I selected SQL Server as a source connection, DBMoto automatically set up SQL Server as a replication distributor.
After defining the source and target, you use the Replication Wizard to set up the replication type, the source and target connections, the data mapping, and the schedule. To support transaction replication on the IBM i, DBMoto required journaling to be active for the files being replicated. Figure 1 shows the Replication Wizard's data mapping screen, which lets you do simple one-to-one mapping. You can use the toolbar to edit the mappings to differently named columns and optionally elect to perform transformations on selected columns.
You use the three wizards to create initial replication definitions. Thereafter, you can browse and monitor them using the DBMoto Enterprise Manager. To modify a definition, right click it in the DBMoto Enterprise Manager and change its properties.
Replication started on schedule, and I found that DBMoto performed well in my tests. I set up one mirroring replication from SQL Server to the IBM i and another from the IBM i to SQL Server. DBMoto automatically and correctly converted all the data types I worked with. DBMoto did not require any schema changes in the tables that it used. I had trouble getting my first replication started; however, a call with the HiT Software support staff quickly solved the problem. The representative was very knowledgeable and experienced with the product.
DBMoto is available in 32-bit and 64-bit versions. Select the version based on the platform where you want to run DBMoto; both versions can access databases on any of the supported databases, regardless of the CPU platform. DBMoto was easy to use, and it supports almost all of today's popular databases. If you're looking for a solution to your heterogeneous transactional database replication problems, DBMoto has the answer. A full-featured 30-day trial of DBMoto is available from HiT Software's website.