Defining and Establishing SQL Server Policies and Procedures

By: Greg Robidoux   |   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.

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

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

About the author

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