Replication 

Replication is a technology built into MySQL that continuously keeps a slave database synchronized with its master.  To learn more, read chapter 16 in the MySQL 5.1 Manual regarding replication configuration.

The simplest configuration is One-Way Replication.  Anyone considering replication is encouraged to run this configuration for a number of months to get very familiar with the administration. This is for advacned users and should be set up by the support technicians at EZ2000 Plus Dental.

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 EZ2000 Plus 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 laptops to 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 a monitoring process in place.

Random Primary Keys
Random primary keys are used when you make changes to multiple replication servers.  By assigning unique key ranges to each server, you ensure there will be no conflicts when data is merged.  Once you start using random primary keys, you can never turn that feature off, but you can freely edit the ranges.  Using random primary keys is not a huge consideration, but you should be aware of the downside. Random primary keys do NOT need to be turned on for One-Way Replication. 

In the Main Menu, click 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.

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. EZ2000 Plus 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. We recommend ranges of at least 10,000,000 numbers. If smaller ranges are used, replication may become slow or unresponsive as the number of available primary keys runs out. This range recommendation 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 EZ2000 Plus Dental.
A to Z folders:  Each server can be assigned separate A to Z folders.  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 A to Z folders synchronized between locations is up to the customer and will require additional software and expertise.  If using different A to Z folders, when doing an update from the main office use a script to copy the contents of the Update Files folder to the other A to Z folders.  Microsoft has a free application named SyncToy 2.1 that may be useful.  See Online Backups.
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.  WARNING: 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 2000 Plus 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.5/"
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 Replication Setup or circular Daisy Chain Replication Setup.  Make sure to use Statement Based Replication, and NOT Row Based Replication.

Monitor
In daisy chain replication, each replication server has a monitor specified in the slave monitor field of the Edit Replication Server window. In the event of a replication failure, users will be told to connect to their database at the other location. See Replication - Slave Monitor. 

Update EZ2000 Plus Dental Versions
1.  Pick one replication server to always perform updates from, and enter the name of that server in Setup, Miscellaneous, Middle Tier Server Name. This will prevent the update from being run from any other computer. Refer to this server as your primary server.
2.  For all locations, coordinate a specific time to perform the update where all users will be out of EZ2000 Plus Dental for at least 60 minutes.
3.  When your scheduled update time has arrived, ensure that all users are out of EZ2000 Plus Dental. From your primary server, boot all users out of EZ2000 Plus Dental by using Tools, Misc Tools, Shutdown All Workstations. This step reminds users about the update and also closes EZ2000 Plus Dental on workstations that might be locked for employees who are on vacation or out sick.
4.  Ensure that all MySQL replication servers are running smoothly by running the SHOW SLAVE STATUS on each server. Do NOT continue if any slave status says that the Slave IO or Slave SQL is not running.
5.  From your primary server, download and install the new setup file, then connect to the database on the primary server to upgrade the database. Leave EZ2000 Plus Dental open and run Setup, Replication, Synch to make sure that all other replication servers receive the database update queries.
6.  The update is complete. Inform all employees that EZ2000 Plus Dental is ready for use.

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