Data dictionary SQLServer versions
-
You never got problems that a client updated the software (adding columns and tables) and after a crash restored a database from before the update? How would you prevent this, other than checking the structure?
Regards ... OttO
OChristiaanse wrote:
You never got problems that a client updated the software (adding columns and tables)
Normally the systems I work on do not allow that. Not sure I have worked on any that did that.
OChristiaanse wrote:
and after a crash restored a database from before the update?
If they added them and failed to restore them then that would be their problem.
OChristiaanse wrote:
How would you prevent this, other than checking the structure?
I would start by not allowing that in the first place. The first possible solution to allow for user defined data is to provide a meta data structure in the database, via appropriate tables, rather than allowing users to add their own. Other than that how do you prevent them from adding something you don't know about? If you don't know about it no solution you come up with will allow you to detect if it is missing.
-
OChristiaanse wrote:
You never got problems that a client updated the software (adding columns and tables)
Normally the systems I work on do not allow that. Not sure I have worked on any that did that.
OChristiaanse wrote:
and after a crash restored a database from before the update?
If they added them and failed to restore them then that would be their problem.
OChristiaanse wrote:
How would you prevent this, other than checking the structure?
I would start by not allowing that in the first place. The first possible solution to allow for user defined data is to provide a meta data structure in the database, via appropriate tables, rather than allowing users to add their own. Other than that how do you prevent them from adding something you don't know about? If you don't know about it no solution you come up with will allow you to detect if it is missing.
(was away for a few days) Sorry, miscommunication: I meant to say: The software was updated, and in the update proces columns and tables are added, and sometimes the content of some tables will be altered. The client doesnt add the columns etc, only performs the update of the software. The client restores an older database. And as result of that, the application crashes after a while, for instance a month later. And I agree: it is the problem of the client, not ours in the first place. The problem is that the updatertool hasn't updated this backupdatabase. Because of this: - a month work has been added in a not so healthy environment X| - corruption/inconsistencies in the db are possible. Nevertheless, the client will come to us with questions like: - Why didn't you prevent this from happening (thats why we added a startup dbstructure check:cool:) - Can you fix this (yes of course we can try, but that will cost a lot of time, and you don't get any garantees). - Can you garantee that no work has been lost (no ofcourse not... If you hadn't restored a prehistoric database, and the software isn't meant to work with that structure...) I can't inmagine that software packages like Exact, etc don't perform somesort of db check at startup. But maybe I'm to optimistic...
Regards ... OttO
-
(was away for a few days) Sorry, miscommunication: I meant to say: The software was updated, and in the update proces columns and tables are added, and sometimes the content of some tables will be altered. The client doesnt add the columns etc, only performs the update of the software. The client restores an older database. And as result of that, the application crashes after a while, for instance a month later. And I agree: it is the problem of the client, not ours in the first place. The problem is that the updatertool hasn't updated this backupdatabase. Because of this: - a month work has been added in a not so healthy environment X| - corruption/inconsistencies in the db are possible. Nevertheless, the client will come to us with questions like: - Why didn't you prevent this from happening (thats why we added a startup dbstructure check:cool:) - Can you fix this (yes of course we can try, but that will cost a lot of time, and you don't get any garantees). - Can you garantee that no work has been lost (no ofcourse not... If you hadn't restored a prehistoric database, and the software isn't meant to work with that structure...) I can't inmagine that software packages like Exact, etc don't perform somesort of db check at startup. But maybe I'm to optimistic...
Regards ... OttO
I'm confused as to what the issue is here. The normal way to solve this issue is to have a decent backup solution implemented on the server. Why is this not appropriate for your problem?
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
-
I'm confused as to what the issue is here. The normal way to solve this issue is to have a decent backup solution implemented on the server. Why is this not appropriate for your problem?
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
My questions are: Does your app check the db structure? And how? And how do you deal with a change of database version like from SQLServer 2000 to 2008R2? How do you take advantage of datatypes like VarChar(max) which is available in 2005 and further, but not in 2000, without forcing all clients to migrate to 2008R2? I then got a question 'why would you check the db structure?' upon which I described a not so fictive situation WHY I would want to check the db structure. See my previous post. I've seen this kind of situations numerous times in the past, most of the time here in the house: 'I have a db, don't know the version, but it works' X| a few hours later: 'oh no, it doesn't work' Backup's aren't the problem. Stupid users and ignorant developers are.
Regards ... OttO
-
My questions are: Does your app check the db structure? And how? And how do you deal with a change of database version like from SQLServer 2000 to 2008R2? How do you take advantage of datatypes like VarChar(max) which is available in 2005 and further, but not in 2000, without forcing all clients to migrate to 2008R2? I then got a question 'why would you check the db structure?' upon which I described a not so fictive situation WHY I would want to check the db structure. See my previous post. I've seen this kind of situations numerous times in the past, most of the time here in the house: 'I have a db, don't know the version, but it works' X| a few hours later: 'oh no, it doesn't work' Backup's aren't the problem. Stupid users and ignorant developers are.
Regards ... OttO
But you talked about the user recovering from a catastrophic database failure by reinstalling the database. That is not the way out of the problem. Being able to seamlessly recover from a backup is the way to bypass this issue.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
-
But you talked about the user recovering from a catastrophic database failure by reinstalling the database. That is not the way out of the problem. Being able to seamlessly recover from a backup is the way to bypass this issue.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
Ofcourse, I agree on that. Nevertheless: how do you signal all kinds of mischief? (the described situation is only a example, maybe not as realistic in a live situation at a clients site.) How do you deduce that a database of a 'strange' origin is attached to your application? Without crashing the app on some non-descriptive error like 'error in query'?
Regards ... OttO
-
Ofcourse, I agree on that. Nevertheless: how do you signal all kinds of mischief? (the described situation is only a example, maybe not as realistic in a live situation at a clients site.) How do you deduce that a database of a 'strange' origin is attached to your application? Without crashing the app on some non-descriptive error like 'error in query'?
Regards ... OttO
Ahh, I see what you're getting at. What we do is have a version table in the database which we query to determine whether or not the latest version is installed. This table is kept up to date by the installation scripts and the version must match the version number we keep in the application configuration.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
-
Ahh, I see what you're getting at. What we do is have a version table in the database which we query to determine whether or not the latest version is installed. This table is kept up to date by the installation scripts and the version must match the version number we keep in the application configuration.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
Thanks for sharing. :) We are considering the same at the moment. That's one vote extra in that direction.:thumbsup: So, you don't check the db structure? Do you use different database versions (SQLServer 2000, 2005, 2008R2, maybe beside those Oracle, MySQL etc)? How do you get the most of them from within the same application? I think I will go for a sort of abstract factory pattern to support the different possibilities of the SQLServer versions. Other database types aren't in the picture at the moment fortunately.
Regards ... OttO
-
Thanks for sharing. :) We are considering the same at the moment. That's one vote extra in that direction.:thumbsup: So, you don't check the db structure? Do you use different database versions (SQLServer 2000, 2005, 2008R2, maybe beside those Oracle, MySQL etc)? How do you get the most of them from within the same application? I think I will go for a sort of abstract factory pattern to support the different possibilities of the SQLServer versions. Other database types aren't in the picture at the moment fortunately.
Regards ... OttO
We have a custom data provider that sits at the back end that allows us to swap between the vendors. We can't use something like nhibernate because we switch between different geo functionality between database vendors. This wraps up a lot of the standard geocoding stuff for us.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
-
We have a custom data provider that sits at the back end that allows us to swap between the vendors. We can't use something like nhibernate because we switch between different geo functionality between database vendors. This wraps up a lot of the standard geocoding stuff for us.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
Thanks for the info.
Regards ... OttO
-
Thanks for the info.
Regards ... OttO
No problem. Glad to help.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
-
(was away for a few days) Sorry, miscommunication: I meant to say: The software was updated, and in the update proces columns and tables are added, and sometimes the content of some tables will be altered. The client doesnt add the columns etc, only performs the update of the software. The client restores an older database. And as result of that, the application crashes after a while, for instance a month later. And I agree: it is the problem of the client, not ours in the first place. The problem is that the updatertool hasn't updated this backupdatabase. Because of this: - a month work has been added in a not so healthy environment X| - corruption/inconsistencies in the db are possible. Nevertheless, the client will come to us with questions like: - Why didn't you prevent this from happening (thats why we added a startup dbstructure check:cool:) - Can you fix this (yes of course we can try, but that will cost a lot of time, and you don't get any garantees). - Can you garantee that no work has been lost (no ofcourse not... If you hadn't restored a prehistoric database, and the software isn't meant to work with that structure...) I can't inmagine that software packages like Exact, etc don't perform somesort of db check at startup. But maybe I'm to optimistic...
Regards ... OttO
OChristiaanse wrote:
The software was updated, and in the update proces columns and tables are added, and sometimes the content of some tables will be altered. The client doesnt add the columns etc, only performs the update of the software.
I solve that all the time. I have a table like: db_version. It has columns like: name and version. Name is a text value. Version has a format like {number}.{number}.{number} (text too) A software 'update' that applies a database update adds one or more rows to that table. Software that uses the database either relies on a specific name/version pair or it relies on a minimum name/version pair. When the software starts it validate the match or minimal versions that it expects. If they don't match then the software issues an error and exits. The name column exists to allow sub functional dependency checks rather than just a global version.
-
OChristiaanse wrote:
The software was updated, and in the update proces columns and tables are added, and sometimes the content of some tables will be altered. The client doesnt add the columns etc, only performs the update of the software.
I solve that all the time. I have a table like: db_version. It has columns like: name and version. Name is a text value. Version has a format like {number}.{number}.{number} (text too) A software 'update' that applies a database update adds one or more rows to that table. Software that uses the database either relies on a specific name/version pair or it relies on a minimum name/version pair. When the software starts it validate the match or minimal versions that it expects. If they don't match then the software issues an error and exits. The name column exists to allow sub functional dependency checks rather than just a global version.
Thanks, your extra vote no.2 for this approach.
Regards ... OttO