Replication 

Replication allows a powerful multi-location database which can also tolerate intermittent lost connections.  Multiple servers are set up, and each one has an identical database.  All the servers are then replicated to each other.  MySQL can perform daisy chain circular replication.  Any of the servers may be temporarily disconnected from the internet for a time, and will continue to function normally.  When reconnected, the replication synchronizes all the new data.

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 two-way replication on a live database 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.  1/11/2012 We are not yet specialists at keeping replication running.  We don't have any suggestions about which monitoring software to use, but we are looking hard. We are also considering alternatives such as this.

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.  But it makes sense not to use ranges that are too large because then the keys can be difficult to work with 100,000,000 is a good key range size.  Also, JavaScript has 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 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.

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, 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.

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.

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.

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.  Learn how to use the monitor tool.

See Symmetric DS.