|
my app uses SQLServer 2000,2005 or 2008R2 depending on the client.
At the start the app checks the table structure StoredProcedures etc.
The description has been stored in XML.
All checks are designed for 2000. Few things have been changed to support 2005 etc.
Now I want to take advantage of more recent stuff like VarChar(max) instead of TEXT.
What is the best way to do this?
Easy maintainable and still keep support for the different versions (90% of our clients still use SQLServer 2000).
Any tools, tips, tricks in SQLServer or external?
Regards .... OttO
|
|
|
|
|
Depends on your app and how you access data.
I suspect that in some situations that if you change the structure of a database then you will need to change the app to correctly access it.
If you don't need to do that then no problem. If you do then either you don't make such changes or you will need to provide a value that lets your app know how to vary depending on the structure of your database.
It might be relevant also to keep in mind that someone might upgrade their database server but not update the database structure. Also what do you think will be in the impact of incremental upgrades over time if you keep adding flags to the app?
|
|
|
|
|
The apps (it is a conglomerate of multiple exes in various versions) accesses the DB via ODBC.
The db structure is upgraded for higher versions of the app via an 'upgrade' tool. The tool is kept up to date and recognizes the current version of each apps own tables. It knows to what version the upgrade must be done per app, and knows the steps. It can change the structure, update the content etc.
The column type, size, default value, 'null allowed' is checked by the apps at startup. same goes for StoredProcedures, functions and nessecary views.
Upgrading SQLServer version hasn't been a major issue in the past. Only some minor differences have been adressed like the change in 'default value'.
Users etc are allowed to add indexes, views etc, but they arent allowed to change the apps tables by adding or changing types of columns.
Our clients can get twice a year a major build upgrade and in between sometimes a update if nessecary. No client follows the upgrades close by, only on a 'need' basis. Clients use at the moment ca 6 minor/major versions.
I get a feeling that not many applications check at startup the structure of the database they use. Is that correct?
Regards ... OttO
|
|
|
|
|
OChristiaanse wrote: I get a feeling that not many applications check at startup the structure of the
database they use
The structure itself? No.
I suspect the majority don't do any validation at all. The app assumes the structure is correct.
Myself I version the database via data to allow runtime checks. But I wouldn't check the schema.
|
|
|
|
|
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.
|
|
|
|
|
(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 
- 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 )
- 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?
|
|
|
|
|
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' 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.
|
|
|
|
|
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.
|
|
|
|
|
Thanks for sharing. 
We are considering the same at the moment. That's one vote extra in that direction.
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.
|
|
|
|
|
Thanks for the info.
Regards ... OttO
|
|
|
|
|
No problem. Glad to help.
|
|
|
|
|
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
|
|
|
|
|
databinding between WPF and sqlserver 2008 express
|
|
|
|
|
Is this a question? If so it is rather meaningless, please try explaining what your problem is.
Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman
|
|
|
|
|
What about it? Rephrase the question if you have one and maybe people can help you.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Your question is not clear. Maybe you should check on msdn.
|
|
|
|
|
During the period of developing new changes request for our application, some of significant experience and mistakes are found which is necessary to make a conclusion in order to enhance team’s work in future.
1.Granularity of items in Solution &Design document.All detail changes in business, logic, UI and DB changes should be described in this document. We are supposed to make any developer get all details from this document.
2.Before talking about CR and drafting this document, it is very important to go through the related exist code of application, which will help to clear the relationship among the business objects and confirm CRs’ feasibility.
When we made an investigation for the relationship between business log and Machine and made a mistake that one log contains multi Machines.
Actually only one Machine in this log. So when we are coding, we found this problem and then we have to update our Solution &Design document again. Certainly it’s disappointing.
3.As to any one item, its corresponding priority (market/user level) and access permission should be taken into consideration as well.
welcome your input for this.
|
|
|
|
|
When customers / sales people / product managers etc. request a change, they are not at all interested in code. They look at the functionality only. Such requirements often start with a headline only, it is then the duty of requirements engineering / product management to precisely describe the requested functionality, possibly with user stories. When appropriate, the description will show the differences to the present situation. Now you have a functional specification.
Next, development has to scrutinize the requested change, and see how the application has to be designed: which interfaces, classes are needed, how they are distributed between server and client, etc. They have to look at the present code and its re-usability. Now you have a technical specification, and you can estimate the efforts for development.
Of course, the "functional" guys may talk with the "technical" guys when they create their functional specification.
When you eventually develop the functionality, you'll document differences from the original plan.
With "big" projects, you'll start with the main functionality, and while the customers can already use that core functionality, more details will be added as outlined in the steps above.
|
|
|
|
|
aoe.Dylan wrote: Certainly it’s disappointing.
Not sure what the question is.
But I believe you are suggesting that you had a Change Request (CR) and that while analyzing/implementing this you found a bug.
The bug, which significant, had nothing to do with the CR. As such it is nothing more than another CR despite originating from a different CR. You might choose to bundle multiple CRs into a release or not. That decision is based on an independent evaluation of each CR.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
