| Author |
Topic |
|
Corobori
Posting Yak Master
105 Posts |
 Posted - 2004-07-14 : 15:46:28
|
| I developped an application running as a standalone running MSDE SQL Server which has been installed on several PCs. We had to do some changes in the data structure and would like that when the application starts 1) Checking if some columns exists in database 2) If they don't create them. Is it a good practice ? How do you handle database structure change ?jean-lucwww.corobori.com |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-14 : 15:53:17
|
| Are you saying that all of your released dbs are not at the same version level?I always make changes in dev...and release a script to production....You should know what the database structure is...there shouldn't be any guess work....You can write a script to compare 2 databases to see whats different...I wouldn't write an automatic object changer though...sounds dangerous....Brett8-) |
 |
|
|
Corobori
Posting Yak Master
105 Posts |
Posted - 2004-07-14 : 17:28:19
|
| Yes they aren't at the same level. In this case I am not in traditional development and production environment, production is indeed "productionssss", my application is a standalone software running on different PCs, some not on a network, some in different location, etc. My problem is that we made some database structure changes and I would like to make sure when my users are going to start a new release I'll be sending to them that those changes will be applied. jean-lucwww.corobori.com |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-14 : 23:21:53
|
| So, all of your users have visibility to the outside world? This wouldn't work if not. If they do, you can have a job that checks periodically for updates. If they are available it could notify the customer. They could then choose to apply them or not. That shouldn't be that hard to setup.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-15 : 09:03:54
|
| I have a table in my database that stores various "registry" type info, and one row is the "version number". When the application connects it checks if the DB is "new enough" and if not says it needs updating. A separate applet does the update - it runs a script to get from the version found to the latest - i.e. one script per version number increment. However, to do this it needs a SysAdmin type connection, hence we don't try to get the application itself to do it, a "more priviledged" user has to log on using the Upgrade Applet.Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-15 : 09:19:17
|
quote:
Originally posted by Kristen
I have a table in my database that stores various "registry" type info, and one row is the "version number". When the application connects it checks if the DB is "new enough" and if not says it needs updating. A separate applet does the update - it runs a script to get from the version found to the latest - i.e. one script per version number increment. However, to do this it needs a SysAdmin type connection, hence we don't try to get the application itself to do it, a "more priviledged" user has to log on using the Upgrade Applet.Kristen
Really?Do your "service packs" become all inclusive, or do they have to be run in order to get to the next version level...Just think of the mess...and the debugging...Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-15 : 10:10:38
|
| Depends. ("Service Packs" is the right anaolgy). Generally they are sequential. We might do a small fix for one client, then another for another client, in which case the second requires the first to be pre-installed.The only real hassle is if we make two changes to a table - so we might get a Create Temp Table, Copy All Rows Across, Delete Old Table & Rename New One type scenario twice - which will be slow on a big table.Every DDL change that is made to the database is scripted and added to the current "Server Patch Script". When we get to a release point (either because of a specific client need, or because we got to "Next Biggie Release") I go through the file and make sure its optimal; and then start a new one ready for the next DDL updates.Separately we generate a bumper script of all the SProcs that have changed - so we do the DDL script(s) first, then the SProcs/Triggers Bumper Script. (We can easily generate a "All SProcs Since Version N" script by concatenating the appropriate .SQL files)For a major release we make a script that will go straight from LastMajorRelease to NewMajorRelease which cuts out the middle-man.General idea is that we have two major releases a year, and we encourage clients NOT to install "enhancement" updates inbetween.All clients have TEST and STAGING environments, and its largely up to them to sign off for acceptance.Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-15 : 10:16:39
|
| ...in the name of the father, son.....Good luck...Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-15 : 10:20:54
|
| Seems to work OK (been doing it a long time - up to patch 48 on the main product ...) but I'm open to ideas on better methods.Kristen |
|
|
|
|
Checking if column exist by code