November 4, 2010 at 8:43 am
Dear Gents,
I am trying to find out about best practices regarding when to take down an SQL Server (in this case 2005) for scheduled maintenance, and when to apply ad hoc changes on live instead.
Please note that any corruption due to Hardware, Driver, OS failures etc. are out of scope.
I would like to hear your opinion, and would greatly appreciate Links to articles supporting concepts/best practices regarding this - i.e. is it considered safe to execute ALTER TABLE / ALTER COLUMN, DROP & CREATE etc. on a Live SQL Server DB?
It would be great to not only include a DBA's perspective, but to include thinking about scenarios where multiple processes interact with the DB while changes are being applied.
I know that with time, a lot of mechanisms such as locking etc. have been integrated into all major DBs, but I can not imagine that it can be considered best practice or safe to do this.
Thanks in advance for your feedback!
Cheers,
rb
November 4, 2010 at 9:10 am
This is going to depend on a LOT of factors.
If, for example, your business requires 24x7x365 uptime, then you will pretty much have to apply them "on the fly".
As for safety in applying a table modification in a database that's in use, it'll depend a lot on your table structure, what modification you're making, and how many rows the table is storing.
For example, if you're adding a bit column to a table that already has 1-7 bit columns, on a small table (a few thousand rows maybe), and no default value being applied to the column, then the addition will be nearly instantaneous, will require no page splits to implement even once data begins being added (since it'll fit in the allocation with the existing bit columns), and won't have to update any rows.
On the other hand, if you're adding a varchar column with a default value, to a table with a couple of hundred million rows, and a not null constraint on that column, then the locking process is going to be heavy duty, you're almost certainly going to end up with page splits on at least some of your table, and the whole thing could end up locking the table for hours. But, even that might not matter if all your queries are handled by covering indexes that aren't being modified to include the new column and if new writes are all at the end of the table.
In other words, the "best practice" for this is to know what you're doing and what impact it will have on your system. That's what Dev and QA servers are for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 10:37 am
GSquared (11/4/2010)
This is going to depend on a LOT of factors....
In other words, the "best practice" for this is to know what you're doing and what impact it will have on your system. That's what Dev and QA servers are for.
As Gus said, the key is to know your environment and know the changes. Personally I prefer to do things like that during a scheduled time-frame. By scheduling it, people are made aware that something is happening. Sometimes it may not be possible to bring the servers down - but at the very minimum there should be some sort of notification sent out that there are system changes being made.
And of course - make the changes in dev and qa first.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 4, 2010 at 10:43 am
Yep, something that says, "System undergoing upgrades. Minor interruptions or slow-downs in service may occur during the following hours. Please excuse any inconvenience, and schedule any critical uses of the system for a time outside those hours if possible." can go a long ways towards preventing customer upsets even in a 24x7 system.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply