I am in a new shop and was told that they do every month updates and patches on production SQL servers without testing the updates on Development box. Could you please guide me to best practices or things to do before applying any updates & patches on SQL servers? I would highly appreciate it. Thank you.
I usually follow this procedure: 1. Read as much as I can about the patch 2. Investigate if the patch is affecting my servers (usually it does) 3. Wait until someone else has applied the patch 4. Read about any issues from others 5. Apply the patch in test 6. Wait a week or two for the testers do do their job 7. Apply the patch in production If it's a critical patch I usually override the first steps. I have never been suprised by a nasty issue after a patch
Never install SPs or Patches on Production with out proper testing on Dev box. Keep backup of all the system database and user database before applying patches. After installing Patches Run the DBCC commands against the datbase. Monitor your Replication, Log-shipping, Clustering and Mirroring if any. Test, Test ... Test! If every thing fine, go ahead with production. Else Rollback or revert whole process.
I have got a follow up question:- To test the SQL 2005 and 2008 patches, do I need to have a copy of all databases on the test box from production and apply the patches? If so, i don't have any client connection to test the application functionality after applying the patches. And, I will need more resources to backup and restore the production database on the test box. What are your thoughts? Please let me know.