DBA guru advice - changes to production database

Hello -
I need to find out how to be able to change a production database, i.e.,
adding a column to a table that will necessitate changes to multiple stored
procedures, without shutting down the application that depends on the
database (or at least shutting it down for the least amount of time
possible). How are 24 X 7 production database changes normally made?
Can anyone explain the procedure to me and/or provide links to reading
material that explains how to do this?
Any help will be greatly appreciated. I am a programmer and these are
entirely new waters for me!
Sandy
Stuff like this is usually done through a documented process. You develop
the scripts in a dev environment and then apply them on a copy of production
in a test environment. If you are adding a NOT NULL column to a production
table, this can take a very long time, if you have many millions of rows.
You may have to XXX the column as NULL and then populate the table
iteratively. This, too, should be tested on a copy of prod first.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:19C65DEA-BF61-4B4D-88D9-67BA62C828A6@.microsoft.com...
Hello -
I need to find out how to be able to change a production database, i.e.,
adding a column to a table that will necessitate changes to multiple stored
procedures, without shutting down the application that depends on the
database (or at least shutting it down for the least amount of time
possible). How are 24 X 7 production database changes normally made?
Can anyone explain the procedure to me and/or provide links to reading
material that explains how to do this?
Any help will be greatly appreciated. I am a programmer and these are
entirely new waters for me!
Sandy
|||"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:19C65DEA-BF61-4B4D-88D9-67BA62C828A6@.microsoft.com...
> Hello -
> I need to find out how to be able to change a production database, i.e.,
> adding a column to a table that will necessitate changes to multiple
> stored
> procedures, without shutting down the application that depends on the
> database (or at least shutting it down for the least amount of time
> possible). How are 24 X 7 production database changes normally made?
> Can anyone explain the procedure to me and/or provide links to reading
> material that explains how to do this?
> Any help will be greatly appreciated. I am a programmer and these are
> entirely new waters for me!
> --
> Sandy
|||Thanks for your response, Tom.
Is the copy of the production database in the test environment generally on
a different server?
How do I then update the production server with the changes, after I work
with them on the test server?
I don't understand how I can run Alter Procedure and Alter Table scripts on
a 24 X 7 production server without mega problems, unless I shut down the
server for at least the time it takes for the scripts to run. Can you
clarify a little bit?
Sandy
"Tom Moreau" wrote:

> Stuff like this is usually done through a documented process. You develop
> the scripts in a dev environment and then apply them on a copy of production
> in a test environment. If you are adding a NOT NULL column to a production
> table, this can take a very long time, if you have many millions of rows.
> You may have to XXX the column as NULL and then populate the table
> iteratively. This, too, should be tested on a copy of prod first.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Sandy" <Sandy@.discussions.microsoft.com> wrote in message
> news:19C65DEA-BF61-4B4D-88D9-67BA62C828A6@.microsoft.com...
> Hello -
> I need to find out how to be able to change a production database, i.e.,
> adding a column to a table that will necessitate changes to multiple stored
> procedures, without shutting down the application that depends on the
> database (or at least shutting it down for the least amount of time
> possible). How are 24 X 7 production database changes normally made?
> Can anyone explain the procedure to me and/or provide links to reading
> material that explains how to do this?
> Any help will be greatly appreciated. I am a programmer and these are
> entirely new waters for me!
> --
> Sandy
>
|||"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:19C65DEA-BF61-4B4D-88D9-67BA62C828A6@.microsoft.com...
> Hello -
> I need to find out how to be able to change a production database, i.e.,
> adding a column to a table that will necessitate changes to multiple
> stored
> procedures, without shutting down the application that depends on the
> database (or at least shutting it down for the least amount of time
> possible). How are 24 X 7 production database changes normally made?
"It depends".
Partly on the scale of the database, the changes to be made, and the
resources available.
Ok, that doesn't help much I know.
Adding a column:
Depends on if the column has defaults or not. If you add a column with
defaults, then you're almost certainly going to lock the table for some
amount of time you may find unacceptable.
Now, if your stored procedures and queries are written correctly, adding a
column to a table should not be an issue. In other words you don't do
"select *" any place.
Now, for the stored procs, generally I've had "luck" doing what I needed in
a batch. Worse case though, have a script that sets the database to single
user mode, executes your script and sets it back. But watch out for errors.
It can be "bad" to have 1/2 your stored procs update, but not the other 1/2.
;-)
Another option is if you have a replicated copy or log-shipped copy of the
database you can use, is move traffic there, make changes and move back.
The problem then is handling transactions that occurred in the meantime.

