Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Defining and Establishing SQL Server Policies and Procedures


By:   |   Last Updated: 2007-08-09   |   Comments (1)   |   Related Tips: More > DBA Best Practices

Problem
As your IT team grows more and more people may have direct impact on your SQL Server databases.  At the onset it may have been just a couple of developers and a DBA, but as your company becomes more successful and more resources are put on developing, maintaining and supporting a database driven application the potential for inconsistent processes and undocumented changes increases.  Just when you need more control and structure things tend to sway the other way, because now there are more people and components that need to be managed.  So what is the best process to put controls in now so when things grow beyond one or two people chaos does not follow?

Solution
With most IT processing it is often essential to document your policies with standardized procedures.  In most cases there are already established rules, whether they are formalized or not there is some type of rules or procedures that are followed on a day to day basis. One potential problem with this un-formalized approach is that one person may have their set of procedures and another person may have a totally different set of procedures.  As mentioned already with a small group of people this may not be as big an issue, but as more and more people perform the same tasks you are bound to have further discrepancies.

Define a standardized format
The first step in formalizing the process is to document your already established rules, although they may be loosely defined there is some process that you are already following.  In order to document your policies and procedures it is helpful to have a standard format that you follow.  This can simply be a Word document or database application that you develop.  Whatever the approach, you should establish a standard template for documentation.

Here is a simple format that can be used to document your policy and procedures.  This is just a starting point and you can add or remove whatever items make sense.

Policy Name of the policy.
Effective Date Date policy became effective.
Purpose Short description of why this policy has been created.
Affected Individuals or groups of people affected by the policy.
References Reference to other existing policies
Definitions Definitions of certain key words to provide context.
Procedures Process that should be followed.

To take this a step further, here is a sample policy for changing the "sa" password.

Policy Quarterly SQL Server "sa" password change
Effective Date 8/9/2007
Purpose The SQL Server "sa" password is used only as a last resort to access the database server.  To ensure that this password is secure and not used by unauthorized users the password will be changed on a quarterly basis.
Affected SQL Server database administrators
References Policy 56B - Use of SQL Server "sa" account

Policy 424A - Strong password policy

Definitions "sa" - the default SQL Server system administrator account
Procedure The password should be changed as follows:
  1. Changed on the first Monday of the following months (January, April, July, October)
  2. The lead DBA on duty that day is responsible for the change
  3. The new password must conform to our strong password policy (424A)
  4. The password change must be logged in our change management application
  5. The new password is locked in safe for emergency use only

As you can see from the sample policy this is not that difficult.  Although this example is pretty simple, if you document things in a step by step approach you should be able to document most procedures without much problem. 

You may also think that since you are the only one responsible for maintaining your systems that going through this process is not worth it.  Think back to a process that you ran a year ago and you know that you will need to run again.  Wouldn't be easier to just document the steps now and then revert back to the policy when you need to duplicate the steps?  Also, you need to think about the potential for more people coming on board and the impact this will have if your policies are not documented.  Also, if you document the policies now, when new people do come on board you can ensure that they follow what you have already outlined by just giving them the well documented procedures.

One common pitfall when documenting things is the assumption that other people know what you are talking about.  Based on this it is very easy to gloss over the details and just write some high level procedures.  Therefore it is helpful to have someone else review the process and even approve the process.  By having someone else read and understand what has been documented there are at least two people now that understand the policy as written.

Standardized Approach
Now that you have formalized some of your processes you can guarantee that the approach that is used is standardized as well.  This can really be for just about everything that affects SQL Server such as:

  • applying service packs
  • deploying database changes
  • adding logins and users
  • sysadmin level access
  • object naming standards
  • coding documentation standards
  • standard login passwords
  • use of standard logins
  • etc...

As you can see a lot of the things that you do on a day to day basis you already have a standard approach for how to address and tackle these issues. Take the time and formalize the approach and ensure that going forward everyone adheres to the same rules.

Next Step

  • Determine the documentation format you are going to use
  • Determine how this will be stored (Word, PDFs, database) and where it will be stored so everyone has read access
  • Start defining policies for your simple tasks before you take on the hard ones
  • If you are the only DBA start the process now, before more people come on board
  • Now that you have a framework start documenting


Last Updated: 2007-08-09


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, July 06, 2009 - 5:42:36 AM - isddarms Back To Top

I'm pretty much in the situation you described. I'm new to SQL Server, taking over as DBA in a shop that hasn't really had a DBA for a couple of years. Would you consider making your policies, or even a list of the policies, available. It would be a great help for me to know what things to consider.

Thanks


Learn more about SQL Server tools