Working with test server development files

[psuchad]

After 6+ years developing on a live database I am looking to switch to an offline development approach. However, I am finding that documentation on best approaches very hard to come by. So I am looking for recommendations from those currently using this model.

I have looked at the data separation model as a solution, however we a very organic database. Changes are made to layout, script, and tables constantly. We are also a 24 hour 7 day a week operation. There are very few days when a field is not modified or added to the table structure. Our current setup causes frequent record lockouts on the user side while data is changed. Also on our larger tables with close to a million records a change can take 5-10 minutes to complete before users can access the records again.

We have also had instanced where a table change caused a hard lock on the database, resulting in corruption issues. My plan on a development server is an attempt to avoid that.

Current Setup:

• Windows Server hosted solution using multiple files.
  
• The multiple files are used to separate data for two agencies under the same organization. They share the same central client file to store basic demographic information. Each agency then has their own case file linked to the client file.
  
• The agency then has multiple files such as case note, plans, etc. These are separated mainly due to high record counts and allows faster recovery during downtime.
  
• We typically have 60-100 concurrent users accessing the system.
  
• We have two developers making layout, script, and table changes at the same time, but are usually making changes in separate files.
  

Proposed Setup:

• Creation of a live server (server1) and development server (server2). These are two separate Windows Server machines.
  
• Changes are made to the development server and moved to the live server on a monthly basis.
  

Problems So Far:

• This model requires a lot of scripting to import data from the live (but offline) database to the development database.
  
• The development server has table occurrences linked to server2. Moving the file from server2 to server1 keeps the development connections. For example: table client.server1 is now showing data from data.server2 instead of a client.server1 -> data.server1.
  
• We also use a model that layouts and scripts are separated in corresponding agency files. Here is how that works: staff login to the system and it takes them to an organization wide file that includes the client table. From there they can either go to agency1 or agency2. When they make this selection, it is scripted to open that agency's file, perform a goto layout script, and close the organization file. This causes a problem where the script on organization.server1 would be set to open the script on agency1.server2 once the development files to moved to sever1. I have developed a workaround for this using the get ( HostName ) function.
  

So how do you work offline in your solution. I am open to any ideas. I am starting fresh with a rebuild in FileMaker 12, so nothing is off the table.

Edited May 4, 2012 by Lee Smith
unreadable font size