"Release" and "deployment" best practices

[HadererDirndl]

A question for the experienced programmers on how to "release" updated versions of the same data base to the users frequently:

I am working on expanding the functionality of an existing Access data base (.accdb), which is being used on a daily basis by 1-5 users (never concurrently). I want to provide the new functionality roughly every two weeks. I never do the new development work on the data base instance that is being used; instead, I make a copy of it and work on it offline. Then, every two weeks, I delete all the tables from the "new" version, import all the tables from the "existing" version into the new version as applicable. This cannot be the best way to do this:

1. I have to take the db offline while I do the imports, so nobody can do work that would then be lost. The unavailability of the data base is causing some productivity problems for my co-workers
2. This seems a pretty risky way to do it, because there is a lot of manual exporting and importing involved
3. One of my table has multi-value fields, which makes this extra difficult

I wonder if anyone has any "best practices" to share? Is there a better way to do this?

Thank you!

 

[pbaldy]

Search on splitting the database. Here's one link:

http://allenbrowne.com/ser-01.html

 

[HadererDirndl]

Thank you, I didn't realize that was what "splitting" deals with. I assume that wouldn't work with table schema changes though?

 

[pbaldy]

Correct. Typically those are rare, and you normally need to get people out of the back end while you make the changes.

 

[sneuberg]

Correct. Typically those are rare, and you normally need to get people out of the back end while you make the changes.

We haven't had that sort of luck. Nearly every new version we deploy requires changes to the backend which we don't have direct access to. When we deploy a new version the AutoExec runs codes that:

1. Checks a version number in the backend to see if the backend is up to date.
2. If the backend needs updating it checks for a lock file and if none exists it updates the backend.

Maybe you don't need to do this since you have direct access to the backend but it would minimized the system down time. After a while you will have a collection of code that modifies the backend in various ways and this becomes less difficult.

 

[pbaldy]

In my experience changes to the back end are not needed nearly as often as changes to the front. I do have clients that are remote. I typically send them a one-time db that makes the back end changes when they are required, rather than incorporate it into the front.

In-house I don't have the problem at all, since we use SQL Server. No need to get people out to make changes.

 

[sneuberg]

I do have clients that are remote. I typically send them a one-time db that makes the back end changes when they are required, rather than incorporate it into the front.

My sister wanted to do it that way but I argued that if we did it that way we could end up with clients using a frontend that wasn't compatible with the backend. With our clients you could count on that happening. You've never had any problems like this?

 

[pbaldy]

I haven't, but your way is probably more foolproof. I don't work with that many remote clients. Most of my work is for my employer, where I have complete control. I am surprised you have changes to the back end that often. Normally I'd suspect a denormalized database which needed new fields all the time, but I don't see you doing that.

 

[sneuberg]

I am surprised you have changes to the back end that often. Normally I'd suspect a denormalized database which needed new fields all the time, but I don't see you doing that.

The system is being developed incrementally which is probably a smart move by the client as they seem to have difficulty in expressing their requirements.

 

[The_Doc_Man]

we could end up with clients using a frontend that wasn't compatible with the backend.

I claim this as nothing special, but here is how my last big DB handled this case.

1. We had a BE/FE split.

2. The version number of the FE was stored in the startup form and we had all the usual things to prevent it coming up bypassing the startup form.

3. The BE technically didn't have a stored version - but we had a history table with a special flag that was set when we had a mandatory update for the FE. We had a query that came up with two results - the version number of the most recent entry in the history table - which was the current version; and the version number of the most recent entry in the history table for which the "mandatory update" flag was set - which was the oldest version of the FE that you could run.

4. Everything was fine if the FE's stored version matched the BE's table entry. We had rules on formation of the version / generation number so that you could have a FE version less than the most recent history entry but NOT less than the most recent "mandatory update" version entry.

As you might guess, if the BE had to change, we took down the BE and the FE to make the changes, then posted the new files and set up a "mandatory update" entry for that version number. On the other hand, if we had a FE-only change, we just broadcast a note to the users (and also displayed a warning banner on the opening form) to let them know that a new version was available.

Since our users had to be able to "see" the shared folder on our DB server, we just let them copy the newest FE when they got around to it, and we added some things to the opening menu's Form_Open routine to prevent it from allowing the FE to start up if you had an FE/BE mismatch OR if you were trying to directly open the shared copy (since of course it can ask the question CurrentDB.Name and get the fully qualified path of the file you just opened. If you opened the file from the shared folder, your session got tossed.