I am learning how to manage databases for my company and while I have pretty good understanding of using a database, I can definately see that it is completely different administering a database.
My possition is we are a small team and we previously had no one manageing the database servers, if something went wrong we would fix it but there was no pro-active maintenance.
I am putting together some documentation of best practices that we should be using, fixes to problems discovered, and methods to prevent issues from occuring. At the moment my managers are reluctant to patch the DB Servers (OS and DB Software), which I believe is insanity but they are senior and "know best".
Our environment is Server 2003, with 5 DB Servers, three MSSQL 2005 and two MSSQL2000. The DB servers are all private and do not face the internet. They are behind our firewall. The fact they are not public is the reasoning behind the managers reluctance to patch the servers.
What I would like to know is:
How people manage patching of the SQL Servers and OS? Is it necessary to patch them? List item When do you apply patches (immeadiately, after a couple days to wait for problems to be encountered and documented, other)? List item White papers, technical documentation, good blogs, etc that you can recommend. Any help would be greatly appreciated as I believe that they should be patched to at the least the most recent SP and perhaps the second recent Cummalative Update, but I dont have any form of best practices to base this on so would be most keen to learn what people do.
If you need further information please dont hesitate in contacting me.
Thanks,
Lima
My advice: yes, patch them. As we have seen even today, malicious content can and will make it into your internal network. Thoughts contrary to this are just plain naive and irresponsible. Slammer was an eye-opener back in the day and a lot has been done to lock SQL down to prevent this, but new threats arise blah blah blah.
With regards to the db servers, it's pretty much the same as your other servers. Put together an install plan, put together a rollback plan, apply the patches in a test environment, when comfortable apply them in production. They should be assessed for priority along with all other patches.
I agree, not patching is insanity. If your business relies on these systems and they're not patching because of discomfort with administering SQL Server then get someone into SQL admin training.
Edit: To your comment, on your test server you duplicate your production environment as closely as possible. To test your patch process you perform the entire process on the test server. If it fails then there's a decent or better chance that it'll fail in production also. Don't patch production until the patches in a test environment are stable.
On application servers you also want to make sure that your app continues to work as designed after the patches are installed so you will want to not only make sure the system itself survives the patch but that you run through the functionality of your application.
Patching databases should be no different than patching anything. Test, then deploy.
Obviously this requires a test system to patch first, but if these databases are as critical as the senior people in your organization believe them to be, I'm sure they have test systems deployed already and ready for testing. ;)
I always find it interesting when people refuse to patch systems because they are "internal only" and therefore secure. I guess you have no computer on your LAN that accesses the Internet and no possibility of ever doing something with a piece of software that will trigger a data corrupting, server crashing bug in the unpatched original release of the software.
Agreed with the above regarding "yes you must patch, and yes, test the patches first".
I will note however, as you mention that your team is new to DB administration, that I find MSSQL to require patches less frequently than Windows. The way it goes for me, I set windows update to "download only" on our test machine, then some night when we're feeling brave we tell Windows to go ahead and install, reboot, then run our regression tests.
The production machine is set the same way - download only, don't install the patches. So if our tests go well on the test machine we wake up at 5 AM the next day and tell the production machine to install the patches as well, let it reboot, then do some simple tests to ensure that the app is still working. We normally do this no more than once per week, as we don't like to wake up at 5 AM, but of course if the description of the patch sounds unusually dire we'll rush it.
Yikes. Well you definitely want to patch them as SQL Slammer et al operate BEHIND the firewall so .... Anyway, "back in the day" I would manually patch them from the SP's that come out from time to time after testing on a backup server. Nowadays I just let "Microsoft Update" catch any SQL Server-related patches. Seems to work just fine.