Replication 

Replication is a technology built into MySQL that continuously keeps a slave database synchronized with its master.  In the simplest configuration, the slave can be useful as a hot backup, running complex reports that would bog down the main server, or for pausing to make clean backups.  Anyone considering replication is encouraged to run this configuration for a number of months to get very familiar with the administration.

In a more complex configuration, the replication can form a ring.  All the databases together are referred to as a single virtual database.  In this configuration, each location can continue to function normally even if the internet connection is lost.  The data from the other locations will not be fresh, but an office typically doesn't care as much about that data.  Once the internet connection is restored, the replication quickly updates the database with current data.  This kind of replication is supported very well by Open Dental.

This also works well for mobile vans that service children or nursing homes. You want all patients in one database, but your network connection may be slow and intermittent, or you might only be able to connect to the network when you return from the field. So instead of the usual single server, you would have multiple servers, one for each mobile van. If you take a laptops to a nursing homes, then each laptop would be a standalone server.  The servers at each location have identical data and they stay in synch using replication.

If you are just taking your laptop home at night or want access from home, do not use replication.  Instead, consider a different Remote access strategy. Also, before using replication, see the Multiple Locations page, and make sure you understand all the alternative strategies listed there.

There are, of course, limitations to this solution. It takes a very skilled database administrator to keep the synchronization running smoothly and to properly handle a downed network.  Setting up the servers is time consuming and requires expertise that we might not be able to provide. Replication also requires that proper safeguards be put in place to monitor and repair any replication problems.  We have found that many offices tend to jump right into replication without having any recovery skills and without putting any monitor in place.

You cannot run replication on live databases without a monitor that immediately notifies all users and IT staff at the exact moment that replication crashes.  We cannot stress this enough.  We are not responsible for the damage done when databases continue to be used after an error crashes replication.  2/8/2012 Version 12.1 has new features to prevent usage of a database where replication has failed.  See the Monitor section further down on this page.

In the Main Menu, select Setup | Replication.

Check the box at the top to turn on random primary keys.  From now on, all of your primary keys in all your tables will be longer, making them not as user-friendly if we need to do troubleshooting. Once you switch to random primary keys which are needed for multiple server merging, you can never go back.  Using random primary keys is not a huge consideration, but you should be aware of the downside.

Edit a server by double click on an existing row, or click the Add button.

Description: Text.  Not actually linked to the Clinic table yet.
server_id: The database admin adds a server_id command to the my.ini file on each server.  See instructions further down for editing the my.ini file.  Each workstation can then use a query to identify which server it is connected to.  The interface above allows setting a key range for each server_id.
Range Start/End: The allowed range is extremely large. Open Dental uses 64 bit signed int types internally and in the database.  The allowed range is from 1 to 9,223,372,036,854,775,807.  This key range is so large that this solution can be scaled up without ever running out of keys, no matter how many clinics are connected.  Try to use ranges of about 10,000,000 each. This will result in keys that are a very manageable 8 digits long.  Also, JavaScript and other programs have a max int size of 9,007,199,254,740,992 (1000 times smaller), so it makes sense to stay under this value for future flexibility with other software and with a Web Version of Open Dental.
AtoZ images folder:  Each server can be assigned a separate AtoZ folder.  This allows the use of a folder in the local area network rather than one that's accessed across a VPN.  This path will also show in the Data Path window.  Keeping the AtoZ folders synchronized between locations is up to the customer and will require additional software and expertise.  If using different AtoZ folders, then when doing an update from the main office, it would be good if there was a script to copy the contents of the Update Files folder to the other AtoZ folders.
Update Blocked: If this box is checked, then no workstation connected to that server will be allowed to initiate an update. This is typically done for servers that are considered slave or peripheral. Use this option carefully.  It really will block the ability of the server to update database versions, and it's possible that this could prevent startup of the program in certain situations.
Slave Monitor: The specified computer will monitor the status of the replication process. If replication fails, this computer will be responsible for disabling access to Open Dental from all computers on this server until replication has been restored.

Changing the my.ini file
With the additional line for server_id, the my.ini file might now look like this:

[mysqld]
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/mysql/data/"
skip-innodb
server-id=2

