|
|
Posted by Jeff Kish on 03/24/06 15:53
On Fri, 24 Mar 2006 13:45:19 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:
>Jeff Kish (jeff.kish@mro.com) writes:
>> I have a database I need to supply something (I'm assuming a t-sql
<snip>
>
>Composing change scripts for production environments is not a task to
>take lightly. Particularly not if you have to apply them while the system
>is operating. (If the system closes for business, you may be able to repair
>a disaster by restorin a backup.)
>
>It requires good understanding of what can go wrong, and how to prevent
>that. For instance, if you need to drop constraints to alter a column,
>you should probably wrap that in a transaction, so you don't end up with
>losing the constraint.
ahh.. I had no idea that transactions could wrap/rollback ddl. I don't think
that is the case in Oracle.
>
>At the same time, ALTER operations that require changes to the physical
>data pages, can take a huge toll on the transaction log, causing it to
>grow rapidly. (Changing varchar lengths should be metadata so that should
>be safe.)
>
>You can use Enterprise Manager to have it to generate change scripts.
>However, there are several flaw in those scripts, and you need to review
>them carefully, and also make several changes to them. For instance, the
>transaction scope in those scripts are wacko.
>
Can you tell me how? I'm having some problem seeing how to get the equivalent
alter table etc scripts out of EM. I looked, honest. I'll even look some more.
>What may prove to be a show-stopper is that EM works with SQL 6.5 as its
>target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
>have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
>update as create new table and copy data over. Which sometimes is the right
>thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.
>
target is only sql server 2000 right now.
>There are other tools on the market. Red Gate's SQL Compare get a lot
>of positive acclaim, but I have not used it myself.
>
>One potential problem is that you don't know the name of the constraints,
>because they were added without a name, so all there is a system-generated
>name. In this case, you need to retrieve the name, and then run DROP
>CONSTRAINT dynamically. I would suggest that you restore the constraints
>with a given name.
>
mmm not sure I understand.. they are originally added specifically. can I just
disable them or do I need to drop them?
>Speaking of constraints, make sure that you re-add them WITH CHECK. The
>script from EM uses WITH NOCHEK, which means that they are not checked. This
>is a lot faster, but it also means that the optimizer will neglect them,
>which can have expensive consequences.
>
>Finally, before you run in production, test on a copy of production!
of course! the scars I have should remind me of that. :> )
thanks so much.
Jeff Kish
Navigation:
[Reply to this message]
|