Robust database with easy rollback capability...

We want to improve our database upgrade so that upon failure, customers
can more easily get back to their previous version while we look into
the problem. We sell a packaged product.
Currently, we export data into a homegrown format, create a new
database, and then import into the new database. We'd like to rid
ourselves of this intermediate, dangerous step (homegrown export
format). The reason it exists is that we used to support Access (Jet).
I'd like to do 'in-place' upgrades, more or less; leave the database
where it is, and tweak schema or data as needed. It seems a lot safer
and would certainly be much quicker.
I wanted to get ideas...what are some techniques that perhaps some of
you use?
Some of my thoughts:
* detach the old database, make a copy, attach to the copy. Upgrade the
copy, but on failure, reattach the original.
* Use DTS to move data from the old to the new. Don't switch to the new
until/unless it looks good.
* Put a transaction frame around the whole upgrade process and roll back
if it fails. Somehow, this sounds a little scary to me, though. Should it?
Other ideas? I'd love to hear more.
One of the goals is to not have the customer do anything manually in all
of this.Do you know for sure that your customers are backing up their database and
that they are not making custom changes to the table structures? If so, then
you could feel more confident about deploying data change scripts.When you
make changes to a table design in Enterprise Manager, but don't click the
[Save] button, you can click the [Save Change Script] button to see what
T-SQL code EM is using to make the changes. It includes all the scripting
needed to insert / select existing data from a temporary table and also
wraps everything in a BEGIN.. END.. transaction. You could deploy the script
as is, or customize as needed.
"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eqMNmLXBFHA.3504@.TK2MSFTNGP12.phx.gbl...
> We want to improve our database upgrade so that upon failure, customers
> can more easily get back to their previous version while we look into
> the problem. We sell a packaged product.
> Currently, we export data into a homegrown format, create a new
> database, and then import into the new database. We'd like to rid
> ourselves of this intermediate, dangerous step (homegrown export
> format). The reason it exists is that we used to support Access (Jet).
> I'd like to do 'in-place' upgrades, more or less; leave the database
> where it is, and tweak schema or data as needed. It seems a lot safer
> and would certainly be much quicker.
> I wanted to get ideas...what are some techniques that perhaps some of
> you use?
> Some of my thoughts:
> * detach the old database, make a copy, attach to the copy. Upgrade the
> copy, but on failure, reattach the original.
> * Use DTS to move data from the old to the new. Don't switch to the new
> until/unless it looks good.
> * Put a transaction frame around the whole upgrade process and roll back
> if it fails. Somehow, this sounds a little scary to me, though. Should
it?
> Other ideas? I'd love to hear more.
> One of the goals is to not have the customer do anything manually in all
> of this.|||This is how I always do it:
I create a change script using the database change management software DB
Ghost (http://www.dbghost.com).
I get all users off the database I'm about to change.
I back up the database I'm about to change.
I run the script.
I run the tests to verify that all is well.
I back up the database again so I have a backup before the changes and a
backup after the changes.
I open the database to all users.
"Mike Jones" wrote:
> We want to improve our database upgrade so that upon failure, customers
> can more easily get back to their previous version while we look into
> the problem. We sell a packaged product.
> Currently, we export data into a homegrown format, create a new
> database, and then import into the new database. We'd like to rid
> ourselves of this intermediate, dangerous step (homegrown export
> format). The reason it exists is that we used to support Access (Jet).
> I'd like to do 'in-place' upgrades, more or less; leave the database
> where it is, and tweak schema or data as needed. It seems a lot safer
> and would certainly be much quicker.
> I wanted to get ideas...what are some techniques that perhaps some of
> you use?
> Some of my thoughts:
> * detach the old database, make a copy, attach to the copy. Upgrade the
> copy, but on failure, reattach the original.
> * Use DTS to move data from the old to the new. Don't switch to the new
> until/unless it looks good.
> * Put a transaction frame around the whole upgrade process and roll back
> if it fails. Somehow, this sounds a little scary to me, though. Should it?
> Other ideas? I'd love to hear more.
> One of the goals is to not have the customer do anything manually in all
> of this.
>|||This is harder to pull off with packaged software. We must send out a
setup program that users can run easily.
> This is how I always do it:
> I create a change script using the database change management software DB
> Ghost (http://www.dbghost.com).
> I get all users off the database I'm about to change.
> I back up the database I'm about to change.
> I run the script.
> I run the tests to verify that all is well.
> I back up the database again so I have a backup before the changes and a
> backup after the changes.
> I open the database to all users.
> "Mike Jones" wrote:
>
>>We want to improve our database upgrade so that upon failure, customers
>>can more easily get back to their previous version while we look into
>>the problem. We sell a packaged product.
>>Currently, we export data into a homegrown format, create a new
>>database, and then import into the new database. We'd like to rid
>>ourselves of this intermediate, dangerous step (homegrown export
>>format). The reason it exists is that we used to support Access (Jet).
>>I'd like to do 'in-place' upgrades, more or less; leave the database
>>where it is, and tweak schema or data as needed. It seems a lot safer
>>and would certainly be much quicker.
>>I wanted to get ideas...what are some techniques that perhaps some of
>>you use?
>>Some of my thoughts:
>>* detach the old database, make a copy, attach to the copy. Upgrade the
>>copy, but on failure, reattach the original.
>>* Use DTS to move data from the old to the new. Don't switch to the new
>>until/unless it looks good.
>>* Put a transaction frame around the whole upgrade process and roll back
>>if it fails. Somehow, this sounds a little scary to me, though. Should it?
>>Other ideas? I'd love to hear more.
>>One of the goals is to not have the customer do anything manually in all
>>of this.

Robust audit tools

With HIPAA security becoming law on April 20th, I've got some customers that
want to know about products that will log events on the server...
Does anyone have any knowledge or experience with tools like this.
For example, tracking...
1) Every call to a SPROC and what parameters were used
2) Every SQL statement executed against the database - from any client tool
3) Change events - original data, new data, who did it - stuff like that...
Thanks in advance.Lumigent Entegra
http://www.lumigent.com/
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:C99ACADF-DD67-48A8-B3A7-FED13F1C99CE@.microsoft.com...
> With HIPAA security becoming law on April 20th, I've got some customers
> that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client
> tool
> 3) Change events - original data, new data, who did it - stuff like
> that...
> Thanks in advance.|||sql profiler will do a nice job with #1 and #2
Greg Jackson
PDX, Oregon|||You have to set up a pretty squeaky-clean trace to justify running profiler
24/7 in a production environment.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> sql profiler will do a nice job with #1 and #2|||yes...very good point
gaj
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23U$iLYEQFHA.3988@.tk2msftngp13.phx.gbl...
> You have to set up a pretty squeaky-clean trace to justify running
> profiler 24/7 in a production environment.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>|||what about changes to the source code and having a complete history/audit
trail of those...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Steve Z" wrote:

