Defining and Establishing SQL Server Policies and Procedures

By:   |   Updated: 2007-08-09   |   Comments (5)   |   Related: 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 Steps
  • 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2007-08-09

Comments For This Article




Monday, August 26, 2019 - 10:48:44 AM - Greg Robidoux Back To Top (82140)

Hi everyone,

here are some ideas for policies:

  • naming conventions: databases, objects, servers
  • process for releasing changes into production
  • limiting sysadmin access to production servers
  • limiting db owner access to production databases
  • documentation process for changes
  • patching SQL Server
  • patching Windows or Linux OS
  • new server configuration
  • password policies
  • auditing policies
  • backup and backup retention polices
  • use of linked server policies
  • restoring production databases to non-production servers

There are plenty more, but these are some ideas you could start with.

-Greg


Thursday, August 22, 2019 - 10:50:12 AM - [email protected] Back To Top (82122)

Hi Greg

I am writing to check if you were able to get something regarding the below.

Thanks for your help


Wednesday, August 14, 2019 - 9:50:23 AM - Greg Robidoux Back To Top (82059)

Hi Lizzy,

Let me see if I can come up with a list and I will update the tip.

Thanks
Greg


Wednesday, August 14, 2019 - 5:57:14 AM - Lizzy Mathebula Back To Top (82055)

Good Day

Thanks for the above informatio it was really helpful.

May I request the response to the below question from isddarms, I also find myself in the same situation and would like to find out where I should start with this policies.

Regards and thank you for your help

Lizzy


Monday, July 6, 2009 - 5:42:36 AM - isddarms Back To Top (3696)

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















get free sql tips
agree to terms