Hints for successfully making the above changes:
1. Stop the MySQL service before editing.
2. In the my.ini file, it's written with a dash (server-id), even though the name of the variable is with an underscore (server_id).
3. If using Vista or Win7, run a text editor as Admin by right-click, Run as Admin. Then, from within the text editor, open the my.ini file to edit.
4. Restart the MySQL service after making the change.
5. Also see Mysql Server Variables.

Setting up the Servers
MySQL comes with its own replication capabilities which can be used for simple one-way or circular daisy chain replication.  We created a document which can help get you started: replication.rtf.  Make sure to use Statement Based Replication, and NOT Row Based Replication.

Monitor
Each replication server has a monitor specified in the slave monitor field in the Edit Replication Server window. In the event of a failure, users will be told to connect to their database at the other location. It is up to the administrators of the replication service to ensure that training is in place to make sure users know what to do when they see this message.

Trigger Rules: Open Dental is designed to detect replication failure. The reaction will be triggered according to the following rules:
1. Open Dental must be running on the designated monitor computer for that slave.
2. Triggered if replication fails or is stopped for any reason.  So do not stop replication unless Open Dental is shut down on the monitor.
3. Loss of internet connection will not trigger the reaction, and MySQL will gracefully continue replication where it left off when reconnected.
4. Stopping the MySQL service will cause other error messages in Open Dental, but will not trigger the reaction.

Technical Details:
1. The monitor polls the slave server every 10 seconds with the following query: SHOW SLAVE STATUS;
2. If the Slave_SQL_Running column does not equal "Yes", then the reaction is triggered as follows:
3. For example, if this is slave with server_id of 3, then:
UPDATE preference SET ValueString = '3' WHERE PrefName = 'ReplicationFailureAtServer_id';
4. All users are kicked out of Open Dental on all servers.
5. When users try to use Open Dental on the failed slave, they are told, "This database is temporarily unavailable. Please connect instead to your alternate database at the other location."

Get Us Involved:  Look in the replication log and find the query that broke replication.  If it was a custom query that you ran, then we do not need to know about it.  But if it is a query coming from Open Dental, then we need to know about it so that we can immediately repair it in the code. We have seen the following issues break replication:
1. "CREATE TABLE mytable".  This is the usual culprit.  At a minimum, it must always be prefaced by "DROP TABLE IF EXISTS mytable"  This applies equally to temp tables that you may create.  But even then, it's still dangerous because someone at another node might reference the same table at around the same time.
2. Error: "Duplicate entry '0001-01-01 00:00:00' for key IndexAckTime".  This was due to a corrupted index file.  Dropping and recreating the index solved the problem.

Repair: Once the reaction has been triggered, it must be repaired before any users can access that slave.
1. Stop MySQL on the slave.
2. On the master, go to Setup | Replication, and click the Clear button at the lower right.
3. Rename the database on the slave.
4. Copy the database from the master to the slave.
5. Wipe out all loose files in the slave's mysql data directory which do not reside in a subfolder, except for any files beginning with "ib" (e.g. iblog_file0, ib_logfile1, ibdata1).
6. Start MySQL and replication.
7. Open Open Dental.

Alternate Repair:  If the database is very large, then the above repair scenario may not be feasible.  If you have a deeper understanding of replication and expertise in restarting failed replication, then you might consider restarting replication and running all the queries below the failure point in the log.

MySQL also provides an enterprise monitor software that will graphically show your replication loop and will let you monitor the status of each server and how many seconds each one is behind its master.  While this is a very useful tool, it is also expensive.

Features that won't work
The following features will not work when using random primary keys and replication.  There are no immediate plans to add support.
- The Anesthesia feature was written by an outside developer and is not used by most dental offices.
- Language Translation.  It uses strings for primary key instead of int. And because English phrases are added automatically and frequently, it would be hard (but not impossible) to adapt it for use with replication.  As long as each computer is set in the Control Panel for English-US, this will not be a problem.
- The eCW bridge might not work. It assumes that various parameters are int32.
- The Public Health School table and County table use strings instead of ints for primary keys.  May end up with a few duplicates if not synching in real-time.  Unsure what consequences would be.  Probably more annoying than critical.
- EHR Measures and EHR Quality Measures makes use of temp tables in the database which will break replication.

Related Pages

Symmetric DS.

Replication Master Configuration