How It Works — Panorama Enterprise Database Sharing


Curious about how Panorama’s unique distributed mesh data sharing system actually works? This page explains the details. (If you would like to see this technology in action, we’ve prepared two screencasts for you: Database Sharing and Web Publishing and Panorama Based Shopping Cart.)

Traditional client/server database systems (FileMaker, SQL, etc.) store the data only on the central server computer. Panorama, in contrast, keeps copies of the data on the server but also keeps duplicate copies on every client.

TraditionalVsEnterprise1

By distributing multiple copies of the data across the network, Panorama uncorks the network/server bottle- neck that hampers traditional client/server database systems. Distributing the data across the network has several key benefits over these traditional systems: high performance, low network/server load, offline operation and high safety margin.

We’ll use a series of diagrams to illustrate how distributed data sharing works and how data flows across the network. Our hypothetical network contains a server and four clients: Alan, Bob, Carl and Dave. For simplicity we’ll assume that these users are only sharing a single database, which we’ll simply call the database. We start with this database all set up and ready to use, with a copy of the database on the hard drive of each client and on the server as well.

01_AllClientsClosed


Opening a Database

The action begins as Alan double clicks on his copy of database to open it. Panorama loads the database into RAM, then contacts the server to request synchronization (more on synchronization in a moment).

02_AlanOpensTheDatabase


In response, the server loads its copy of the database into RAM. It then gathers the most recently modified data and sends it back to Alan’s computer.

03_AlanOpensTheDatabase


Alan’s computer takes these recent changes and updates its local copy of the database. Because Panorama is RAM based, this synchronization process is very rapid — often nearly instantaneous. Alan now has an up-to-date copy of the database available for searching, sorting, reports, etc. All of these operations are performed at blazing speed in RAM, just as it would be done if this was a single user database.

The Synchronization Process

How did the Panorama server know which changes were recent, and which changes Alan already had on his computer? And how did the Panorama client know how to merge this recently changed data with the data it already had? When a Panorama database is converted from single user to shared two invisible fields are added to each record in the database: ID and Time Stamp . The ID is a unique number that is assigned to the record when it is first created and never changes. The Time Stamp keeps track of when this record was last modified. (These extra fields are both invisible, and cannot be seen or modified by the user.) Panorama also keeps an overall time stamp for each database. This overall time stamp keeps track of when the database was last synchronized with the server. When a client requests synchronization, it passes this overall time stamp as part of the request. The server gathers all records with later time stamps (including any brand new records) and sends them back to the client. The client matches up the ID numbers to update existing records, and simultaneously adds the new records to complete the synchronization process.

A Note about Time Stamps: Time stamps are not really “clock times” in the ordinary sense of days, hours, minutes and seconds. Instead, this “time” is really a count of the total number of changes made to this database since the database was first created. Like a clock, this number always increases as time goes on. But since we don’t use a real clock, it doesn’t matter if the time is incorrectly set on one or more of your computers, or even if some of the computers are in a different time zone.

Editing and Record Locking

To edit a record, Alan double clicks a data cell in the record. Panorama sends a request to the server asking to edit the record (the request includes the records unique ID number). (Notice that we are assuming that Bob, Carl and Dave have now all opened this database, so the database is loaded into RAM on all four of these client machines.)

04_AlanEditsARecord


If no one else is currently editing that record, the server will respond that it is ok to edit the data. The server will also send a copy of the most up-to-date data for this record. Using this data, Panorama performs a “mini-synchronization” of just this record. This insures that the data in this record is completely up-to-date before Alan makes any changes.

05_AlanEditsARecord


Suppose that Carl double clicks on a data cell in the same record before Alan finishes editing it? Just like before a request is sent to the server.

06_AlanEditsARecord


But Alan still has this record locked, so Carl’s request is denied. Carl will see an alert suggesting that he try again later. Of course Carl can edit any other record in the database.

07_AlanEditsARecord


When Alan finishes editing the record (indicated by clicking on another record, clicking on another window, saving the database or simply being inactive too long) the freshly edited data is transmitted to the server.

08_AlanEditsARecord


The server updates its copy of the database and releases the lock.

09_AlanEditsARecord


Now suppose Carl tries to edit the same record again. This time his request will succeed.

10_AlanEditsARecord


As Carl double clicks on the data cell he’ll see Alan’s changes appear before he begins editing. In some cases this can be very important. For example, suppose this is an inventory database and Alan subtracted items from a particular inventory record. Now Carl wants to subtract additional items from the same record. It’s vital that if Alan subtracted 12 and Carl now subtracts 9, the final amount will be the original amount minus 21. Because the server updates each client as the record is locked, this correct total is assured no matter how many calculations are chained together. In this example we’ve assumed that the data is being edited manually, but the same principle applies when modifications are made by a program. Whenever a record is modified it, is always locked and updated first, whether that modification is manual or done by a program.

Taking Data On The Road (Offline Database Operation)

