Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
austegard
Starting Member
2 Posts |
 Posted - 2003-02-27 : 11:57:37
|
| We have a classic 3 tier system setup with a development database, a qa database and a production database. So far, our production db has not undergone too many changes, and so any changes needed have been applied manually.We're now looking at applying changes to production on a fairly regular basis, as we're releasing our app and will be adding features post release. We therefore need to keep the data of the production db, but update the schema to match that of the development db. Any static lookup tables also need to have their data updated to match that of the development db. Of course everything is tested first on the qa server, so we're really talking about moving from qa to prod, rather than straight from dev to prod.Is there any set of best practices out there regarding this? It seems like an incredibly standard situation, but I have yet to find a whole lot of information on the issue.Any help is appreciated,ThanksOskar Austegard |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-02-27 : 12:04:48
|
| In my experience it's also incredibly tedious. We do several things to assist in this (our setup is identical to yours). First, during development we do our own things (different developers) on the dev box and keep everything in Visual Source Safe. When the developer has completed their change(s) the also move everything to test (qa). While doing that we have a single working script that contains any scripts for data changes, structure changes, etc. When it's time to move to production 2 of us schedule to come in around 3 am, run compares of views, procedures, functions from test and production using Redgate SQL Compare. Now we have two sources of things that need to be change - structures and data as well as code. We apply the structure and data change script then manually take each view, procedure and function from VSS and run it on production. Once that is done we do another compare against test to make sure nothing was missed and if all is well perform a couple admin tasks (backups, etc) and go home at noon. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-27 : 12:30:43
|
| I also do pretty much what tfountain says that they do at his company. I use SQLCompare to compare the database objects between development and QA. If there are changes that have been made and did not get into Visual Source Safe, then I contact the developers to get their changes into VSS. As far as lookup tables though, we don't normally update these since they are very static. When they do get updated though, a developer will contact me to let me know which table(s) and then I'll either write a script to update the data (you know an insert or something like that if the changes are very small) or I'll bcp the data out and then bcp them in on the QA. Once everything has been blessed on the QA server, we then use the same scripts that we used to updated QA and then deploy it to PROD. |
|
|
|
|
|
|
|
|
Best Practices for moving from dev to production?