Database - schema creation and updates

[zuperman]

Hi guys...

I would like some comments about how do you manage the creation and updates of a database (SQL Server) schema in a Vb.net application...

do you ask your customers to load scripts in SQL Server Management Studio (or query analiser)... or dou you make your own application to perform this task?

and what about versions... does your applications validate database schemas versions? and how do you manage to do that?

im searching for days for this matter... all i find is information about batch files calling osql... i dont consider this a good and professional solution...

thx in advance...

[zuperman]

...

in my previous applications i left this task to database administrators... but 99% of my customers are small companies, and dont have database administrators...

[RobDog888]

Yes, they check the db schema and apply alter table statements etc to update a db to a new version of an app. I never rely upon a client to run anything other then an executable update/service pack or something because they usually are not tech savy or if they do something wrong it takes forever to walk them through any type of analysis.

[zuperman]

well... my databases have a version number...

what i am trying to do is provide the customer the sql script with a xml config file, with version/patch numbers, release dates, and some tech information... the application would have somekind of database admin section, that would check database version, impose the db update and enable the user to select the xml config file... after that would validate and execute the sql script against the database...

my problem is just i cant find how to execute a sql script generated by sql manager console without errors...

and i dont want to embed sql statements in code...

[zuperman]

how do you manage to run that update statements?

[szlamany]

Have you looked into the DTSRUN command line utility?

Or even using OSQL in a .BAT file to run a .SQL text script?

Personally - we manage our updates to customer databases - they pay for annual maintenance and expect us to handle that end of the DBA work.

We even have full DBA contracts with some customers so that we are responsible for backups and disk space and whatever else a SQL box needs to run smooth.

[zuperman]

Have you looked into the DTSRUN command line utility?

Or even using OSQL in a .BAT file to run a .SQL text script?

Personally - we manage our updates to customer databases - they pay for annual maintenance and expect us to handle that end of the DBA work.

We even have full DBA contracts with some customers so that we are responsible for backups and disk space and whatever else a SQL box needs to run smooth.

DTSRUN... let me take a look at that...

osql and bat files??? as i said...dont consider this a good and professional solution

i cant manage myself customer for db updates...

[RobDog888]

The osql utility is the command line version of Query Analyzer and it does take arguments and returns values too.

My updater is basic. It just verifies the tables and fields exist and if not executes the alter query to update the table to the current version.[/color]

[zuperman]

The osql utility is the command line version of Query Analyzer and it does take arguments and returns values too.

My updater is basic. It just verifies the tables and fields exist and if not executes the alter query to update the table to the current version.[/color]

sql embebbed in your application?... i want to make a tool so i dont have to create new versions only to upadte db...

[RobDog888]

Then you can have it read it the xml file that you were talking about.
Like I was saying, mine is very basic as I didnt have time to make it dynamic or fancy

[zuperman]

and what bout first time creation... in a new customer... do it yourself?

[RobDog888]

If ts first time creation and not using .NET 2005 then it would also be CREATE DATABASE and GRANT statements to create the db and assign permissions to a Windows Security group. Then the end user can manually add/remove their windows users to/from that group as a way of controlling access to the program running.

[szlamany]

and what bout first time creation... in a new customer... do it yourself?

There are so many issues related to this...

I've got a customer who would not give me admin rights to the SQL server. This meant they would take only a .BAK that they would RESTORE onto the server or the choice I took - they created 5 empty DB's and I used scripts myself to create objects (tables, sprocs and what not).

btw - when we used OSQL in a nightly process 5 years ago to keep a SQL box sync'd with a mainframe we developed a "review" program that would "read" the OSQL output log and look for errors.

What is so unprofessional about OSQL in your opinion?

[zuperman]

What is so unprofessional about OSQL in your opinion?

...the most annoyng thing is that i cant catch errors to the user...unlike you, i havent figured out to do that

if i manage to do that i think it would be a good solution... can you explain how you do it... (dont need code, only the main steps)...

thx...

[szlamany]

We got to know what the log file produced by OSQL would look like and simply opened it with a little VB program and read through it looking for errors.

You could distribute your .SQL script along with a .ExpectedLogFile with it - and after you run OSQL you compare the .LOG output to the .ExpectedLogFile with a little VB program - looking for errors.