Mobile users can use their local copy of the database even when disconnected from the network. When mobile databases are reconnected to the network, the server and remote databases are automatically synchronized and reconciled with each other on a record-by-record basis. When Carl takes his laptop on the road he may not always be able to connect to the server back at the office. Even when he’s not connected, however, he can always open and view his local copy of the database. He can search, sort, even calculate summaries and print reports. Of course he won’t have the most up-to-date data, but in many cases that is much better than nothing.

11_Offline


The next time Carl connects to the office, even over the Internet, he can synchronize to bring his local copy back up-to-date.

12_Offline


(Of course as long as Carl is connected to the server he can work normally, editing data and synchronizing just as if he were back at the office.)

Modifying Data Offline

Can Carl edit his database when he is not connected to the server (off-line)? By default the answer is no. However, this ability can optionally be enabled when setting up the sharing options for any database. If it is enabled, Panorama will keep track of all of the changes Carl makes when he is off-line.

13_Offline


Of course since Carl is not connected to the server there is no record locking. You should leave off-line editing disabled for any databases for which record locking is vital to database integrity.

The next time Carl connects to the server (whether locally or over the Internet), the system will perform a two way synchronization. All of Carl’s new changes will be updated on the server, and all of the changes made by other users will be transferred to Carl.

14_Offline


Since there is no record locking when off-line, it’s possible that Alan, Bob or Dave may have changed some of the same records that Carl has changed while off line. When you set up the database options you can configure either the server or the client to have priority. As a third options, Carl can review the conflicting changes manually and “cherry pick” the final data on a field-by-field basis. Keep in mind that off-line editing is entirely optional. If you are concerned about database integrity you should consider leaving this option disabled.



            Benefits of Distributed (Mesh) Database Sharing



Distributing the data across the network has several key benefits over these traditional systems: high performance, low network/server load, offline operation and high safety margin.

High Performance


When sharing a Panorama database, each client keeps a full copy of the database in RAM on the client computer. Operations like searching, sorting and reports happen almost instantaneously in RAM with no network or server delay. The server only gets involved when data is modified (it handles record locking and updating other clients as necessary). (Traditional client/server systems, in contrast, channel every database action (query, sort, report, etc.) through the network and the server. The client is only used to display the results.)

Since Panorama is RAM based, it doesn’t need or use indexes. (Indexes are special tables that disk based databases use to speed up searching and sorting.) Eliminating indexes simplifies database design, allows more flexible searching and also dramatically reduces RAM and disk requirements on each client (Panorama databases are typically much smaller than the traditional data + index tables combination — in some cases up to 90% smaller).

Low Network/Server Load


Since the network and server are not involved in a majority of database actions, the load on these components is much lower than when using a traditional client/server system. Bottom line — you’ll be able to scale to more clients without upgrading your network or server hardware. Offline Operation Unlike a traditional client/server database, Panorama database clients don’t “go dark” if a network connection is unavailable. Panorama database sharing allows off-line database browsing and even modification (configurable on a per database basis). If allowed, offline changes are automatically synchronized with the server when the client re-connects to the network. Because both client and server are RAM based, this synchronization is extremely fast. If there is a potential conflict between the modifications made offline and the modifications made by other users, you will be notified and given the option to resolve the conflict manually. A Panorama database can even be configured to operate primarily offline. This is similar to the way e-mail works - users perform data entry offline, then press Submit or Connect to submit their data and receive updates. In the past applications like this had to be built from scratch, but Panorama database sharing allows this with little or no custom programming.

High Safety Margin


Engineers understand that critical structures like bridges and aircraft must be resiliently designed to eliminate the possibility of total failure. Your data is critical to you so the Panorama Enterprise Edition Server includes layered safeguards to protect every byte.

Zero-Loss Interruption Recovery


The Panorama server is RAM based for high performance, but it also keeps a disk based transaction journal for full data recovery after any kind of power failure or system interruption of any kind (the journal is a simple sequential file with minimal impact on performance). The server will automatically recover any unsaved data when the system reboots — no manual intervention is necessary.

Automatic Live Backups


The Panorama server includes a built in backup system that can perform automatic backup of "live" databases without shutting down the server. (Live backups are performed to another folder or hard drive, we highly recommend that you use a secondary backup system to regularly copy the backed up files to a remote location. The small file size of Panorama databases helps make this process smoother, especially if you are backing up over the Internet or other relatively slow network.)

Detailed Logging and E-mail Notification


The Panorama server keeps detailed, configurable logs of server activity and can also e-mail the administrator automatically if any kind of server error occurs (including reboots).

Rebuild from any client


In a worst case scenario Panorama’s distributed architecture provides an automatic built-in backup system. If your regular backup is unavailable (earthquake? hurricane? tsunami?) you can always rebuild the server database from the data in any client.

No delicate index to get corrupted


Since Panorama is RAM based, it doesn’t need or use indexes (see “High Performance” above). In addition to the excessive space they take up, indexes have another disadvantage — they are complex and fragile structures that are easily corrupted. Although a corrupted index usually doesn’t mean the data itself is lost, a database may be offline for a considerable time while waiting for an index to be rebuilt. Since Panorama doesn’t use indexes, the possibility of a corrupted index is eliminated.