My problem is that we need a method of updating the database without messing the data, or crashing the website.
I am sure this is a common problem with a number of solutions. Couldanyone please direct me to a good article on the best practices forupdating databases like this?
What do you mean by updating the database? Just inserting records o chaging its structure?
|||Anything. Inserting records is not a problem. It is changing the structure which is.
When I am developing, I dont want to be working on the live database.
But if I copy the database and edit the copy, what happens to any new data on the live database?
Hope that is not confusing.
|||When you make updates to the structure of the database you should consider whether it affects the behaviour of the application or not. If it doesn′t you can make the update at anytime (But it will be better to schedule it when there is no heavy traffic). If it affects the application, the update should be accompanied with an update in the application. In this case you should schedule a maintainance stop (obviously the users should be adviced with anticipation), and use that time to update everything, the application, the database, configurations, etc. I also work for a huge company with a lot of servers, databases and web applications running, and this is the way we behave.
|||
I was just wondering what the best approach to this kind of thing is.
For example, lets say I have version 1.0 of an application with a db backend. Now, when I start creating version 2, the application and db will change. Do I :
1. Edit the live db making extra care that version 1.0 is not affected by the changes. This has its obvious problems - one mistake and the whole application goes down.
2. Create a copy of the db, edit that, and then link that in with version 2 when it is ready. The problem with this is that, between the time ot copying the db and releasing version 2.0, the users are still using version 1.0 of the db. I would have to then copy that over. This isnt a problem with minor changes, but it is when I start changing/creating keys and constraints (especially in sql server).
So I was wondering what the industry works around creating the next version of software without corrupting the current version.
I know there is no right answer - it depends on the situation. But any guidence would be appriciated.
jagdipa wrote:
I know there is no right answer - it depends onthe situation. But any guidence would be appriciated.
I don't know that I agree with that statement. I think there is aright answer. It is called the Development, Staging, Productionmodel (DSP). The Staging database starts out as a mirror image ofthe Production database. Update scripts are run and tested on theStaging database until the consistent desiredresult is reached, with the Staging database being restored with abackup of theProduction database before each cycle of tests. This way, whenyou are ready to go live with your changes, you simple run the updatescripts that have been tested and perfected on the Staging databaseagainst your Production database.
Check out these links:
The Development, Staging, and Production Model
Setting up a DSP Environment -- look especially at the Managing Database Development section, and the Staging Environment section
|||Brilliant. Exactly what I was looking for.
I am just wondering if DSP is a standard method the industry uses, and are there other methods applied to this problem?
Again, thank you for the guidance
Jagdip
|||Just wondering if you have any more links about DSP?
|||Hi Jagdip, I was hoping someone else might chime in. I have noidea if the DSP approach is the industry standard, but it's the methodI've come to employ as a best practice for myself (in ideal situationsat least, sometimes some of the steps have been shortcut) and it is theway I've noticed some of my peers have worked. I've only recentlypicked up on the term "DSP" to be honest. When I read thedescription of the acronym I said to myself, "oh, I didn't know therewas a term appplied to this approach".
I just Googled a bit more and came up with this, which uses the DSP approach without using the term:
Migration to Production
I don't have many further resources for you, I'm afraid. The keyis to make your rollout to production consistently repeatable in yourstaging environment. This way when you go live you have minimizedyour risks as much as possible. I can't imagine an approach thatwould be better.
|||The only real problem we have is with updating the database. Writtingthe SQL scripts and saving then is a great way of keeping documentationon DB updates as well as keeping the existing data. Up until now, mycolleage has been working on the live database!!!!
I have had a word with him and he likes it. So thank you for theadvice. I'm in the process of writting and testing a procedure usingDSP, and I will have a look at the other article you gave me.
I guess the only real problem I have with DSP is that it will reallyslow down our RAD ideal. But the boss asked for something like this, sohe's going to have to live with it :-)
