how to push new developments to production environment?

Question

Once our web application is being used on production environment we have a really hard time updating it with enhancements or bug fixes. The main reason is an initial flaw in the design of our CMS system. We have to usually spent hours to update each prod environment and many times with errors and long hours of debugging. It looks like I am whining so let get back to the objective part of the discussion.

The flaw consist on how the content and programming features are stored in the database together. So basically how the data is display and the data itself is stored in the database. There are some file system changes but they are not content related. Also the application itself creates a lot of references that we cannot track in the database.

As you can see when the application is being used by the end-user and stakeholders, our development team has to publish the changes in production in a manual way.

One more issue is that the application dynamically changes the file system when the database is modified. So both our dev environment and the prod environment now are different and in order to sync them their data has to go, or the dev team has to go through a lot of hand work.

I can explain more or even give you some more examples. Please shoot me a comment if you need.

Answer 1

Automate the handwork. Note down carefully all the actions you need to perform in order to move the application from the development to the production environment, and create a script to run those actions automatically. For instance, the script will drop all the tables that contain application-specific data and create them again, based on the application's current needs.

Split the script into multiple parts to simplify testing and debugging. For instance, one part could be shell commands, and another SQL commands. Use ample logging and error checking. Make the scripts part of your application's development process, and put them under revision control.

Answer 2

Although this won't address all your issues, something you might find useful is to have a "one-click" set of scripts/batch file that refreshes your dev db to have the same structure as production and populates it with some sample data. (This is what we had in my last job, and it worked well for us)

Answer 3

We do all of our development, QA, and production deployments the same way, via a script. No hand poking what-so-ever in QA nor production. In our case we use Ant, but just because it gives us the functionality necessary to make all the changes. So, you need to get your build process under control and versioned as a script just like any other code.

Answer 4

I have definitely experience similar pains in the past... Here is how I overcame them.

1. Split out the data that is code (that is any data in the database that is not customer data but is essential to the functioning of the app. Pull all those tables into a seperate database.

2. Write SQL scripts that populate that data into the database. This will allow you to recreate the skeleton of your app without relying on production data or a backup.

3. Take as much of the data that doesn't truly dynamically change, and put it in property files and configuration files. Flat file storage for this kind of data is ideal. Its faster to read, and doesn't have to be maintained with the database.

Answer 5

It also sounds like you could use a good database compare tool. It may help you generate scripts that allow you to migrate between environments, etc.

If you have SQL Server, I hear Red Gate has good tools, the compare tool is called SQL Compare.

I am of the same opinion, you need to figure out how to automate the 'upgrade', either through an installer, a SQL script or custom .NET app to manipulate the database and 'upgrade'.

Answer 6

Additionally you might be interested in Liquibase. It's a opensource tool to track database data and schema changes and makes it easy to apply refactorings you performed on a dev db in the production environment. And it's DBMS (vendor) independend. I would also recommend to automate the steps to upgrade the prod deployment as mentioned before.