Schema Change Deployment

Question

Schema Change Deployment

t little

SSC Veteran

Points: 261
More actions
November 1, 2007 at 10:50 am

#119133

I'm trying to figure out the best way to update our client's systems. We have a web app with a SQL 2000 db, and a windows app that accesses the same db. Our clients are not very technically adept. The way we currently perform the update is that we connect to the client's server via VPN. We manually apply our change scripts for the version to the client's db using Query Analyzer. Then we manually copy over the web pages and windows app files. Because of our growing client base, this is getting to be fairly time consuming to do it for all of our clients.
Any suggestions as to how I can stream-line this?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

How do you write your change script? Do you use Alter Database alter table?

I found out if you use alter table, it would take a long time to copy the data. The easiest way is to rename the existing table to different name, then create the table and insert the data. If there is any PK, FK, you have to rename those too.

Rename a table

exec sp_rename 'Table1', 'Table1_Backup'

Create table Table1....

INSERT INTO Table1

SELECT ...

FROM Table1_Backup

t little SSC Veteran Points: 261 More actions · Answer 2

Due to foreign key constraints, we don't go the renaming route.

So generally, we do stuff like this to change the structure:

ALTER TABLE dbo.Foo

ADD Column FooBar INT NULL

Sometimes we add tables, add data or update stored procedures.

Loner SSC-Insane Points: 21279 More actions · Answer 3

That was exactly what I wanted to say, if you use 'ALTER TABLE ADD COLUMN....', if the table was big, it would take a long time because during the alter table, the data would be updated and it would check PK, FK....

That was the reason why I dropped the foreign key, renamed the table into backup table, create the new table, insert the table from the backup table and re-created the PK, FK, it would be much faster.

However it is just another option.

t little SSC Veteran Points: 261 More actions · Answer 4

Thanks for the info on improving our change script efficiency. However, I'm more looking for something to automate the deployment and/or running of the scripts

Loner SSC-Insane Points: 21279 More actions · Answer 5

You mean when you have a deployment ready, you can start a job and update all the clients?

Then why don't you put the clients in a table, then write a stored procedure to read that table?

When you are ready to deploy, then start a job to run the stored procedure. Of course I simplify a lot of things, you have to put in table name, which client needs which table....

you get the point.

Hope it help.

t little SSC Veteran Points: 261 More actions · Answer 6

I think we're on a different page.

I want to send my client a .zip file that contains my script and files. The client can then use some program which will automatically run the scripts and copy over some of my files. I need a recommendation of a program that will do this.

goodguy SSCertifiable Points: 5456 More actions · Answer 7

You can save your script as .sql or .txt and send it to your clients as it is or zipped up, and have them run it from QA.

Alternatively, you can create a standard project in VB and compile it to a exe. Have the project prompt the user for the script file (or better still, hardcode it to target a fixed filepath and filename to avoid prompting users), open it and read its contents and use ADO to execute each line (or batch) as a command against the connection object that represents your target database.

You can use SQL's "Go" batch separator to split the script text into a string array and then loop through executing one batch after another.

If you make a generic VB exe application, you can use it over and over again with any script file.

If you do not want to let your users "see" a script, you can code it into the project and compile a new exe every time. Another option is to load the text file into a resource file and then read it from there, but it will still require recompiling after every change.

One big unknown in using this approach is how to handle errors that may crop up in the process.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Loner SSC-Insane Points: 21279 More actions · Answer 8

I used to work for a software company and I used Installshield to write install script. My DBA used Perl to write install script. No matter what, your clients have to know their system, server security, user security. Those things you cannot incorporate in your script. If you create a new table and send it to your clients, who is responsible for user permission ? You cannot put it in your script.