MS SQL Server - Safely updating stored procedures, functions and table structure with minimal downtime.

Author

Safely updating stored procedures, functions and table structure with minimal downtime.

Strider80

2006-12-10, 7:14 pm

Hello,

I am working as an in-house programmer on a reservation system for a
relatively small company. It is a client/server application which is
being used inside the local network (no direct access by customers).
Only the server can access the sqlserver database, which is running on
the same machine. We are using ms sqlserver 2000 sp3.

The system is updated every few weeks. It is often the case that there
are some changed or new stored procedures, functions or tables. Note
that for existing tables only the structure changes, not the data!
What we do is: shutdown the system and then copy all these changes
using the enterprise manager.
We do this by clicking our development database, double clicking the
object to be copied, and then copy the text, then click the "real"
database and corresponding object and paste this text. (e.g. select all
the text of a changed or new stored procedure and copy this text to the
procedure in the live database).
For tables we simply open the "design table" menu and make the same
changes we did in our development database.

During all these very manual operations the reservation system can NOT
be used! Fortunately most of the time this doesn't take a lot more than
15 minutes, but for sure there must be a better way than copy/pasting
procedure or function text....

So my question is this: is there a fast and guaranteed to be SAFE way
to update the changes we have made in our development database to the
live database without losing any data.
By this I mean a way to simply copy all the new/changed stored
procedures and functions, and for new tables to simply copy this table,
possibly including its data, but for EXISTING tables only copy the
change in structure (such as an extra field or changed data type) with
the guarantee that no data will be lost?

My goal is this: to have an automated way for a system update so that
the downtime is less than let's say one minute.

The procedure looks like this:
1) Make sure all clients are logged out
2) shut down the server
3) UPDATE THE CHANGES TO THE LIVE DATABASE from the development
database
4) copy the new server and client program to the server
5) push the new client program to all clients (no problem on this small
network).
6) Start the server

Steps 4) and 5) can be done automatically and only take a few seconds.
The bottleneck of the procedure is step 3). I cannot think of a way to
update (a copy of?) the live database while keeping the server running
while also guaranteeing that no data inserted during this update
procedure is lost.
Therefore we must minimalize the time of step 3) while guaranteeing no
data will be lost.

I aplogize if I am a bit too elaborate or asking for a frequently asked
question... I didn't know where to find the answer for our exact
situation.

Thanks for your time.

Alex Kuznetsov

2006-12-10, 7:14 pm

Strider80 wrote:
> Hello,
>
> I am working as an in-house programmer on a reservation system for a
> relatively small company. It is a client/server application which is
> being used inside the local network (no direct access by customers).
> Only the server can access the sqlserver database, which is running on
> the same machine. We are using ms sqlserver 2000 sp3.
>
> The system is updated every few weeks. It is often the case that there
> are some changed or new stored procedures, functions or tables. Note
> that for existing tables only the structure changes, not the data!
> What we do is: shutdown the system and then copy all these changes
> using the enterprise manager.
> We do this by clicking our development database, double clicking the
> object to be copied, and then copy the text, then click the "real"
> database and corresponding object and paste this text. (e.g. select all
> the text of a changed or new stored procedure and copy this text to the
> procedure in the live database).
> For tables we simply open the "design table" menu and make the same
> changes we did in our development database.
>
> During all these very manual operations the reservation system can NOT
> be used! Fortunately most of the time this doesn't take a lot more than
> 15 minutes, but for sure there must be a better way than copy/pasting
> procedure or function text....
>
> So my question is this: is there a fast and guaranteed to be SAFE way
> to update the changes we have made in our development database to the
> live database without losing any data.
> By this I mean a way to simply copy all the new/changed stored
> procedures and functions, and for new tables to simply copy this table,
> possibly including its data, but for EXISTING tables only copy the
> change in structure (such as an extra field or changed data type) with
> the guarantee that no data will be lost?
>
> My goal is this: to have an automated way for a system update so that
> the downtime is less than let's say one minute.
>
> The procedure looks like this:
> 1) Make sure all clients are logged out
> 2) shut down the server
> 3) UPDATE THE CHANGES TO THE LIVE DATABASE from the development
> database
> 4) copy the new server and client program to the server
> 5) push the new client program to all clients (no problem on this small
> network).
> 6) Start the server
>
> Steps 4) and 5) can be done automatically and only take a few seconds.
> The bottleneck of the procedure is step 3). I cannot think of a way to
> update (a copy of?) the live database while keeping the server running
> while also guaranteeing that no data inserted during this update
> procedure is lost.
> Therefore we must minimalize the time of step 3) while guaranteeing no
> data will be lost.
>
> I aplogize if I am a bit too elaborate or asking for a frequently asked
> question... I didn't know where to find the answer for our exact
> situation.
>
> Thanks for your time.