> Can anyone explain the procedure to me and/or provide links to reading
> material that explains how to do this?
I don't unfortunately. In my last job I did this sort of thing all the
time, but also knew the system pretty well (but still messed up a few times
;-)
Contact me if you need more advice.

> Any help will be greatly appreciated. I am a programmer and these are
> entirely new waters for me!
> --
> Sandy
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OErxvtacHHA.4984@.TK2MSFTNGP05.phx.gbl...
> Stuff like this is usually done through a documented process. You develop
> the scripts in a dev environment and then apply them on a copy of
> production
> in a test environment. If you are adding a NOT NULL column to a
> production
> table, this can take a very long time, if you have many millions of rows.
> You may have to XXX the column as NULL and then populate the table
> iteratively. This, too, should be tested on a copy of prod first.
Grr, Tom's right. I meant NOT NULL, not DEFAULT in my post.
And I'll also emphasize the point he made.. do this on a copy first if you
can.
Test out your procedure as much as possible.
And have a roll-back plan!

> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Sandy" <Sandy@.discussions.microsoft.com> wrote in message
> news:19C65DEA-BF61-4B4D-88D9-67BA62C828A6@.microsoft.com...
> Hello -
> I need to find out how to be able to change a production database, i.e.,
> adding a column to a table that will necessitate changes to multiple
> stored
> procedures, without shutting down the application that depends on the
> database (or at least shutting it down for the least amount of time
> possible). How are 24 X 7 production database changes normally made?
> Can anyone explain the procedure to me and/or provide links to reading
> material that explains how to do this?
> Any help will be greatly appreciated. I am a programmer and these are
> entirely new waters for me!
> --
> Sandy
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Typically, you don't have a test/pre-prod database on the same physical
server. Rather, you have a separate box, of identical hardware (ideally).
This way, if your testing produces some undesirable effect, it's not
affecting production.
Whatever script you intend to run in prod - including an ALTER TABLE
script - would be run here. Once you are satisfied that it is working
properly, then run the same script in prod.
An ALTER PROC statement can be run in prod (after testing, of course). This
typically takes no time and if there is an error, the ALTER PROC fails and
you have the original proc.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:6B577153-11DD-4DB4-AA7C-3CDFCE9926A7@.microsoft.com...
Thanks for your response, Tom.
Is the copy of the production database in the test environment generally on
a different server?
How do I then update the production server with the changes, after I work
with them on the test server?
I don't understand how I can run Alter Procedure and Alter Table scripts on
a 24 X 7 production server without mega problems, unless I shut down the
server for at least the time it takes for the scripts to run. Can you
clarify a little bit?
Sandy
"Tom Moreau" wrote:

