Editor's Note: In this month's Reader to Reader section, Xingguang Ou, second runner-up in SQL Server Magazine's 2003 Innovator Awards contest, shares his winning solution. Xingguang is a DBA for CallPlus Ltd. in Auckland, New Zealand.
Replicating to Another Database
Our company uses SQL Server for information management in our ISP and most of our business applications, and we use MySQL on a Linux platform for our Remote Authentication Dial-In User Service (RADIUS), Web, and mail servers. We ran into difficulties because we needed to push data from the SQL Server database to the MySQL database, but the source table structures and data are different from those of the destination tables. The original solution was to bulk copy data from SQL Server views to text files and populate the MySQL tables with those text files. This operation ran every 15 minutes to refresh the database to reflect any changes. We had two main problems with this solution. First, changes weren't effective until the next refresh. Second, we risked losing all data in a MySQL table if errors occurred during the process-for example, if the table cleared but failed to be populated.
I created a new solution that features live replication of data changes and clears the destination tables only once-the first time I set up replication. I use triggers to capture the data changes in the source tables and to produce a set of transactions and commands stored in SQL Server tables to apply to the MySQL database. A replication engine running as a Windows NT service, similar to a SQL Server Distribution Agent, picks out the commands and delivers the updates to the MySQL database. For initializing replication to MySQL, I have a process similar to the SQL Server Snapshot Agent. This process bulk copies all the data to text files and produces MySQL commands to delete all rows in the MySQL tables and load the text files. The benefits of this solution include nearly instant response to any change in RADIUS, Web, and mail information on SQL Server, improved help-desk efficiency, and improved ISP service.
