Recently, a DBA asked one of us if there were any documents that might assist in creating a good overall set of policies and procedures for managing a large SQL environment. We were stumped. We really couldn't even think of anywhere he could go for help. This is strange. Whereas there are plenty of experts prepared to help with the technology, few seem to come forward when the struggle is against the bureaucracy rather than a recalcitrant server. Where are all the SQL MVBs (Masters at Vanquishing Bureaucracy) when you need them!?

We looked more closely at what he required, to try to get a feel for the scale of the problem that the DBA had described. He was managing a large SQL environment (100+ servers) and needed to create standard policies for everything from minimum hardware requirements to backup and restore to security. The documentation of each area is, in its own right, a major undertaking and certain areas, Security in particular, are an obvious minefield. From database and OS security policies, user access policies, sensitive data policy, encryption, general compliance documents, certificate authorities, auditing requirements…one quakes slightly just trying to note down even the basic areas it would have to cover. Also, regulatory requirements, plus and the need to get buy-in and support from various managers, business owners and so on, could make this a bureaucratic nightmare.

A Backup policy feels more manageable but even a brief brainstorm reveals such issues as:

• Disaster recovery requirements for each database (toleration for data loss in event of accidental data loss, corruption or hardware failure),
• Basic procedures for day-to-day backups in terms of scheduling of full, differential and log backups, as required
• Special Backup Procedures in event of server maintenance or upgrade
• Procedures in event of backup failure
• Storage considerations – how many days of backup kept onsite? Offsite storage media considerations
• Backup security
• Backup tools – policies regarding compression, encryption.
• Procedures to test recovery capability and backup integrity
• Which team has ultimate responsibility for implementing the backup policy?

And I'm sure I've missed out several important considerations.

Surely, this DBA isn't the only one awash in a sea of policies in which there doesn't seem to be any lifeboat? And surely there are enough DBAs who've been through all this before?

It seems to me that with a bit of community effort, we could amass a common repository of tried and tested template documents that contain current 'best practice' policies for managing large SQL environments within IT departments. If you think this could be a worthwhile endeavor and would like to get involved, please get in touch via the comments.

I look forward to hearing for everyone. If we get enough interest and volunteer contributors, we could even turn it into a proper community eBook, and get it published. We might even start issuing MVB awards! Then we can move on to how to implement these policies…

Cheers,

Tony.