> Stuff like this is usually done through a documented process. You develop
> the scripts in a dev environment and then apply them on a copy of
> production
> in a test environment. If you are adding a NOT NULL column to a
> production
> table, this can take a very long time, if you have many millions of rows.
> You may have to XXX the column as NULL and then populate the table
> iteratively. This, too, should be tested on a copy of prod first.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Sandy" <Sandy@.discussions.microsoft.com> wrote in message
> news:19C65DEA-BF61-4B4D-88D9-67BA62C828A6@.microsoft.com...
> Hello -
> I need to find out how to be able to change a production database, i.e.,
> adding a column to a table that will necessitate changes to multiple
> stored
> procedures, without shutting down the application that depends on the
> database (or at least shutting it down for the least amount of time
> possible). How are 24 X 7 production database changes normally made?
> Can anyone explain the procedure to me and/or provide links to reading
> material that explains how to do this?
> Any help will be greatly appreciated. I am a programmer and these are
> entirely new waters for me!
> --
> Sandy
>
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OVRU8FbcHHA.4888@.TK2MSFTNGP02.phx.gbl...
> Typically, you don't have a test/pre-prod database on the same physical
> server. Rather, you have a separate box, of identical hardware (ideally).
> This way, if your testing produces some undesirable effect, it's not
> affecting production.
To expand upon this, we had a dev environment, a QA, and a prod. And
sometimes even setup a "staging" environment.
Dev code could be all over the place. QA typically mirrored Prod, or real
close to it. (We'd track differences).
Staging was used for major changes or ones we expected to be more difficult.
Typically we'd take a log-shipped copy of the Production environment. That
way we'd know we were dealing with up to date data AND the same amount of
data. (Dev, QA were typically far smaller data sets.)

> Whatever script you intend to run in prod - including an ALTER TABLE
> script - would be run here. Once you are satisfied that it is working
> properly, then run the same script in prod.
> An ALTER PROC statement can be run in prod (after testing, of course).
> This
> typically takes no time and if there is an error, the ALTER PROC fails and
> you have the original proc.
Ayup. Typically in my experience, the biggest problem was getting my ALTER
PROC statement to run.
Often it was blocked by 100s of calls to the existing one that had been made
before it.
We typically created a change plan.
In it we'd detail the changes to be made (and often why).
The plan to be followed.
The tests to make sure that it succeeded.
And very importantly, the "what if something goes wrong?" plan.
Sometimes that was trivial sometimes it was fairly complex.
If it was a single procedure and the "what if" was "it didn't get applied"
then we'd do nothing.
Or worse, it was applied, but we found out the new code syntactically was
correct, but was much slower than we could afford it to be, what would we do
then?
The big question really comes down to how long the alter table scripts will
run.
Another option (which does NOT work really in a replicated environment) is
create a NEW table with all the data, fields you want, and then rename the
old one and then the new one.
This can be useful in some cases, but not all.

> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Sandy" <Sandy@.discussions.microsoft.com> wrote in message
> news:6B577153-11DD-4DB4-AA7C-3CDFCE9926A7@.microsoft.com...
> Thanks for your response, Tom.
> Is the copy of the production database in the test environment generally
> on
> a different server?
> How do I then update the production server with the changes, after I work
> with them on the test server?
> I don't understand how I can run Alter Procedure and Alter Table scripts
> on
> a 24 X 7 production server without mega problems, unless I shut down the
> server for at least the time it takes for the scripts to run. Can you
> clarify a little bit?
>
> --
> Sandy
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks for responding, Greg -

>In other words you don't do "select *" any place.
What do you mean by the above?
What do you mean by: ". . . have a script that sets the database to single
user mode, executes your script and sets it back"?
Also, what is your best suggestion for a "rollback plan"?
Any help is greatly appreciated!
Sandy
"Greg D. Moore (Strider)" wrote:

> "Sandy" <Sandy@.discussions.microsoft.com> wrote in message
> news:19C65DEA-BF61-4B4D-88D9-67BA62C828A6@.microsoft.com...
> "It depends".
> Partly on the scale of the database, the changes to be made, and the
> resources available.
> Ok, that doesn't help much I know.
> Adding a column:
> Depends on if the column has defaults or not. If you add a column with
> defaults, then you're almost certainly going to lock the table for some
> amount of time you may find unacceptable.
> Now, if your stored procedures and queries are written correctly, adding a
> column to a table should not be an issue. In other words you don't do
> "select *" any place.
> Now, for the stored procs, generally I've had "luck" doing what I needed in
> a batch. Worse case though, have a script that sets the database to single
> user mode, executes your script and sets it back. But watch out for errors.
> It can be "bad" to have 1/2 your stored procs update, but not the other 1/2.
> ;-)
>
> Another option is if you have a replicated copy or log-shipped copy of the
> database you can use, is move traffic there, make changes and move back.
> The problem then is handling transactions that occurred in the meantime.
>
> I don't unfortunately. In my last job I did this sort of thing all the
> time, but also knew the system pretty well (but still messed up a few times
> ;-)
> Contact me if you need more advice.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:A0393922-029D-4B8D-AAD9-D79F8547556A@.microsoft.com...
> Thanks for responding, Greg -
>
Anytime you do a select in production code, you want to explicitely provide
the columns you are selecting.
Some programmers, being lazy will say write a select as;
SELECT * FROM tablename where Foo='bar'
This is wrong on several levels:
1) It probably brings back too much data, which is slower and consumes
bandwidth, etc.
2) It assumes the order columns are returned is constant, which is
safe...99% of the time. But not 100%
3) It assumes the number of columns is fixed. So if you add a column to the
underlying table, the query now returns one more row, which the calling code
now has to deal with. If you specify columns you only need to update the
selects that need the extra column.

> What do you mean by the above?
> What do you mean by: ". . . have a script that sets the database to
> single
> user mode, executes your script and sets it back"?
ALTER DATABASE testing SET SINGLE_USER WITH ROLLBACK IMMEDIATE
<your scripts here>
ALTER DATABASE testing SET MULTI_USER
That simply assures no one else is doing anything while your scripts run.