> With HIPAA security becoming law on April 20th, I've got some customers th
at
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client to
ol
> 3) Change events - original data, new data, who did it - stuff like that.
.
> Thanks in advance.sql

Robust audit tools

With HIPAA security becoming law on April 20th, I've got some customers that
want to know about products that will log events on the server...
Does anyone have any knowledge or experience with tools like this.
For example, tracking...
1) Every call to a SPROC and what parameters were used
2) Every SQL statement executed against the database - from any client tool
3) Change events - original data, new data, who did it - stuff like that...
Thanks in advance.
Lumigent Entegra
http://www.lumigent.com/
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:C99ACADF-DD67-48A8-B3A7-FED13F1C99CE@.microsoft.com...
> With HIPAA security becoming law on April 20th, I've got some customers
> that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client
> tool
> 3) Change events - original data, new data, who did it - stuff like
> that...
> Thanks in advance.
|||sql profiler will do a nice job with #1 and #2
Greg Jackson
PDX, Oregon
|||You have to set up a pretty squeaky-clean trace to justify running profiler
24/7 in a production environment.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> sql profiler will do a nice job with #1 and #2
|||yes...very good point
gaj
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23U$iLYEQFHA.3988@.tk2msftngp13.phx.gbl...
> You have to set up a pretty squeaky-clean trace to justify running
> profiler 24/7 in a production environment.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
|||what about changes to the source code and having a complete history/audit
trail of those...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Steve Z" wrote:

> With HIPAA security becoming law on April 20th, I've got some customers that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client tool
> 3) Change events - original data, new data, who did it - stuff like that...
> Thanks in advance.