Try SQL Compare from Red Gate - it not only detects all the differences
between two databases, it also generates a scripts that either
successfully deploys all the changes or rolls them back if anything
fails. Very convenient.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Strider80

2006-12-10, 7:14 pm

Thank you, this sounds useful.

I wonder if there are any free alternatives that (partially) solve my
problem? Like using enterprise manager for only exporting the things I
need from the development to the production database?

Lee Boozer

2006-12-10, 7:14 pm

Yes, there is. I would propose you use change scripts for modifications to your
database. As you're making changes, save the scripts for the change in a
specific directory, which all developers should share. Since scripts sometimes
have to be run in a specific order, you may wish to number the scripts (i.e.
01-Alter Table A.sql, 02-Alter View A.sql, etc)

To prep for deployment, you can create a CMD file which will execute the scripts
in order using OSQL or ISQL. Visual Studio 2003 has a wonderful "Create Command
Script" option on its database projects that will generate a script to
compile/run all your database scripts. (For some reason they didn't carry this
over to VS2005.)

To guarantee you don't have issues when moving to production, you should test
all your changes in a QA environment. Make a backup of your production database
and restore it to QA. Apply your changes (via your script) to QA. If your script
runs flawlessly there, it's almost guaranteed to do so in production (beware
things outside your database, such as logins, linked servers, etc). With this
technique, you can put your database into DBO Use Only mode, apply your changes,
and be back online (with no mistakes) in probably 5 mins or less.

As for new tables and data copy - you may want to consider creating and
populating them ahead of time. There's no harm in having a table your
applications just aren't referencing yet. Or if it's a small table, you can just
have a script that creates the table and populates it with INSERT statments.

Regards,
Lee

On 10 Dec 2006 10:30:43 -0800, "Strider80" <strider80@hotmail.com> wrote:

>So my question is this: is there a fast and guaranteed to be SAFE way
>to update the changes we have made in our development database to the
>live database without losing any data.

Strider80

2006-12-11, 7:13 pm

Thanks for your response.

Sounds like a good idea to use scripts that make the desired changes.
Unfortunately I have no experience in making scripts, maybe it is about
time I learn it :)
Are these scripts written in normal sql? If so, can they be
automatically generated, or do I need to know for example the SQL code
needed to change a table structure? (I always use the GUI for that).
Keeping track of them in the source control (we use sourcesafe) sounds
like a good idea, we already keep track of the names of changed
objects.

Indeed copying new objects to the production database before won't
interfere with the old code.
And yes it's a good idea to make a copy of the production database to
another server and run the scripts there before I try them on the
production database. But what does DBO use only mode mean?

We do not use visual studio; the project is written in Java. Is it
possible to make a script that calls all other scripts? Or else, is
there anything against putting all the changes in just use one script
that has multiple sql statements?

Thanks for the help.

Lee Boozer schreef:
[color=darkred]
> Yes, there is. I would propose you use change scripts for modifications to your
> database. As you're making changes, save the scripts for the change in a
> specific directory, which all developers should share. Since scripts sometimes
> have to be run in a specific order, you may wish to number the scripts (i.e.
> 01-Alter Table A.sql, 02-Alter View A.sql, etc)
>
> To prep for deployment, you can create a CMD file which will execute the scripts
> in order using OSQL or ISQL. Visual Studio 2003 has a wonderful "Create Command
> Script" option on its database projects that will generate a script to
> compile/run all your database scripts. (For some reason they didn't carry this
> over to VS2005.)
>
> To guarantee you don't have issues when moving to production, you should test
> all your changes in a QA environment. Make a backup of your production database
> and restore it to QA. Apply your changes (via your script) to QA. If your script
> runs flawlessly there, it's almost guaranteed to do so in production (beware
> things outside your database, such as logins, linked servers, etc). With this
> technique, you can put your database into DBO Use Only mode, apply your changes,
> and be back online (with no mistakes) in probably 5 mins or less.
>
> As for new tables and data copy - you may want to consider creating and
> populating them ahead of time. There's no harm in having a table your
> applications just aren't referencing yet. Or if it's a small table, you can just
> have a script that creates the table and populates it with INSERT statments.
>
> Regards,
> Lee
>
>
> On 10 Dec 2006 10:30:43 -0800, "Strider80" <strider80@hotmail.com> wrote:
>

Strider80

2006-12-11, 7:13 pm

The script way sounds to me like a good and free alternative to third
party software, I hope making the script(s) doesn't take a lot of time.
You mentioned something about command scripts. I must say I am only
familiar with batch files (.bat) or are these the same?
How can I run the database script(s) from a batchfile/command script
(if possible from another computer, but on the server isn't much of a
problem).

This is important because I would like to be able to do the entire
update procedure by running just one single script.
So I think this script should call some others that perform the
separate tasks of copying the new programs and running the database
scripts.

Thanks for your time.

Strider80

2006-12-11, 7:13 pm

We decided we will give Red Gate's SQL compare a try.

Sponsored Links