> Also, what is your best suggestion for a "rollback plan"?
Planning :-)
Seriously, this is a fairly complex question and depends a lot on your
situation. It's not something I can answer too easily over a newsgroup.
But basically think about what can go wrong and how to mitigate the
problems.
Create a script to add your columns and update your stored procs.
Create a script to REMOVE your columns and restore your old stored procs.
Just in case.
Stuff like that.

> Any help is greatly appreciated!
> --
> Sandy
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
sql

DBA - Service Request Form

Hi,
I'm looking to implement a paper-based service request form for all DBA
related activities i.e., adding logins, users, tables etc... granting
permissions, dropping objects, etc... Does anyone have a link to an online
form that I can start with or have an in-house form (.doc) that I can use to
build my form?
No need to recreate the wheel if it has already been done.
Thanks in advance.
Jerry
Hi Jerry,
I made a long ago with excel file for the form. I have to find it from my
doc somewhere but it may give you a basic idea where you can start with.
I can attach it to you, let me know what email address you would like me to
send.
-- Sharing is a good thing...
Sunny
"Jerry Spivey" wrote:

> Hi,
> I'm looking to implement a paper-based service request form for all DBA
> related activities i.e., adding logins, users, tables etc... granting
> permissions, dropping objects, etc... Does anyone have a link to an online
> form that I can start with or have an in-house form (.doc) that I can use to
> build my form?
> No need to recreate the wheel if it has already been done.
> Thanks in advance.
> Jerry
>
>
|||Thanks. The address for this account will work fine.
Jerry
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:E6ACFE30-A74E-4120-B09C-9CEF3380A41D@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> I made a long ago with excel file for the form. I have to find it from my
> doc somewhere but it may give you a basic idea where you can start with.
> I can attach it to you, let me know what email address you would like me
> to
> send.
> -- Sharing is a good thing...
> Sunny
> "Jerry Spivey" wrote:

DBA - Service Request Form

Hi,
I'm looking to implement a paper-based service request form for all DBA
related activities i.e., adding logins, users, tables etc... granting
permissions, dropping objects, etc... Does anyone have a link to an online
form that I can start with or have an in-house form (.doc) that I can use to
build my form?
No need to recreate the wheel if it has already been done.
Thanks in advance.
JerryHi Jerry,
I made a long ago with excel file for the form. I have to find it from my
doc somewhere but it may give you a basic idea where you can start with.
I can attach it to you, let me know what email address you would like me to
send.
-- Sharing is a good thing...
Sunny
"Jerry Spivey" wrote:

> Hi,
> I'm looking to implement a paper-based service request form for all DBA
> related activities i.e., adding logins, users, tables etc... granting
> permissions, dropping objects, etc... Does anyone have a link to an onlin
e
> form that I can start with or have an in-house form (.doc) that I can use
to
> build my form?
> No need to recreate the wheel if it has already been done.
> Thanks in advance.
> Jerry
>
>|||Thanks. The address for this account will work fine.
Jerry
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:E6ACFE30-A74E-4120-B09C-9CEF3380A41D@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> I made a long ago with excel file for the form. I have to find it from my
> doc somewhere but it may give you a basic idea where you can start with.
> I can attach it to you, let me know what email address you would like me
> to
> send.
> -- Sharing is a good thing...
> Sunny
> "Jerry Spivey" wrote:
>

DBA - Service Request Form

Hi,
I'm looking to implement a paper-based service request form for all DBA
related activities i.e., adding logins, users, tables etc... granting
permissions, dropping objects, etc... Does anyone have a link to an online
form that I can start with or have an in-house form (.doc) that I can use to
build my form?
No need to recreate the wheel if it has already been done.
Thanks in advance.
JerryHi Jerry,
I made a long ago with excel file for the form. I have to find it from my
doc somewhere but it may give you a basic idea where you can start with.
I can attach it to you, let me know what email address you would like me to
send.
-- Sharing is a good thing...
Sunny
"Jerry Spivey" wrote:
> Hi,
> I'm looking to implement a paper-based service request form for all DBA
> related activities i.e., adding logins, users, tables etc... granting
> permissions, dropping objects, etc... Does anyone have a link to an online
> form that I can start with or have an in-house form (.doc) that I can use to
> build my form?
> No need to recreate the wheel if it has already been done.
> Thanks in advance.
> Jerry
>
>|||Thanks. The address for this account will work fine.
Jerry
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:E6ACFE30-A74E-4120-B09C-9CEF3380A41D@.microsoft.com...
> Hi Jerry,
> I made a long ago with excel file for the form. I have to find it from my
> doc somewhere but it may give you a basic idea where you can start with.
> I can attach it to you, let me know what email address you would like me
> to
> send.
> -- Sharing is a good thing...
> Sunny
> "Jerry Spivey" wrote:
>> Hi,
>> I'm looking to implement a paper-based service request form for all DBA
>> related activities i.e., adding logins, users, tables etc... granting
>> permissions, dropping objects, etc... Does anyone have a link to an
>> online
>> form that I can start with or have an in-house form (.doc) that I can use
>> to
>> build my form?
>> No need to recreate the wheel if it has already been done.
>> Thanks in advance.
>> Jerry
>>

