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

Db Trigger Question

Hello,

I have a db trigger (example below) that is supposed to update a column with the current date. However, I need to know which row is being updated by a user so that I would update ONLY this row. Is there a straightforward way to figure out which row is updated by a user? My table includes a column called rowid which is a primary key.

Thanks for any help!

CREATE TRIGGER dbo.[DatetimeUpdate]

ON dbo.DepartmentMapping] AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

UPDATE dbo.DepartmentMapping

SET DateUpdated = GetDate()

Donnie:

You need to take advangage of the INSERTED pseudo-table in your trigger. Look up CREATE TRIGGER in books online and examine their examples. Your update ought to change to something like:

UPDATE dbo.DepartmentMapping
SET DateUpdated = GetDate()
FROM inserted i
INNER JOIN dbo.DepartmentMapping a
ON a.{departmentMappingKey} = i.{departmentMappingKey}

|||

When code inside a trigger is being executed, the code has access to two 'Virtual Tables' named Inserted and Deleted.

In the case of an UPDATE statement, the Deleted table contains the rows as they would have been before any changes and the Inserted table contains the rows as they are after the changes. The virtual tables have the same columns as the table on which the trigger is defined.

You could amend your code to that shown below.

Chris

UPDATE dm

SET DateUpdated = GetDate()

FROM dbo.DepartmentMapping dm

INNER JOIN deleted d ON d.DepartmentMappingID = dm.DepartmentMappingID

--or equally

--INNER JOIN inserted i ON i.DepartmentMappingID = dm.DepartmentMappingID

|||Great. Thank You!|||Of course you wouldn't want to use deleted in this case, since you would be trying to update a deleted row :)sql

DB Questions

1. If the TempDB Database is deleted from MS-SQL Server what will
happen..?
2. How to insert a not null column in an existing table with
records..?
3. If a table is deleted, what will happen for the Stored procedures
and Views, which that table reffered..?1. TempDB will be re-created automatically when you restart SQLServer. You
can't delete TempDB while SQLServer is running.

2. Either assign a DEFAULT value to the column, or make it nullable to start
with, populate the column and then alter it to NOT NULL.

3. Dependent SPs and views won't be deleted but they will generate an error
when they are used.

--
David Portas
SQL Server MVP
--

DB Optimization Failed

Error 21268: [SQL-DMO]Row or column specified is outside the range of the
specified query result set.
This is the error I am getting while running optimization on a database, it
is a mid size database about 30gb having lots of documnets, text datatas
Any quick help is highly appreciated.
Regards...Piku.And in another OPTIMAZION Job getting this Msg, similar lype of database as
mentioned above :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
I don't know why the error Msg says : DBCC failed actually it is an
Optimization Job which failing. I also tried by dropping and recreating the
same.
"Piku" wrote:
> Error 21268: [SQL-DMO]Row or column specified is outside the range of the
> specified query result set.
> This is the error I am getting while running optimization on a database, it
> is a mid size database about 30gb having lots of documnets, text datatas
> Any quick help is highly appreciated.
> Regards...Piku.

DB Optimization Failed

Error 21268: [SQL-DMO]Row or column specified is outside the range of th
e
specified query result set.
This is the error I am getting while running optimization on a database, it
is a mid size database about 30gb having lots of documnets, text datatas
Any quick help is highly appreciated.
Regards...Piku.And in another OPTIMAZION Job getting this Msg, similar lype of database as
mentioned above :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
I don't know why the error Msg says : DBCC failed actually it is an
Optimization Job which failing. I also tried by dropping and recreating the
same.
"Piku" wrote:

> Error 21268: [SQL-DMO]Row or column specified is outside the range of
the
> specified query result set.
> This is the error I am getting while running optimization on a database, i
t
> is a mid size database about 30gb having lots of documnets, text datatas
> Any quick help is highly appreciated.
> Regards...Piku.