Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ragingbender
Starting Member
1 Post |
 Posted - 2010-11-04 : 09:05:14
|
| 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 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-04 : 13:25:09
|
| Of course it's safe. Schema changes can be done with any number of users using the system, the worst you'll get is users blocked by the locks required to do the schema modifications.It may not be a good idea due to the locks that will be taken, it'll cause queries to wait and possibly timeout, but that's the worst that will happen. It's generally recommended to make schema changes during a maint window but that is only because of the concurrency problems (locks, blocks) that the schema modification will need and cause.--Gail ShawSQL Server MVP |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-04 : 15:15:54
|
| Our updates tend to include:Occasionally some DDL that modifies the Tables / ColumnsUsually some New / Revised SprocsUsually some Meta data - e.g. CMS content changes for the APP to useApplying these takes several minutes. It would not be acceptable to do this "live" so we throw the users off (in our case we put up a holding page as its a Web application; such changes are made at 05:00 when there are very few people using the site)We aim to have all changes proven before release. They start life on a DEV server then we build a "release package" and put that on our QA system and run all the QA tests against it. If that passes then the Release Package is available to clients.For a Client we refresh their TEST system from their LIVE system (TEST is NOT a copy of LIVE, it does not include any "Customer" data, only Meta data, Product data, and so on - this avoids accidentally Emailing real customers during testing!). We install the Release Package, perform any tailoring needed, and then we/Client performs tests.For our "Enterprise" customers we the perform an additional STAGING test. We copy the current LIVE database to STAGING and carry out a rollout from TEST to the STAGING database. For a rollout we have scripts where we Find and Replace the actual Server Name, and there are usually some "If XXX then do YYY" type instructions etc. During the Staging Rollout we save all the scripts so that they are "ready to use" with no further modifications. Any optional parts / unwanted parts etc. are all amended as necessary, and then saved in that form. This increases the reliability of subsequent rollout to LIVE but much more importantly it dramatically reduces the time duration of the rollout to Live as there is no "faffing about". We will rollback Staging, and re-do, if the rollout is not flawless.For non Enterprise customers we do the Rollout direct from TEST to LIVE. If we hit a major snag we have to roll back the changes and go-another-day."when to take down an SQL Server (in this case 2005) for scheduled maintenance"We ONLY EVER do server maintenance Mon-Wed. This leaves Thu-Fri to discover any issues before the weekend. Always amazes me that our Customers want us to roll everything out at 5PM on a Friday afternoon and then have major disruption for all our staff, their staff, and LOTS of annoyed customers over the weekend!"when to apply ad hoc changes on live instead."We do apply isolated bug fixes where singleton Stored Procedures are involved. This process requires that a senior coder has done the peer review to approve the changes - basically we are asking our most experienced people to make a risk assessment on whether the changes are likely to upset anything, or not. The code is very narrowly isolated, and it is easy to roll out a single, or a small number, or Sprocs in this way, and it has the benefit that the business logic is small and self contained.Our Clients have a completely different perspective though. They think completely rebuilding a database can safely happen at any time of the day ... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-04 : 18:27:01
|
| Do note that all Kristen's controls and scheduling for updates has to do with:1) Impact on users due to locks/heavy load from the schema changes2) Potential for bugs in the code (and necessity to fix such bugs)3) Potential for failed deployments (often because someone didn't test or forgot a script in my experience)None have to do with the possibility for database corruption from schema changes on an in-use database.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-04 : 18:31:52
|
quote:
Originally posted by Kristen
We ONLY EVER do server maintenance Mon-Wed. This leaves Thu-Fri to discover any issues before the weekend.
At the bank we used to do server maintenance (reboots, service packs, etc) on a saturday afternoon. It was an investment bank and the stock market is closed over weekends. That way, if something went wrong we had sunday to fix it before it disrupted business.Deployments were done 18h00 on a Thursday evening, 3 weeks a month. Friday mornings were usually slow (not because of the deployments) so if anything was picked up as wrong (and missed when business tested post-deployment) it wasn't as bad as if it had been monday morning (which was usually quite a busy day).--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Data Corruption Scenarios caused by Live Changes?