DB locking up when adding indexes programmatically

I've got this big giant script that drops all my default constraints, drops
all indexes, then drops all clustered index constraints, then adds alot of
new indexes, constraints, and clustered indexes. I've got it running
without throwing any errors now. However, sometimes the database locks up.
It seems to be set to single user mode but it's not really. Often times one
user can still use the database but everybody else gets their Enterprise
Manager locked up if they try to do anything with that database. Even Cold
Fusion queries time out. During this time we can't even see the current
activity. If we close out the window in Query Analyzer that I used to run
it, it will warn me that there are uncommitted transactions. Restarting the
SQL Server service will fix the problem. When it's back up, I can see that
everything got dropped, but no indexes or anything was added. I have only
done this to test databases so far but I have 140 customer databases I have
to run this on and I cannot do that at this point due to the lockups. I
thought it was due to us having a failover cluster but yesterday I got it to
happen on a development server that is not clustered. Any ideas?
Thanks in advance,
CoryHi
A clustered index build requires a table lock in SQL Server 2000.
During this time, nobody can acces that table. It also needs disk space, so
it might need to grow the DB.
Run sp_who2 whilst the script is running to see what is happening.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Cory Harrison" wrote:

> I've got this big giant script that drops all my default constraints, drop
s
> all indexes, then drops all clustered index constraints, then adds alot of
> new indexes, constraints, and clustered indexes. I've got it running
> without throwing any errors now. However, sometimes the database locks up
.
> It seems to be set to single user mode but it's not really. Often times o
ne
> user can still use the database but everybody else gets their Enterprise
> Manager locked up if they try to do anything with that database. Even Col
d
> Fusion queries time out. During this time we can't even see the current
> activity. If we close out the window in Query Analyzer that I used to run
> it, it will warn me that there are uncommitted transactions. Restarting t
he
> SQL Server service will fix the problem. When it's back up, I can see tha
t
> everything got dropped, but no indexes or anything was added. I have only
> done this to test databases so far but I have 140 customer databases I hav
e
> to run this on and I cannot do that at this point due to the lockups. I
> thought it was due to us having a failover cluster but yesterday I got it
to
> happen on a development server that is not clustered. Any ideas?
>
> Thanks in advance,
> Cory
>
>|||If the table(s) is(are) big then Dropping and Re-creating is going to be
pretty resource intensive during which time it is going
to freeze the server. You might want to look at improving the performance of
the Index Creation process.
Did you try the SORT IN TEMPDB option ? This might help a bit though not to
a large extent.
Also, I hope you are Dropping the Non-Clustered Index before Dropping
Clustered Index and Create Clustered Index
before creating the Non-Clustered Index.
Gopi
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:%238yru4LTFHA.336@.TK2MSFTNGP09.phx.gbl...
> I've got this big giant script that drops all my default constraints,
> drops all indexes, then drops all clustered index constraints, then adds
> alot of new indexes, constraints, and clustered indexes. I've got it
> running without throwing any errors now. However, sometimes the database
> locks up. It seems to be set to single user mode but it's not really.
> Often times one user can still use the database but everybody else gets
> their Enterprise Manager locked up if they try to do anything with that
> database. Even Cold Fusion queries time out. During this time we can't
> even see the current activity. If we close out the window in Query
> Analyzer that I used to run it, it will warn me that there are uncommitted
> transactions. Restarting the SQL Server service will fix the problem.
> When it's back up, I can see that everything got dropped, but no indexes
> or anything was added. I have only done this to test databases so far but
> I have 140 customer databases I have to run this on and I cannot do that
> at this point due to the lockups. I thought it was due to us having a
> failover cluster but yesterday I got it to happen on a development server
> that is not clustered. Any ideas?
>
> Thanks in advance,
> Cory
>
>